How to do a MySQL backup and restore using Terminal?

Question

How to do a backup and restore a table in MySQL using Terminal?

1. How to backup a table with only certain data, e.g., data for certain months, data for certain companies etc.?
2. How to compress your backup so that it is easy to transport from one server to another?
3. How do I restore the table on another system/server?

Answer 1

Here are a few things that I have learned so far.

The Backups

To backup the complete table and have the output in .sql format

mysqldump -h localhost -u root -p schema_name table_name > table_name_backup.sql

To backup the complete table and have the output in .sql.gz format - Pipe with gzip - this will compress the output to a good extent. Really helps if you have to transport a backup file from one server and restore it on another server

mysqldump -h localhost -u root -p schema_name table_name | gzip > table_name_backup.sql.gz

A word about skipping locks in the dump file: While doing backup and archival jobs, if the application continues to read from and write to the mysql database, you must use --skip-add-locks option with mysqldump. This will exclude the code in the file dumped by mysqldump.

mysqldump -h localhost -u root -p --skip-add-locks my_schema my_table | gzip > my_table.sql.gz

Especially when you have monthly or weekly data archival processes, you will need to archive data for a certain period of time: just add a where clause like this:

mysqldump -h localhost -u root -p my_schema my_table --where="creation_date between 
'2015-10-01' and '2015-10-30'" | gzip > my_table_backup.sql.gz

What if I have multiple tables?

mysqldump -h localhost -u root -p my_schema my_table1 my_table2 my_table3 | gzip > my_tables_backup.sql.gz

What about the complete database schema?

mysqldump -h localhost -u root -p my_schema | gzip > my_schema.sql.gz

The Restores

The first time I took a backup, I couldn't locate the file but I knew the filename. For tracing the path of a file, if you know the filename use readlink -f name_of_the_file

readlink -f name_of_the_file

This will give you the exact location of the file.

A simple gunzip or zcat will do your job. I prefer zcat but you can use gunzip -c also to do the same job.

zcat /home/username/backups/my_table_backup.sql.gz | 
mysql -h localhost -u root -p my_schema

Please keep in mind that you would have to have the same table schema set up wherever you are restoring the dump file, unless you have the create table definition included within the dump file. Yes, you can do that - you can also include schema definition too.

Sometimes, the --where clause used in the mysqldump does not guarantee precision. e.g., when I loaded a table which had 30 million records, I found more than 1000 records not fulfilling the where clause I used while dumping the file. If anyone has any idea on this, please add.