The most complete Mysql backup method in history _mysql

I have
Used backup methods are: mysqldump, mysqlhotcopy, backup TABLE, SELECT into
outfile, or Backup binary log (Binlog), can also be a direct copy of data files and related configuration files. MyISAM
Tables are stored in the form of files, so they are relatively easy to back up, and several of the methods mentioned above can be used. Innodb All tables are saved in the same data file ibdata1
Medium (or multiple files, or separate tablespace files), which is relatively difficult to back up, the free scheme can be copied data files, backup Binlog, or
Mysqldump.

1.mysqldump Backup

Mysqldump is a SQL-level backup mechanism that guides data tables into SQL script files and is relatively appropriate for upgrades between different versions of MySQL, which is also the most common method of backup.
Example: Mysqldump-uroot-p database table >/home/jobs/back.sql
Mysqldump can also do incremental backup, mysqldump related parameters on the Web more than this one by one to repeat the

2.mysqlhotcopy Backup

Mysqlhotcopy is a PERL program. It uses LOCK TABLES, FLUSH
TABLES and CP or SCP
To quickly back up the database. It is the quickest way to back up a database or a single table, but it can only run on the same machine as the database file (including the datasheet definition file, the data file, the index file).
Mysqlhotcopy can only be used for backup MyISAM and can only be run on Unix-like and NetWare systems.
Mysqlhotcopy supports the one-time copying of multiple databases while also supporting regular expressions.
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/
/tmp For more detailed usage check the manual, or call the following command to view Mysqlhotcopy's help:
Perldoc/usr/local/mysql/bin/mysqlhotcopy Note that to use mysqlhotcopy, you must have
SELECT, RELOAD (to perform FLUSH TABLES) permissions, and you must also have permission to read the Datadir/db_name directory.

The Restore Mysqlhotcopy backup is the entire database directory, which can be copied directly to mysqld when used
The specified DataDir (here is the/usr/local/mysql/data/) directory, but also attention to the issue of permissions, the following 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 mysqld run user)

3.SQL Syntax Backup

3.1 Back up backup TABLE syntax actually and mysqlhotcopy
The work principle is similar, all is the lock table, then copies the data file. It can achieve online backup, but the effect is not ideal, so it is not recommended. It copies only the table structure file and the data file, not simultaneously copies the index text
, so recovery is slower. Example: Back TABLE tbl_name to '/tmp/db_name/'; Note that you must have a FILE
Permission to execute this SQL, and the directory/tmp/db_name/must be writable by the mysqld user, and the exported file cannot overwrite existing files to avoid security issues.
Restore a file backed up with the backup table method, and you can run the Restore table statement to recover the datasheet. Example: RESTORE TABLE from '/tmp/db_name/'; permission requirements are similar to those described above.

3.2 SELECT into outfile is to export the data to become a normal text file, you can customize the way the field interval, to facilitate processing of these data. Example:
SELECT into outfile '/tmp/db_name/tbl_name.txt ' from tbl_name; Note that there must be
File permission to execute this SQL, and files/tmp/db_name/tbl_name.txt must be mysqld
User-writable, exported files cannot overwrite existing files to avoid security issues.
Files backed up with the SELECT into OutFile method can run the LOAD data INFILE statement to recover the datasheet. Example: LOAD
DATA INFILE '/tmp/db_name/tbl_name.txt ' into TABLE
tbl_name; permission requirements are similar to those described above. Before pouring the data, the data table must already exist. Add REPLACE If you are concerned about duplication of data
keyword to replace existing records or ignore them with the IGNORE keyword.

4. Enable binary log (Binlog)

The binlog approach is relatively flexible, hassle-saving, and can also support incremental backups.
You must restart Mysqld 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
Then start mysqld on it. Binlog.000001 and Binlog.index are generated during the run, and the previous file is Mysqld
All updates to the data are logged, and subsequent files are all Binlog indexes that cannot be easily deleted. For information about Binlog, please check the manual.
When you need a backup, you can execute the SQL statement first, letting mysqld terminate the current Binlog
Write, you can back up the file directly, so you can achieve the purpose of the incremental backup: FLUSH LOGS; If you are backing up a replication system from a server, you should also back up
Master.info and Relay-log.info files.
Binlog files that are backed up can be viewed using the tools provided by MySQL Mysqlbinlog, such as:
/usr/local/mysql/bin/mysqlbinlog/tmp/binlog.000001 This tool allows you to display all of the specified database
SQL statements, and can also limit the time range, quite convenient, detailed view of the manual.
When recovering, you can do this by using the following statement:/usr/local/mysql/bin/mysqlbinlog/tmp/binlog.000001
| Mysql-uyejr-pyejr Db_name executes the SQL statement that mysqlbinlog output directly as input.
If you have a spare machine, you might want to back it up in this way. As the slave machine performance requirements are relatively low, so low-cost, with low cost can realize incremental backup and can share a part of the data query pressure, why not?

5. Copy Files

The direct backup data file compared with the previous methods, the backup data file is the most direct, fast and convenient, the disadvantage is that the incremental backup is basically not possible.
To ensure data consistency, the following SQL statements need to be executed before the back file: FLUSH TABLES with READ
Lock: Flushes the data in memory to disk while locking the datasheet to ensure that no new data is written in the copy process. This method backs up the data recovery is also very simple, direct copy back to
The original database directory can be.
Note that for Innodb type tables, you also need to back up their log files, that is, ib_logfile* files. Because when the Innodb table is corrupted, you can rely on these log files to recover.

6. Use rsync backup
Rsync can also be used as a synchronization tool to do backups, but to configure server-side and client
Example RSYNC-VZRTOPG--progress--delete root@192.168.1.3::root/tmp/
Related rsync configuration can refer to http://fanqiang.chinaunix.net/a6/b7/20010908/1305001258.html
The disadvantage is that rsync is an incremental backup based on file modification times, so the backup database is fully prepared and cumbersome to configure.

Of course, you can also refer to the following article
A collection of MySQL database backup and Restore methods