Mysql不停机不锁表主从配置

1. master配置

1. 主库my.cnf配置

[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=100 #设置server-id,设置为当前ip的最后一个段的数字,这样不会乱
binlog-do-db=sakzss #需要同步的数据库，不在内的不同步。(不添加这行表示同步所有)
binlog-ignore-db=performance_schema
1
2
3
4
5

配置完成后重启mysql

2. 配置mysql用户

1. mysql命令行下配置用户

CREATE USER 'slave'@'%' IDENTIFIED BY '密码';#创建用户
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';#分配权限
flush privileges;   #刷新权限
1
2
3

2. 查看master状态

SHOW MASTER STATUS;
1

2.从库配置

docker快速新建从库命令

docker run -p 3307:3306 --name mysql-xxx -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=xxx -d mysql:5.7
1

1. 配置my.cnf设置从库id

[mysqld]
server-id=110 #设置server-id，192.168.0.110
slave_skip_errors=1062 #跳过主键冲突 Duplicate entry
gtid_mode=on #开启gtid
enforce_gtid_consistency=on
#如果需要同步的数据库名相同
replicate-do-db=zxkang_sync  #需要同步的数据库名。如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。

#如果需要同步的数据库名不同
replicate-rewrite-db=zxkang_sync->test  # master 上的数据库名为 zxkang_sync ， slave 上的库名为 test

replicate-ignore-db=mysql  #不同步mysql系统数据库
slave-skip-errors = all   #跳过所有的错误错误，继续执行复制操作
1
2
3
4
5
6
7
8
9
10
11
12
13

配置完成后重启服务。

1. mysql下配置权限

CHANGE MASTER TO MASTER_HOST='192.168.0.110', MASTER_USER='slave', MASTER_PASSWORD='密码', MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=154;
1

3 .锁master表并导出数据

1. master节点锁表导出数据：

mysql>  FLUSH  TABLES WITH READ LOCK; #锁定表
mysql> show master  status; #显示position
mysqldump   -uroot   -p   --databases xxx   > xxx.sql
1
2
3

2. slave节点下导入数据

mysql> source xxx.sql; #导入数据
CHANGE MASTER TO MASTER_HOST='192.168.0.110', MASTER_USER='slave', MASTER_PASSWORD='密码', MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=154; #slave节点重置position
mysql> start slave; # 启动同步任务
mysql> SHOW SLAVE STATUS\G;#检查同步状态
1
2
3
4

3. master节点解锁

UNLOCK  TABLES;
1

3. 不锁master同步数据

1. master 导出数据

不停机实现主从搭建的关键点就是以下两个参数：

• –single-transaction
• –master-data

master-data参数主要用来记录主库的binlog_file和pos，它有两个值，分别是：

1:在mysqldump过程中，将binlogfile和pos信息记录在sql中，并且不是以注释信息的方式记录，这样在执行导入的时候自动执行这部分信息

2:在mysqldump过程中，将binlogfile和pos信息以注释的方式记录在sql中

single-transaction参数则是通过提交单一事务来确保数据一致性，通过在FLUSH TABLES WITH READ LOCK 后添加START TRANSACTION 语句，开启单一事务，此时加锁，仅仅是为了获取准确的master-data中的binlogfile和pos信息，在开启事务后，锁已经释放了，所以对业务影响很小

通过以上两个参数，可以在不长时间锁表的情况下获取准确的binlogfile和pos信息，从而完成主从配置

mysqldump -uroot -p -h192.168.0.100 --single-transaction --master-data=2 --databases xxx xxx > databases.sql
1

2.slave导入数据

source databases.sql
1

查看master_log_file和master_log_pos 参数通过master-data=2参数导出的，在sql文件里面开头部分。查看

head -n 50 databases.sql
1

从库配置主从参数：

change master to master_host='xxx', master_user='xxx', master_password='xxx', master_port=3306, master_log_file='mysql-bin.000252', master_log_pos=001;
1

3. 开启slave同步

start slave
show slave status\G; #查看从库状态
1
2

4. 设置从库为只读

mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.03 sec)

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

其他命令及操作

slave停止或重置：

reset slave;
stop slave;
1
2