docker 搭建 MYSQL并且完成主从复制

时间：2021-12-22 17:38:48

mysql主从复制逻辑：

1.从库执行start slave 开启主从复制。

2.从库请求连接到主库，并且指定binlog文件以及位置后发出请求。

3.主库收到从库请求后，将信息返回给从库，除了信息日志外，还包含新的文件名称以及下一个更新节点。

4.从库接收到主库发送的信息后，会将信息更新至自身的relay log中，并且将新的文件名记录到master-info中。

5.从库SQL线程会检测本地relay-log，如有变化，会将信息解析为SQL可执行语句并且执行该语句，同时在relay-log.info中记录当前文件名以及位置点。

mysql 主从复制操作方法:
主库:
1.保证bin-log的开启(可在mysql中利用 show variables where variable_name like 'bin%' 中查看,其中栏位值为ON则为开启，OFF则为关闭 bin-log具体逻辑取决于my.cnf设定值中的bin-log)
2.新建用户并且设定其为replication slave账户(grant replication slave on db_name.tb_name to 'user_name'@'host_name' identified by 'password';)
3.对主库锁表只读(flush table with read lock;)
4.收集master的信息，包括(replication slave username and password , master status filename and master status position(get master status: show master status ) )
5.导出主库数据，解锁(unlock tables;)

从库:
1.将主库数据导入从库
2.将从库连接主库(change master to master_host='host_name',master_port='master_port',master_user='master_user',paster_password='master_password',master_log_file='master_status_log_file',master_log_pos='master_status_log_position');
3.开启主从复制,start slave; 查看从库状态是否OK(show slave status\G;)如果确定状态为NO时，则需要重新启动master和slave,再次检查状态是否OK

0.创建新的docker网络，用于主从服务器指定ip

 liwangdeMacBook-Air:~ liwang$ docker network create --subnet= mynetwork

1.安装主服务器

1.1 利用docker run一个新的centos容器，并且指定其ip地址，将数据库文件挂载至宿主机上

 liwangdeMacBook-Air:~ liwang$ docker run -i -t --name mysql_master --net mynetwork --ip : -v /Users/liwang/docker/mysql_data:/data centos /bin/bash

1.2 下载mysql二进制包进行安装配置

mysql下载路径:https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

下载完毕后，利用docker cp将包copy至容器内

 liwangdeMacBook-Air:Downloads liwang$ docker -linux-glibc2.-x86_64.tar.gz `docker inspect -f {{.ID}} mysql_master`:/soft

安装步骤:

  创建用户:
 [root@76ffc9a23bf6 /]# useradd mysql -s /sbin/nologin -M
  解压:
 [root@76ffc9a23bf6 soft]# -linux-glibc2.-x86_64.tar.gz
  初始化:
 [root@76ffc9a23bf6 mysql--linux-glibc2.-x86_64]# bin/mysqld --initialize --basedir=/soft/mysql--linux-glibc2.-x86_64 --datadir=/data --user=mysql
  启动mysql:
 [root@76ffc9a23bf6 mysql--linux-glibc2.-x86_64]# /soft/mysql--linux-glibc2.-x86_64/bin/mysqld_safe --user=mysql &

当第一个容器能够顺利打开mysql时，利用docker commit将容器制作成镜像，以便slave直接run出来即可

 liwangdeMacBook-Air:Downloads liwang$ docker commit `docker ps -a | grep mysql_master | awk '{print $1}'` generil_mysql

利用generil_mysql,将run slave

 liwangdeMacBook-Air:Downloads liwang$ docker run -i -t --name mysql_slave --net mynetwork --ip : -v /Users/liwang/docker/mysql_data_3307:/data generil_mysql /bin/bash

3.配置主从复制

1.主库配置

1.1 保证my.cnf中有log-bin

 [root@76ffc9a23bf6 /]# grep "log-bin" /etc/my.cnf
     log-bin = /data/mysql-bin

　　mysql 检查是否开启

 mysql> show variables where variable_name like 'log_bin';
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | log_bin       | ON    |
 +---------------+-------+
  row in set (0.00 sec)

为ON则开启，为OFF则为关闭

1.2 创建从库连接的复制用户

 mysql> grant replication slave on *.* to 'slave_copy'@'%' identified by 'mysql';
 Query OK,  rows affected,  warning (0.10 sec)

 mysql> show grants for 'slave_copy';
 +----------------------------------------------------+
 | Grants for slave_copy@%                            |
 +----------------------------------------------------+
 | GRANT REPLICATION SLAVE ON *.* TO 'slave_copy'@'%' |
 +----------------------------------------------------+
  row in set (0.00 sec)

 mysql>

1.3查看目前主服务器的状态

 mysql> show master status;
 +------------------+----------+--------------+------------------+-------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+-------------------+
   |              |                  |                   |
 +------------------+----------+--------------+------------------+-------------------+
  row in set (0.00 sec)

 mysql>

1.4 做主从复制前，最好将主数据库进行备份，可用mysql> flush table with read lock; 然后可备份，备份完毕后可利用mysql> unlock tables;恢复可写

2.从库配置

2.1 测试ping主服务器，以及利用mysql工具连接主服务器，测试没问题

 [root@f325480c1179 /]#
 PING () bytes of data.
  bytes from  ttl= time=0.105 ms
  bytes from  ttl= time=0.231 ms
  bytes from  ttl= time=0.120 ms
  bytes from  ttl= time=0.170 ms

 --- 172.18.0.2 ping statistics ---
  packets transmitted,  received, % packet loss, time 3120ms
 rtt min/avg/max/mdev = 0.105/0.156/0.231/0.050 ms
 [root@f325480c1179 /]# /soft/mysql--linux-glibc2.-x86_64/bin/mysql -ulw -h172. -pwl
 mysql: [Warning] Using a password on the command line interface can be insecure.
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection
 Server version: -log MySQL Community Server (GPL)

 Copyright (c) , , Oracle and/or its affiliates. All rights reserved.

 Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.

 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 mysql> exit
 Bye
 [root@f325480c1179 /]#

2.2 利用change master连接至主服务器

 mysql> change master to
     -> master_host='172.18.0.2',
     ,
     -> master_user='slave_copy',
     -> master_password='mysql',
     -> master_log_file='mysql-bin.000005',
     ;
 Query OK,  rows affected,  warnings (0.15 sec)

2.3 开启主从复制并且检查状态是否正常

 mysql> start slave;
 Query OK,  rows affected (0.01 sec)

运行mysql> show slave status; 可以查看状态信息是否OK，其Slave_IO_Running: Yes， Slave_SQL_Running: Yes，Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

则为OK,如果遇到从库两个线程为NO的情况下，可以重启一下master and slave服务器，再次check是否为YES,

4.测试

在主库上新增一个用户，返回从库时，同时新增了用户，则证明搭建OK。



相关文章