Three different methods to move your MySQL database
When blogging you will face different kinds of issues, be it change of your hosting company, duplicating your blog database on your local computer to do testing and development. So questions arise on how to copy/move your database? Copying or moving blog files are very easy, all you need is to connect to your FTP and download the whole public_html or htdocs or www folder and that’s it! But database can be a little bit confusing for you…
So here’s the three ways to copy/move your MySQL databases:
- PhpMyAdmin - probably the easiest way. Most of the hosting companies offer PhpMyAdmin already pre-installed on their servers. You can find it somewhere in your control panel. You will need select the appropriate database and click on the Export tab. You can choose between SQL, LaTeX, PDF, Excel, Word, CSV, XML formats, choose if you want the structure only or the data within the database to export, choose your compression method. PhpMyAdmin will then provide you with SQL commands if you did not choose compression, or let you download the database in compressed format.
- If you have SSH,telnet or can login directly to the server then you can use mysqldump feature. When doing this, it’s better if you lock the tables so that no changes will be done while you are doing export.mysqldump -u “your_username” -p –lock-tables “your_database” > your_database_dump.sqlif you want to export multiple databases use:mysqldump -u “your_username” -p –lock-tables –databases DB1 [DB2 DB3...] > your_database_dump.sql
This option, mysqldump, works well with larger databases. So if you have been blogging for years now, this way could be a faster way of exporting your databases. But bear in mind that it’s slightly technical then the others.
- 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 :LOCK TABLE table_name READ[, table_name2 READ, ...]or if you want to lock all tables on the server: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 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.
There are other Administration GUI tools also which you can use for export/import MySQL databases. These are:
This article was partially copied from PatchLog’s methods to move a MySQL database
Comments
3 Responses to “Three different methods to move your MySQL database”
Leave a Reply
You must be logged in to post a comment.
About
Welcome to Arstan's personal blog. This blog includes tips on Linux, Apache, MySQL, Shell Scripts. Hardware/software and internet technologies reviews and updates.
Hi Arstan. I’m glad you found my article useful, but I think you should make it more clear that you have copied my article. And if you do add a link in the end the link text should have the name of the article in it so that at least I get some seo from this
Hello Mihai,
thanks for your feedback, I changed the link text and anchor text. I hope this will boost your SEO!
[...] Three different methods to move your MySQL database - A quick info post from Arstan Jusupov. This is something i didnt cover in my post ‘Moving your blog to a different directory, domain name or host‘ so it should definately help anyone who needs to move their blog to another domain. [...]