docker之MySQL主從複製

MySQL主從複製

主伺服器

配置文件目錄
mkdir /var/lib/mysql/master/conf.d
數據存儲目錄
mkdir var/lib/mysql/master/data

配置my.cnf文件
# cd /var/lib/mysql/master/conf.d
# vim my.cnf

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
log-bin = mysql-bin
server-id = 1

啟動master容器：

docker run -it -p 3306:3306 --name master -v /var/lib/mysql/master/conf.d:/etc/mysql/conf.d -v /var/lib/mysql/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest

重啟master容器

docker stop master slave
docker start master slave

進入master容器，設置mysql主從複製用戶reader

docker exec -it master /bin/bash
root@1e8e984a9a30:/# mysql -uroot –p123456(前面設置的密碼MYSQL_ROOT_PASSWORD=123456)

MySQL舊版賦權方法：

GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON . to 'reader'@'%' identified by 'readerpwd';

註意：因為新版的的mysql版本已經將創建賬戶和賦予許可權的方式分開了，所以如果是最先版本的mysql鏡像按下麵的方法創建用戶、賦權。

創建賬戶:create user '用戶名'@'訪問主機' identified by '密碼';
賦予許可權:grant 許可權列表 on 資料庫 to '用戶名'@'訪問主機' ;(修改許可權時在後面加with grant option)
mysql> create user 'reader'@'%' identified by 'readerpwd';
Query OK, 0 rows affected (4.57 sec)
mysql> GRANT REPLICATION SLAVE ON . to 'reader'@'%';
Query OK, 0 rows affected (1.13 sec)
FLUSH PRIVILEGES;

查看二進位日誌是否開啟：
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.99 sec)
mysql> show master status；

從伺服器

配置文件目錄
mkdir /var/lib/mysql/slave/conf.d
數據存儲目錄
mkdir var/lib/mysql/slave/data

# cd /var/lib/mysql/slave/conf.d
# vim my.cnf

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
server-id = 2
relay_log = mysql-relay-bin #打開Mysql日誌，日誌格式為二進位
read_only = 1 #設置只讀許可權
log_bin = mysql-bin #開啟從伺服器二進位日誌
log_slave_updates = 1 #使得更新的數據寫進二進位日誌中

啟動slave容器：

 docker run -it -p 3307:3306 --name slave -v /var/lib/mysql/slave/conf.d:/etc/mysql/conf.d -v /var/lib/mysql/slave/data:/var/lib/mysql    -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest

進入slave容器，啟動從伺服器複製線程，讓slave連接master，並開始重做master二進位日誌中的事件

docker exec -it slave /bin/bash
root@6df92c02669a:/# mysql -uroot -p123456
mysql>change master to master_host='192.168.1.24',master_port=3306,master_user='reader',master_password='readerpwd',master_log_file='mysql-bin.000001',master_log_pos=155;
mysql> show slave status\G;

測試：

主伺服器：

create database slavetest;

從伺服器：觀察是否創建了slavetest資料庫,如果看到了，就說明實驗成功了

show databases；