Test and analysis of mysql cp replication and mysqldump backup

Backup policy

For different scenarios, we should develop different backup policies to back up databases. Generally, there are three backup policies:

Copy database files directly using cp and tar
Copy bin logs using mysqldump
Lvm2 snapshot copy BIN LOGS
Xtrabackup
The above solutions are pin-down for different scenarios
If the data volume is small, you can use the first method to directly copy database files.
If the data volume is okay, you can use the second method. First, use mysqldump to completely back up the database, and then regularly back up BINARY logs to achieve incremental backup.
If the data volume is normal and does not affect the service operation too much, you can use the snapshot of lvm2 to back up the data file, and then regularly back up the binary log to achieve incremental backup.
If the data volume is large and does not affect business operations too much, you can use the fourth method. After using xtrabackup for full backup, you can regularly use xtrabackup for incremental backup or differential backup.
Practical drills

Cp copy data file backup and recovery

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Sys |
+ -------------------- +
4 rows in set (0.03 sec)

Mysql> create database test;
Query OK, 1 row affected (0.03 sec)

Mysql> use test
Database changed
Mysql> show tables;
Empty set (0.00 sec)

Mysql> create table a (id int, name varchar (10 ));
Query OK, 0 rows affected (0.04 sec)

Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| A |
+ ---------------- +
1 row in set (0.00 sec)

Mysql> insert into a (id, name) values (1, 'gao ')
->;
Query OK, 1 row affected (0.01 sec)

Mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Mysql> select * from
->;
+ ------ +
| Id | name |
+ ------ +
| 1 | gao |
+ ------ +
1 row in set (0.00 sec)
 
[Root @ my57 mysql] # mkdir/backup

[Root @ my57 mysql] # cp-a/data/mysql/data/*/backup/

[Root @ my57 mysql] # ls/backup/
Auto. cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 my57.err my57.pid mysql mysqld_safe.pid performance_schema sys test xtrabackup_info

[Root @ my57 mysql] # rm-rf/data/mysql/data /*
[Root @ my57 mysql] # service mysql restart

ERROR! MySQL server PID file cocould not be found!
Starting MySQL... ERROR! The server quit without updating PID file (/data/mysql/data // my57.pid ).
At this time, it cannot be started. We can copy the backup file back and start it.

Cp-a/backup/*/data/mysql/data/
Replication and restoration of mysqldump

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Sys |
| Test |
+ -------------------- +
5 rows in set (0.02 sec)

Mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> show tables;
+ ---------------- +
| Tables_in_test |
+ ---------------- +
| A |
| B |
+ ---------------- +
2 rows in set (0.00 sec)
Mysql> select * from B;
+ ------ +
| Id |
+ ------ +
| 1 |
+ ------ +
1 row in set (0.00 sec)

# Remember the value of position before backup
Mysql> show master status
->;
+ ---------------- + ---------- + -------------- + -------------------- + ----------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ---------------- + ---------- + -------------- + -------------------- + ----------------- +
| Bin-log.000001 | 567 |
+ ---------------- + ---------- + -------------- + -------------------- + ----------------- +
1 row in set (0.00 sec)
Start backup

[Root @ my57 data] # mysqldump -- all-databases -- lock-all-tables>/backup. SQL
Create a database for incremental testing

Mysql> create database test1;
Query OK, 1 row affected (0.00 sec)

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Sys |
| Test |
| Test1 |
+ -------------------- +
6 rows in set (0.00 sec)

# Record the current position
Mysql> show master status;
+ ---------------- + ---------- + -------------- + -------------------- + ----------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ---------------- + ---------- + -------------- + -------------------- + ----------------- +
| Bin-log.000001 | 869 |
+ ---------------- + ---------- + -------------- + -------------------- + ----------------- +
1 row in set (0.00 sec)
Back up binary logs

Cp bin-log.000001/backup/
Stopping mysql is starting. Compilation and installation cannot be started and must be reinitialized.

[Root @ my57 data] # service mysql stop
Shutting down MySQL. SUCCESS!

# Simulate data file deletion
[Root @ my57 data] # rm-rf *

# The compiled and installed database cannot be started after deleting the data file
[Root @ my57 data] # service mysql start
Starting MySQL... ERROR! The server quit without updating PID file (/data/mysql/data // my57.pid ).

# Reinitializing mysql
[Root @ my57 data] #/usr/local/mysql/bin/mysqld -- initialize-insecure -- user = mysql
2016-06-14T02: 43: 04.084864Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use -- explicit_defaults_for_timestamp server option (see documentation for more details ).
2016-06-14T02: 43: 04.084944Z 0 [Warning] 'NO _ ZERO_DATE ', 'no _ ZERO_IN_DATE' and 'error _ FOR_DIVISION_BY_ZERO 'SQL modes shocould be used with strict mode. they will be merged with strict mode in a future release.
2016-06-14T02: 43: 04.084952Z 0 [Warning] 'NO _ AUTO_CREATE_USER 'SQL mode was not set.
2016-06-14T02: 43: 04.459619Z 0 [Warning] InnoDB: New log files created, LSN = 45790
2016-06-14T02: 43: 04.502310Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-06-14T02: 43: 04.567746Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b7fa4c2e-31d9-11e6-983a-080027fff0b0.
2016-06-14T02: 43: 04.571117Z 0 [Warning] Gtid table is not ready to be used. Table 'MySQL. Gtid_executed' cannot be opened.
2016-06-14T02: 43: 04.575259Z 1 [Warning] root @ localhost is created with an empty password! Please consider switching off the -- initialize-insecure option.

# Start
[Root @ my57 data] # service mysql start
Starting MySQL. SUCCESS!

# Use the original differential recovery method to find that the restoration was completed but test1 was missing
Mysql> source backup. SQL

Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Sys |
| Test |
+ -------------------- +
5 rows in set (0.00 sec)

# Restore test1 using mysqlbinlog binary. The above start position and end position are useful.
[Root @ my57 backup] # mysqlbinlog -- start-position = 567 -- stop-position = 869/backup/bin-log.000001 | mysql-uroot-p
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Sys |
| Test |
| Test1 |
+ -------------------- +
6 rows in set (0.00 sec)