Share MySQL the most complete backup method

History of the most complete MySQL backup method, you need friends can refer to the next. I have used the backup methods are: mysqldump, mysqlhotcopy, BACKUP TABLE, SELECT INTO
OUTFILE, or backup binary log (binlog), but also can be directly copied data files and related configuration files. MyISAM
Table is saved into the form of documents, it is relatively easy to back up, several methods mentioned above can be used. Innodb all the tables are stored in the same data file ibdata1
(Or may be more than one file, or a separate table space file), relatively poor backup, free program can copy the data files, backup binlog, or use
mysqldump
1.mysqldump backup
mysqldump is a SQL-level backup mechanism that directs data tables to SQL script files, which is relatively appropriate when upgrading between different MySQL versions, which is also the most commonly used backup method.
Example: mysqldump -uroot -p database table> /home/jobs/back.sql
mysqldump can also do incremental backup, mysqldump related parameters online more, do not go into details one by one
2.mqlqlhotcopy 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 files (including datasheet definition files, data files, index files).
mysqlhotcopy can only be used to back up MyISAM, and only run on Unix and NetWare-like systems.
mysqlhotcopy support multiple copies of the database at one time, but also supports regular expression.
Example: root # / usr / local / mysql / bin / mysqlhotcopy -h = localhost -u = root
-p = 123456 database / tmp (copy the database directory database 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 A more detailed method of use Please refer to the manual, or call the following command to view mysqlhotcopy help:
perldoc / usr / local / mysql / bin / mysqlhotcopy Note that you want to use mysqlhotcopy must have
SELECT, RELOAD (to perform FLUSH TABLES) permissions, and must also be able to read datadir / db_name directory permissions.
Restore mysqlhotcopy backup is the entire database directory, you can use directly copied to mysqld
Specified datadir (in this case / usr / local / mysql / data /) directory can be, and pay attention to the issue of permissions, as in the following example: root # cp
-rf db_name / usr / local / mysql / data / root # chown -R nobody: nobody
/ usr / local / mysql / data / (the owner of the db_name directory changed mysqld run user)
3. SQL syntax backup
3.1 Backup BACKUP TABLE syntax and mysqlhotcopy fact
The working principle is similar, are the lock table, and then copy the data file. It can achieve online backup, but the effect is not satisfactory, it is not recommended. It copies only the table structure files and data files, copy index files at the same time, so restore slower. Example: BACK TABLE tbl_name TO '/ tmp / db_name /'; Note that you must have FILE
Permissions to execute this SQL, and the directory / tmp / db_name / must be writable by the mysqld user, the exported file can not overwrite the existing file to avoid security problems.
Restore BACKUP TABLE method of backup files, you can run the RESTORE TABLE statement to restore the data table. Example: RESTORE TABLE FROM '/ tmp / db_name /'; The permission requirements are similar to those described above.
3.2 SELECT INTO OUTFILE is to export the data into a normal text file, you can customize the way the field interval, easy to handle these data. example:
SELECT INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name; Note, you must have
FILE permissions to execute this SQL, and the file /tmp/db_name/tbl_name.txt must be mysqld
Users can write and export files can not overwrite existing files to avoid security problems.
With the SELECT INTO OUTFILE method to back up the 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 those described above. Before pouring into the data, the data sheet should already exist. If you are worried about data duplication, you can increase REPLACE
Keyword to replace existing records or IGNORE keyword to ignore them.
4. Enable binary log (binlog)
Binlog approach is relatively more flexible, worry and effort, but also can support incremental backup.
Enable binlog must restart mysqld. First, turn off mysqld, open my.cnf, add the following lines:
server-id = 1
log-bin = binlog
log-bin-index = binlog.index
Then start mysqld on it. Will produce binlog.000001 and binlog.index during operation, the previous file is mysqld
Record all the data update operation, the back of the file is all binlog index, can not be easily deleted. For binlog information please see the manual.
Need to back up, you can execute the SQL statement, let mysqld terminate the current binlog
Write, 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 from the server, you should back up
master.info and relay-log.info files.
Backup binlog file MySQL can provide tools mysqlbinlog to view, such as:
/ usr / local / mysql / bin / mysqlbinlog /tmp/binlog.000001 The tool allows you to display all the specified under the database
SQL statement, and can also limit the time range, quite convenient, detailed, please see the manual.
Recovery, you can use something similar to the following statement: / usr / local / mysql / bin / mysqlbinlog /tmp/binlog.000001
| mysql-uyejr-pyejr db_name The mysqlbinlog output SQL statements directly as input to execute it.
If you have free machines, may wish to use this way to back up. Because as a slave machine performance requirements are not so high, so low cost, low cost can achieve incremental backup but also share part of the data query pressure, why not?
5. Copy files Direct backup data files Compared to the previous methods, the most direct backup data files, fast and easy, the disadvantage is that basically can not achieve incremental backup.
In order to ensure the consistency of the data, you need to execute the following SQL statement before the backrest file: FLUSH TABLES WITH READ
LOCK; That is, the data in memory are flushed to the disk, while locking the data table to ensure that no new data is written in the copy process. This method of backup data recovery is also very simple, you can directly copy back to the original database directory.
Note that for Innodb typed tables, you also need to back up their log files, ib_logfile * files. Because when Innodb table is damaged, you can rely on these log files to recover.
Use rsync backup
rsync as a synchronization tool can also be used for backup, but to configure the server-side and client examples 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 the file modification time, so the backup database is all prepared, and the configuration is too much trouble.
Of course, you can refer to the following article