You are your website editor, owner and now administrator. You want control over the whole process and you are migrating from a managed site. Or you are a versed user on unix-like environments and you are just willing to export your database. This is a simple post on how to export a MySQL database.
One thing you have to know before you accomplish this task is the following. PhpMyAdmin is not the best solution to export a MySQL database whatsoever for big DB’s. Don’t get scared though. PhpMyAdmin is a great tool to import and export databases for many sites, but it’s got its limitations. It is known and reported by the developers themselves it can miss behave with databases bigger than 50MB. So there are only two alternatives left. Grab a backup from your hosting provider. Or dig into the terminal. The first one is easy so anyone will know how to do this. Let’s do the second option.
SSH into the remote server. Once in, you will use a command called mysqldump. The dump command is an old one and a very well known for the old UNIX admins. Mysqldump basically extracts (dumps) the whole content from a database into a file. For more information read the documentation.
The command basically goes as:
myslqdump -u (username here) -p (databasenamehere) > outputilfe.sql
Before that make sure nothing is being written on this database. This may require locking the DB down or deviating the writes to another replica DB which will be the master when you are dumping the slave, if you happen to have a high volume site. And yes there is a solution for this if you read the MySQL manual so you can avoid a secondary DB.
So now you are finish the task: export a MySQL database. After that you will copy the database to the new host. Or you can also back it up in doing this and storing it wherever you may like. Mind the longest operation will not be exporting the database but importing it. Consider this if your database is big. For the low hundreds of MB databases this won’t take longer than a few minutes.