Mysql – How to move a database from one server to another

backupmigrationMySQLmysqldump

How can I move MySQL tables from one physical server to another?

Such as this exact scenario:
I have a MySQL server that uses innodb table and is about 20GB size.

I want to move it to a new server, what's the most efficient way to do this?

Best Answer

My favorite way is to pipe a sqldump command to a sql command. You can do all databases or a specific one. So, for instance,

mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserverpassword

You can do all databases with

mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver

The only problem is when the database is too big and the pipe collapses. In that case, you can do table by table or any of the other methods mentioned below.

Related Solutions

MySQL – Fastest Way to Move Database Between Servers

If you are changing versions, DO NOT MOVE THE mysql SCHEMA.

Why should you not move the mysql folder? It has to do with the authentication privileges.

The number of columns in mysql.user is different from version to version

If you run desc mysql.user

You will see 31 rows for MySQL 4.1
You will see 37 rows for MySQL 5.0
You will see 39 rows for MySQL 5.1
You will see 42 rows for MySQL 5.5

I wrote about this before

It is OK to move everything else. On the new machine that has MySQL 5.5.24, do this:

mv /var/lib/mysql /var/lib/mysql/mysql55
mkdir /var/lib/mysql
<scp or rsync /var/lib/mysql of MySQL 5.1.41 over to /var/lib.mysql of MySQL 5.5.24>
rm -f /var/lib/mysql/mysql/*
cp /var/lib/mysql/mysql55/* /var/lib/mysql/mysql/*
chown -R mysql:mysql /var/lib/mysql
service mysql start

So, the question remains:

How do you move the User Privileges in the old MySQL 5.1.41 to MySQL 5.5.24 ???

There are two ways to do this starting on the MySQL 5.1.41 machine:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Either way, move MySQLUserGrants.sql over to the MySQL 5.5.24 machine and execute the script

I wrote about this before : importing myisam 5.0 database into a 5.5 innodb server

Mysql – What’s the fastest way to replicate a MySQL database from one machine to another

I would personally do it in one step with a pipe using mysqldump like so

mysqldump -u user_source -p --opt database_name table_name | mysql -u user_target -p --host=target_db_ip -C target_database

but if mysqldump is not allowed just save the dump somewhere you can access it then use wget to move it to the other server and unpack there using mysql. I normally use this command because it doesn't require me to stop the database and is a single command with no files rather than a process where I have to manage where the data files are.