Restoring MySQL database

Restoring MySQL database from one server to another

I have been using MySQL for a really long time... since 1998 if I recall correctly.
It is among the most trustworthy 'workhorses' on my toolpack.

... It has failed me few times but only on CentOS after updating the MySQL-server package (major version upgrade)

This time something went wrong during updating and I was just too darn tired of fixing the mysql-server so I decided to use the good 'ol copy the datafiles technique. So I will just show how utterly simple it is (assumptions: no root ssh/sftp allowed on each server, user belongs to sudoers on each server, working ssh/sshd, mysql datadir is default /var/lib/mysql - pretty standard stuff :-) ):

Source server
CentOS 6.5 64bit with MySQL server version 5.1.73-3 (rpm -qa|grep mysql-server  -as I can't get the server to start)

Target laptop
Fedora 17 64bit with MySQL server version 5.5.32 (mysql --version)
(also worked when transferred to Ubuntu 12.04.4 LTS with MySQL 5.5.35)

The actual steps to restore (the console way)

On the source server (as a sudoer) just compress the data (assumption is that the mysql-server is not running at this time)

sudo tar -czvf ~/databasedump.tar.gz /var/lib/mysql/[databasename]

On the remote server (as a sudoer) fetch the compressed file using sftp, uncompress it to mysql datadir and set the ownerships OK

sftp [user_that_grabbed_the_database]@[remote_server]
get databasedump.tar.gz
exit
sudo tar -xvzf databasedump.tar.gz -C /
sudo chown -Rf mysql:mysql /var/lib/mysql/[databasename]/

That is it! 

So MySQL is kind of indestructible; You can just grab a copy of the datafiles from a 'broken' server and easily restore them on a remote server running different version of MySQL database.

(IMHO: 1) avoid root, be a sudoer, follow the best practices 2) take care of a proper MySQL backup (for example with mysqldump), don't play with the fire 3) This used to be a bit more painful before all Linux distros were UTF-8 so enjoy the modern days)