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…