Migrate all mysql databases to new server!

This is more for me as a reminder.


I had to move my database for projects i had done on my Model B Pi and move them to my new PI 2 B.

Found this super command!!!

mysqldump -u root -pPASSWORD --all-databases | sshUSER@NEW.HOST.COM 'cat - | mysql -u root -pPASSWORD'

Such an amazing command you run this on the OLD server.

So change the first password for your current root mysql password  change USER in the SSH part to your root user on the new system and change the @ newhost to the ip/domain for the new server and then the last part change the password to the new root password.

ONE MAJOR THING!

the moment you do this tho it does change the mysql root password to the same as your old one!

Boom. This ran surprisingly quickly for me, and you can of course just as easily use this method to transfer a single database.

Three gotchas:

• If the link dies, you need to start again, so don’t do this over a flakey connection, and I suggest you run the command in a screen if the first server isn’t localhost.
• You need to restart the database server on the target machine for the new users and privileges to come into effect.
• On Debian, you may see an error along the lines of:
  

  Got error: 1045: Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES) when trying to connect

  Fix this by executing the command:
  

  GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'THEPASSWORD' WITH GRANT OPTION;

  Where THEPASSWORD is the password found in /etc/mysql/debian.cnf

One final note: this will only work if both servers are the same major version number. 

also you can change ----all-databases  to --database 

this will allow you to move one or more databases!

found @ https://www.marcus-povey.co.uk/2013/03/25/moving-a-mysql-database-between-servers-using-a-single-ssh-command/