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.