MySQL之主从复制

　　

实现过程:

　　(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

　　(2) slave将master的binary log events拷贝到它的中继日志(relay log)；

　　(3) slave重放中继日志中的事件，将改变反映它自己的数据。

 

场景:

　　从单机架构切换到一主一从或一主多从, 目前数据库中已经有数据并且运行了一段时间

 

实现方案:

　　1.忽略主库之前的数据, 不进行同步处理[不重要的数据]

　　2.对主库中的数据进行备份,然后将主库导出的数据导入到从数据库中, 然后开启主从复制,来保证主从数据库数据的一致性[重要数据]

 

　　总结: 我们采取第二种方案进行数据的处理,具体操作步骤如下:

 

具体操作

 

条件: 使用docker搭建MySQL主从

1)拉取指定版本MySQL

　　

docker pull mysql:5.7

 

2)启动两个MySQL主从服务容器

　　主:

　　　　

docker  run  -p 3339:3306  --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

 

　　从:

　　　　

docker run -p 3340:3306  --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

 

 

　　参数说明:

　　　　run: 运行一个容器

　　　　-p: 指定端口映射(外部端口:容器内端口)

　　　　-e: 设置环境变量 --> 设置root的密码

　　　　-d: 后台守护进程方式运行

　　　　mysql:5.7  指定使用的镜像:版本

 

3)主从配置

　　Master配置

　　　　进入Master:  

docker exec -it mysql-master /bin/bash

 

　　　　修改my.cnf : vim /etc/my.cnf

　　　　文件中添加配置信息

　　　　

[mysqld]

## 同局域网要唯一
server-id=100

## 开启二进制日志功能,
log-bin=mysql-bin

 

 

 

 

　　配置完成重启mysql服务:

　　　　

service  mysql  restart

 

          这个时候docker服务会停止并退出,需要重新启动一下容器即可

　　　　

docker  start mysql-master

 

 

　　Slave配置

　　　　进入slave容器,修改配置文件, 之前的操作Master的操作一致

　　　　修改配置/etc/my.cnf

　　　　

[mysqld]

## server-id,唯一
server-id=101

## 开启二进制日志功能
log-bin=mysql-slave-bin

## relay_log配置中继日志
relay_log=edu-mysql-relay-bin

 

 

 

 

　　重启MySQL,docker,操作与Master一致

 

问题:

　　a) 容器中无法使用vim命令

　　　　

apt-get install vim

 

    b) apt-get 无法安装vim

　　　　

apt-get update

 

 

4) 锁定主数据库,只允许读取不允许写入,防止备份过程中有新数据的插入 导致主从数据不一致的情况

 

　　登陆主库:

　　　　使用mysql登陆(本地没有这个客户端, 可以自行安装 yum install -y mysql)

　　　　

mysql -h127.0.0.1 -uroot -p123456 -P3339

 

　　　　参数说明:

　　　　　　-h: 指定登陆IP

　　　　　　-u: 指定登陆用户

　　　　　　-p: 指定登陆用户密码

　　　　　　-P: 指定MySQL服务的端口号

 

　　登陆成功之后,执行锁库操作:

　　　　

flush tables with read lock;

 

 

　　将主库中的数据同步到从库:

　　　　退出mysql终端,执行备份命令

　　　　

docker exec mysql-master /usr/bin/mysqldump -u root --password=123456 --all-databases > backup.sql

 

 

　　导入从数据库

　　　　

cat  backup.sql | docker exec -i mysql-slave /usr/bin/mysql  -u root --password=123456

 

 

 

 

　　问题

　　　　a)

　　　　可进入容器中修改配置/etc/mysql/my.cnf 添加配置信息:

　　　　

[mysqldump]

user = root
password = rootpassword

 

　　　　配置完成之后, 不需要指定用户名与密码

 

 

　　查看主库状态 记录file与POS参数:

　　　　

show master status\G;

 

 

 

 

　　创建数据同步用户:

　　　　

create user 'slave'@'%' identified by '123456';

grant replication slave, replication client on *.* to 'slave'@'%';

 

 

 

 

 

 

5) 从数据库配置

　　登陆Slave,操作与Master操作一样 注意要修改登陆的端口P

　　执行命令:

　　　　

change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=4331, master_connect_retry=30;

 

　　参数说明:

　　　　master_host: Master地址, 容器是独立ip, 可以使用命令进行查询

　　　　

docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master

 

 

 

 

　　　　master_port: master 端口号 --> 容器的端口号

　　　　master_user: 数据同步的用户

　　　　master_password: 数据同步的用户的密码

　　　　master_log_file: 指定从哪个日志文件开始进行复制, 在主库中查询到的File

　　　　master_log_pos: 从哪个位置开始读, 即上文提到的Position字段

　　　　master_connect_retry: 如果连接失败重试的时间间隔, 默认时间为60, 单位秒

 

 

6) 执行主从复制

　　在从数据库中执行命令

　　　　

start slave;

 

　　之后查看Slave的状态:

　　　　

show slave status\G;

 

　　若 SlaveIORunning 和SlaveSQLRunning 都是Yes, 说明开启主从复制成功

 

　　若出现错误:

　　可以通过Last_IO_Error: 后面的错误信息查看

　　检查项:

　　　　a) 网络不通(ip 端口)

　　　　b) 链接错误(用户名,密码错误)

　　　　c) Master 的Position位置不对

 

 

7) 通过在主库中添加一些数据, 测试是否在从库中会进行同步, 测试完成之后 解锁主数据库

　　登陆主库,执行命令:

　　　　

unlock  tables;

 

 

实践:

#1.主
1)开启binlog
[mysqld]
...

server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
binlog_format=mixed
# 允许同步的数据库
binlog-do-db=data_cms
binlog-do-db=bi
binlog-do-db=bi_log
# 忽略同步数据库
binlog-ignore-db=mysql

需要重启服务

2)创建从账号
create user slaveUser@'xxxx' identified by '123456';
grant replication slave on *.* to slaveUser@'xxxx';
flush privileges;

3)设置为只读,防止有新数据的产生
flush tables with read lock;

4)导出数据
mysqldump -u root -p --host=xxxx -B data_cms > /root/sqls/data_cms.sql
mysqldump -u root -p --host=xxxx -B bi > /root/sqls/bi.sql
mysqldump -u root -p --host=xxxx -B bi_log > /root/sqls/bi_log.sql

--host: 可以导出远程数据库
-B: 包含创建数据库语句


5)查看master信息
show master status\G;
重要内容:
  File
  Position


#2.从
1)开启binlog
server-id=2
log-bin=mysql-bin
replicate-do-db=tt_data_cms
replicate-do-db=tta_bi
replicate-do-db=tta_bi_log
replicate-ignore-db=mysql

2)导入数据
登录数据库
source /root/sqls/game_cms.sql;
source /root/sqls/bi.sql;
source /root/sqls/bi_log.sql;

3)配置同步
change master to master_host='xxxx',master_port=3306,master_user='slaveUser',master_password='123456',master_log_file='master-bin.000001',master_log_pos=15088;

4)开启从同步进程
start slave;

5)查看同步状态
show slave status\G;

    Slave_IO_Running: Yes    
    Slave_SQL_Running: Yes

Log_File、Log_Pos要于master状态下的File,Position相同



同步完成之后, 将主库锁释放
unlock tables;

之后可以进行相关插入,修改操作, 看是否正常同步


=============================  其他命令





    

　　

 

停止slave:
stop slave io_thread;

show status like 'Slave_open_temp_tables'; 保证不为0时,执行下面的操作

stop slave sql_thread;
stop slave;

启动slave:
start slave;

重置slave配置:
reset slave;

　　

 

 

 

 

注意点:

　　1. 尽量优化流程, 减少锁表时间

　　2. 尽可能减少锁表范围, 只锁定相关的数据库