Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to back up and restore MySQL database". In daily operation, I believe many people have doubts about how to back up and restore MySQL database. Xiaobian consulted all kinds of information and sorted out simple and easy operation methods. I hope to help you answer the doubts about "how to back up and restore MySQL database"! Next, please follow the small series to learn together!

This tutorial describes how to back up and restore a MySQL or MariaDB database from the command line using the mysqldump utility. The backup file created by the mysqldump utility is basically a set of SQL statements that can be used to recreate the original database. If the database is not backed up, a software error or hard drive failure can be catastrophic. To help you save a lot of time and frustration, it is highly recommended that you take precautions and back up your MySQL database regularly.

You can also use the mysqldump utility to transfer a MySQL database to another MySQL server. Mysqldump command syntax, Before we talk about how to use the mysqldump command, let's review the basic syntax. The mysqldump utility expression takes the form:

mysqldump [options] > file.sql

options - mysqldump opti

file.sql -dump (backup) file

Backup a single MySQL database

To create a backup of a database named database_name using root and save it to a file named database_name.sql, you would run the following command:

mysqldump -u root -p database_name > database_name.sql

You will be prompted for the root password. Enter it, press Enter, and the dump process will begin. Depending on the size of the database, this process may take some time.

If you are logged in as the same user you used to perform the export, and that user does not require a password, you can omit the-u and-p options:

mysqldump database_name > database_name.sql

Backup multiple MySQL databases

To back up multiple MySQL databases with a single command, you need to use the--database option followed by a list of databases to back up. Each database name must be separated by spaces.

mysqldump -u root -p --database database_name_a database_name_b > databases_a_b.sql

The above command creates a dump file containing two databases.

To back up all MySQL databases, you would use the--all-databases option:

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

As in the previous example, the above command creates a dump file containing all the databases.

Backup all MySQL databases to a separate file

The mysqldump utility does not provide the option to back up all databases as separate files, but we can easily do this with a simple bash FOR loop:

for DB in $(mysql -e 'show databases' -s --skip-column-names); do

mysqldump $DB > "$DB.sql";

done

The command above creates a separate dump file for all databases using the database name as the file name.

Create a compressed MySQL database backup

If the database size is very large, compressing the output is a good idea. To do this, simply pipe the output to the gzip utility and redirect it to the file, as follows:

mysqldump database_name > | gzip > database_name.sql.gz

Create backups using timestamps

If you want to keep multiple backups in the same location, you need to add a date to the backup filename:

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

The above command creates a file with the following format database_name-20180617.sql

If you use cronjob to automate database backups, you can also delete any backups older than 30 days using the following command:

find /path/to/backups -type f -name "*.sql" -mtime +30 -delete

Of course, you'll need to adjust the command based on the backup location and file name. To learn more about the find command, check out how we find files in Linux using the command-line guide.

Recovery MySQL dump

You can recover MySQL dumps using mysql tool. In most cases, you need to create the database you want to import. If the database already exists, it needs to be deleted.

In the following example, the first command creates a database named database_name and imports 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 complete MySQL dump, if you backed up all databases using the-all-databases option, and to restore a single database from a backup file containing multiple databases, use the--one-database option, as shown in the figure:

mysql --one-database database_name < all_databases.sql

In exporting and importing MySQL databases in one command, rather than creating a dump file from one database and then importing the backup to another MySQL database, you can use the following single line:

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

The above command passes the output to the mysql client on the remote host and imports it into a database named remote_database_name. Ensure that the database already exists on the remote server before running the command.

At this point, the study of "how to backup and restore MySQL database" is over, hoping to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.