Backup MySQL database with mysqldump

In real life, backing up the database on a regular basis is an extremely important task. When we use MySQL, there is a lot of choice in database backup, and this article will show readers how to use MySQL's mysqldump to back up the database.

First, the importance of data backup

At work, if you accidentally delete an important document or directory, then the result will be miserable. Especially when the data that is accidentally deleted involves important customers or key projects, and the data can't be recreated easily, it doesn't have to be what I say you can imagine. Unfortunately, data like this is ubiquitous in the corporate environment, such as sales records, customer contact information, and so on.

So, in real life, backing up the database regularly is a matter of great concern, otherwise it can have disastrous consequences. When we use MySQL, there is a lot of room for choice in database backup. This article will show readers how to use MySQL's mysqldump to back up the database.

Ii. about Mysqldump

MySQL itself offers a number of command-line tools, such as MySQL tools that can be used to interact with MySQL's database schema, data, users, and configuration variables, while the Mysqladmin tool can perform various administrative tasks, as well as the mysqldump described below. There's a lot more, but that's beyond the scope of this article. Tool mysqldump can be used both to back up the database schema and to back up the data, using it to not only make a blanket backup of all the databases on a single server, but also to selectively back up a database or even some of the tables specified in the database.

When you install MySQL on the server, you should modify the system path so that the global command line can access the individual client programs. Open the terminal window and execute 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 method of using Mysqldump program is given here. For example, to back up all database schemas and data to file Backup092210.sql, you can perform the following command:

　　

%>mysqldump-u root-p--all-databases > Backup092210.sql Enter Password:

Here, in order to be able to access all databases, we need a root permission, so here we need to enter the corresponding password after the prompt. Also, if you are practicing these commands on a development server, take some time to look at the contents of the backup file. You'll see a series of SQL statements, first the statements that were recreated after the tables were deleted, and then the statements that recreated the relevant data. In addition, because we have backed up all the databases in the example above, you will also find that the backup file will create each database (if it does not yet exist) and then switch to the database by using the use command to create the tables and data associated with the database.

To back up a single database, such as to back up a database named Wiki to a file named Wiki-backup092210.sql, we can use the command shown below:

　　

%>mysqldump-u root-p wiki > wiki-backup092210.sql Enter password:

Finally, to back up a table named users in the database, for example, to back it up to a file named Wikiusers-backup092210.sql, we can use the command shown below:

　　

%>mysqldump-u root-p wiki users > Wikiusers-backup092210.sql Enter Password:

Third, adjust backup content

Sometimes, we may just want to back up the schema of the database, or just want to back up the database data. To back up the database schema, you can pass parameter--no-data to mysqldump as follows:

　　

%>mysqldump-u root-p--no-data wiki > Wiki-backup092210.sql

To back up only the data in the database, you can use parameter--no-create-info to Mysqldump, as follows:

　　

%>mysqldump-u root-p--no-create-info wiki > Wiki-backup092210.sql

Iv. How to Automate backups

For the previous example, the mysqldump command is executed with a small number of keystrokes. However, there are still a lot of work to do, and if you keep executing these commands, life will be too monotonous. So we can try to automate these processes by using cron tools to achieve our goals, which are found on all UNIX-like operating systems. In order to automate the backup task, we need to create a new properly named file. For example nightly-backup.sh, the specific command looks like this:

　　

#!/bin/sh Mysqldump-uroot-psecret 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 Wiki-backup-092210.sql. You need to be aware of how the username root and password secret are passed to mysqldump, which are placed behind the options-U and-P respectively. From a security point of view, we have to do the right set of permissions.

Next, we'll give this script to cron by using crontab. To do this, we can execute the following command:

　　

%>crontab-e

This opens the Crontab file for the currently logged-on user and automatically creates a new one if the file does not exist. In this file, we can add the following to make sure that the backup script is run 3 o'clock in the morning every day:

　　

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

If you are unfamiliar with Crontab's syntax, the parameters here may leave you scratching your head. The first five parameters here correspond to the time that the script was executed, in minutes, hours, days, months, and weeks. Therefore, to execute the script every Tuesday 4:45am, you can use the parameter 45 4 * * 3.

Once you have inserted a line of content shown above, save the file, and our task begins to schedule execution at a given time. Note that the next morning must check the appropriate directory to see if everything is OK.

V. Other backup scenarios

As mentioned earlier in this article, Mysqldump is just one of the many backup scenarios for MySQL. In addition, you can use the MySQL binary log file for incremental backups, or copy data from the MySQL master server to the from server.

Vi. Summary

In this article, we have built a simple MySQL backup solution with our readers. If you have not implemented any solution before, you may wish to try it immediately. The important thing is that it only takes you a few minutes, and I think it's very well drawn.