MySQL Backup and recovery

One: type of backup

1.1 by the state of the database server

1.2 by the type of backup file

1.3 backing up the contents of a database

Two: Backup strategy

2.1 What should backups be backed up?

2.2 Physical Backup or logical backup?

2.3 Backup Policy

Three: MySQL Backup Tools

3.1 mysqldump

3.2 mysqlhotcopy

3.3 Ibackup

3.4 Xtrabackup

1.1 by the state of the database server

Hot backup: Online backup, read and write operation not affected

Warm backup: Can read not write, only can perform read operation

Cold backup: Offline backup, read and write operations are not possible

1.2 by the type of backup file

Physical backup: Backing up data files directly

Logical Backup: Export data to a text file

1.3 backing up the contents of a database

Full backup: Backs up all data, possibly all of a library's data, or all of the library's data

Incremental backup: Backs up only data that has changed since the last full or incremental backup

Differential backup: Backs up only data that changed since the last full backup

2.1 What should backups be backed up?

Data, configuration files, binary logs, transaction logs

2.2 Physical Backup or logical backup?

Physical Backup: Fast speed

logical backup: Slow speed, loss of floating point accuracy, easy to use text processing tools directly to its processing, portable ability, cross- MySQL Server version

2.3 Backup Policy

Depending on the size of the data, different choices can be made, for example: Once a week is complete, once a day increments or variances, or one months a full backup, one-day incremental or differential backup

Full + incremental

full + diff

3.1mysqldump Logical Backup tool,MyISAM ( warm backup ) ,InnoDB ( hot backup )

3.1.1 to back up a specific table in a single database or library

DB_NAME[TB1][TB2] : back up the specified database, excluding the command to create the database, so you must manually create the database when you restore

Mysqldump-uroot-p mydb >/root/mydb.sql

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/70/wKioL1VkfWqjzsKLAACievyamzU103.jpg "style=" float: none; "title=" 1.png "alt=" Wkiol1vkfwqjzsklaacievyamzu103.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/75/wKiom1Vke9qg9GztAAFbZW6mxsU043.jpg "style=" float: none; "title=" 2.png "alt=" Wkiom1vke9qg9gztaafbzw6mxsu043.jpg "/>

Note: The above way is in MySQL backups performed during a server run can cause inconsistent data if data is inserted during the backup process. So be sure to lock the table when backing up.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/70/wKioL1VkfZijWMDIAACC9p7X_kc291.jpg "style=" float: none; "title=" 3.png "alt=" Wkiol1vkfzijwmdiaacc9p7x_kc291.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/75/wKiom1VkfAjDkuBMAABjEDsCMD8163.jpg "style=" float: none; "title=" 4.png "alt=" Wkiom1vkfajdkubmaabjedscmd8163.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/70/wKioL1VkfZixwilPAABM8f77cmc006.jpg "style=" float: none; "title=" 5.png "alt=" Wkiol1vkfzixwilpaabm8f77cmc006.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/75/wKiom1VkfAjj_03JAABhkPSSgY0911.jpg "style=" float: none; "title=" 6.png "alt=" Wkiom1vkfajj_03jaabhkpssgy0911.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/70/wKioL1VkfZiDmI6_AABY8Ety-sE242.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1vkfzidmi6_aaby8ety-se242.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/75/wKiom1VkfAmjWa5VAADmc56lbmM645.jpg "style=" float: none; "title=" 8.png "alt=" Wkiom1vkfamjwa5vaadmc56lbmm645.jpg "/>

Note: The last data is not in the backup file, so if you want to take the backup file to restore, is not able to recover to the server before the accident state, but can go to the binary log file to read out, but in the binary log file to find the time, How do you know when you just inserted the data? Restore should be a full backup + full backup after the event to recover, but how to know the binary log file from which the event started? or which location? At what point in time? And each time you have to manually record which binary log file is stored in.

3.1.2--master-date={0|1|2}

0 indicates that the binary log file and its event location are not logged

1 represents the Change MASTER to the way the binary log file location is logged, can be used to start the server directly after recovery

2 represents the Change MASTER to the binary log file location is logged, but is commented out by default

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/70/wKioL1VkfmHTzAh5AABcHeVPFaQ421.jpg "style=" float: none; "title=" 9.png "alt=" Wkiol1vkfmhtzah5aabchevpfaq421.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/75/wKiom1VkfNKjJA2tAABOsn4_2co964.jpg "style=" float: none; "title=" 10.png "alt=" Wkiom1vkfnkjja2taabosn4_2co964.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/75/wKiom1VkfNLAehUMAABnMF24vps828.jpg "style=" float: none; "title=" 11.png "alt=" Wkiom1vkfnlaehumaabnmf24vps828.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/70/wKioL1VkfmLhWH3IAABXecN98Rs520.jpg "style=" float: none; "title=" 12.png "alt=" Wkiol1vkfmlhwh3iaabxecn98rs520.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/70/wKioL1Vkfm-BLLbnAACNh-4G0kg894.jpg "style=" float: none; "title=" 13.png "alt=" Wkiol1vkfm-bllbnaacnh-4g0kg894.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/75/wKiom1VkfOCSTZH1AAD_zNVUdOg003.jpg "style=" float: none; "title=" 14.png "alt=" Wkiom1vkfocstzh1aad_znvudog003.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/70/wKioL1Vkfm-x5BYxAAFwPDaLvO0408.jpg "style=" float: none; "title=" 15.png "alt=" Wkiol1vkfm-x5byxaafwpdalvo0408.jpg "/>

3.1.3--lock-all-tables : Lock All Tables

3.1.4--flush-logs : Perform log scrolling

3.1.5 If the table type in the specified library is InnoDB , you can use --single-transaction start the hot spare and use it without the need to use --lock-all-tables option, it will automatically release locking

3.1.6 backing up multiple libraries

--all-databases : back up all libraries

--databases db_name1,db_name2 ... : back up the specified library

Note: Both options will be created automatically CreateDatabase command, so you don't have to specify the library manually when you restore

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/70/wKioL1Vkfq6xuj8PAABJhZlM8dc261.jpg "title=" 16.png "alt=" Wkiol1vkfq6xuj8paabjhzlm8dc261.jpg "/>

3.1.7 Other Options

--event : Backing up the event scheduler for a database

--routines : Backing up stored procedures and storage functions

--triggers : Backup Trigger

3.1.8 perform backup and restore

Backup strategy: Week full + Daily Increment

full backup:mysql>mysqldump

Incremental backup: Backing up binary log files (flush logs)

Simulate a full backup and then daily backup increments

1. If it is the first time to perform a full backup of the MyDB database

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/75/wKiom1VkfWyBqtAZAACxHfwGrqo672.jpg "style=" float: none; "title=" 17.png "alt=" Wkiom1vkfwybqtazaacxhfwgrqo672.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/70/wKioL1VkfvzDvue7AABJgzbaGPo326.jpg "style=" float: none; "title=" 18.png "alt=" Wkiol1vkfvzdvue7aabjgzbagpo326.jpg "/>

2. After performing a full backup, enter the database to do some operations to simulate these actions are made by the user the next day

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/75/wKiom1VkfY6DxX0CAACxWywQiRI296.jpg "title=" 19.png "alt=" Wkiom1vkfy6dxx0caacxwywqiri296.jpg "/>

3. then simulate the next day's incremental backup

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/75/wKiom1Vkfdfja97nAABTA1W3sYM510.jpg "title=" 20.png "alt=" Wkiom1vkfdfja97naabta1w3sym510.jpg "/>

4.MYSQL-BIN.000019 is the increment of the first day

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/70/wKioL1Vkf5uB-yAMAADNfoB2vVw952.jpg "style=" float: none; "title=" 21.png "alt=" Wkiol1vkf5ub-yamaadnfob2vvw952.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/75/wKiom1VkfgzT4vOjAABeQZNle3U018.jpg "style=" float: none; "title=" 22.png "alt=" Wkiom1vkfgzt4vojaabeqznle3u018.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/70/wKioL1Vkf5vgdUNrAAA6B5-go-s480.jpg "style=" float: none; "title=" 23.png "alt=" Wkiol1vkf5vgdunraaa6b5-go-s480.jpg "/>

5. Simulation to the next day, added some information

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/75/wKiom1VkfizDgLTIAACcDt7VoM0644.jpg "title=" 24.png "alt=" Wkiom1vkfizdgltiaaccdt7vom0644.jpg "/>

6. If the entire database is accidentally deleted, if the binary log files and data are not put in the same directory

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/70/wKioL1Vkf9egfMTOAABbwJ96QRk625.jpg "title=" 25.png "alt=" Wkiol1vkf9egfmtoaabbwj96qrk625.jpg "/>

7. Turn off the MySQL service, initialize the database, start the MySQL service

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/71/wKioL1VkgArxPog9AACFRwQyeJ0285.jpg "style=" float: none; "title=" 26.png "alt=" Wkiol1vkgarxpog9aacfrwqyej0285.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/75/wKiom1VkfnuB2SMoAAC4-_h7oyk582.jpg "style=" float: none; "title=" 27.png "alt=" Wkiom1vkfnub2smoaac4-_h7oyk582.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/71/wKioL1VkgAuxEw0kAACq-2DiBrk537.jpg "style=" float: none; "title=" 28.png "alt=" Wkiol1vkgauxew0kaacq-2dibrk537.jpg "/>

8. start using Backup files for database recovery

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/6D/75/wKiom1VkfsXhesMaAABgtibKbTE880.jpg "style=" float: none; "title=" 29.png "alt=" Wkiom1vkfsxhesmaaabgtibkbte880.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/71/wKioL1VkgFXz3_zbAADNg2y7piE747.jpg "style=" float: none; "title=" 30.png "alt=" Wkiol1vkgfxz3_zbaadng2y7pie747.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/75/wKiom1VkfsXxJp4SAACYkYXCTfk006.jpg "style=" float: none; "title=" 31.png "alt=" Wkiom1vkfsxxjp4saacykyxctfk006.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/71/wKioL1VkgFXwIhVkAABmLEolhYQ193.jpg "style=" float: none; "title=" 32.png "alt=" Wkiol1vkgfxwihvkaabmleolhyq193.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6D/75/wKiom1VkfsXgebzYAACJtC47TVY448.jpg "style=" float: none; "title=" 33.png "alt=" Wkiom1vkfsxgebzyaacjtc47tvy448.jpg "/>

Note: There is no return to the database at this point in time before the state of the incident, the need to perform a database before the moment of the binary log file for final recovery

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/6D/71/wKioL1VkgH-ihqzHAABhS9t8i_Q516.jpg "title=" 34.png "alt=" Wkiol1vkgh-ihqzhaabhs9t8i_q516.jpg "/>

This article is from the "Everyman" blog, please make sure to keep this source http://caoyt.blog.51cto.com/9978141/1655444

MySQL Backup and recovery