This short guide will help you move all users, databases, tables, etc from server1 (current server) to server2 (new server).
Stop the mysql service on both servers:
sudo /etc/init.d/mysql stop
Setup a temporary directory in the home folder of your user on server2. (This is because we don’t have root access remotely.)
mkdir ~/varlibmysql
On server1:
sudo rsync -axv –progress –stats /var/lib/mysql/ user@server2:~/varlibmysql/
On server2:
sudo rsync -axv –progress –stats ~/varlibmysql/ /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/
At this point if you try to start mysql, you will most likely see the following error:
error: ‘Access denied for user ‘debian-sys-maint’@'localhost’ (using password: YES)’
To fix this (on server2):
cd /etc/mysql
sudo mv debian.cnf debian.cnf.backup (creates a backup of your original)
On server1:
sudo cat /etc/mysql/debian.cnf
(highlight and copy the contents of this file to your clipboard)
On server2:
sudo vim /etc/mysql/debian.cnf
press i on the keyboard to begin editing
paste clipboard into file
press Esc, type :wq and press Enter
Start the SQL service on server2:
sudo /etc/init.d/mysql start
And you’re all set…
No Comments on "Moving MySQL to a new server (Ubuntu Server 8.10)"