MySQL database backup tools and six methods

With regard to the Mysql database BACKUP method, helper's editor summarizes all the current BACKUP methods, including mysqldump, mysqlhotcopy, backup table, 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) is relatively difficult to back up. The free solution can be copying data files, backing up binlogs, 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. If mysqldump has many parameters online, I will not repeat them here.

2. mysqlhotcopy backup

Mysqlhotcopy is a PERL program. It uses lock tables, 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, you must have the permission 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. In this way, the backup data recovery is also very simple, just copy it back to 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 more information about rsync configuration, see here: http://www.bkjia.com/a/view/14287.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.