mysqldump搭建主从的注意事项

原创进击的CJR 2022-04-24

2309

主库已经上了生产，现在要用mysqldump搭建一个备库，如何在线上处理

mysqldump重要参数详解

master-data

# --master-data=2功能：
（1）在备份时，会自动记录，二进制日志文件名和位置号
0  默认值
1  以change master to命令形式，可以用作主从复制
2  以注释的形式记录，备份时刻的文件名+postion号
（2）自动锁表
（3）如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，实际上是实现快照备份。复制

single-transaction

# --single-transaction
开启InnoDB表的一致性快照备份，可以不锁表。
保证数据备份一致性的参数
原理：在备份开启的时候，显式的开启RR隔离级别来进行备份，
RR隔离级别读取的是事务开启时的数据快照，
所以备份的时候是备份开启时间点的数据。
然后这种方式下，加锁的时间很少。可以在尽量不影响业务的情况下，保证数据的一致性。
复制

测试

mysqldump -uroot -S /tmp/mysql.sock --single-transaction --master-data=1 --set-gtid-purged=OFF --hex-blob --triggers --routines --events -A > /tmp/1.sql

2022-04-24T07:23:06.435443Z        12 Query     FLUSH /*!40101 LOCAL */ TABLES
2022-04-24T07:23:06.435502Z        12 Query     FLUSH TABLES WITH READ LOCK
2022-04-24T07:23:06.435563Z        12 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-04-24T07:23:06.435626Z        12 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2022-04-24T07:23:06.435692Z        12 Query     SHOW MASTER STATUS
2022-04-24T07:23:06.435787Z        12 Query     UNLOCK TABLES
复制

查看备份文件

[root@chen ~]# head -n 40 /tmp/1.sql  
-- MySQL dump 10.13  Distrib 5.7.33, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version       5.7.33-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 */;

--
-- Position to start replication or point-in-time recovery from
--

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=194;

--
-- Current Database: `mysql`
--复制

可以看到change master 没有加注释,并且在备份中有flush table with read lock 操作获取到binlog位点后立即解锁

mysqldump -uroot -S /tmp/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=OFF --hex-blob --triggers --routines --events -A > /tmp/1.sql

2022-04-24T07:30:49.562809Z        13 Query     FLUSH /*!40101 LOCAL */ TABLES
2022-04-24T07:30:49.570847Z        13 Query     FLUSH TABLES WITH READ LOCK
2022-04-24T07:30:49.570926Z        13 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-04-24T07:30:49.570976Z        13 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2022-04-24T07:30:49.571039Z        13 Query     SHOW MASTER STATUS
2022-04-24T07:30:49.571129Z        13 Query     UNLOCK TABLES复制

查看备份文件

[root@chen ~]# head -n 30 /tmp/2.sql
-- MySQL dump 10.13  Distrib 5.7.33, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version       5.7.33-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 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=194;

--
-- Current Database: `mysql`
--复制

可以看到change master 加了注释,并且在备份中有flush table with read lock 操作获取到binlog位点后立即解锁

mysqldump -uroot -S /tmp/mysql.sock --single-transaction --master-data=0 --set-gtid-purged=ON --hex-blob --triggers --routines --events -A > /tmp/4.sql

2022-04-24T08:28:05.952221Z        23 Connect   root@localhost on  using Socket
2022-04-24T08:28:05.952348Z        23 Query     /*!40100 SET @@SQL_MODE='' */
2022-04-24T08:28:05.952438Z        23 Query     /*!40103 SET TIME_ZONE='+00:00' */
2022-04-24T08:28:05.952573Z        23 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-04-24T08:28:05.952653Z        23 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2022-04-24T08:28:05.952774Z        23 Query     SHOW VARIABLES LIKE 'gtid\_mode'
2022-04-24T08:28:05.954390Z        23 Query     SELECT @@GLOBAL.GTID_EXECUTED
2022-04-24T08:28:05.954475Z        23 Query     UNLOCK TABLES复制

查看备份文件

-- Server version       5.7.33-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='2f49e28c-bacf-11ec-9945-5254000a56df:1-15090';

--
-- Current Database: `mysql`
--
复制

可以看到master-data不记录change master

set-gtid-purged

mysqldump -uroot -S /tmp/mysql.sock --single-transaction --master-data=2 --set-gtid-purged=ON --hex-blob --triggers --routines --events -A > /tmp/3.sql

2022-04-24T07:53:03.096378Z        17 Query     FLUSH /*!40101 LOCAL */ TABLES
2022-04-24T07:53:03.099390Z        17 Query     FLUSH TABLES WITH READ LOCK
2022-04-24T07:53:03.099472Z        17 Query     SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2022-04-24T07:53:03.099530Z        17 Query     START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2022-04-24T07:53:03.099624Z        17 Query     SHOW VARIABLES LIKE 'gtid\_mode'
2022-04-24T07:53:03.101047Z        17 Query     SELECT @@GLOBAL.GTID_EXECUTED
2022-04-24T07:53:03.101161Z        17 Query     SHOW MASTER STATUS
2022-04-24T07:53:03.101242Z        17 Query     UNLOCK TABLES复制

备份文件

-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version       5.7.33-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='2f49e28c-bacf-11ec-9945-5254000a56df:1-15090';
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=194;
--
-- Current Database: `mysql`复制

可以看到set-gtid-purged=ON，在备份文件中，有了关闭sql_log_bin的操作，还有个purged的操作

如果是用备份做从库的话，需要将这个参数打开，默认是打开的，因为如果不关闭sql_log_bin会生成本地GTID，如果是要应用到主库的话，需要将该参数设置为--set-gtid-purged=OFF

基于GTID的主从应该如何搭建

（1）主备开启GTID并设置server_id

server_id=572533306
gtid-mode=on
enforce-gtid-consistency=on
binlog_format=ROW  复制

（2）建复制用户

create user 'repl'@'%' identified by 'repl123@UIOP';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
复制

（3）导出数据

前面测试已经证明，备份用作从库需要--set-gtid-purged=ON

并且需要--master-data=0来保证主库不被锁表

mysqldump -uroot -S /tmp/mysql.sock   --single-transaction 
--master-data=0 --set-gtid-purged=ON --hex-blob --triggers --routines 
--events -A > /tmp/all.sql

（4）从库导入数据

source /tmp/all.sql

（5）从库执行reset master

防止gtid_executed表在导入数据的过程中被覆盖，最好重置gtid_purged变量

（6）提起gtid_purged变量，执行

head -n 40 /tmp/all.sql

--

SET @@GLOBAL.GTID_PURGED='2f49e28c-bacf-11ec-9945-5254000a56df:1-15090';

--复制

执行

SET @@GLOBAL.GTID_PURGED='2f49e28c-bacf-11ec-9945-5254000a56df:1-15090';复制

（7）使用MASTER_AUTO_POSITION建立同步

CHANGE MASTER TO
MASTER_HOST='1.1.1.1',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1;
复制

（8）启动slave

start slave ;复制

墨力计划 mysql

最后修改时间：2023-05-25 15:50:16

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

2人已赞赏

文章被以下合辑收录

MySQL知识网络（共38篇）

MySQL的地基

筱悦星辰

1年前

学会长板思维，找到一件自己擅长的事情，然后将它练到精通。

1年前

墨天轮福利君

2年前

您好，您的文章已入选墨力原创作者计划合格奖，10墨值奖励已经到账请查收！ ❤️我们还会实时派发您的流量收益。

2年前

mysqldump搭建主从的注意事项

mysqldump重要参数详解

master-data

single-transaction

set-gtid-purged

基于GTID的主从应该如何搭建

（1）主备开启GTID并设置server_id

（2）建复制用户

（3）导出数据

（4）从库导入数据

（5）从库执行reset master

（6）提起gtid_purged变量，执行

（7）使用MASTER_AUTO_POSITION建立同步

（8）启动slave

文章被以下合辑收录

评论

相关阅读