Most of the times, you want to backup your databases to have backups to prevent loosing data. If you don’t backup your databases, a code bug or a server/disk failure could be a disaster. To help save you lots of time and frustration, it is strongly recommended that you take the precaution of regularly backing up your MySQL databases. You use mysqldump to back up your databases.

shell> mysqldump [options] > backup.sql

options – mysqldump options

backup.sql – the back up file

If the database size is very large it is a good idea to compress the output. To do that simply pipe the output to the gzip utility, and redirect it to a file as shown below:

mysqldump -u - root -p database_name | gzip > database_name.sql.gz

Authentication with username, password, and host

To get into the server, you must have the username and password for authentication. You use the -u for username and -p for password. By default –host is localhost so you can omit it when working with your localhost. Make sure you specify your host uri when you are not pointing to your localhost.

mysqldump -u username -p --host localhost database_name > backup.sql

You can even specify the username and password like this

mysqldump -uusername -ppassword -hlocalhost database_name > backup.sql

Dump all databases

To dump entire databases, do not name any tables following db_name, or use the --databases or --all-databases option.

mysqldump -u root -p --all-databases > all_databases.sql

Dump a single database

To backup a single MySQL database with one command you need to use the --databases option followed by the name of the database. You can also omit the –database option which is a more common way of doing this. Note that by using the --databases option, you will have the CREATE DATABASE command which is useful on restoring.

mysqldump -u root -p database_name > backup.sql
mysqldump -u root -p --databases database_name > backup.sql

Dump a single database with a timestamp

If you want to keep more than one backup in the same location, then you can add the current date to the backup filename:

mysqldump  database_name > database_name-$(date +%Y%m%d).sql

Dump multiple databases

To backup multiple MySQL databases with one command you need to use the --database option followed by the list of databases you want to backup. Each database name must be separated by space.  mysqldump  treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names.

mysqldump -u root -p --databases database_name_a database_name_b > backup_a_b.sql

Dump only some tables within a database

If you use the –databases then you have to use the –tables option to specify the tables.

mysqldump -u username -p database_name table_name1 table_name2 > backup.sql

// if --databases option is specified
mysqldump -u username -p --databases database_name --tables table_name1 table_name2 > backup.sql

Dump only some table rows within a database table

The –where option is applied to all tables not just one. There is only one –where option. You might be thinking to use the –where option for each table but that does not work. In case you configure this command with multiple –where options, only the last –where option will be evaluated.

mysqldump -u username -p database_name table_name --where="id<=100" > few_rows_backup.sql

Dump with store procedures

mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

• --routines – FALSE by default
• --triggers – TRUE by default

so in mysqldump command , add --routines. Include triggers for each dumped table in the output. This option is enabled by default; disable it with --skip-triggers. --routines includes stored routines (procedures and functions) for the dumped databases in the output. This option requires the global SELECT privilege.

mysqldump -u root -p --triggers --routines --databases database_name > backup.sql

Create a cron job to backup a database on your mac

1. Type crontab -e on your terminal to open your cron jobs
2. Add a crontab command

3. * * * * *  command to execute
   │ │ │ │ │
   │ │ │ │ └─── day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names; 7 is Sunday, the same as 0)
   │ │ │ └──────── month (1 - 12)
   │ │ └───────────── day of month (1 - 31)
   │ └────────────────── hour (0 - 23)
   └─────────────────────── min (0 - 59)

4. 0 * * * * /usr/local/mysql/bin/mysqldump -u root database_name > {location}/database_name-$(date +"%Y%m%d").sql

   Command to execute is the dump command which, in this case, is run every hour.

5. Delete file over 30 days old

   0 * * * * /Users/folaukaveinga/db_backups -type f -name "*.sql" -mtime +30 -delete

    

Restore MySQL Dump

You can restore a MySQL dump using the mysql command. 

mysql -u username -p  database_name < backup.sql

In some cases, you need to create a database to import into because you did not use the –databases option when creating the dumb.

mysql -u root -p -e "create database database_name";
mysql -u root -p database_name < backup.sql

Restore a single database

If you backed up all your databases using the -all-databases option and you want to restore a single database from a backup file which contains multiple databases use the --one-database option.

mysql -u username -p --one-database database_name < backup.sql

Export and Import a MySQL database in one command

Instead of creating a dump file from one database and then import the backup into another MySQL database you can use the following one-liner.

mysqldump -uLocalUsername -pLocalPassword --databases local_database_name | mysql -hRemoteHost -uRemoteUsername -pRemotePassword remote_database_name