MYSQL backup database _ MySQL

The restoration process of the MYSQL backup database includes two sources of information-backup files and binary logs. backup files can be restored to the status when backup is executed, while binary logs can be restored to the status when a fault occurs. The following describes how to use these two files to restore a database or a single data table.

Steps for restoring the entire database:

Copy the contents of the entire directory of the database to be restored to other places for backup.

Use the latest backup file to reload the database. If you use the backup generated by mysqldump, you can use them as the mysql input overload. if you copy the Database Directory to back up the backup, you need to disable the database server, copy the backup to the data directory again, and then restart the database server.

Redo the transaction using the binary log to restore the data to the error point. The specific operation is as follows: use mysqlbinlog to convert the log to the ASCII format, use it as the mysql input, and specify the -- one-database option, so that mysql can only restore the database you specified. For example:

% Ls-t-r-l binlog. [0-9] * | xargs mysqlbinlog | mysql -- one-database db_name
However, the preceding command is only applicable when all log file names have the same length. Otherwise, the following Perl code is required:

#! /Usr/bin/perl-w
# Sort_num.pl

Use strict;

My @ files = <>; # read all input file
@ Files = sort {my $ anum = $1 if $ a = ~ //. (/D +) $/; # sort them by numeric extension
My $ bnum = $1 if $ B = ~ //. (/D +) $ /;
$ Anum <=> $ bnum;
} @ Files;
Print @ files; # print them
Exit (0 );

Use the script as follows:
% Ls-l binlog. [0-9] * | sort_num.pl | xargs mysqlbinlog | mysql -- one-database db_name
The above discussion is about the need for all log files, but in most cases we only need to back up several log files generated after the backup, then the following command can be used to redo.

% Mysqlbinlog binWebjxCom prompt: There are two methods to back up data in MySQL, one is to use mysqldump program, one is to directly copy database files using packaging programs such as mysqlhotcopy, cp, tar, and cpio. The mysqldump program backs up the database slowly, but the text files it generates are easy to transplant. Using mysqlhotcopy and other programs for fast backup is required because it directly operates on system files.

Regular backups can greatly reduce the loss caused by database crashes.

There are two ways to back up data in MySQL. one is to use the mysqldump program, and the other is to use the mysqlhotcopy, cp, tar, cpio, and other packaging programs to directly copy database files. The mysqldump program backs up the database slowly, but the text files it generates are easy to transplant. Using mysqlhotcopy and other programs to back up data is fast, because it directly performs operations on system files and requires manual coordination of database data backup consistency.

Using mysqldump to back up a database is actually dumping the database into a series of create table and INSERT statements, through which we can regenerate the database. Use mysqldump as follows:

% Mysqldump -- opt testdb | gzip>/data/backup/testdb. bak
# -- Opt option will optimize the dump process, the generated backup file will be a little smaller, and the subsequent pipeline operations will compress the data
% Mysqldump -- opt testdb mytable1, mytable2 | gzip>/data/backup/testdb_mytable.bak
# Only the specified data table can be exported after the database name. Multiple Data tables can be separated by commas (,).

The -- opt option can also activate the -- add-drop-table option, which will add a drop table if exists statement before each create table in the backup file. This allows you to easily update data tables without the "data table already exists" error.

You can use the mysqldump command to directly transfer the database to another server without generating backup files. You can regularly update the remote database after repeated execution.

% Mysqladmin-h remote_host create testdb
% Mysqldump -- opt testdb | mysql-h remote_host testdb
In addition, you can remotely call programs on the server through ssh, such:
% Ssh remote_host mysqladmin create testdb
% Mysqldump -- opt testdb | ssh remote_host mysql testdb
You can directly copy system files to back up the database. During backup, make sure that no one modifies the database. To do this, it is best to shut down the server. If the data table cannot be closed, try to lock the data table with the read-only side. The following are some examples:

% Cp-r db/backup/db # back up the database to the/backup/db Directory
% Cp table_name. */backup/db # back up only the table_name data table
% Scp-r db remotehot:/usr/local/mysql/data # use scp to directly copy the database to a remote server

When directly copying a database to a remote host, note that the two machines must have the same hardware structure or copy all the copied data tables as portable data tables.

Mysqlhotcopy is a Perl DBI script that backs up a database without shutting down the server. it has the following advantages:

It directly copies files, so it is faster than mysqldump.

Data locking can be automatically completed without shutting down the server during backup.

Refresh the log so that the checkpoints of backup files and log files can be synchronized.

The following is an example of this tool:

% Mysqlhotcopy db/bakcup/# back up the db database to the backup/db Directory. a db directory is automatically created.
Use the backup table statement to back up data. this statement was first used in MySQL 3.23.25 and is only applicable to MyISAM data tables. The usage is as follows:

Mysql> backup table mytable TO '/backup/db'; # back up the mytable data table to the/backup/db directory.
To execute this statement, you must have the FILE and SELECT permissions for those tables, and the backup directory must also be writable by the server. When this statement is executed, the data in the memory is first written to the disk, and then the data of each data table is. frm (table structure definition file ),. copy the MYD (data) file from the data directory to the backup directory. It does not copy the. MYI (index) file because it can be rebuilt with the other two files. When the backup table statement is used to back up data tables, the data tables are locked in sequence. when multiple data tables are backed up at the same time, the data tables may be modified. Therefore, when BACKUP 0 is complete, the data in the backup file may be different from the data in the current data table. to eliminate this difference, we can lock the data table in read-only mode and unlock it after the backup is complete. For example:

Mysql> lock tables tb1 READ, tb2 READ;
Mysql> backup table tb1, tb2 TO 'backup/db ';
Mysql> unlock tables;
Data tables backed up using the backup table statement can be restore table to be reloaded to the server.

InnoDB and BDB databases can also be backed up using mysqldump and direct copy. When using the direct copy method, you must copy all the files that comprise the InnoDB and BDB databases, for example, InnoDB. frm files, log files, and tablespace configuration files; BDB data files and log files.

Using the image mechanism for backup, we can use the slave stop statement to suspend the image of the SLAVE server and create a backup using the copy method or other tools on the SLAVE server. After the backup is complete, use slave start to restart the image and synchronize the image from the server to the master server again to receive the modifications made by the master server during the backup.

There is no command to rename the database in MySQL, but we can use mysqldump to dump the database, create a new empty database, and load the dump file to the new database, in this way, the database is renamed. For example:

% Mysqldump old_db> db. SQL # dump db database data
% Mysqladmin create new_db # create an empty database
% Mysql new_db <db. SQL # load the database data to the new database
% Mysqladmin drop old_db # delete old database
A simpler way to rename a database is to directly modify the database directory name, but this method is not applicable to InnoDB and BDB databases. Note: After renaming, you must update the relevant data table information in the permission table. you must execute the following statement:

Mysql> UPDATE db SET db = 'new _ db' WHERE db = 'old _ db ';
Mysql> UPDATE tables_priv SET db = 'new _ db' WHERE db = 'old _ db ';
Mysql> UPDATE columns_priv SET db = 'new _ db' WHERE db = 'old _ db ';
Mysql> UPDATE host SET db = 'new _ db' WHERE db = 'old _ db ';
Log.1234 | mysql -- one-database db_name
% Mysqlbinlog binlog.1235 | mysql -- one_database db_name
...
If we need to recover a DATABASE that is damaged by executing the drop database, drop table or DELETE statement, we need to DELETE this statement in the log file. Otherwise, the results will be the same after redo. Therefore, you need to convert the log into ASCII format and save it. Then, open the file with the editor, delete these statements, and then redo them.

If you use text change logs, you do not need to use the mysqlbinlog program because the log file itself is in ASCII format.

RESTORE the available restore table statements for the data TABLE backed up by the backup table command:

Backup statement:
Mysql> backup table table_name1, table_name2 TO '/backup/table_backup'; recovery statement:
Mysql> restore table table_name1, table_name2 FROM '/backup/table_backup ';
Restore a data table with a foreign key. SET FOREIGN_KEY_CHECK = 0 is available. the Statement first closes the key-word check and then restarts the table. if the value is 1, the check is valid.

Restore the InnoDB tablespace. when the server restarts, the InnoDB processing program will perform automatic recovery. set-variable = innodb_force_recovery = level in the option file [mysqld] to set the automatic recovery level, the typical startup value is 4. If you need to recover from the backup file, it is the same as the method described above. The recovery of BDB data tables is also the same. it will be automatically restored when the server is started.