Tag: ubuntu mysql mysqldump
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