Backup and Recovery for MySQL

Backup and Recovery for MySQL

Backup type:

Full backup refers to backing up the entire data set (that is, the entire database)

Partial backup refers to the backup part of the data set (for example: Only one table is backed up)

Incremental backup refers to data that has changed since the last backup (incremental or complete); Features: Save space, restore trouble

Differential backup refers to data features that have changed since the last full backup: Wasted space, restore is simpler than incremental backups

Backup policy:

This article describes the first method of backup, using the system's own tool Mysqldump,mysqldump only supports full-scale backup

Mysqldump

Full Volume +binlog

MyISAM Storage Engine: Support Win Bei, lock table when backing up;

-X,--lock-all-tables: Locks All tables of all libraries, reads locks;

-L,--lock-tables: Locks All tables in the specified library;

InnoDB Storage Engine: Supports Win Bei and hot spares;

--single-transaction: Create a transaction to perform a backup based on this snapshot;

Other options:

-R,--routines: Backs up stored procedures and storage functions for the specified library;

--triggers: Backup triggers for the specified library;

-E,--events:

--master-data[=#]

1: Record as change MASTER to statement, this statement is not commented;

2: Record as change MASTER to statement, this statement is commented;

--flush-logs: After the lock table is completed, the log refresh operation is performed;

1. First we create a database and a table belonging to this database

MariaDB [(None)]> CREATE Database hidb;

MariaDB [(None)]> use HIDB; MariaDB [hidb]> CREATE TABLE students (ID int unsigned auto_increment primary key,name char () not null,age tinyint uns Igned,gender ENUM (' F ', ' M '), major varchar (200)); MariaDB [hidb]> desc students; View table structure

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/14/d193eaab26bf255317a82e8a691cf32c.png "title=" picture. png "Width=" height= "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:92px;height:25px; "alt=" D193eaab26bf255317a82e8a691cf32c.png "/>

2. Then insert some data into the table

Create an array, give HIDB the students table in this library automatically add some content, ask for automatic name increment, age 80 modulus plus 18, gender definition array random f or M

[[email protected] ~] #GENDER = (' F '   ' M ') [[email  Protected] ~] #echo  ${gender[0]}[[email protected] ~] #echo  ${GENDER[1]}[[email  Protected] ~] #for  i in {1..1000}; do mysql -e  "insert into  Hidb.students (Id,name,age,gender)  values (' $i ', ' stu$i ', ' $[$RANDOM%80+18] ', ' ${gender[$RANDOM%2]} '); "; donemariadb [hidb]> select * from students;...............|   998 | stu998  |   38 | F       | null  | |   999 | stu999  |   91 | F       | null  | |  1000  | stu1000 |   80 | M       | null  |+------+---------+------+--------+--------------+ 

3. Finally start the binary log

Create a directory to hold data and log files

[[email protected] ~] #mkdir  -pv /mydata/{data,logs} mkdir: created directory  '/mydata ' mkdir: created directory  '/mydata/data '   Storing data mkdir: created directory  '/mydata/logs ' [[email protected] ~] #chown  -R  mysql.mysql /mydata/*  change the owner and owning group under the/mydata/directory to mysql[[email protected] ~] #vim  /etc/ my.cnf.d/server.cnf  Edit configuration file specifies the path where the binary log is stored [server]log_bin = /mydata/logs/master-log   no suffix [[email protected] ~] #systemctl  restart mariadb  Restart Service for log files to take effect [[email  protected] ~]mysqlmariadb [hidb]> set @ @session .sql_log_bin=on;  to start binary logging mariadb  [hidb]> grant all on *.* to [email protected] ' 172.18.77.% '   identified by  ' CentOS ';  authorized users can connect via Telnet 

4, all the above environment to be ready; Next we prepare another server for backup data

[[Email protected] ~] #mysqldump-uroot-pcentos-h172.18.77.7--single-transaction-r--triggers-e--databases hidb--mas ter-data=2--flush-logs >/root/hidb-$ (Date +%f-%h-%m-%s). sql

[[Email protected] ~] #mysql < hidb-2017-11-14-11-38-09.sql backup recovery

The backup is done here, but how to back up the data after the backup from here, this is the incremental backup, using the binary log for backup

5, let us simulate, for example, we on the original main MySQL server deleted a row of data to insert a row of data

MariaDB [hidb]> Delete from students where id=999; MariaDB [hidb]> INSERT INTO students (Name,age,gender) VALUES (' Huangshang ', ' + ', ' M ');

The deleted and inserted data is not visible in the backup database on another host

[[Email protected] ~] #less hidb-2017-11-14-11-38-09.sql See these two files to see where to start adding new content for reference backup use

Match to is master-log.000006 this log, starting from 245 bytes

650) this.width=650; "src=" Https://s5.51cto.com/oss/201711/14/1fdbbce3350582358c3c0210a89d85e6.png "title=" Picture 1.png "alt=" 1fdbbce3350582358c3c0210a89d85e6.png "/>

[[email protected] logs] #mysqlbinlog-j 245 master-log.000006 >/tmp/binlog.sql[[email protected] logs] #scp/tmp/binl Og.sql 172.18.77.77:/root/

[[Email protected] ~] #mysql < binlog.sql backup recovery

6. Last Login View

999 rows of data deleted on the primary server and a newly inserted row of data have been backed up to the latest data

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/14/ef1901c7a9c3d8d88fccba6cf0b465e3.png "title=" Picture 2.png "Width=" height= "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:92px;height:25px; "alt=" Ef1901c7a9c3d8d88fccba6cf0b465e3.png "/>

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/14/1e5813508147a7e4eb04c0d612e8d853.png "title=" Picture 2.png "alt=" 1e5813508147a7e4eb04c0d612e8d853.png "/>

Note: since the host name of the above two hosts is bixia, it is possible to distinguish between which is the main MySQL, which is used for backup of the MySQL host, in order to facilitate their own sub-clear recommendation to modify the hostname is not the same, to prevent confusion.

Backup and Recovery for MySQL