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)
Comments
Post a Comment