-
数据库
站-
热门城市 全国站>
-
其他省市
-
-
400-636-0069
沉沙
2018-05-17
来源 ：
阅读 578
评论 0
摘要：本篇MySQL教程讲解了Docker实现MySQL主从复制的过程，希望阅读本篇文章以后对大家学习MySQL数据库有所帮助，学会如何用Docker实现MySQL主从复制。
MySQL实现主从复制
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 NAMES81c75e5b943d e934aafc2206 "/usr/sbin/init" About a minute ago Up About a minute mysql_slave4c17cbb73b09 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.sockdefault-storage-engine = InnoDBlog-bin = mysql-binlog-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 = 8Mlog-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.sockdefault-storage-engine = InnoDBlog-bin = mysql-binlog-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 = 8Mlog-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)
这种模式简答的实现了主从复制，记录主从复制的搭建和了解主从复制的原理过程。
本文由职坐标整理发布，欢迎关注职坐标MySQL频道，学习更多数据库知识！
喜欢 | 0
不喜欢 | 0
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了，明天再试试吧！
我们会在第一时间安排职业规划师联系您！
您也可以联系我们的职业规划师咨询：
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com，All Rights Reserved.
沪公网安备 31011502005948号
索取资料
答疑解惑
技术交流
职业测评
面试技巧
高薪秘笈
