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

Today, I will talk to you about how to use mysqldump to back up MySQL database, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

I. the importance of data backup

At work, if you accidentally delete important files or directories, the result will be miserable. Especially when the mistakenly deleted data involves important customers or key projects, and the data cannot be easily recreated, you can imagine what it feels like. Unfortunately, data like this can be found everywhere in the corporate environment, such as sales records, customer contact information, and so on.

Therefore, in real work, regular backup of the database is the most important thing to pay attention to, otherwise it may bring disastrous consequences. When we use MySQL, there are many options for database backup. This article will show readers how to use MySQL's mysqldump to back up a database.

II. About mysqldump

MySQL itself provides many command-line tools, such as the mysql tool that can be used to interact with MySQL's database schema, data, users, and configuration variables, while the mysqladmin tool can perform a variety of administrative tasks, as well as mysqldump, which will be described below. There are many more, but that is beyond the scope of discussion of comrades in Shanghai in this article. The tool mysqldump can be used to back up both database schema and data; with it, we can not only back up all the databases on a server, but also optionally 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 individual clients. Open the terminal window and execute the following command:

% > mysqldumpUsage: mysqldump [options] databasetables] ORmysqldump [options]

Databases[OPTIONS] DB1[DB2DB3...] ORmysqldump[OPTIONS] all-databases [OPTIONS]

Formoreoptions,usemysqldumphelp

Here is a way to use the mysqldump program. For example, to back up all database schemas and data to the file backup092210.sql, execute the command as follows:

% > mysqldump-uroot-pall-databases > backup092210.sqlEnterpassword:

Here, in order to be able to access all databases, we need a root permission, so here we need to enter the appropriate password at the prompt. In addition, if you are practicing these commands on a development server, take some time to look at the contents of the backup files. At this point you will see a series of SQL statements, first the statements that are recreated after deleting each table, and then the statements that recreate the relevant data. In addition, since we backed up all the databases in the above example, you will also find that the backup file will create each database (if it does not already exist), and then switch to that database with the USE command to create tables and data related to that database.

To back up a single database, for example, to back up a database named wiki to a file named wiki-backup092210.sql, we can use the following command:

% > mysqldump-uroot-pwiki > wiki-backup092210.sqlEnterpassword:

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

% > mysqldump-uroot-pwikiusers > wikiusers-backup092210.sqlEnterpassword:

How to back up MySQL Database with mysqldump

Third, adjust the backup content

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

> mysqldump-uroot-pno-datawiki > wiki-backup092210.sql

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

> mysqldump-uroot-pno-create-infowiki > wiki-backup092210.sql

4. How to make backup automatically

For the previous example, only a small number of keystrokes are needed to execute the mysqldump command. However, there is still a lot of work to be done at work, and life will become too monotonous if you keep carrying out these orders. Therefore, we can try to automate these processes, and we can achieve our goal with the help of the cron tool, which can be found on all UNIX-like operating systems. In order to automate the backup task, we need to create a new file with the correct name. For example, nightly-backup.sh, the specific command is as follows:

#! / bin/shmysqldump-uroot-psecretwiki > / 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 according to the backup time *, such as wiki-backup-092210.sql. You need to notice how the user name root and password secret here are passed to mysqldump, putting them after the options-u and-p, respectively. From a security point of view, we must set the right permissions.

Next, we provide the script to cron by using crontab. To do this, we can execute the command as follows:

This will open the crontab file for the currently logged-in user, and maxman will automatically create a new one if the file does not exist. In this file, we can add the following to ensure that the backup script is run at 3 a.m. Every day:

03***/home/backup/scripts/nightly-backup.sh

If you are new to the syntax of crontab, the parameters here may confuse you. Here, the first five parameters correspond to the time of execution of the script, which in turn are minutes, hours, days, months, and days of the week. Therefore, if you want to execute the script on the 4:45am every Tuesday, you can use the parameter 454room3.

After inserting the line shown above, save the file, and our task will begin to be scheduled for execution at a given time. It is important to note that the next morning be sure to check the appropriate directory to see if everything is all right.

5. Other backup schemes

As mentioned earlier in this article, mysqldump is just one of many backup solutions for MySQL. In addition, you can use MySQL's binary log files for incremental backups, or use to copy data from the MySQL master server to the slave server.

After reading the above, do you have any further understanding of how to back up the MySQL database using mysqldump? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.