Migrating MySQL databases from an old server to a new one
Posted by superuser
I had to transfer my old mysql databases from my old server to a new one. The source and destination servers runs different OSes but this is not a problem, the only requirement to succesfully transfer my dbs is to having setup the mysql server to the destination server. The following steps worked fine in my case. I do a complete backup of my Mysql databases, i transfer the backup file using scp (secure copy) and repristinate with a single command. All that you need is no more than three simple unix command lines.
First i create a complete backup of my mysql databases. There is no need to stop the mysql server to create this backup file. MysqlDump
blocks all databases during backup process. The following command will create the backup file backup.sql (provide your mysql root password when asked).
$ mysqldump -u root -p --all-databases > backup.sql
The second step consists in transfer the backup.sql file in my new server. Although you can use ftp or curl to do so, i prefer SecureCoPy. scp is a very cute unix command line which provides an easy and secure way to copy a single file or an entire directory from a host/server to another host/server node. From my old server i give
$ scp -pr fileORdirectory remote-user@remote-hostname-or-ip:directory/, where "fileORdirectory" is my fileORdir from the old server, "remote-user" is my system username to the remote server and "directory/" is the directory where the "fileORdirectory" will be moved on. The initial location is the home directory of the "remote-user", in the same way as you do with a simple ssh connection. Note that ":" is not a port symbol. Saying that now i'm sending my backup.sql
$ scp -pr backup.sql remote-user@my.newserver.lan:mysql_backups/
Last step: Enter to your new server ("remote-user" should be your username) and make "mysqldump" targeting your backup.sql file to complete the migration.
$ mysql -u root -p < backup.sql
Note that in this way also the mysql root password will be copied to the new server. So, if during the installation you had setup a different root mysql password in the new server, after the backup process this will be changed to the root mysql password of the old server.
