Mysql database and backup and recovery are important to back up your database in situations where database tables are lost or corrupted. If a system crash occurs, you will restore your table as the least possible data to the status of the crash occurred. Sometimes it is the mysql administrator caused damage. The administrator already knows that the table is destroyed, with editors such as VI or Emacs, etc., try to edit them directly, this pair is definitely not a good thing!
Two main methods of backup databases are to copy database files (such as CP, CPIO or TAR, etc.) with mysqldump programs. Each method has its advantages:
MySQLDUMP works with the MySQL server. Direct copying methods are performed outside the server, and you must take action to ensure that you don't have a customer that you will copy the table. If you want to back up the database with a file system backup, the same problem occurs: If the database table is modified during the file system backup process, enter the backup table file subject inconsistent state, and the subsequent recovery table will lose meaning. The difference between file system backup and direct copy file is that you fully control the backup process for the latter so that you can take steps to ensure that the server makes the table uninterrupted.
MySQLDUMP is slower than the direct copy.
MySQLDUMP generates text files that can be ported to other machines, even those machines with different hardware structures. Direct copy files cannot be ported to other machines unless you are copying the table using Myisam storage format. The ISAM table can only be copied on a similar hardware structure. The Myisam table storage format introduced in MySQL 3.23 solves this problem because the format is not related to the machine, so the direct copy file can be ported to a machine 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 instead of ISAM format.
No matter which backup method you use, if you need to recover your database, there are several principles to follow to ensure the best results:
Regularly implement the backup. Establish a plan and strictly abide by.
Let the server execute the update log. When you need to recover data after crash, the update log will help you. After you recover your data with your backup file to your backup, you can use the backup after the query in the update log, which restores the table in the database to the crash.
In terms of file system backup, the database backup file represents full dumps, and the update log represents an incremental dump.
Use a unified and easy-to-understand backup file naming mechanism. Like backup1, buckup2, etc., etc. is not particularly meaningful. When you implement your recovery, you will waste time to find out what is in the file. You may find that the database name and date constitute the backup file name will be useful. 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 generating backups. Backup is generally very big! You also need to have your backup files with expiration time to avoid them filled with your disk, just like you have expired your log file.
Back up your backup file with file system. If you encounter a complete collapse, not only remove your data catalog, and clear the disk drive that contains your database backup, you will really encounter trouble. Also back up your update log.
Place your backup file on a file system different from the database used for your database. This will reduce the possibility of filling the file system containing the data directory due to generating a backup.
Techniques for creating backups are also useful for copying databases to another. Most commonly, a database is transferred to a server running on another host, but you can also transfer data to another server on the same host.
1. Use mysqldump backup and copy database When you use the MySQLDUMO program to generate a database backup file, it is default, the file content contains the CREATE statement that creates a table in dump and an INSERT statement containing the table in the table. In other words, the output generated by mysqldump can be used as an input to Mysql in the future to rebuild the database. You can pour 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 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 remaining part of the file has more INSERT and CREATE TABLE statements.
If you want to compress backup, use the following command:
% mysqldump samp_db | gzip> /usr/archives/mysql/samp_db.1999-10-02.gz
If you want a huge database, the output file will also be very large, it may be difficult to manage. If you like, you can list separate table names after the database name of the mysqldump command line, which divides the dump file into a smaller, easier to manage files. The following example shows how to pour some forms of the SAMP_DB database into the subsequent file:
% mysqldump Samp_DB Student Score Event ABSENCE> Grapbook.sql% mysqldump Samp_DB Member President> Hist-League.sql
If you generate a backup file that is ready to refresh another database content, you may want to use the -add-drop-Table option. This tells the server to write the Drop Table if Exists statement into the backup file, 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 fall out of a database so you can transfer the database to another server, you don't even have to create a backup file. To ensure that the data is in another host, then use the pipe to pour the database, so Mysql can directly read the output of mysqldump. For example: You want to copy database SAMP_DB to BOA.SNAKE.NET from the host pit-viper.snake.net, which can be easier:
% mysqladmin -h bag_db% mysqldump SAMP_DB | MySQLDUMP SAMP_DB | MySQLDUMP SAMP_DB | MySQL -H Boa.Snake.net SAMP_DB, if you want to refresh the database on Boa.Snake.net, skip the mysqladmin command, but to add mysqldump plus ADD-DROP-TABLE to avoid the existing error:% mysqldump --add-drop-table samp_db | mysql -h Boa.snake.net Samp_DB mysqldump Other useful options include:
- Flush-logs and --lock-tables combinations will help your database checkpoint. --Lock-Tables lock all tables you are dumping, and -flush-logs turn off and reopen the update log file, the new update log will only include the query of the modified database from the backup point. This will set your update log checkpoint backup time. (However, if you need to execute a updated customer, lock all the tables access to the client during the backup is not a good thing.)
If you use -flush-logs to set checkpoints to backup, it is best to pour the entire database. If you dump separate files, it is difficult to synchronize the update log checkpoint with the backup file. During recovery, you usually extract the update log content according to the database basis, and do not extract updates for a single table, so you have to extract them yourself.
By default, mysqldump reads the entire content of a table into memory before writing. This is often unnecessary, and actually if you have a big table, it is almost failed. You can use the --quick option to tell MysqldUMP to write every line as long as it retrieves a line. In order to further optimize the dumping process, use -opt instead --quick. --opt options Open other options, accelerate data, and read them back.
Implementing backups with --Opt may be the most common method because the advantages of backup speeds. However, to warn you, - OPT options do cost, - OPT optimized is your backup process, not other customers' access to the database. --Opt options By locking all tables to prevent anyone from updating any table you are pouring. You can easily see its effect on general database access. When your database is usually used very frequently, it is only adjusted for backup a day.
An option for the opposite effect of --opt is - Dedayed. This option makes MySQLDUMP write an Insert Delayed statement instead of an Insert statement. If you load a data file into another database and you want to be the smallest impact on the query that may appear in the database, - Delayed is very helpful.
- Compress option is helpful when you copy the database to another machine because it reduces the number of network transmission bytes. There is an example below, notice that -compress is given to the program communicating with the server on the remote host, not a program that is connected to the local host:
% mysqldump --opt samp_db | mysql --compress -h Boa.snake.Net Samp_DB mysqldump has many options, see "MySQL Reference Manual". 2, use direct copy database backup and copy method
Another way that does not involve MySQLDUMP backup databases and tables are directly copied database table files. Typically, this uses, such as CP, TAR or CPIo utilities. The examples of this article use CP.
When you use a direct backup method, you must ensure that the table is not used. If the server changes it when you are copying a table, the copy will lose meaning.
The best way to ensure your copy integrity is to close the server, copy file, and then restart the server. If you don't want to close the server, lock the server while performing the table check. If the server is running, the same constraint is also suitable for copy files, and you should use the same lock protocol to let the server "quiet". Suppose the server is turned off 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):
% CD DATADIR% cp -r samp_db / usr / archive / mysql
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 complete your backup, you can restart the server (if you turn it off) or release the lock on the table (if you let the server run).
To copy a database from a machine to another with a direct copy file, you can copy the file to the appropriate data directory of another server host. To make sure the file is a MyiaSM format or two machines have the same hardware structure, otherwise your database has strange content on another host. You should also guarantee that the server on another machine does not access them when you are installing a database table.
3, replicate database (Replicating Database)
Replication is similar to the copy database to another server, but its exact meaning is to ensure complete synchronization of the two databases in real time. This feature will appear in version 3.23, and it is not very mature, so this article does not introduce a detailed introduction.
4. Restore data with backup
There are many reasons why database damage, and the degree is different. If you walk, you may only damage one or two tables (such as dropping), if you are unlucky, you may have to replace the entire data directory (such as disk corruption). In some cases, you need to recover, such as the user incorrectly deleted a database or table. Regardless of these unlucky events, you will need to implement some recovery.
If the table is damaged but not lost, try to fix them with Myisamchk or ISAMCHK, if such a damage can be fixed, 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 kinds of information: your backup file and an update log. The backup file restores the status when the table is implemented, but the general table has been modified within the time between the backup and the problem, and the update log contains a query for these modifications. You can use the log file as a MySQL input to repeat the query. This is exactly why the update log is enabled.
The recovery process is different depending on the information you have to recover. In fact, recovering the entire database than a single table, because the update log for the database is easier than a single table.
4.1 Restore the entire database
First, if you want to recover the database is a MySQL database that contains an authorized form, you need to run the server with the --skip-grant-table option. Otherwise, it will complain that you can't find a license table. After you have recovered the table, execute mysqladmin flush-privileges to tell the server to load the authorization standard and use them.
Copy the contents of the database directory to some places, if you need them later. Reissue the database with the latest backup file. If you generate files generated by mysqldump, use it as a MySQL input. If you use the file from the database directly to the database directory, you will need to turn off the database before copying the file and then restart it.
Use the update log to repeat the query of the modified database table after the backup. For any applicable update log, use them as the MySQL input. Specify --one-Database options allow mysql only to perform queries that you are interested in recovery. If you know that you need to use all update log files, you can use this command in the directory containing the log:% ls -t -r -1 update. [0-9] * | xargs cat | mysql --one-data DB_NAME
The LS command generates a single list of updated log files, which produces their order according to the server (ideas: If you modify any file, you will change the order of sorting, which causes the order of the update log to be used.)
It is likely that you will use a few update logs. For example, since the update log generated since your backup is updated to update.392, update.393, etc., you can re-run this:
% mysql --one-database db_name If you are implementing recovery and using update log recovery Due to a missed DROP DATABASE, DROP TABLE, or DELETE statement, you can ensure that these statements are removed from it before using the update log. 4.2 Restore a single table Restore a single table is more complicated. If you use a backup file generated by mysqldump, and it does not contain data you are interested in, you need to extract them from the relevant row and use them as MySQL input. This is an easy part. The difficulty is to pull out the pieces from the update log for the table. You will find that the mysql_find_rows utility is very helpful, it extracts multi-line queries from the update log. Another possibility is to use another server to recover the entire database, then copy the table file you want to the original database. This may be really easy! When you copy the file back to the database directory, make sure that the server of the original database is turned off.
