A brief introduction to MySQL database backup basics

Two primary methods for backing up a database are to use the Mysqldump program or directly copy the database files (such as CP, Cpio, or tar, etc.). Each method has its advantages and disadvantages:

Mysqldump is working with the MySQL server. The direct copy method is performed outside the server, and you must take steps to ensure that no client is modifying the table you are copying. The same problem can occur if you want to back up the database with a file system backup: If the database table is modified during the file system backup, the table file that is backed up is not in a state where the subject is inconsistent, and the subsequent recovery table will lose its meaning. The difference between a file system backup and a direct copy file is that you have complete control over the backup process, so you can take steps to ensure that the server keeps the table undisturbed.

Mysqldump are slower than direct copies.

Mysqldump generates text files that can be ported to other machines, even on machines with different hardware structures. Direct copy files cannot be ported to other machines unless you are copying the tables using the MyISAM storage format. ISAM tables can only be copied on machines with similar hardware structures. The MyISAM table storage format introduced in MySQL 3.23 solves the problem because the format is machine-independent, so direct copy files can be ported to machines with different hardware structures. As long as two conditions are met: The other machine must also run MySQL 3.23 or later, and the file must be represented in MYISAM format, not ISAM format.

Regardless of which backup method you use, if you need to restore the database, there are several principles that should be adhered to to ensure the best results:

Perform backups on a regular basis. Set up a plan and keep it strictly.

Have the server perform an update log. When you need to recover data after a crash, the update log will help you. After you use the backup file to restore the data to the state of the backup, you can use the query in the update log to reuse the subsequent modifications, which restores the tables in the database to the state when the crash occurred.

In the term of file system backup, the database backup file represents full dump, while the update log represents progressive dumping (incremental dump).

Use a unified and understandable backup file naming mechanism. Like Backup1, buckup2, etc. are not particularly meaningful. When implementing your recovery, you'll waste time figuring out what's in the file. You may find it useful to use database names and dates to form a backup file name. For example:

The code is as follows	Copy Code
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02 %mysqldump Menagerie >/usr/archives/mysql/menagerie.1999-10-02

You may want to compress them after a backup is generated. Backups are generally very large! You also need to have your backup files expire to prevent them from filling up your disk, just as you would have your log file expire.

Back up your backup files with a file system backup. If you encounter a complete crash that not only clears your data directory, but also clears the disk drive that contains your database backup, you will be in real trouble. Also, back up your update log.

Put your backup files on a different file system than you would for your database. This reduces the likelihood of filling up a file system that contains a data directory because of backup generation.

The technology used to create the backup is also useful for copying the database to another machine. Most commonly, a database is moved to a server running on another host, but you can also transfer data to another server on the same host.

1 using mysqldump to back up and copy databases

When you use the Mysqldumo program to generate a database backup file, the file contents contain the Create statement that creates the table being dumped and the INSERT statement that contains the row data in the table, by default. In other words, the output produced by Mysqldump can later be used as MySQL input to rebuild the database.

You can dump the entire database into a separate text file, as follows:

The code is as follows	Copy Code
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02

The beginning of the output file looks like this:

　　

code is as follows

copy code

# MySQL Dump 6.0 # # Host:localhost database:samp_db #--------------------------------------- # Server Version 3.23.2-alpha-log # # Table structure for table ' absence ' # CREATE TABLE Absence ( student_id Int (a) unsigned DEFAULT ' 0 ' not NULL, Date Date DEFAULT ' 0000-00-00 ' not NULL, PRIMARY KEY (student_id,date) ); # # Dumping data for table ' absence ' # INSERT into Absence VALUES (3, ' 1999-09-03 '); INSERT into Absence VALUES (5, ' 1999-09-03 '); INSERT into Absence VALUES (10, ' 1999-09-08 '); ......

The rest of the file consists of more insert and CREATE TABLE statements.

If you want to compress the backup, use a command similar to the following:

The code is as follows	Copy Code
%mysqldump samp_db | Gzip >/usr/archives/mysql/samp_db.1999-10-02.gz

If you want a large database, the output file will be very large and may be difficult to manage. If you want, you can dump the file into smaller, more manageable files by listing individual table names after the mysqldump command line's database name. The following example shows how to dump some of the tables in the SAMP_DB database into separate files:

The code is as follows	Copy Code
%mysqldump samp_db Student Score Event Absence >grapbook.sql %mysqldump SAMP_DB Member President >hist-league.sql

You may want to use the--add-drop-table option if you generate backup files that are ready to be used to refresh the contents of another database on a regular basis. This tells the server to write the DROP TABLE if exists statement to the backup file, and then when you take out the backup file and load it into the second database, you won't get an error if the table already exists.

If you dump a database so that you can transfer the database to another server, you don't even have to create a backup file. To ensure that the data inventory is another host, and then dump the database pipeline, so that MySQL can directly read the mysqldump output. For example: you want to copy the database from the host pit-viper.snake.net samp_db to Boa.snake.net, you can easily do this:

The code is as follows	Copy Code
%mysqladmin-h boa.snake.net Create samp_db %mysqldump samp_db | Mysql-h boa.snake.net samp_db

Later, if you want to refresh the database on Boa.snake.net again, skip the mysqladmin command, but add--add-drop-table to mysqldump to avoid the error that the table already exists:

The code is as follows	Copy Code
%mysqldump--add-drop-table samp_db | Mysql-h boa.snake.net samp_db

Other useful options for mysqldump include:

The--flush-logs and--lock-tables combinations will help with your database checkpoint. --lock-tables Lock All the tables you're dumping, and--flush-logs close and reopen the update log file, the new update log will include only queries that modify the database from the backup point. This will set up your update log to check the bit backup time. (However, if you have customers who need to perform an update, locking all the tables for customer access during the backup is not a good thing.) )

If you use--flush-logs to set up checkpoints to backup, it may be best to dump the entire database. If you dump a separate file, it's harder to sync the update log checkpoint with the backup file. During recovery, you typically extract the update log content on a database basis, and you have no option to extract updates for a single table, so you have to extract them yourself.

By default, mysqldump reads the entire contents of a table into memory before writing. This is usually really unnecessary, and actually if you have a big table, it's almost a failure. You can use the Quick option to tell Mysqldump to write each line as soon as it retrieves one line. To further optimize the dumping process, use--opt rather than--quick. The--OPT option turns on other options to accelerate the dumping of data and to read them back.

Implementing backups with--opt may be the most common method, because of the advantages of backup speed. However, to warn you that the--opt option does have a price,--opt optimizes your backup process, not other clients ' access to the database. The--opt option prevents anyone from updating any of the tables you are dumping by locking all tables at once. You can easily see the effect on general database access. When your database is used very frequently, it only adjusts backups once a day.

An option that has the opposite effect of--opt is--dedayed. This option causes Mysqldump to write the insert delayed statement instead of the INSERT statement. --delayed is helpful if you load a data file into another database and you want to minimize the impact of this operation on queries that might appear in the database.

The--compress option is helpful when you copy the database to another machine, because it reduces the number of bytes transmitted by the network. Here's an example of a program that--compress to communicate with a server on a remote host, rather than a program that connects to a local host:

The code is as follows	Copy Code
%mysqldump--opt samp_db | MySQL--compress-h boa.snake.net samp_db

Mysqldump has many options, as detailed in the MySQL reference manual.

2 Backup and Copy methods using the direct copy database

Another way to not involve mysqldump backup databases and tables is to copy the database table files directly. Typically, this is used as a CP, tar, or cpio utility. Examples of this article use CP.

When you use a direct backup method, you must ensure that the table is not in use. If the server changes when you are copying a table, the copy loses its meaning.

The best way to ensure your copy integrity is to shut down the server, copy the files, and reboot the server. If you do not want to shut down the server, lock the server while performing a table check. If the server is running, the same restrictions apply to copying files, and you should use the same locking protocol to "calm down" the server.

Assuming the server is down or you have locked the table you want to copy, the following shows how to back up the entire samp_db database to a backup directory (DataDir represents the server's data directory):

　　

The code is as follows	Copy Code
%CD DataDir %cp-r Samp_db/usr/archive/mysql A single table can be backed up as follows: %CD datadir/samp_db %CP member.*/usr/archive/mysql/samp_db %CP score.*/usr/archive/mysql/samp_db ....

When you have completed the backup, you can reboot the server (if it is turned off) or release the lock added to the table (if you let the server run).

To copy a database from one machine to another using a direct copy file, just copy the file to the appropriate data directory on another server host. To make sure that the file is in myiasm format or that the two machines have the same hardware structure, your database has strange content on another host. You should also make sure that servers on another machine do not access them while you are installing the database tables.

3 Replicated databases (replicating database)

Replication (Replication) is similar to copying a database to another server, but its exact meaning is to ensure a full synchronization of two databases in real time. This feature will appear in version 3.23 and is not very mature, so this article is not covered in detail.

4 Restoring data with Backup

Database corruption occurs for a number of reasons and varies from one degree to another. If you are lucky, you can damage only one or two tables (such as power off), if you are unlucky, you may have to replace the entire data directory (such as disk corruption). In some cases, recovery is also required, such as when a user mistakenly deletes a database or table. Whatever the cause of these unfortunate events, you will need to implement some kind of recovery.

If the table is corrupted but not lost, try to fix them with myisamchk or isamchk, and if such damage can be repaired by a hotfix, you may not need to use the backup file at all. For the process of table repair, see "Database Maintenance and repair."

The recovery process involves two sources of information: your backup file and an update log. The backup file restores the table to the state when the backup was implemented, but the general table has been modified in the time between the backup and the problem, and the update log contains the queries for making these modifications. You can use the log file as a MySQL input to repeat the query. This is the reason why you should enable the update log.

The recovery process varies depending on how much information you have to recover. In fact, it is easier to recover the entire database than a single table because it is easier to use an update log for a database than a single table.

4.1 Restore the entire database

First, if the database you want to restore is a MySQL database that contains authorization tables, you need to run the server with the--skip-grant-table option. Otherwise, it complains that the authorization table cannot be found. After you have recovered the table, execute mysqladmin flush-privileges tell the server to mount the authorization label and use them.

Copy the contents of the database directory to somewhere else, if you need them later.

Reload the database with the latest backup file. If you use mysqldump to generate the file, use it as a MySQL input. If you copy files directly from the database to the database directory, then you need to close the database before copying it, and then restart it.

Use the update log to repeat the query that modifies the database table after the backup. For any applicable update logs, use them as input to MySQL. Specifying the--one-database option allows MySQL to execute only queries that you are interested in restoring the database. If you know that you need to use all the update log files, you can use this command in the directory containing the log:

The code is as follows	Copy Code
% ls-t-r-1 update. [0-9]* | Xargs Cat | MySQL--one-database db_name

The LS command generates a single-column list of update log files, sorted by the order in which they were generated by the server (idea: If you modify any of the files, you will change the sort order, which causes the update log to be used in the wrong order.) )

It is likely that you will be using a few update logs. For example, the update log that has been generated since you backed up is named update.392, update.393, and so on, so you can rerun:

The code is as follows	Copy Code
%mysql--one-database Db_name < update.392 %mysql--one-database Db_name < update.393 .....

If you are implementing a recovery and using the update log to recover information that is missing due to a bad recommended drop DATABASE, drop table, or DELETE statement, make sure to delete the statements from it before applying the update log.

4.2 Recover a single table

Recovering a single table is more complex. If you use a backup file generated by mysqldump and it does not contain data for the tables you are interested in, you need to extract them from the related rows and use them as MySQL input. This is the easy part. The hard part is pulling the fragments out of the update log that is used only in the table. You will find that the Mysql_find_rows utility is useful for extracting multiline queries from the update log.

Another possibility is to use another server to recover the entire database, and then copy the table files you want to the original database. It could be really easy! When you copy the file back to the database directory, make sure the server for the original database shuts down.

Summarize:

No matter how you back up your database, we have to do a good job of security to avoid data loss, in MySQL, he has provided a lot of good database backup methods, we can carefully refer to other tutorials in this site.