Three Ways to Migrate MySQL Database

1. phpMyAdmin:

Export your database: Go in phpMyAdmin, select your database and go to Export tab. You should see a set of options, by default both data and structure should be checked so your export will contain the database structure and the actual data in the database, for transferring the database you should use the SQL format. At the bottom of the page there should be a for that will let you specify the file name, and compression. If you select “none” the export will be dumped in your browser, this is not recommended because you would have to copy that sql and then paste it in phpMyAdmin on the new server and if the dump is large it may not work.
Import the database on the new server: Click on the SQL button in the left sidebar in phpMyAdmin. In the pop up window that just showed up go to “Import files” tab. In there you will be able to import the sql file you get from your first server. The problem with the phpMyAdmin approach is that most web server configurations limit the maximum size of files you upload or the maximum execution time of a php script or the maximum memory that a PHP script can use. So unless you have small dumps (under 10 MB) this method is not recommended.

2. Command line mysql client tools: if you have shell access to any of the servers ( ssh or telnet ) you can use the command line mysql client to either export (dump ) or import a mysql database . To dump the database use mysqldump:

mysqldump -u "your_username" -p —lock-tables \\
"your_database" > your_database_dump.sql

I added —lock-tables there so that it puts a read lock on all tables while running the dump process to make sure that no one can modify the databases and create inconsistencies in the dump. If you have more then one database that needs to be exported you can use the —databases option like this:

mysqldump -u "your_username" -p —lock-tables \\
—databases DB1 **[**DB2 DB3…**]** > your_database_dump.sql

If you want to export all of your databases you can just replace —databases DB1 [DB2 DB3…] with —all-databases

To import the databases on the new server you can try the phpMyAdmin method or if you have shell access (preferable ) you can use the mysql client. If you’re going to use the mysql client you will have to transfer the dump file to the server ( use ftp or sftp/scp ).You might want to compress the file before transferring it

    bzip2 your_database_dump.sql
    scp your_database_dump.sql.bz2 user@newserver:~

and after the transfer finished, run this on the new server: mysql -u “your username” -p "your_database" < database_dump.sql

Or if your dump includes a “CREATE DATABASE” statement ( usually when a file contains the dump of more then one database or if you exported you databases using —all-databases or —databases options ) then you can just do: mysql -u “your_username” -p < your_database_dump.sql

This method works with very large databases.

3. File transfer: The MySQL server stores database structure and data in regular files on disk. This means that if you can login on the server with privileges to access the folder where the databases are stored ( usually /var/lib/mysql) then you can just copy or transfer then to another server via ftp, scp, sftp, or rsync.

Before doing so, you have to make sure no one is writing to the databases that you want to transfer so you should put a read lock on them.

FLUSH TABLES WITH READ LOCK

Leave the mysql client running and then copy or transfer transfer the files. After the transfer finished, exit the mysql client or type:

    UNLOCK TABLES

To release the read lock.

This method also works with large databases, and it is faster than the previous method in this case MySQL server does not have to parse and process queries or recreate indexes because the whole data including indexes is transferred from the old server.