MySQL database backup rules and methods

The biggest benefit of backup is that you can recover from the loss or corruption of your database tables. Even if you are not greedy, just hope to recover part of you are also very happy, right? Sometimes, it is the MySQL administrator that causes the damage. The administrator has already known that the table has been destroyed, using editors such as VI or Emacs to try to edit them directly, which is definitely not a good thing for the table! There are 2 main ways to back up a database: Use the MYSQLDUMP program to copy database files directly (e.g., with CP, CPIO, or tar).

Each approach has its pros and cons: Mysqldump with the MySQL server. The direct copy method takes place outside the server, and you must take steps to ensure that no customer is modifying the table that you will copy. 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 file system backup, the table file that is backed up is not in a consistent state, then the recovery table will lose meaning. The difference between a file system backup and a direct copy file is that you have complete control over the backup process so that you can take steps to ensure that the server keeps the table undisturbed. Mysqldump is slower than direct copy. Mysqldump generates text files that can be ported to other machines, even those with different hardware structures. Direct copy files cannot be ported to other machines unless you are copying tables that use the MyISAM storage format. The ISAM tables can only be copied on machines with similar hardware structures. The MyISAM table storage format introduced in MySQL 3.23 solves this problem because the format is machine independent, so direct copy files can be ported to machines with different hardware architectures. As long as two conditions are met: Another machine must also run MySQL 3.23 or later, and the file must be in MyISAM format, not the ISAM format.

No matter which backup method you use, if you need to recover the database, here are a few principles that you should follow to ensure that you can recover the best results:

Regularly implement backups, establish a plan and strictly follow

Have the server perform the update log. When you need to recover data after a crash, the update log will help you. After you restore the data to the backup state using the backup file, you can re-apply the changes after the backup by running the query in the update log, which restores the tables in the database to the state when the crash occurred. In terms of file system backup, the database backup file represents a full dump, while the update log represents a progressive dump (incremental dump).

Use a unified and easy-to-understand backup file naming mechanism

Like Backup1, buckup2 and so on are not particularly meaningful. When implementing your recovery, you will waste time finding out what is in the file. You may find it useful to make a backup file name with a database name and date. For example:

%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 the backup is generated. Backups are generally big! You also need to have your backup files expire to prevent them from filling up your disk, as if you had your log file out of date. Back up your backup files with a file system backup. If you encounter a complete crash that not only clears up your data directory, but also clears the disk drive containing your database backup, you will be really in trouble. You also need to back up your update logs. Place your backup file on a different file system than the one used for your database. This reduces the likelihood of filling up the file system that contains the data catalog due to the backup being generated.

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

1. Backing up and copying databases using mysqldump

When you use the Mysqldumo program to generate a database backup file, the file content defaults to the Create statement that creates the table being dumped and the INSERT statement that contains the row data in the table. In other words, the output generated by mysqldump can be used later as the input to MySQL to rebuild the database. You can dump the entire database into a separate text file, as follows:

%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02

The beginning of the output file looks like this:

# 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 (10)

Unsigned default ' 0 ' NOT NULL, date date of 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 remainder 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:

%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 like, you can list separate table names after the mysqldump command line's database name to pour into their content, which will dump the files into smaller, more manageable files. The following example shows how to pour some of the tables from the SAMP_DB database into separate files:

%mysqldump samp_db Student Score Event Absence >grapbook.sql

%mysqldump SAMP_DB Member President >hist-league.sql

If you generate a backup file ready to be used to periodically refresh the contents of another database, you may want to use the--add-drop-table option. This tells the server to write the drop table if exists statement to the backup file, and then, when you remove the backup file and load it into the second database, you will not get an error if the table already exists. If you pour out a database so that you can move the database to another server, you don't even have to create a backup file. To ensure that the data is stored in another host, and then use a pipeline to dump the database, so that MySQL can directly read the output of mysqldump. For example: you want to copy the database from the host pit-viper.snake.net samp_db to Boa.snake.net, which is easy to do:

%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 getting the table already existing error:

%mysqldump--add-drop-table samp_db│mysql-h boa.snake.net samp_db

Mysqldump other useful options include:

The combination of--flush-logs and--lock-tables will help you with your database checkpoint.

--lock-tables Lock All the tables you're dumping

While--flush-logs closes and re-opens the update log file, the new update log will only include queries that modify the database from the backup point. This will set your update log to check the point backup time. (However, if you have a customer who needs to perform an update, locking all tables for client access during backup is not a good thing.) If you use--flush-logs to set up checkpoints to backup, it may be better to dump the entire database. If you dump a separate file, it is more difficult to synchronize the update log checkpoint with the backup file. During recovery, you typically extract the update log content on a database basis, and you do not have the option to extract updates for individual tables, so you must extract them yourself. Instead, 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 long as it retrieves one line. To further optimize the dumping process, use the--opt instead of the--quick.--opt option to turn on other options to speed up data dumping and read them back. Implementing backups with--opt is probably the most common approach 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 the other client's 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 generally used very frequently, it only adjusts backups once a day. An option that has a--opt opposite effect is--dedayed. This option causes Mysqldump to write out 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 have the least impact on the queries that might appear in that database. The--compress option is helpful when you copy a database to another machine, because it reduces the number of bytes transmitted over the network.

Here is an example of a program that--compress to communicate with a server on a remote host, rather than a program that is connected to a local host:

%mysqldump--opt samp_db│mysql--compress-h boa.snake.net samp_db

Mysqldump has many options, see the MySQL reference manual.

2. Backup and copy methods using a direct copy database

Another way to back up databases and tables that do not involve mysqldump is to copy the database table files directly. Typically, this is used with applications such as the CP, tar, or cpio utility. The example of this article uses CP. When you use a direct backup method, you must ensure that the table is not being used. If the server changes it while 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 then restart the server. If you do not want to shut down the server, you need to lock the server while performing the 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 data directory for the server):

%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 restart the server (if it is turned off) or release the lock on 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 the other 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 ensure that the servers on the other machine do not access them while you are installing the database tables.

3. Replicating databases (replicating database)

Replication (Replication) is similar to copying a database to another server, but its exact meaning is to guarantee 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 described in detail.

4. Recovering data with a backup

Database corruption occurs for a number of reasons and in varying degrees. If you are lucky, you may only damage one or two tables (such as power-down), and 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. Regardless of the causes of these unfortunate events, you will need to implement some kind of recovery. If the table is damaged but not lost, try repairing them with myisamchk or isamchk, and if such damage can be repaired, you may not need to use a 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 files and an update log. The backup file restores the table to the state it was in when the backup was implemented, but the general table has been modified during the time between the backup and the problem, and the update log contains the queries used to make those changes. You can use the log file as input for MySQL to repeat the query. This is why you should enable the update log. The recovery process varies depending on how much information you have to recover. In fact, recovering an entire database is easier than a single table, because

The database is easier to use than a single table for updating logs.

4.1 Recovering the entire database

First, if the database you want to restore is a MySQL database with an authorization table, 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 restored the table, execute mysqladmin flush-privileges tell the server to load the authorization labels 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 files. If you use mysqldump to generate the file, use it as input to MySQL. If you use files copied directly from the database, copy them directly back to the database directory, but at this point you need to close the database before copying the file and restart it. The query that modifies the database table after the backup is repeated with the update log. For any applicable update logs, use them as input to MySQL. Specifying the--one-database option makes MySQL only perform queries for databases that you are interested in recovering. If you know you need to use all the update log files, you can use this command in the directory containing the logs:

% 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 according to the order in which the server produces them (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 it:

%mysql--one-database Db_name < update.392

%mysql--one-database Db_name < update.393

......

If you are implementing recovery and using update log recovery due to a faulty proposed drop DATABASE, drop TABLE, or DELETE statement resulting in lost information, ensure that the statements are removed from the update log before applying it.

4.2 Recovering 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 relevant rows and use them as input to MySQL. This is the easy part. The hard part is to pull the fragment from the update log that applies only to the table. You will find that the Mysql_find_rows utility is helpful in extracting multiple rows of queries from the update log. Another possibility is to use another server to recover the entire database, and then copy the table file you want into the original database. This may be really easy! When you copy the file back to the database directory, make sure that the server for the original database is closed.

Is there any feeling of physical and mental exhaustion after reading it? The presence of multiple backups at this moment will make you feel compelled to use! Why?

Because it does not require you to know so much expertise oh, you just fill in the data information can be automatically backed up. When you need to click on a button to recover, the migration is OK. Such a simple operation, backup recovery that is not a thing! If you don't believe it, you can try to register and know if I lied to you.

This article turns from http://bbs.dbfen.com/forum-41-1.html

This article is from the "Big Meatball" blog, please make sure to keep this source http://12478147.blog.51cto.com/9663367/1597809

MySQL database backup rules and methods