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

How to use mysqldump to backup MySQL database, I believe many inexperienced people are helpless about this, this article summarizes the causes and solutions of the problem, through this article I hope you can solve this problem.

I. Importance of data backup

At work, if you accidentally delete important files or directories, the result can be disastrous. I don't have to tell you what it's like to accidentally delete data that relates to important customers or key projects and can't be easily recreated. Unfortunately, data like this is ubiquitous in corporate environments, such as sales records, customer contact details, and so on.

Therefore, in the real world, regular database backup is a matter of great concern, otherwise it may bring disastrous consequences. When we use MySQL, there are many options for database backups. This article will show you how to use MySQL's mysqldump to back up databases.

Second, about mysqldump

MySQL itself provides a number of command-line tools, such as mysql tools that can be used to interact with MySQL database schema, data, users, and configuration variables, mysqladmin tools that can perform various administrative tasks, and mysqldump, described below. There are many others, but they are beyond the scope of this article. The tool mysqldump can be used to back up both database schemas and data; with it, we can not only make a blanket backup of all databases on a server, but we can also selectively back up a certain database and even certain tables in the database.

When installing MySQL on the server, modify the system path so that the global command line can access individual clients. Open a terminal window and execute the following command:

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

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

For more options, use mysqldump --help

Here is how to use the mysqldump program. For example, to back up all database schemas and data to the file backup092210.sql, you can execute 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 root privileges, so we need to enter the appropriate password at the prompt. Also, if you're practicing these commands on a development server, spend some time looking at the contents of the backup file. You see a series of SQL statements, first deleting tables and recreating them, then recreating related data. Also, since we backed up all databases in the example above, you'll also find that the backup file creates each database (if it doesn't already exist) and then switches to that database via the USE command, creating tables and data associated with that database.

To back up a single database, such as a database named wiki, into a file named wiki-backup092210.sql, we can use the following command:

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

Finally, to back up the database table named users, for example, 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 may want to back up only the schema of the database, or just the data of the database. To back up the database schema, you can pass the parameter--no-data to mysqldump as follows:

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

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

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

IV. How to backup automatically

For the previous example, it takes only a small number of keystrokes to execute the mysqldump command. However, there is still a lot of work to be done at work, and life becomes too monotonous if these orders are always followed. So we can try to automate these processes, and we can do that with the cron tool, which is found on all UNIX-like operating systems. 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/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 notice how the username root and password secret are passed to mysqldump here, i.e., after the options- u and- p, respectively. From a security perspective, we must set permissions correctly.

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

%>crontab -e

This opens the crontab file for the currently logged in user, or automatically creates a new one if it doesn't already exist. In this file, we can add the following to ensure that the backup script runs at 3 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. Here, the first five parameters correspond to the execution time of the script, which is minute, hour, day, month and day of the week. Therefore, to execute the script every Tuesday at 4:45am, use the parameter 45 4 * * 3.

After inserting the line shown above, save the file, and our task will start executing at the given time schedule. Be sure to check the corresponding catalog the next morning to see if everything is OK.

V. Other backup schemes

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

After reading the above, do you know how to use mysqldump to backup MySQL database? If you still want to learn more skills or want to know more related content, welcome to pay attention to the industry information channel, thank you for reading!

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.