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命令备份数据库。
[ ~]# 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】表定义已经被修改，请重新开启事务。
场景三发生时备份会失败。
[ 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 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 ceshi2020-06-07T12:47:35.128931Z 12 Query SHOW CREATE DATABASE IF NOT EXISTS `ceshi`2020-06-07T12:47:35.129049Z 12 Query SAVEPOINT sp2020-06-07T12:47:35.129201Z 12 Query show tables2020-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=12020-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_database2020-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 sp2020-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=12020-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_database2020-06-07T12:47:35.135196Z 12 Query SHOW TRIGGERS LIKE ‘t2‘ 2、 查看备份文件all.sql [ ~]# 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 @@CHARACTER_SET_CLIENT */; /*!40101 SET @@CHARACTER_SET_RESULTS */; /*!40101 SET @@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @@TIME_ZONE */; /*!40103 SET TIME_ZONE=‘+00:00‘ */; /*!40014 SET @@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO‘ */; /*!40111 SET @@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文件发送到备库
[ ~]# scp all.sql 192.168.150.103:~ 192.168.150.103‘s password: all.sql 100% 845KB 19.0MB/s 00:00 [ ~]#
4、
登录103服务器，导入数据
[ ~]# mysql -uroot -p123456 < all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[ ~]#
[ ~]# mysql -uroot -p123456Type ‘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备。
相关推荐
zhanbuquan 2020-08-15
云之飞踢 2020-08-15
swazerz 2020-07-28
Andrea0 2020-07-04
zjuwangleicn 2020-06-25
景泽元的编程 2020-06-21
txt 2020-06-14
MissFuTT 2020-06-14
chenjiazhu 2020-06-08
msmysql 2020-06-06
inhumming 2020-06-06
muzirigel 2020-05-27
zjx0 2020-05-19
webliyang 2020-05-14
CoderJiang 2020-04-22
MySQL 2020-04-20
achiverhai 2020-05-08
xjd0 2020-04-26