moving mysql databases using mysqldump & ssh

moving mysql databases using mysqldump & ssh

Moving MySQL Databases Using mysqldump

On old server:

Check the /etc/mysql/my.cnf file and make note of the address listed in bind-addresses.

On the new server:
When you install mysql, define a temporary password to root. This password will get overwritten during the transfer, after a restart of the mysql service.

Add a new interface eth0:x in /etc/network/interfaces with the ip address noted in the old server’s /etc/mysql/my.cnf file. LEAVE THIS INTERFACE DOWN UNTIL THE FINAL SWITCH.

Edit the /etc/mysql/my.cnf file

bind-addresses = <address of the new server> or, less specific, 0.0.0.0

Restart the service mysql. NOTE: reload doesn’t load the changes in my.cnf.

Service mysql restart

Use this command to move the databases:

ssh (your username)@(old-server’s FQDN or IP) “mysqldump -u (db-username, probably root) –all-databases > /(dirpath)/(filename)” | “mysql -u root -p (temp pw designated at mysql install on the new server) -h (ip address of new server) < /(dirpath)/(filename)”

NOTE: After the restoration of the databases on the new server, your current credentials will work until the mysql service is restarted.

If, for any reason,  you need to do a complete re-install of MySQL, use this procedure to remove MySQL completely from server:

service mysql stop #or mysqld
deluser mysql
delgroup mysql
killall -9 mysql
killall -9 mysqld
apt-get remove –purge mysql-server mysql-client mysql-common
apt-get autoremove
apt-get autoclean
rm -rf /var/lib/mysql

Then re-install:

apt-get install mysql-server

ssh-keygen -R (FQDN)

Matt Long

3/31/2015