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

Mysql Big Data Backup and Recovery

MySQL backup generally adopts full database backup and log backup;MySQL can use full backup and log backup to restore data to any position or time before the last binary log backup;mysql binary log records all additions, deletions and changes of the database, and also includes the execution time of these operations.

Binlog's purpose: master-slave synchronization, database recovery

Backup with binlog tool

Check if binlog is on, because it is off by default.

As can be seen from the above figure, off is closed, logbin is generally read-only, and it is opened under/etc/my.cnf

restart the database

After restart, check whether bin log is generated under directory, ××× indicates log

Create databases, tables, data

auto_increment when creating table

Restart a new log file

flush logs; #Regenerate new binaries

delete from tb1 where id=2; #Delete data with serial number 2 in id column for testing convenience

insert into tb1(name) values ('tom '); #Create a new data name named tom

view data

View binary logs on mysql

View binary log events

mysql> show binlog events;

+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+

| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.13-log, Binlog ver: 4 |

| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |

| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000001 | 219 | Query | 1 | 316 | create database test1 |

| mysql-bin.000001 | 316 | Anonymous_Gtid | 1 | 381 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000001 | 381 | Query | 1 | 525 | use `test1`; create table tb1(id int primary key auto_increment,name varchar(20)) |

| mysql-bin.000001 | 525 | Anonymous_Gtid | 1 | 590 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000001 | 590 | Query | 1 | 663 | BEGIN |

| mysql-bin.000001 | 663 | Table_map | 1 | 713 | table_id: 108 (test1.tb1) |

| mysql-bin.000001 | 713 | Write_rows | 1 | 758 | table_id: 108 flags: STMT_END_F |

| mysql-bin.000001 | 758 | Xid | 1 | 789 | COMMIT /* xid=10 */ |

| mysql-bin.000001 | 789 | Anonymous_Gtid | 1 | 854 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000001 | 854 | Query | 1 | 927 | BEGIN |

| mysql-bin.000001 | 927 | Table_map | 1 | 977 | table_id: 108 (test1.tb1) |

| mysql-bin.000001 | 977 | Write_rows | 1 | 1026 | table_id: 108 flags: STMT_END_F |

| mysql-bin.000001 | 1026 | Xid | 1 | 1057 | COMMIT /* xid=11 */ |

| mysql-bin.000001 | 1057 | Rotate | 1 | 1104 | mysql-bin.000002;pos=4 |#This is a log rotation event, caused by flush logs

+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------+

17 rows in set (0.00 sec)

View events for the specified binary log (the above command can only view default 1)

mysql> show binlog events in 'mysql-bin.000002';

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.13-log, Binlog ver: 4 |

| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |

| mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000002 | 219 | Query | 1 | 292 | BEGIN |

| mysql-bin.000002 | 292 | Table_map | 1 | 342 | table_id: 108 (test1.tb1) |

| mysql-bin.000002 | 342 | Delete_rows | 1 | 391 | table_id: 108 flags: STMT_END_F |

| mysql-bin.000002 | 391 | Xid | 1 | 422 | COMMIT /* xid=14 */ |

| mysql-bin.000002 | 422 | Anonymous_Gtid | 1 | 487 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

| mysql-bin.000002 | 487 | Query | 1 | 560 | BEGIN |

| mysql-bin.000002 | 560 | Table_map | 1 | 610 | table_id: 108 (test1.tb1) |

| mysql-bin.000002 | 610 | Write_rows | 1 | 654 | table_id: 108 flags: STMT_END_F |

| mysql-bin.000002 | 654 | Xid | 1 | 685 | COMMIT /* xid=15 */ |

+------------------+-----+----------------+-----------+-------------+---------------------------------------+

12 rows in set (0.00 sec)

Use the mysqlbinlog tool's-v (--verbose) option, which reconstructs line events into annotated pseudo-SQL statements. If you want to see more detailed information, you can give this option twice, such as-vv, so that you can include some data types and metadata comments, such as

First switch to the directory where binlog is located

mysqlbinlog mysql-bin.000001

mysqlbinlog -v mysql-bin.000001

mysqlbinlog -vv mysql-bin.000001

-h, -P, -p, -u, etc. These arguments are valid only if--read-from-remote-server is specified.

Recover exported binlog logs

Check whether the data of the table is complete after the recovery is completed

Common options are:

--start-datetime #reads log events from binary logs after a specified timestamp or local computer time

--stop-datetime #reads log events from binary logs prior to the specified timestamp or local computer time

--start-position #reads the specified position event position from the binary log as the start

--stop-position #reads the specified position event position from the binary log as the event cutoff

Because the deletion was skipped when the backup was done, zhangsan with id 2 was also restored.

Using mysqldump backup recovery

It is used for backup and data migration tools, generally in the case of relatively small data use such as a few G, when the data is relatively large, it is recommended not to use;mysqldump can be single (multiple) tables, single (multiple) databases and all databases for export operations;

mysqldump [options] db_name [tbl_name ...] #Export specified database or single table

mysqldump [options] --databases db_name ... #Export multiple databases

mysqldump [options] --all-databases #Export all

Backup database test1

mysqldump -p123456 --flush-logs test1 > /opt/test1.spl

#Backup the entire database and reopen a new binlog

mysql -p123456 test1

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.