MySQL database backup and recovery

(1) Full-scale backup: Backup of all data in the database

Mysqldump-u root-p-f-b-A |gzip >/server/backup/mysqlbak_$ (date +%f). sql.gz Backup format: mysqlbak_2016-04-19.sql.gzmysq Ldump-u root-p-f-b-A |gzip >/server/backup/mysqlbak_$ (date + "%d-%m-%y"). Sql.gz Backup format: mysqlbak_19-04-2016.sql . GZ backs up a library (for example, the Zabbix library) mysqldump-u root-p-f-b Zabbix |gzip >/server/backup/mysqlbak_$ (date +%f). sql.gz Backup format: Mysqlba K_2016-04-19.sql.gzmysqldump-u root-p-f-b Zabbix |gzip >/server/backup/mysqlbak_$ (date + "%d-%m-%y"). Sql.gz Preparation Format is: mysqlbak_19-04-2016.sql.gz

2. Incremental backup

Incremental data is the new data updated since the last full-scale backup, and for MySQL, binlog logs are incremental backups.

MySQL backup data: Backup retention time (180 days a week)

Backup by day

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M01/86/3D/wKioL1e5wCWSWNC5AAEgBv_Z1rk545.png-wh_500x0-wm_3 -wmp_4-s_3919211754.png "title=" Mysql.png "style=" Float:none; "alt=" wkiol1e5wcwswnc5aaegbv_z1rk545.png-wh_50 "/ >

Advantages:

Short recovery times

Low maintenance costs

Disadvantages:

Occupy more space

Consumes more system resources

Frequent lock tables affect user experience

According to the comprehensive preparation

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M00/86/3D/wKiom1e5wCWiYz9AAAGd4gH3Kd0666.png-wh_500x0-wm_3 -wmp_4-s_3232461653.png "title=" Mysql2.png "style=" Float:none; "alt=" wkiom1e5wcwiyz9aaagd4gh3kd0666.png-wh_50 "/ >

Advantages:

Less space occupied

Consumes less system resources

The number of lock tables is low

Disadvantages:

High Maintenance costs

Recovering trouble

Long time

Enterprise Use scheme:

(1) The total amount of small and medium-sized enterprises is generally once a day, the operating flow underestimated implementation of full standby, the backup will lock the table;

(2) The single database How to increment with rsync (with the timing of the task frequency large point or inotify) master-slave replication, all the Binlog backup to the remote server, as far as possible from replication;

Examples of incremental backups:

rsync-avz/var/lib/mysql/mysql-bin.00* [Email Protected]:backup--password-file=/etc/rsync.passwd

(3) Large company Zhou Bei. 600 points a week, full, next Sunday-next week, 00 increments.

Advantages: Save backup time, reduce backup pressure, disadvantage; incremental binlog log file copy too many, restore trouble

(4) One master more from there will be a backup from the library, delayed synchronization

When does MySQL's mysqldump backup come in handy?

1. When migrating or upgrading a database

2. Increase from Kushi

3, if due to hardware or special abnormal situation, the host or from the library down, master and slave can switch without backup

4, man-made DDL, DML statements, master and slave libraries do not, all libraries will execute, at this time need to backup

5, cross-room disaster preparedness, need to backup to offsite

Generally by the artificial or the logical way of the program in the database execution time error operation, resulting in data loss and so on, need data recovery.

Prerequisites for MySQL incremental restore

1. Turn on MySQL log-bin log

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/86/3D/wKioL1e5x2yTkknQAARSQrg4Xxw359.png "title=" Mysqldump2.png "alt=" Wkiol1e5x2ytkknqaarsqrg4xxw359.png "/>

In general, all data is written to a stop. Can be rejected with a firewall or the load balancer will stop

This article is from the "Keep Dreaming" blog, please be sure to keep this source http://dreamlinux.blog.51cto.com/9079323/1840925

MySQL database backup and recovery