Easy methods to Again Up and Restore MySQL Databases with Mysqldump

This tutorial explains how one can backup and restore MySQL or MariaDB databases from the command line utilizing the mysqldump utility.

The backup information created by the mysqldump utility are mainly a set of SQL statements that can be utilized to recreate the unique database. The mysqldump command may generate information in CSV and XML format.

You too can use the mysqldump utility to switch your MySQL database to a different MySQL server.

In the event you do not backup your databases, a software program bug or a hard-drive failure could possibly be disastrous. To assist prevent a lot of time and frustration, it’s strongly really helpful that you simply take the precaution of repeatedly backing up your MySQL databases.

Mysqldump Command Syntax #

Earlier than going into how one can use the mysqldump command, let’s begin by reviewing the essential syntax.

The mysqldump utility expressions take the next kind:

mysqldump [options] > file.sql

To make use of the mysqldump command the MySQL server have to be accessible and working.

Backup a Single MySQL Database #

The commonest use case of the mysqldump instrument is to backup a single database.

For instance, to create a backup of the database named database_name utilizing the consumer root and put it aside to a file named database_name.sql you’ll run the next command:

mysqldump -u root -p database_name > database_name.sql

You may be prompted to enter the basis password. After profitable authentication, the dump course of will begin. Relying on the database measurement, the method can take a while.

If you’re logged in as the identical consumer that you’re utilizing to carry out the export and that consumer doesn’t require a password, you possibly can omit the -u and -p choices:

mysqldump database_name > database_name.sql

Backup A number of MySQL Databases #

To backup a number of MySQL databases with one command it’s worthwhile to use the –database choice adopted by the checklist of databases you need to backup. Every database title have to be separated by area.

mysqldump -u root -p –databases database_name_a database_name_b > databases_a_b.sql

The command above will create a dump file containing each databases.

Backup All MySQL Databases #

Use the –all-databases choice to again up all of the MySQL databases:

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

Similar as with the earlier instance the command above will create a single dump file containing all of the databases.

Backup all MySQL databases to separate information #

The mysqldump utility does not present an choice to backup all databases to separate information however we simply obtain that with a easy bash FOR loop:

for DB in $(mysql -e ‘present databases’ -s –skip-column-names); do
mysqldump $DB > “$DB.sql”;
performed

The command above will create a separate dump file for every database utilizing the database title because the filename.

Create a Compressed MySQL Database Backup #

If the database measurement could be very giant it’s a good suggestion to compress the output. To do this merely pipe the output to the gzip utility, and redirect it to a file as proven beneath:

mysqldump database_name | gzip > database_name.sql.gz

Create a Backup with Timestamp #

If you wish to maintain a couple of backup in the identical location, then you possibly can add the present date to the backup filename:

mysqldump database_name > database_name-$(date +%Ypercentmpercentd).sql

The command above will create a file with the next format database_name-20180617.sql

Restoring a MySQL dump #

You’ll be able to restore a MySQL dump utilizing the mysql instrument. The command basic syntax is as follows:

mysqld database_name < file.sql

Generally you will have to create a database to import into. If the database already exists, first it’s worthwhile to delete it.

Within the following instance the primary command will create a database named database_name after which it should import the dump database_name.sql into it:

mysql -u root -p -e “create database database_name”;mysql -u root -p database_name < database_name.sql

Restore a Single MySQL Database from a Full MySQL Dump #

In the event you backed up all of your databases utilizing the -all-databases choice and also you need to restore a single database from a backup file which incorporates a number of databases use the –one-database choice as proven beneath:

mysql –one-database database_name < all_databases.sql

Export and Import a MySQL Database in One Command #

As an alternative of making a dump file from one database after which import the backup into one other MySQL database you should utilize the next one-liner:

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

The command above will pipe the output to a mysql consumer on the distant host and it’ll import it right into a database named remote_database_name. Earlier than working the command, be certain the database already exists on the distant server.

Automate Backups with Cron #

Automating the method of backing up the databases is so simple as making a cron job what is going to run the mysqldump command at specified time.

To arrange automated backups of a MySQL database utilizing cronjob, comply with the steps beneath:

  1. Create a file named .my.cnf in your consumer residence listing:

    sudo nano ~/.my.cnf

    Copy and paste the next textual content into the .my.cnf file.

    [client]
    consumer = dbuser
    password = dbpasswd

    Don’t forget to interchange dbuser and dbpasswdwith the database consumer and consumer’s password.

  2. Limit permissions of the credentials file in order that solely your consumer has entry to it:

    chmod 600 ~/.my.cnf

  3. Create a listing to retailer the backups:

    mkdir ~/db_backups

  4. Open your consumer crontab file:

    crontab -e

    Add the next cron job that can create a backup of a database title mydb every single day at 3am:

    zero 3 * * * /usr/bin/mysqldump -u dbuser mydb > /residence/username/db_backups/mydb-$(date +%Ypercentmpercentd).sql

    Don’t forget to interchange username together with your precise consumer title.

You too can create one other cronjob to delete any backups older than 30 days:

discover /path/to/backups -type f -name “*.sql” -mtime +30 -delete

In fact, it’s worthwhile to modify the command in accordance with your backup location and file names. To be taught extra concerning the discover command test our Easy methods to Discover Recordsdata in Linux Utilizing the Command Line information.

Conclusion #

This tutorial covers solely the fundamentals, however it needs to be a very good beginning for anybody who desires to discover ways to create and restore MySQL databases from the command line utilizing the mysqldump utility.

If you wish to be taught extra about working with MySQL from the command line, check out our Easy methods to handle MySQL consumer accounts and databases information.

You too can test the tutorial about how one can reset a MySQL root password in case you’ve forgotten it.

When you have any questions or suggestions, be happy to depart a remark.

Supply

Germany Devoted Server

Leave a Reply