MySQL 5: Transferring database from MySQL 3.23.XX

Earlier, when I wrote about upgrading to MySQL 5, I didn't transfer any databases from MySQL 3.23.56 to MySQL 5 because I was going to use the new MySQL 5 installation as a client and load all old tables to MySQL 5 server on a separate machine. Today I needed to transfer some tables from MySQL 3.23.56 to new MySQL 5 (on the same machine) so here I am writing this post.

First, I copied my database directory

cp -p -r /drive2/oldsdd4/mysql/mydb /var/lib/mysql/

Once the MySQL database directory has been copied, you may need to fix permissions (see below).

Once the database directory has been moved, the following command can be used to dump the contents of the database.

mysqldump -uroot -p"mypass" mydb > mydb.sql

To load data back, use the following

mysqlimport -uroot -p"mypass" mydb mydb.sql

Fixing Permissions
I was getting the following error:

PHP Fatal error: Table 'mytable' is read only! query: INSERT INTO mytable (s, h, t) values('MySecretSession', '192.168.0.1', 1132694778) in /var/www/html/domain.com/includes/database.mysql.inc on line 120

I used the following to fix the error

chmod -R u=rwx,g=rwx,o=rx /var/lib/mysql/mydb
chown -R mysql:mysql /var/lib/mysql/mydb

You can use the following to view the status of the table

SHOW TABLE STATUS LIKE 'sessions' \G

Remember to make a backup :)

Good luck!

---

Also see: Upgrading MySQL,
upgrading tables from 4.0, upgrading tables from 3.23
Technorati: MySQL

---

To buy: MYSQL 5 Definitve Guide by Michael Kofler