docker 搭建 MYSQL並且完成主從複製

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

1 liwangdeMacBook-Air:~ liwang$ docker network create --subnet=172.18.0.0/16 mynetwork

1.安裝主伺服器

1.1 利用docker run一個新的centos容器，並且指定其ip地址，將資料庫文件掛載至宿主機上

1 liwangdeMacBook-Air:~ liwang$ docker run -i -t --name mysql_master --net mynetwork --ip 172.18.0.2 -p 3306:3306 -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至容器內

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

安裝步驟:

1 1 創建用戶:
2 [root@76ffc9a23bf6 /]# useradd mysql -s /sbin/nologin -M
3 2 解壓:
4 [root@76ffc9a23bf6 soft]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz
5 3 初始化:
6 [root@76ffc9a23bf6 mysql-5.7.22-linux-glibc2.12-x86_64]# bin/mysqld --initialize --basedir=/soft/mysql-5.7.22-linux-glibc2.12-x86_64 --datadir=/data --user=mysql
7 4 啟動mysql:
8 [root@76ffc9a23bf6 mysql-5.7.22-linux-glibc2.12-x86_64]# /soft/mysql-5.7.22-linux-glibc2.12-x86_64/bin/mysqld_safe --user=mysql &

當第一個容器能夠順利打開mysql時，利用docker commit將容器製作成鏡像，以便slave直接run出來即可

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

利用generil_mysql,將run slave 

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

3.配置主從複製

1.主庫配置

1.1 保證my.cnf中有log-bin

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

　　mysql 檢查是否開啟

1 mysql> show variables where variable_name like 'log_bin';
2 +---------------+-------+
3 | Variable_name | Value |
4 +---------------+-------+
5 | log_bin       | ON    |
6 +---------------+-------+
7 1 row in set (0.00 sec)

為ON則開啟，為OFF則為關閉

1.2 創建從庫連接的複製用戶

 1 mysql> grant replication slave on *.* to 'slave_copy'@'%' identified by 'mysql';
 2 Query OK, 0 rows affected, 1 warning (0.10 sec)
 3 
 4 mysql> show grants for 'slave_copy';
 5 +----------------------------------------------------+
 6 | Grants for slave_copy@%                            |
 7 +----------------------------------------------------+
 8 | GRANT REPLICATION SLAVE ON *.* TO 'slave_copy'@'%' |
 9 +----------------------------------------------------+
10 1 row in set (0.00 sec)
11 
12 mysql> 

1.3查看目前主伺服器的狀態

1 mysql> show master status;
2 +------------------+----------+--------------+------------------+-------------------+
3 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
4 +------------------+----------+--------------+------------------+-------------------+
5 | mysql-bin.000005 |      443 |              |                  |                   |
6 +------------------+----------+--------------+------------------+-------------------+
7 1 row in set (0.00 sec)
8 
9 mysql> 

1.4 做主從複製前，最好將主資料庫進行備份，可用mysql> flush table with read lock; 然後可備份，備份完畢後可利用mysql> unlock tables;恢復可寫

2.從庫配置

2.1 測試ping主伺服器，以及利用mysql工具連接主伺服器，測試沒問題

 1 [root@f325480c1179 /]# ping 172.18.0.2 -c 4
 2 PING 172.18.0.2 (172.18.0.2) 56(84) bytes of data.
 3 64 bytes from 172.18.0.2: icmp_seq=1 ttl=64 time=0.105 ms
 4 64 bytes from 172.18.0.2: icmp_seq=2 ttl=64 time=0.231 ms
 5 64 bytes from 172.18.0.2: icmp_seq=3 ttl=64 time=0.120 ms
 6 64 bytes from 172.18.0.2: icmp_seq=4 ttl=64 time=0.170 ms
 7 
 8 --- 172.18.0.2 ping statistics ---
 9 4 packets transmitted, 4 received, 0% packet loss, time 3120ms
10 rtt min/avg/max/mdev = 0.105/0.156/0.231/0.050 ms
11 [root@f325480c1179 /]# /soft/mysql-5.7.22-linux-glibc2.12-x86_64/bin/mysql -ulw -h172.18.0.2 -pwl
12 mysql: [Warning] Using a password on the command line interface can be insecure.
13 Welcome to the MySQL monitor.  Commands end with ; or \g.
14 Your MySQL connection id is 6
15 Server version: 5.7.22-log MySQL Community Server (GPL)
16 
17 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
18 
19 Oracle is a registered trademark of Oracle Corporation and/or its
20 affiliates. Other names may be trademarks of their respective
21 owners.
22 
23 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
24 
25 mysql> exit
26 Bye
27 [root@f325480c1179 /]# 

2.2 利用change master連接至主伺服器

1 mysql> change master to
2     -> master_host='172.18.0.2',
3     -> master_port=3306,
4     -> master_user='slave_copy',
5     -> master_password='mysql',
6     -> master_log_file='mysql-bin.000005',
7     -> master_log_pos=443;
8 Query OK, 0 rows affected, 2 warnings (0.15 sec)

2.3 開啟主從複製並且檢查狀態是否正常

1 mysql> start slave;
2 Query OK, 0 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。