Migrating from mysql 4.0 to mysql 5.0 in FreeBSD

Final decision has been made, upgrade to mysql 5.0 is a must.

But how to do that?

How about user data?

After doing it by myself I found it’s easy. I try to share my experience in sbs (step by step) form 🙂

Situation :

I have two server kongja and proxy :

Server kongja has mysql 4.0.27 installed

Server proxy don’t have mysql server installed.

I wanna try migration in server proxy before doing it in kongja.

Here’s the steps :

1. Install mysql40-server in proxy

cd /usr/ports/databases/mysql40-server/ && make install

2. Compress mysql directory in kongja

cd /var/db

tar cvzf mysql.tar.gz mysql

export this file (mysql.tar.gz) to proxy.

Continue reading

Backup and Restore many mysql database in one shot

Its my bad for not reading database manual that lead me for doing boring task .

Dump a few database from server and dumping it in my windows xp. one by one.

Perhaps someone who see this post can learn and not doing same mistake as me.

Command to backup one database :

backup database pendidikan :

edp# mysqldump -ualam -p pendidikan > pd.sql

backup database cms_study :

edp# mysqldump -ualam -p cms_study> cms1.sql

backup database cms_stmik :

Continue reading

Diff database structure with sqlyog

When requirement for information stored in database grow, old table structure need to adjust for flexibility.

Common task to do in  normal cycle :

1. Build new application in other place

2. Synchronize database ( tables)

3. Upload application.

Second step ( synchronize tables) often  painful when a lot of changes made at home and need to sync with server environment.

A handy tool to sync database table I often use is sqlyog.

For example I have two database :

db1 (at server)

db2 (at home)

table structure has changed.

In db1, table testing has this fields :

CREATE TABLE `testing` (
`id` tinyint(3) NOT NULL auto_increment,
 `name` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`id`)
 Continue reading 

Installing your favorite mysql version in FreeBSD

Ports offer more flexibility than package in general but when dealing with size I prefer using package 🙂

My common question when see this on freebsd ports page :

to get mysql support on php, I must install mysql-client-5.0.41 thats means I must install mysql5 server too if I decide to install mysql in my server, I prefer to install mysql4. Is it possible?

I’m luckly, freebsd ports offer flexible options, simple trick to use mysql 4.0.27 with php4 is installing mysql4 server first.

after installing mysql server version 4 then php4-mysql will use mysql4-client automatically.

with this option some options for favorit mysql version available, depend on you.

-bash-2.05b$ cd /usr/ports/databases/
-bash-2.05b$ ls | grep mysql

Continue reading

Some mysql sql basic command I must remember

I use phpmyadmin in daily environment, do I still know  sql command?

My sql file too large, it’s hard to import using phpmyadmin, please help me

Some question around mysql from my student, my false make them spoil using phpmyadmin all day long 🙂

Here’s my answers :

1. Command to create database

create database databasename

2. Delete database

drop database databasename

3. Import database

mysql -uusername -ppassword databasename < databasename.sql

4. Export database

mysqldump -uusername -ppassword databasename > databasename.sql

5. Pick database and choose one to manage

use databasename

6. List all table in database

show tables

 Illustration in mysql command line :

Continue reading