As a programmer or system admin, taking database backup is an inevitable task that we have to do. mysqldump is the de-facto tool for exporting data from MySQL server. It allows us to easily export databases or selected tables very easily. For instance, we can use the following command to take a backup of users table from my_db database.

mysqldump -uroot -proot my_db users > users.sql

In some cases we might need to export only a subset of the table, using a where condition. Here is how.

mysqldump -uroot -proot my_db users --where="id < 100 "> users.sql

If we need a bit more advanced solution, like to select the users who have made any orders from our (imaginary) website, here is the command.

mysqldump -uroot -p my_db -t --lock-tables=false users --where="id in( SELECT distinct user_id from orders ) "> users.sql

mysqldump creates a read lock on the tables we are dumping, to ensure a consitant backup. In the above command we are telling mysqldump to not lock the tables by passing --lock-tables=false as a parameter. But this can result in having incosistant backup of the database. So it's not recommended to run this command on a server with high volume of database operations. In those cases, it's better to take the backup from a read-only slave database.

Tags :
blog comments powered by Disqus