mysqldump 逻辑备份搭建备库
在前两节的基础环境上，使用mysqldump命令再搭建一个备库，
环境：
主库：192.168.150.101
备库1:192.168.150.102
备库2（当前服务器）：192.168.150.103
备库2已经安装好MySQL数据库，并修改my.cnf配置文件中server_id=3
1、
到主库使用mysqldump命令备份数据库。
[root@host101 ~]# mysqldump -uroot -p123456 -h127.0.0.1 -P3306 --single-transaction --all-databases --master-data=2 -E -R >all.sql
加入--single-transaction参数，在备份过程中不会锁表，开启通用日志，看下不锁表备份原理.
在正式备份前会有一次flush table with read lock锁库的操作，随便设置隔离级别为可重复读，拿到当前binlog位点后，再解锁，之后开始正式备份数据。
从通用日志中看到，备份会先获取表结构，再依次备份各个表中的数据。
如果在备份过程中主库执行DDL可能会导致备份失败，
场景一：DDL操作发生在表已经备份完成了，没有问题。
场景二：DDL操作发生在表正在备份过程中，会遇到MDL锁。会话状态是 【Waiting for table metadata lock】
场景三：DDL操作发生在表还没有备份，当备份到这个表的时候，会报错【ERROR 1412 (HY000): Table definition has changed, please retry transaction】表定义已经被修改，请重新开启事务。
场景三发生时备份会失败。
[root@host101 data]# tail -f host101.log /usr/local/mysql/bin/mysqld, Version: 5.7.29-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument 2020-06-07T12:18:48.555923Z 11 Connect root@127.0.0.1 on using SSL/TLS 2020-06-07T12:18:48.556383Z 11 Query /*!40100 SET @@SQL_MODE='' */ 2020-06-07T12:18:48.556956Z 11 Query /*!40103 SET TIME_ZONE='+00:00' */ 2020-06-07T12:18:48.557430Z 11 Query FLUSH /*!40101 LOCAL */ TABLES #关闭所有表 2020-06-07T12:18:48.560743Z 11 Query FLUSH TABLES WITH READ LOCK #设置数据库为只读模式 2020-06-07T12:18:48.561254Z 11 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ #设置当前会话隔离级别为可重复读 2020-06-07T12:18:48.561635Z 11 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ #开启事务 2020-06-07T12:18:48.562073Z 11 Query SHOW VARIABLES LIKE 'gtid\_mode' #获取GTID模式 2020-06-07T12:18:48.566816Z 11 Query SELECT @@GLOBAL.GTID_EXECUTED #获取GTID位点 2020-06-07T12:18:48.567266Z 11 Query SHOW MASTER STATUS #获取当前binlog位点 2020-06-07T12:18:48.567644Z 11 Query UNLOCK TABLES #解锁 2020-06-07T12:18:48.568043Z 11 Query SELECT .....2020-06-07T12:18:48.569246Z 11 Query SELECT .....2020-06-07T12:18:48.570099Z 11 Query SHOW DATABASES
2020-06-07T12:47:35.126606Z 12 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2020-06-07T12:47:35.128796Z 12 Init DB ceshi
2020-06-07T12:47:35.128931Z 12 Query SHOW CREATE DATABASE IF NOT EXISTS `ceshi`
2020-06-07T12:47:35.129049Z 12 Query SAVEPOINT sp
2020-06-07T12:47:35.129201Z 12 Query show tables
2020-06-07T12:47:35.129503Z 12 Query show table status like 't1'
2020-06-07T12:47:35.130266Z 12 Query SET SQL_QUOTE_SHOW_CREATE=1
2020-06-07T12:47:35.130386Z 12 Query SET SESSION character_set_results = 'binary'
2020-06-07T12:47:35.130492Z 12 Query show create table `t1` #拿到t1表结构
2020-06-07T12:47:35.130677Z 12 Query SET SESSION character_set_results = 'utf8'
2020-06-07T12:47:35.130826Z 12 Query show fields from `t1`
2020-06-07T12:47:35.131325Z 12 Query show fields from `t1`
2020-06-07T12:47:35.131692Z 12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1` #开始备份t1数据
2020-06-07T12:47:35.131957Z 12 Query SET SESSION character_set_results = 'binary'
2020-06-07T12:47:35.132061Z 12 Query use `ceshi`
2020-06-07T12:47:35.132166Z 12 Query select @@collation_database
2020-06-07T12:47:35.132283Z 12 Query SHOW TRIGGERS LIKE 't1'
2020-06-07T12:47:35.132608Z 12 Query SET SESSION character_set_results = 'utf8'
2020-06-07T12:47:35.132779Z 12 Query ROLLBACK TO SAVEPOINT sp
2020-06-07T12:47:35.132890Z 12 Query show table status like 't2'
2020-06-07T12:47:35.133291Z 12 Query SET SQL_QUOTE_SHOW_CREATE=1
2020-06-07T12:47:35.133392Z 12 Query SET SESSION character_set_results = 'binary'
2020-06-07T12:47:35.133493Z 12 Query show create table `t2` #拿到t2表结构
2020-06-07T12:47:35.133666Z 12 Query SET SESSION character_set_results = 'utf8'
2020-06-07T12:47:35.133865Z 12 Query show fields from `t2`
2020-06-07T12:47:35.134295Z 12 Query show fields from `t2`
2020-06-07T12:47:35.134682Z 12 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2` #开始备份t2数据
2020-06-07T12:47:35.134871Z 12 Query SET SESSION character_set_results = 'binary'
2020-06-07T12:47:35.134975Z 12 Query use `ceshi`
2020-06-07T12:47:35.135079Z 12 Query select @@collation_database
2020-06-07T12:47:35.135196Z 12 Query SHOW TRIGGERS LIKE 't2'
2、
查看备份文件all.sql
[root@host101 ~]# head -35 all.sql -- MySQL dump 10.13 Distrib 5.7.29, for linux-glibc2.12 (x86_64) -- -- Host: 127.0.0.1 Database: -- ------------------------------------------------------ -- Server version 5.7.29-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; SET @@SESSION.SQL_LOG_BIN= 0; -- -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED='9fef2262-97b1-11ea-92b5-000c29cd3ff3:1-6, adc4403d-97b2-11ea-b803-000c298076e0:1-65'; #由于我之前的测试环境是运行在GTID复制模式下，所以备份会有这项信息，跳过这些GITD位点。 -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=234; #普通的复制的binlog位点信息。 -- -- Current Database: `ceshi` --
3、
将all.sql文件发送到备库
[root@host101 ~]# scp all.sql root@192.168.150.103:~ root@192.168.150.103's password: all.sql 100% 845KB 19.0MB/s 00:00 [root@host101 ~]#
4、
登录103服务器，导入数据
[root@localhost ~]# mysql -uroot -p123456 < all.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@localhost ~]#
[root@localhost ~]# mysql -uroot -p123456
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; #ceshi chai test这些数据库已经导入进来了。
+--------------------+
| Database |
+--------------------+
| information_schema |
| ceshi |
| chai |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
7 rows in set (0.01 sec)
配置主从同步
mysql> change master to master_host='192.168.150.101',master_port=3306,master_user='replicator',master_password='123456',master_auto_position=1; #使用gtid复制模式去连接主库。 Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G #IO、和SQL线程状态都是YES了 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.150.101 Master_User: replicator Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000023 Read_Master_Log_Pos: 1083 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000023 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 234 Relay_Log_Space: 1427 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 2021 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 9fef2262-97b1-11ea-92b5-000c29cd3ff3 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Waiting for dependent transaction to commit Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 9fef2262-97b1-11ea-92b5-000c29cd3ff3:7-10 Executed_Gtid_Set: 9fef2262-97b1-11ea-92b5-000c29cd3ff3:1-6, adc4403d-97b2-11ea-b803-000c298076e0:1-65 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
在主库新建ceshi3
mysql> create database ceshi3; Query OK, 1 row affected (0.00 sec) mysql>
在103服务器备库查看，ceshi3 库已经同步过来了。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ceshi | | ceshi3 | | chai | | mysql | | performance_schema | | sys | | test | +--------------------+ 8 rows in set (0.04 sec) mysql>
总结：备库2搭建完成，目前加构是1主2备。
