Most of the web sites use some form of database from text files to MySQL, PostgreSQL, Oracle, MSSQL , Sqlite and others.

At some point as a webmaster you may have to change the server hosting a website and If you have to transfer a mysql database from a server to another you have a various options. Not all of them can be used on any servers and each has it's advantages and disadvantages.

This is a list of methods alog with a description and howto for each method as well as advices about when to use each method.

1. phpMyAdmin. For this example I used php phpMyAdmin 2.9.1. Older versions may be a little different.
   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. You can also pick the format of your export ( sql, csv, pdf, xml ...etc). 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 ( 1-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:

   span style="color: #ff0000;">"your_username" -p --lock-tables \\
   "your_database"

   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:

   span style="color: #ff0000;">"your_username"

   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

   and after the transfer finished, run this on the new server:

    
   mysql -u "your username" -p "your_database"

   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"

   This method works with very large databases.

3. other tools:
   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 using tools like ftp, scp, sftp, rsync. Before you use any of those tools 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. If you want to lock a table you will have to use the MySQL client to login to your MySQL server and then just type in :

   or if you want to lock all tables on the server:

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

   to release the read lock.
   This method also works with large databases, and it is faster then 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.