Using mysqldump to back up a database

mysqldump is a simple and efficient database backup program that transforms the structure of a database that needs to be backed up into a text file of the MySQL language that contains all the MySQL statements that were made to create the database. So when we recover, it is the program that reads these statements to recreate the database, create the table, and insert the data, but because it is single-threaded, it is slower for larger database operations.

The mysqldump supports InnoDB hot standby, MyISAM Win Bei, which can be incrementally backed up based on binary logs, with selective recovery based on the operating points recorded in the binary file file.

mysqldump Command Introduction

Grammar:

mysqldump [ OPTIONS] Database [tables]: Backing up a single library, or one or more tables specified by a library

mysqldump [options]–databases [OPTIONS] DB1 [DB2 DB3 ...] : Backing up one or more libraries

mysqldump [options]–all-databases [OPTIONS]: Backing Up all libraries

Other options:

-x,–lock-all-tables: Lock All tables

-l,–lock-tables: Locks the backed-up tables, which are available for MyISAM and InnoDB Win Bei

–single-transaction: Initiates a large single transaction implementation backup, enabling the hot provisioning of the InnoDB

–flush-logs: Before the backup, the request to the lock after the rolling log, can be used to implement the backup after the binary log write to the new log file, facilitate the binary log backup when looking for recovery points

        –master-data=[0|1|2]: Used to determine whether to add in the backup fileChange Master statement,0: not recorded;1: Record as change master statement;2: The change master statement recorded as a comment;The change master statement records the location of the log file in which the backup operation Records are located, plus this option will open–lock-all-tablesoption, backup completion will automatically close–lock-all-tablesoptions.

Mysqldump Backup and recovery steps

Request Lock: You can use –lock-all-tables, if you use –master-data=1|2, you do not need to specify this, if InnoDB is used –single-transaction option for hot standby

Scrolling logs: Available options –flush-logs

Experimental process:

Modify the MARIADB configuration file to store the log file in the log directory separately

650) this.width=650; "Src=" http://linuxu.qiniudn.com/wp-content/uploads/2015/02/ Wpid-655c6724bb017899ca2d3b691027646c_28163140.png "style=" font-size:10.5pt;line-height:1.5; "border=" 0 "/>

Create the required directory

Mkdir/mydata/binlog[[email protected]]# chown-r mysql.mysql/mydata/mkdir/backup

Import Experimental Database

[[email protected] ~]# MySQL </tem/hellodb.sql

650) this.width=650; "Src=" http://linuxu.qiniudn.com/wp-content/uploads/2015/02/ Wpid-655c6724bb017899ca2d3b691027646c_817eb964-5409-4a19-a290-c90a17a1f287.png "style=" FONT-SIZE:10.5PT; line-height:1.5; "border=" 0 "/>

MariaDB [(None)]> use hellodb;database changedmariadb [hellodb]> SHOW tables;+-------------------+| Tables_in_hellodb |+-------------------+| Classes | | COC | | Courses | | Scores | | Students | | Teachers | | TOC |+-------------------+7 rows in Set (0.00 sec)

Backing Up the database

[Email protected] ~]# mysqldump--database hellodb--flush-logs--master-data=2 >/backup/backup1.sql

We're working on the data after the backup is done.

2. MariaDB [hellodb]> CREATE TABLE city (id int auto_increment not null primary key,code varchar(30)); 

4. Query OK, 0 rows affected (0.21 sec)

6. 
   

8. MariaDB [hellodb]> SHOW TABLES;

10. +-------------------+

12. | Tables_in_hellodb |

14. +-------------------+

16. | city              |

18. | classes           |

20. | coc               |

22. | courses           |

24. | scores            |

26. | students          |

28. | teachers          |

30. | toc               |

32. +-------------------+

34. 8 rows in set (0.01 sec)

The new table has been added to the database and we simulated the deletion of the HELLODB database

2. MariaDB [hellodb]> DROP DATABASE hellodb;

4. Query OK, 8 rows affected (0.20 sec)

6. 
   

8. MariaDB [(none)]> SHOW DATABASES;

10. +--------------------+

12. | Database           |

14. +--------------------+

16. | information_schema |

18. | mysql              |

20. | performance_schema |

22. | test               |

24. +--------------------+

26. 4 rows in set (0.01 sec)

If we do not restore the files that we have backed up after the backup, we need to do a fixed point recovery in the binary file, in order to determine the recovery point, we first export all the binaries , The binaries used here are generated after our backup, because we back up the –flush-logs used, so the log files are regenerated after the backup.

[[email protected] binlog]# Mysqlbinlog mysql-bin.000003 >/backup/bin3.sql[[email protected] binlog]# vim/backup/ Bin3.sql

650) this.width=650; "Src=" http://linuxu.qiniudn.com/wp-content/uploads/2015/02/ Wpid-655c6724bb017899ca2d3b691027646c_a42b5724-c0f6-4a04-95c8-e3ec898dfdd2.png "style=" FONT-SIZE:10.5PT; line-height:1.5; "border=" 0 "/>

From this we can see that we delete the database is at 586 this point, so we can use 586 as a stop point to export binary files

[Email protected] binlog]# Mysqlbinlog--stop-position=586/mydata/binlog/mysql-bin.000003 >/backup/bin586.sql

Now we start restoring the database.

[[email protected] ~]# MySQL </backup/backup1.sql

View Database

MariaDB [(None)]> use hellodb;database changedmariadb [hellodb]> SHOW tables;+-------------------+| Tables_in_hellodb |+-------------------+| Classes | | COC | | Courses | | Scores | | Students | | Teachers | | TOC |+-------------------+7 rows in Set (0.01 sec)

There is no city table we created at this time

The following can be used to restore operations using a binary file

[[email protected] ~]# MySQL </backup/bin586.sql

Look again, the table city is back.

2. MariaDB [(none)]> USE hellodb;

4. Database changed

6. MariaDB [hellodb]> SHOW DATABASES;

8. +--------------------+

10. | Database           |

12. +--------------------+

14. | hellodb            |

16. | information_schema |

18. | mysql              |

20. | performance_schema |

22. | test               |

24. +--------------------+

26. 5 rows in set (0.00 sec)

28. 
    

30. MariaDB [hellodb]> SHOW TABLES;

32. +-------------------+

34. | Tables_in_hellodb |

36. +-------------------+

38. | city              |

40. | classes           |

42. | coc               |

44. | courses           |

46. | scores            |

48. | students          |

50. | teachers          |

52. | toc               |

54. +-------------------+

56. 8 rows in set (0.01 sec) 

So we have completed a data mistakenly deleted and based on the backup and binary file recovery process, from here can be seen that the binary files is very important, so you need to put the binary files in a storage place, try not to and database on a disk, of course, the backup data should be dedicated to storage, In this case, the database is unexpectedly or the database disk is damaged, and it can be recovered by backup and binary files.

Using mysqldump to back up a database