Back up a MySQL database using mysqldump

In practical work, regular database backup is extremely important. When using MySQL, we have a lot of options for database backup. This article will introduce you to how to use MySQL mysqldump to back up the database.

I. Importance of Data Backup

At work, if an important file or directory is accidentally deleted, the result will be miserable. This is especially true when accidentally deleted data involves important customers or key projects that cannot be easily re-created. Unfortunately, such data is everywhere in the company environment, such as sales records, customer contact information, and so on.

Therefore, regular database backup is the most important thing in actual work. Otherwise, it may have disastrous consequences. When we use MySQL, there are many options for database backup. This article describes how to use mysqldump of MySQL to back up databases.

Ii. About mysqldump

MySQL itself provides many command line tools. For example, mysql can be used to interact with MySQL database modes, Data, users, and configuration variables, while MySQL admin can perform various management tasks, in addition, mysqldump will be introduced below. There are many more, but that is beyond the scope of this article. The mysqldump tool can be used to back up both the database mode and data. With it, we can not only perform carpet backup on all databases on a server, at the same time, we can also selectively back up a database or even some specified tables in the database.

When installing MySQL on the server, modify the system path so that the global command line can access various customer programs. Open the terminal window and run the following command:

　　

%> Mysqldump Usage: mysqldump [OPTIONS] database [tables] OR mysqldump [OPTIONS]

 

　　

-- Databases [OPTIONS] DB1 [DB2 DB3. ..] OR mysqldump [OPTIONS] -- all-databases [OPTIONS]

 

　　

For more options, use mysqldump -- help

 

The following describes how to use the mysqldump program. For example, to back up all the database modes and data to the file backup092210. SQL, run the following command:

　　

%> Mysqldump-u root-p -- all-databases> backup092210. SQL Enter password:

 

Here, in order to access all databases, we need a root permission, so we need to enter the corresponding password at the end of the prompt. In addition, if you are practicing these commands on the development server, you may wish to spend some time looking at the backup file content. Then you will see a series of SQL statements. First, the statements that are deleted from each table and re-created, and then the statements that re-create the relevant data. In addition, because we back up all databases in the above example, you will also find that the backup file will create each database (if it does not exist ), then, USE the USE command to switch to the database to create tables and data related to the database.

To back up a single database, for example, backing up a database named wiki to a file named wiki-backup092210. SQL, run the following command:

　　

%> Mysqldump-u root-p wiki> wiki-backup092210. SQL Enter password:

 

Finally, to back up a table named users in the database, for example, backing it up to a file named wikiusers-backup092210. SQL, we can use the following command:

　　

%> Mysqldump-u root-p wiki users> wikiusers-backup092210. SQL Enter password:

 

Iii. Adjust backup content

Sometimes, we only want to back up the database mode, or just want to back up the database data. To back up the database mode, you can pass the parameter -- no-data to mysqldump, as shown below:

　　

%> Mysqldump-u root-p -- no-data wiki> wiki-backup092210. SQL

 

To back up only the database data, you can use the -- no-create-info parameter to mysqldump, as shown below:

　　

%> Mysqldump-u root-p -- no-create-info wiki> wiki-backup092210. SQL

 

4. Automatic Backup

For the preceding example, the mysqldump command can be executed with only a few keys. However, there are still a lot of jobs to do, and if you always execute these commands, your life will become too monotonous. Therefore, we can try to automate these processes. At this time, we can use the cron tool to achieve our goal, which can be found on all UNIX-like operating systems. To automatically execute the backup task, we need to create a new file named correctly. For example, the nightly-backup.sh command is as follows:

　　

#! /Bin/sh mysqldump-uroot-plugin cret wiki>/home/backup/SQL/wiki-backup-'date + % m % d % Y'. SQL

 

When we execute this script, it backs up the wiki database to a file named after the backup time, such as a wiki-backup-092210. SQL. Note how the username root and password secret are passed to mysqldump, which are placed after option-u and-p respectively. From the security perspective, we must set the correct permissions.

Next, we will provide the script to cron by using crontab. Therefore, we can execute the following command:

　　

%> Crontab-e

 

This will open the crontab file of the current Login User. If the file does not exist, a new one will be created automatically. In this file, we can add the following content to ensure that the backup script is run at three o'clock A.M. every day:

　　

0 3 ***/home/backup/scripts/nightly-backup.sh

 

If you are unfamiliar with crontab syntax, the parameters here may be confusing. The first five parameters correspond to the script execution time, which are minute, hour, day, month, and day. Therefore, to execute the script at am every Tuesday, you can use the parameter 45 4 ** 3.

Insert the line shown above and save the file. Then, our task will start to be scheduled and executed according to the given time. Note that you must check the corresponding directory the next morning to see if everything is normal.

V. Other backup solutions

As mentioned above, mysqldump is only one of the many MySQL backup solutions. In addition, you can use the MySQL binary log file for Incremental backup, or use the MySQL master server to copy data to the slave server.

Vi. Summary

In this article, we have established a simple MySQL backup solution with readers. If you have not implemented any solutions before, try it now. It only takes a few minutes. I think this is a good decision.