Mysql主从复制

 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
27
28
29
30
31
32
33

pt-table-checksum [options] [dsn]
# DSN指向的是主的地址
# 常用的参数
--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
--replicate-check-only :只显示不同步的信息。
--replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。
--databases= ：指定需要被检查的数据库，多个则用逗号隔开。
--tables= ：指定需要被检查的表，多个用逗号隔开
--host | h= ：Master的地址
--user | u= ：用户名
--passwork | p=：密码
--Post | P= ：端口

 pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=snailsir --password=root
 # 返回
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
11-11T23:27:10      0      1        3          1       1       0   0.064 mytest.t

# 参数解读
TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。
以上信息，我们只要关注diffs字段就可以，然后判断这个值是否不等于0；则可以判断是否一致

同时检测信息会被放到check_data库里的checksums表中

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

# 在主库执行
h: 从库ip
u：从库用户名
p：从库密码
P：从库端口
--databases: 从库库名
--print：打印需要同步的sql语句，这样就可以知道那些数据有问题
pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=root,P=3306 --databases=mytest --print

# 返回
DELETE FROM `mytest`.`t` WHERE `id`='3' LIMIT 1 /*percona-toolkit src_db:mytest src_tbl:t src_dsn:P=3306,h=192.168.29.102,p=...,u=mytest dst_db:mytest dst_tbl:t dst_dsn:P=3306,h=192.168.29.103,p=...,u=mytest lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:76531 user:root host:localhost.localdomain*/;

#返回参数：
--replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
--databases= : 指定执行同步的数据库，多个用逗号隔开。
--tables= ：指定执行同步的表，多个用逗号隔开。
--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
h=127.0.0.1 ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。
u=root ：帐号。
p=123456 ：密码。
--print ：打印，但不执行命令。
--execute ：执行命令。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

#!/usr/bin/env bash
NUM=`pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=mytest --password=rot | awk 'NR>1{sum+=$3}END{print sum}'`
if [ $NUM -eq 0 ] ;then
	echo "Data is ok!"
else
	echo "Data is error!"
  # pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=rot,P=3306 --databases=mytest --print
  pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=rot,P=3306 --databases=mytest --execute
  # pt-table-sync --sync-to-master h=192.168.29.104,u=mytest,p=rot,P=3306 --databases=mytest --print
  pt-table-sync --sync-to-master h=192.168.29.104,u=mytest,p=rot,P=3306 --databases=mytest --execute
fi

1

* 24 * * * /home/pt-check-sync.sh

1
2
3
4
5
6
7
8

CREATE TABLE heartbeat (
  ts VARCHAR (26) NOT NULL,
  server_id INT UNSIGNED NOT NULL PRIMARY KEY,
  file VARCHAR (255) DEFAULT NULL, -- SHOW MASTER STATUS
  position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
  relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
  exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);

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

# 在主库上启动服务 1s检测一次
pt-heartbeat --user=root --ask-pass --create-table --database mytest --interval=1 --update --replace --daemonize
# 查看启动状态
ps -ef | grep pt-heartbeat
# 返回
root      76637      1  0 23:52 ?        00:00:00 perl /usr/bin/pt-heartbeat --user=root --ask-pass --create-table --database mytest --interval=1 --interval=1 --update --replace --daemonize
root      76643  76367  0 23:53 pts/2    00:00:00 grep --color=auto pt-heartbeat

# 检测延迟
pt-heartbeat --database mytest --table=heartbeat --monitor --user=root --password=root --master-server-id=1
# 返回结果
0.02s [ 0.00s, 0.00s, 0.00s ]
0.00s [ 0.00s, 0.00s, 0.00s ]
# 这其中 0.02s 表示延迟了 ，没有延迟是为0 而 [ 0.00s, 0.00s, 0.00s ] 则表示1m,5m,15m的平均值， 而这期中需要注意的是 --master-server-id 为主服务器的服务id就是在my.cnf中配置的 server_id的值

 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

# 启动主
docker run --name master -d -p 3310:3306 -v /docker/mysql/master/conf:/etc/mysql/conf.d -v /docker/mysql/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

# 修改配置
vim /docker/mysql/master/conf/my.cnf

[mysqld]
## 同一局域网内注意要唯一
server-id=100  
## 开启二进制日志功能，可以随便取（关键）
log-bin=mysql-bin

#重启主服务器
docker restart master

# 进入容器
docker exec  -it master /bin/bash

# 创建主从同步用户
create user 'slave'@'%' identified by '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

flush privileges;

# 查看状态
show master status;

 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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

# 启动从1
docker run --name slave -d -p 3311:3306 -v /docker/mysql/slave/conf:/etc/mysql/conf.d -v /docker/mysql/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

# 修改配置
vim /docker/mysql/slave/conf/my.cnf

[mysqld]
## 设置server_id,注意要唯一
server-id=101  
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=mysql-slave-bin   
## relay_log配置中继日志
relay_log=mysql-relay-bin  

# 重启slave01服务器
docker restart slave

# 进入容器
docker exec -it slave /bin/bash

# 执行同步sql语句，同步master账号信息
change master to
master_host='172.17.0.4',
master_user='slave',
master_password='123456',
master_log_file='log_bin.000013',
master_log_pos=154;


# 启动
start slave;

# 查看salve状态
show slave status;
# 如果报错
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
# 执行
reset slave;
start slave;

# 查看同步状态
show slave status;

1
2
3
4
5
6
7
8

# 导出master_slave数据库为master_slave.sql文件到/home目录
mysqldump -h192.168.29.1 -usnailsir -p master_slave > /home/master_slave.sql

# 查看执行时间
time mysqldump -h192.168.29.1 -usnailsir -p master_slave > /home/master_slave.sql

# 导入数据
mysql -f -uroot -p master_slave < /home/master_slave.sql

1
2

# 导出master_slave数据库为master_slave.sql文件到/home目录
time mydumper -h 192.168.29.1 -u snailsir -p -B master_slave -o /home/master_slave.sql

1
2
3
4
5
6
7
8
9

xtrabackup --defaults-file=/etc/my.cnf --user=root --password=root --port=3306 --backup --target-dir=/home/master_slave
# 然后将备份的数据传输到从数据库中
scp -r /home/master_slave root@192.168.3.2:/home/master_slave

# 然后进入从数据库中进行恢复
xtrabackup --defaults-files=/etc/my.cnf --copy-back --target-dir=/home/master_slave
# 这样之前的mysql中的data目录会变成data2
# 新生成的数据库会占用data目录名，但是我们要修改下目录权限
chown -R mysql:mysql /usr/local/mysql/data