I have received a request to move a mysql database from the current database to a new server. The easiest way is to use phpMyAdmin to export the database. Or we can simply use the command mysqldump.

Howerver, I do not have phpMyAdmin access. And I get the following error when using the command "mysqldump".

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) when trying to connect

So I try to copy the databse in the directory /var/lib/mysql/your_database to the new server. In the new server, some tables are missing when using phpMyAdmin. But if I use command prompt to access to mysql, the tables can be found by using "show tables;". It is due to the tables different storage engines of the copied database: InnoDB and InnoDB.

InnoDB

For storage engine InnoDB, each table will generate two files in extensions .MYD and .MYI. For example of table `user`, you can find the files user.frm, user.MYD and user.MYI in the directory /var/lib/mysql/your_database. You can just copy the three files in the new server and the tables should work fine.

InnoDB

For storage engine InnoDB, only .frm files will be generated. It does not work if you just copy this file to the new server. Three more files are required:

  • /var/lib/mysql/ibdata1
  • /var/lib/mysql/ib_logfile0
  • /var/lib/mysql/ib_logfile1

Attention: DO NOT just replace these files to the new server if you have other databases on the new server, otherwise, the existing databases will be corrupted.

If you can access to phpMyAdmin, just use the simplest option "Export" to export the database.

If you can use the command mysqldump, just use type the command mysqldump.

If you can change the storage engine to InnoDB, just change it.

But all of them do not apply my case. The following steps are for this case:

Source Server

  • Backup the whole database /var/lib/mysql/your_database
  • Backup the files /var/lib/mysql/ib*

Middle Server (Cannot avoid to use mysqldump or phpMyAdmin sad)

  • Backup the files /var/lib/mysql/ib*
  • Move the source database /var/lib/mysql/your_database
  • Replace the files /var/lib/mysql/ib* by source files /var/lib/mysql/ib*
  • Restart MySQL
  • Use command mysqldump or access to phpMyAdmin to dump the database
  • Revert the files /var/lib/mysql/ib* 
  • Restart MySQL

Destination Server

  • Just create a new database and import the database exported in Middle Server.