[MySQL]-the most comprehensive MySQL backup method in history

Source: http://www.dabaoku.com/jiaocheng/biancheng/mysql/200911072934.shtml

 

I once
Used backup methods include mysqldump, mysqlhotcopy, backup table, and select.
OUTFILE, or BINLOG backup, or directly copy data files and related configuration files. MyISAM
Tables are saved as files, so they are relatively easy to back up. The methods mentioned above can be used. All InnoDB tables are stored in the same data file ibdata1
Medium (or multiple files, or independent tablespace files), it is relatively difficult to back up data. The free solution can be copying data files, backing up BINLOG, or using
Mysqldump.

 

 

1. mysqldump backup

 

Mysqldump adopts an SQL-level backup mechanism. It imports data tables into SQL script files and is suitable for upgrading between different MySQL versions. This is also the most common backup method.

Example: mysqldump-uroot-P database table>/home/jobs/back. SQL

Mysqldump can also be used for Incremental backup. Many mysqldump-related parameters are available on the Internet, so I will not repeat them here.

 

2. mysqlhotcopy backup

 

Mysqlhotcopy is a PerlProgram. It uses lock tables and flush
Tables and CP or SCP
To quickly back up databases. It is the fastest way to back up a database or a single table, but it can only run on the machine where the database file (including the data table definition file, data file, and index file) is located.
Mysqlhotcopy can only be used to back up MyISAM and can only run on Unix-like and Netware systems.

Mysqlhotcopy supports copying multiple databases at a time and regular expression.

Example: Root #/usr/local/MySQL/bin/mysqlhotcopy-H = localhost-u = root
-P = 123456 database/tmp (copy the database directory to/tmp
Under) root #/usr/local/MySQL/bin/mysqlhotcopy-H = localhost-u = root-P = 123456
Db_name_1... db_name_n/tmproot #/usr/local/MySQL/bin/mysqlhotcopy
-H = localhost-u = root-P = 123456 db_name./RegEx/
For more detailed usage of/tmp, see the manual or call the following command to view the help of mysqlhotcopy:

Perldoc/usr/local/MySQL/bin/mysqlhotcopy Note: To use mysqlhotcopy, you must have
Select and reload (to execute flush tables) permissions, and must be able to read the datadir/db_name directory.

 

Restore the entire database directory backed up by mysqlhotcopy, which can be directly copied to mysqld during use.
The specified datadir directory (here it is/usr/local/MySQL/data/). Pay attention to the permission issues, for example: Root # cp
-RF db_name/usr/local/MySQL/data/root # chown-r nobody: Nobody
/Usr/local/MySQL/data/(change the owner of the db_name directory to the mysqld running user)

 

 

3. SQL syntax backup

3.1 backup table syntax and mysqlhotcopy
The working principle is almost the same. They all lock tables and then copy data files. It can achieve online backup, but the effect is not ideal, so it is not recommended. It only copies table structure files and data files, and does not copy index files at the same time.
So the recovery is slow. Example: Back table tbl_name to '/tmp/db_name/'; note that file is required
Permission to execute this SQL statement, and the directory/tmp/db_name/must be writable by mysqld users. exported files cannot overwrite existing files to avoid security issues.

Run the restore TABLE statement to restore the data table. Example: Restore table from '/tmp/db_name/'; the permission requirements are similar to described above.

 

3.2 select into OUTFILE is to export data to a common text file. You can customize the field interval to process the data conveniently. Example:
Select into OUTFILE '/tmp/db_name/tbl_name.txt' from tbl_name; note that
File Permission to execute this SQL statement, and the file/tmp/db_name/tbl_name.txt must be mysqld
Users can write and exported files cannot overwrite existing files to avoid security issues.

If you use the select into OUTFILE method to back up a file, you can run the load data infile statement to restore the data table. Example: Load
Data infile '/tmp/db_name/tbl_name.txt' into table
Tbl_name; permission requirements are similar to described above. Before you import data, the data table already exists. If you are worried that the data will be duplicated, you can add replace
Keyword to replace existing records or ignore them with the ignore keyword.

 

4. Enable BINLOG)

 

The BINLOG method is more flexible and labor-saving, and supports Incremental backup.

Mysqld must be restarted when BINLOG is enabled. First, close mysqld, open my. CNF, and add the following lines:

Server-id = 1

Log-bin = BINLOG

Log-bin-Index = BINLOG. Index

Start mysqld. Binlog.000001 and BINLOG. index are generated during running. The preceding file is mysqld.
Record all data update operations, and subsequent files are indexes of all binlogs, which cannot be easily deleted. For information about BINLOG, see the manual.

When a backup is required, run the SQL statement to terminate the current BINLOG.
In this way, you can directly back up the file, so that you can achieve the purpose of Incremental Backup: flush logs; if it is a backup copy system slave server, you should also back up
Master.info and relay-log.info files.

The BINLOG file backed up can be viewed using mysqlbinlog, a tool provided by MySQL, for example:
/Usr/local/MySQL/bin/mysqlbinlog/tmp/binlog.000001 this tool allows you to display all
SQL statement, and time range can be limited, which is quite convenient. For details, please refer to the manual.

You can use a statement similar to the following for restoration:/usr/local/MySQL/bin/mysqlbinlog/tmp/binlog.20.01
| Mysql-uyejr-pyejr db_name runs the SQL statement output by mysqlbinlog directly as input.

If you have an idle machine, use this method for backup. Because the performance requirements of the Server Load balancer are not that high, the cost is low. with low costs, Incremental Backup can be achieved and the Data Query pressure can be shared. Why not?

 

5. Copy an object

 

Compared with the previous methods, direct backup of data files is the most direct, fast, and convenient. The disadvantage is that Incremental backup is basically not supported.

To ensure data consistency, run the following SQL statement: flush tables with read
Lock, that is, refresh the data in the memory to the disk and lock the data table to ensure that no new data is written during the copy process. The data backed up in this way is also very simple, and is directly copied back.
In the original database directory.

Note: For an InnoDB table, you also need to back up its log file, that is, the ib_logfile * file. Because when the InnoDB table is corrupted, these log files can be recovered.

 

6. Backup Using rsync

Rsync can also be used as a synchronization tool for backup, but you need to configure the server and client

Example: rsync-vzrtopg -- Progress -- delete-Root@192.168.1.3: Root/Tmp/

For details about rsync configuration, referHttp://fanqiang.chinaunix.net/a6/b7/20010908/1305001258.html

The disadvantage is that rsync is Incremental Backup Based on the file modification time, so the backup database is full backup and configuration is troublesome.