MySQL Delete recovery

First, the simulation of the deletion of data table Recovery 12 binary logging feature enabled

Vim/etc/my.cnf[mysqld]log-bin

2 Full backup

Mysqldump-a-F--master-data=2--single-transaction |gzip >/data/all.sql.gz

3 Modifying data tables

Insert students (Name,age) VALUES (' a ', +);     Modify the first Insert students (Name,age) VALUES (' B ', +);     Modify the second flush logs;                                   Toggle binary Log Insert students (Name,age) VALUES (' e ', +);     Modify the first time

4 Delete Data Sheet by mistake

drop table students;                          Delete Table by mistake

5 users continue to modify other tables

Insert Teachers (Name,age) VALUES (' mage ', +);  Continue to modify after accidental deletion

6 Discovery Table Removal

Flush tables with read lock;            The discovery table is deleted, the first time the table is locked, and is set to read-only

7 Clean System Restore

Systemctl Stop mairadb      pause service rm-rf/var/lib/mysql/         Delete the datadir indicated in config to ensure that the user cannot access the server Vim/etc/my.cnf[mysqld] Skip-networking=1systemctl Start mariadb

8 Full backup Restore

gzip-d/data/all.sql.gz                      < /data/all.sql                         Import a full backup of All.sql

9 Restoring an incremental backup

Less/data/all.sql//View binary log location-change MASTER to master_log_file= ' mariadb-bin.000002 ', master_log_pos=245; View full backup information to determine the location of the binary recovery//Find all binary logs after mariadb-bin.000003 245 mariadb-bin.000002 mariadb-bin.000004mysqlbinlog  - -start-position=245/var/lib/mysql/mariadb-bin.000002 >/data/binlog.sqlmysqlbinlog   <  / Data/binlog.sql

10 Recovering users ' access

Vim/etc/my.cnf[mysqld]skip-networking=1 Delete this row

# #mysql-E "show Databases" |grep-ev "Database|information_schema|performance_schema" |sed-r ' s# (. *) #mysqldump-B \1 |gz IP >/data/\1-' Date +%f '. sql.gz# ' |bash

Back up all databases in databases except Information_schema&performance_schema

Second, Xtrabackup full backup and restore

1 full backup on original host

Xtrabackup  --backup--target-dir=/backups/Backup source host to/backupsscp-r/backups/192.168.134.192:/

2 on the target host

Systemctl Stop MARIADB/            /Stop Recovery host MARIADB service Mkdir/data/mysql-pvchown  mysql.mysql/data/mysql/   // Make sure directory permissions are fine vim/etc/my.cnfdatadir=/data/mysql/              //Modify configuration Xtrabackup  --prepare--target-dir=/backups   // Pre-recovery pre  -xtrabackup--copy-back|move-back  --target-dir=/backups//Copy or move preprocessed backups directory to Datadirchown-r mysql.mysql/data/mysql///Make sure all MySQL files are MySQL user and group systemctl restart MARIADB    //Completed

Iii. xtrabackup full, incremental backup and restore 1 implemented on the original host 1) full backup

MKDIR/BACKUPS/{FULL,INC1,INC2}-pvxtrabackup  --backup--target-dir=/backups/full/

2) change data for the first time

MySQL hellodb-e "Insert hellodb.students (name,age) VALUES (' a ', 18)"

3) First Incremental backup

Xtrabackup  --backup--target-dir=/backups/inc1/--incremental-basedir=/backups/full

4) The second time the data is modified

MySQL-  e "Insert hellodb.students (name,age) VALUES (' B ', 28);"

5) Second Incremental backup

Xtrabackup  --backup--target-dir=/backups/inc2/--incremental-basedir=/backups/inc1/scp-r/backups/  target Host :/

2 in target host restore 1) Clear data, stop service

Systemctl Stop MARIADBRM-RF  /data/mysql/*

2) Preprocessing database

Xtrabackup  --prepare--apply-log-only--target-dir=/backups/fullxtrabackup  --prepare--apply-log-only-- Target-dir=/backups/full  --incremental-dir=/backups/inc1/xtrabackup  --prepare  --target-dir=/ Backups/full  --INCREMENTAL-DIR=/BACKUPS/INC2

3) Copying data

Xtrabackup--copy-back--target-dir=/backups/full

4) Permissions and start-up services

Chown-r mysql.mysql/data/mysql/systemctl Start mariadb

MySQL implements master-slave-cascade replication MySQL Cascade replication (A->B->C)

MySQL master-slave architecture is the most used architecture in practice. The new two servers B and C, to replace the previous old server A, and B and C are the new master-slave relationship. Therefore, it is configured to cascade replication, to migrate data, and to switch easily.

The architecture diagram is as follows:

Master A------> Slave B (to this main from)------> Slave C

MySQL Replication
? Copy filter:
To copy only the specified database from the node, or specify a table for the specified database
? Two ways to implement:
? (1) Server Option: Primary server records only events related to a particular database in the binary log
Note: This item is associated with Binlog_format
See: Https://mariadb.com/kb/en/library/mysqld-options/#-binlogignore-db
binlog_do_db = database Whitelist list, multiple databases need multiple rows to implement
binlog_ignore_db = Database blacklist list
Problem: Binary-based restore is not possible;
? (2) When an event from the server Sql_thread in the replay trunk log is read only with a specific number
Events related to the library (specific table) and apply to the local
Problem: will cause network and disk IO waste
MySQL Replication
? replication filter-related variables from the server
replicate_do_db= Specifies the whitelist of the replication library
? replicate_ignore_db= Specify the Replication library blacklist
Replicate_do_table= Specify a whitelist for replication tables
Replicate_ignore_table= Specify a blacklist of replicated tables
? replicate_wild_do_table= foo%.bar% supports wildcard characters
? replicate_wild_ignore_table=

Experimental Machine IP

192.168.134.191 Primary server >192.168.134.192 from server (master-slave replication)

First, the Master node configuration: (1) enable the binary log

Vim/etc/my.cnf[mysqld]log_bin

#log_bin启用二进制日志

(2) Set a globally unique ID number for the current node

Vim/etc/my.cnf[mysqld]server_id=1

(3) Create a user account with copy rights

MySQL > GRANT REPLICATION SLAVE on * * to ' Wang ' @ ' HOST ' identified by ' CentOS ';

Second, from the node configuration: (1) Start the relay log

vim/etc/my.cnf[mysqld]server_id=2

(2) Connect to the primary server with a user account with copy rights and start the replication thread

MySQL > Change MASTER to  master_host= ' 192.168.134.191 ',  master_user= ' Wang ',  master_password= ' CentOS ',  master_port=3306,  master_log_file= ' mariadb-bin.000001 ',  master_log_pos=245;

(3) Start slave

MySQL >  START SLAVE

This is when MySQL's master-slave replication is complete.

How to configure and start the slave node if the primary node has been running for some time and there is a lot of data

Experimental machine: 192.168.134.191 Primary server >192.168.134.192 from server >192.168.134.193 n slave server (cascade replication)

Recovering data from the server by backup

When copying the starting location for a backup, the binary log file and its POS

If you want to enable Cascade replication, you need to use the following configuration on the slave server (192.168.134.192)

Vim/etc/my.cnf[mysqld]log_bin    enable binary logging Log_slave_updates

#一般情况下slave不会把从master接收到的binlog记录写入自己的binlog, this parameter causes slave to write Binlog from master to its binlog via SQL thread, But the premise is that slave must open its own binlog, this parameter is generally used for cascading replication, for example, a copy to B,b to copy to C, then B will turn on this parameter.

MySQL Semi-synchronous implementation

Primary service configuration:

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

mysql> SET GLOBAL  rpl_semi_sync_master_enabled=1;

mysql> SHOW VARIABLES like '%semi% ';

mysql> SHOW STATUS like '%semi% ';

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;

MySQL Delete recovery