Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysql backup detailed analysis, the content of the article is carefully selected and edited by the author, mysql backup detailed analysis has a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the next topic content.

A backup type according to whether offline 1) Hot backup, online backup, read and write will not be affected Difficult 2) warm backup, can read and write 3) cold backup, offline backup, read and write abort physical backup, copy data file logical backup, copy data export to text file full backup: backup all data, may be all data incremental backup of a database: backup only data that has changed since the last full backup or incremental backup Differential backup: only the data that has changed since the last full backup is backed up; backup methods can be combined freely

Second, the question to be considered in backup: 1 what should I backup?

Data, configuration files, binary logs, transaction logs 2 offline backup or online backup? MyISAM is not suitable for hot backup. If there is a problem in backup, it cannot be used for restore. It is recommended to use warm backup (lock all tables in a shared way) if you must use hot backup, use LVM for snapshot InnoDB hot backup tool: xtrabackup (physical backup), mysqldump (logical backup) it is best to use offline backup, which is more secure. Using master-slave implementation, you can stop the service of slave CVM at a certain time. Offline backup 3 physical backup or logical backup? Physical backup, advantages: directly copy data files, fast disadvantages: some data rely on the file system, migration capacity is limited logical backup advantages: easy to use text processing tools to deal with it directly, strong migration ability

Disadvantages: copy data exported to a text file, slow data, loss of floating-point precision (lost when converted to text) 4 backup strategy options? Full backup + incremental backup or full backup + differential backup

Three mysql backup tools

Mysqldump, logical backup tool, MyISAN (warm standby), InnoDB (hot backup) mysqlhotcopy, physical backup tool, cold backup file system backup tool cp: cold backup lvm: logical volume snapshot, almost hot backup mysql > lock tables user read; # mysql > unlock tables; mysql > FLUSH TABLES; create snapshot: release lock, and then copy data Note: InnoDB has a problem: after flush and lock tables, the database may also have data operations, such as things without commit

So make sure that the data is fully written to disk before snapshots can be taken.

Ibback, commercial tool extrabackup, open source tool network backup tool porconna-tool

4 mysqldump backup 1 backup a library mysqldump DB_NAME [tb1] [tb2] back up a single database, or a specific table in the library; when restoring, you need to manually create a database mysql > LOCK TABLES; lock table

Mysql > FLUSH TABLES; data is written to disk

Mysql > flush logs; binary log mysql > show bianry log; display binary log

Backup: mysqldump-u root-h localhost-p jiaowu > jiaowu_bak.sql

Restore: mysql jiaowu

< jiaowu_bak.sql --master-data={0|1|2} 0: 不记录二进制日志文件及路位置； 1：以CHANGE MASTER TO的方式记录位置，可用于恢复后直接启动从云服务器； 2：以CHANGE MASTER TO的方式记录位置，但默认为被注释；--lock-all-tables：锁定所有表--flush-logs: 执行日志flush；如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备，不需要锁表2 备份多个库： --all-databases: 备份所有库 --databases DB_NAME,DB_NAME,...: 备份指定库 --events --routines --triggers五 即时点还原1 备份策略：每周完全+每日增量 完全备份：mysqldump 增量备份：备份二进制日志文件(flush logs)2 完全备份# mysqldump -u root -p --flush-logs --master-data=2 --flush-logs --all-databases --lock-all-tables [--triggers --routines] --events >

Alldb.sql time past day 3 incremental backups # cd / data/mysql/

# mysqladmin flush-logs

# mysqlbinlog mysql-bin.000019 > / root/monday-increment.sql

3.1 incremental backup # mysqladmin flush-logs

# mysqlbinlog mysql-bin.000020 > / root/tuesday-increment.sql4 database corruption mysql could not initiate initialization of mysql database 4.1 restore full backup # mysql

< alldb.sql 4.2 恢复增量备份即可4.3 导出最后的二进制日志并恢复mysql>

Mysqlbinlog mysql-bin.000020 | mysql

Description: clear binaries to mysql > show binary logs

Mysql > purge binary logs to 'mysql-bin.000019'

# mysqladmin flush-logs

After reading the above detailed analysis of mysql backup, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.