MySQL实现主从复制
Docker容器
一、安装Centos 7纯净镜像
前提条件：已正确安装Docker，如无安装可参考本人简书Centos 7 安装Docker
1.拉取一个纯净Centos 7镜像
从阿里镜像中心拉取一个纯净Centos 7纯净镜像
[root@localhost ~]# docker pull centos
2.创建2个容器 Mysql_Master/Mysql_Slave
查看已经拉取的镜像
[root@localhost ~]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
centos latest e934aafc2206 2 weeks ago 199MB
创建mysql_master 容器
[root@localhost ~]# docker run --privileged -ti --name mysql_master centos:latest /usr/sbin/init
创建mysql_slave 容器
[root@localhost ~]# docker run --privileged -ti --name mysql_slave centos:latest /usr/sbin/init
查看已经存在的容器列表信息
[root@localhost ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
81c75e5b943d e934aafc2206 "/usr/sbin/init" About a minute ago Up About a minute mysql_slave
4c17cbb73b09 e934aafc2206 "/usr/sbin/init" 3 minutes ago Up 3 minutes Mysql_master
3.上传Mysql安装RPM源文件进行Mysql安装
拷贝宿主机安装源文件到docker容器
[root@localhost home]# docker cp /home/mysql57-community-release-el7-11.noarch.rpm mysql_master:/root/
[root@localhost home]# docker cp /home/mysql57-community-release-el7-11.noarch.rpm mysql_slave:/root/
进入docker 容器，进行Mysql安装
[root@localhost home]# docker exec -it mysql_master /bin/bash
[root@4c17cbb73b09 ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
开始Master容器安装Mysql
[root@4c17cbb73b09 ~]# yum install -y mysql-server
启动容器的Mysql
[root@e314477e8ba4 ~]# systemctl start mysqld
同样的操作，进入mysql_slave 节点，进行Mysql安装
[root@localhost ~]# docker cp /home/mysql57-community-release-el7-11.noarch.rpm mysql_slave:/root/
[root@localhost ~]# docker exec -it mysql_slave /bin/bash
[root@e85d5de9eb0d ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
[root@4c17cbb73b09 ~]# yum install -y mysql-server
查看master节点Mysql默认密码
[root@e85d5de9eb0d ~]# grep 'temporary password' /var/log/mysqld.log
设置mysql初始密码
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
mysql> set password = password('zyl00712');
允许root远程登录
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'zyl00712' WITH GRANT OPTION;
mysql> flush privileges;
同理，登录slave节点，设置slave mysql默认密码，授权远程访问，此处不再赘述。
二、Mysql主从复制的配置实现
主从复制实现原理
主从复制实现原理:
主从复制通过BinLog+中继日志文件实现MySQL数据同步，其中BinLog为二进制文件，MySQL日志文件记录了DDLhe DML
Relay-log(中继日志)：主要永固同步的中间过程
1. 创建同步复制的用户
mysql> create user 'repl'@'172.17.0.%' identified by 'zyl00712';
2.给同步用户赋权
mysql> grant replication slave on *.* to 'repl'@'172.17.0.%' identified by 'zyl00712';
mysql> flush privileges;
3.开启binLog二进制日志
修改 /etc/my.cnf 文件增加以下内容
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# 此server-id 每台机器唯一
server-id = 2
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
default-storage-engine = InnoDB
log-bin = mysql-bin
log-bin-index = mysql-bin.index
relay-log = mysql-relay
relay-log-index = mysql-relay.index
expire-logs-days = 10
max-binlog-size = 100M
max_binlog_cache_size = 8M
log-slave-updates = 1
binlog_cache_size = 4M
replicate-wild-ignore-table = mysql.%
sync_binlog = 1
relay_log_recovery = 1
log_slave_updates = 1
binlog_format = MIXED
sql_mode=STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 32M
注意：
Replication-do-db的坑，如果多个库则使用多行Replication-do-db进行配置
Replication-ignore-db的坑，如果忽略多个库则使用多行Replication-ignore-db进行配置
配置完成重启Mysql服务
[root@e85d5de9eb0d etc]# systemctl restart mysqld
重启完成之后会生成mysql-bin.index 日志文件
[root@e85d5de9eb0d mysql]# pwd
/var/lib/mysql
[root@e85d5de9eb0d mysql]# ll
total 122928
-rw-r-----. 1 mysql mysql 56 Apr 23 13:36 auto.cnf
-rw-r-----. 1 mysql mysql 342 Apr 24 12:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 50331648 Apr 24 12:14 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Apr 23 13:36 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Apr 24 12:14 ibdata1
-rw-r-----. 1 mysql mysql 12582912 Apr 24 12:14 ibtmp1
drwxr-x---. 2 mysql mysql 4096 Apr 23 13:37 mysql
-rw-r-----. 1 mysql mysql 154 Apr 24 12:14 mysql-bin.000001
-rw-r-----. 1 mysql mysql 19 Apr 24 12:14 mysql-bin.index
srwxrwxrwx. 1 mysql mysql 0 Apr 24 12:14 mysql.sock
-rw-------. 1 mysql mysql 4 Apr 24 12:14 mysql.sock.lock
drwxr-x---. 2 mysql mysql 8192 Apr 23 13:37 performance_schema
drwxr-x---. 2 mysql mysql 8192 Apr 23 13:37 sys
查看二进制同步文件内容
[root@e85d5de9eb0d mysql]# mysqlbinlog --start-position=0 ./mysql-bin.000001
mysql命令查看binlog
mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 2 | 123 | Server ver: 5.7.22-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 2 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001';
mysql> show binlog events in 'mysql-bin.000001' from 4;
mysql> show binlog events in 'mysql-bin.000001' from 4\G; # 详细信息
查看主机节点状态
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
4.配置mysql_slave 子节点信息实现主从复制【此模式从节点不能新增数据】
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# 此server-id 每台机器唯一
server-id = 3
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
default-storage-engine = InnoDB
log-bin = mysql-bin
log-bin-index = mysql-bin.index
relay-log = mysql-relay
relay-log-index = mysql-relay.index
expire-logs-days = 10
max-binlog-size = 100M
max_binlog_cache_size = 8M
log-slave-updates = 1
binlog_cache_size = 4M
replicate-wild-ignore-table = mysql.%
sync_binlog = 1
relay_log_recovery = 1
log_slave_updates = 1
binlog_format = MIXED
sql_mode=STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 32M
重启 slave 节点mysql的服务
[root@e314477e8ba4 etc]# systemctl restart mysqld
从机节点Slave 节点Mysql命令行执行以下命令
- 第一步
mysql> stop slave;
- 第二步【关键步骤】
Change master to
Master_host='172.17.0.2',
Master_user='repl',
Master_password='zyl00712',
Master_log_file='mysql-bin.000001',
Master_log_pos=154;
注意：
master_log_pos 位置需要根据Master主节点的日志记录位置开始，不可随意指定。
- 第三步 启动从节点
mysql> start slave;
查看从节点状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 523
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 662aa548-46fb-11e8-99db-0242ac110002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| 2 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 3 | system user | | NULL | Connect | 1137 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 1137 | Slave has read all relay log; waiting for more updates | NULL |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
这种模式只是简答的实现了主从复制，存在很多坑，只是记录主从复制的搭建和了解主从复制的原理过程，后续会测试MariadDB的Galera Cluster集群模式。
网友评论