Mysql主从复制

1
2
3
4
5
6
7

# 禁用dns解析(只能使用ip)
skip-name-resolve

# 二进制日志控制 
server_id = 10    # 每个节点id不同 
log-bin = /data/mysql57/3307/binlog/mysql-bin
binlog_format = row

1
2
3

$> mysqldump --defaults-file=/data/mysql57/3307/etc/my.cnf -A -R -B --triggers --master-data=2 --single-transaction > /tmp/aa.sql 
$> sed -n '22p' /tmp/aa.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=596; 

1
2

$> grant replication slave on *.* to repl@'10.0.2.%' identified by 'gC74lgK9sSkzwBbrztd3'; 
$> flush privileges;

1

$> mysql < /tmp/aa.sql 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

--- 指定同步连接信息(从库) 
mysql> change master to MASTER_HOST='10.0.2.25',
  MASTER_USER='repl',
  MASTER_PASSWORD='gC74lgK9sSkzwBbrztd3',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000004',
  MASTER_LOG_POS=596,
  MASTER_CONNECT_RETRY=3;

--- 若初次搭建则(或从0开始全同步)
mysql> change master to MASTER_HOST='10.0.2.25',
  MASTER_USER='repl',
  MASTER_PASSWORD='gC74lgK9sSkzwBbrztd3',
  MASTER_PORT=3307,
  MASTER_AUTO_POSITION=1;

--- 启动同步 
mysql> start slave
--- 查看启动状态 
mysql> show slave status\G 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

mysql> show slave status\G 
Slave_IO_Running              | Yes
Slave_SQL_Running             | Yes
## 最后一次IO的错误号码 
Last_IO_Errno                 | 0
## 最后一次IO的错误信息
Last_IO_Error                 | 
## 最后一次SQL的错误号码
Last_SQL_Errno                | 0
## 最后一次SQL的错误信息
Last_SQL_Error                | 
## 从库(同步)落后于主库的秒数
Seconds_Behind_Master         | 0

1
2
3
4

  mysql> stop slave;
  mysql> reset slave all;
  mysql> change master to ....;
  mysql> start slave;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

  ## 1. 直接重建主从同步(推荐)
  ## 2. 一切以主库为准，创建失败时候删除从库多余的重启同步即可
  ## 3. 执行忽略操作(此操作有一定风险，出非明确知道在干什么，否则不建议使用)
  mysql> stop slave;
  mysql> set global sql_slave_skip_counter = 1; ## 将同步移动至下一个操作，如果多次不同步，可重复操作此项
  mysql> start slave;
  ## 4. 同3,只是在配置文件中忽略对应编码 
  $> vim my.cnf
  slave-skip-errors = 1032,1062,1007 
  ## 5. 为防止SQL线程故障，一般会设置从库只读(只针对普通用户)
  mysql> set global read_only=1;  ## $> my.cnf: read_only=1   

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

## 控制 my.cnf (不建议在主库进行设置,建议在从库进行设置)
## 白名单(在此当中的才会复制)

## replicate_do_db = test 
--replicate-do-db = test 
# replicate_do_table = test.t1 
--replicate-do-table = test.t1 
# replicate_wild_do_table = test.t% 
--replicate-wild-do-table = test.t% 

## 黑名单(在此中的将不会复制)
# replicate_ignore_db = test
--replicate-ignore-db = test
# replicate_ignore_table = test.t1 
--replicate-ignore-table = test.t1 
# replicate_wild_ignore_table = test.t%
--replicate-wild-ignore-table = test.t%

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- 主执行 加载插件(默认就有该组建)
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 从执行 加载插件(默认就有该组建)
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
-- 查看是否加载成功 
mysql> show plugins;

-- 主启动 
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
-- 从启动 
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启从库上的IO线程 
mysql> stop slave io_thread;
mysql> start slave io_thread;

-- 查看主/备是否在运行 
mysql> show status like 'Rpl_semi_sync_master_status';
mysql> show status like 'Rpl_semi_sync_slave_status';

--- 默认情况下,到达10秒还没有返回ack,从中关系自动切换为普通复制 
rpl_semi_sync_master_timeout | 10000 

1
2
3
4
5
6

## 启用GTID,不启用则为普通复制 
gtid-mode=on 
## 强制GTID的一致性 
enforce-gtid-consistency=true
## slave 强制刷新从库的二进制日志 
log-slave-updates=1

1
2
3
4
5

mysql> stop slave;
mysql> set gtid_next='xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:2'; --gtid_next 为slave sql thread 报错的GTID，或者是想要跳过的gtid
mysql> begin;commit;
mysql> set gtid_next='AUTOMATIC';
mysql> start slave 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- 主库创建复制用户
$> reset master;
$> grant replication slave on *.* to repl@'10.0.2.%' identified by 'gC74lgK9sSkzwBbrztd3'; 
$> flush privileges;

-- 从库连接 
$> reset master;
mysql> change master to MASTER_HOST='10.0.2.25',
  MASTER_USER='repl',
  MASTER_PASSWORD='gC74lgK9sSkzwBbrztd3',
  MASTER_PORT=3307,
  MASTER_AUTO_POSITION=1;

$> show slave status\G
-- 以下值，正常情况下应该和主库`show master status`的Executed_Gtid_Set值相同，如果不同则可能从库已经产生了写入
-- 接收到的
Retrieved_Gtid_set: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:1 
-- 已经执行的
Executed_Gtid_Set: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx:1