Docker安裝mysql

首先拉取docker鏡像

docker pull mysql

啟動(dòng)容器

Master(主)：
docker run -p 3339:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=135246 -d mysql

Slave(從)：
docker run -p 3340:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=135246 -d mysql

Master對外映射的端口是3339，Slave對外映射的端口是3340。因?yàn)閐ocker容器是相互獨(dú)立的，每個(gè)容器有其獨(dú)立的ip，所以不同容器使用相同的端口并不會(huì)沖突。這里我們應(yīng)該盡量使用mysql默認(rèn)的3306端口，否則可能會(huì)出現(xiàn)無法通過ip連接docker容器內(nèi)mysql的問題。

到Master容器內(nèi)部

docker exec -it mysql-master /bin/bash

容器內(nèi)部添加mysql帳號(hào)

mysql -uroot -p135246

CREATE USER 'user_w'@'％' IDENTIFIED BY '135246';
GRANT ALL PRIVILEGES ON * .* TO 'user_w'@'％';

容器外連接可能遇到報(bào)錯(cuò)

mysql -h127.00.1 -P3339 -u user_w -p135246
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded

容器內(nèi)部設(shè)置解決報(bào)錯(cuò)問題

ALTER USER 'user_w'@'%' IDENTIFIED WITH mysql_native_password BY '135246';

修改my.cnf

在master容器內(nèi)
cd /etc/mysql切換到/etc/mysql目錄下，然后vi my.cnf對my.cnf進(jìn)行編輯。此時(shí)會(huì)報(bào)出bash: vi: command not found，需要我們在docker容器內(nèi)部自行安裝vim。使用apt-get install vim命令安裝vim
會(huì)出現(xiàn)如下問題：

Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package vim

執(zhí)行apt-get update，然后再次執(zhí)行apt-get install vim即可成功安裝vim。然后我們就可以使用vim編輯my.cnf，在my.cnf中添加如下配置：

[mysqld]
## 同一局域網(wǎng)內(nèi)注意要唯一
server-id=100  
## 開啟二進(jìn)制日志功能，可以隨便取（關(guān)鍵）
log-bin=mysql-bin

重啟docker 容器生效配置

下一步在Master數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)同步用戶，授予用戶 slave REPLICATION SLAVE權(quán)限和REPLICATION CLIENT權(quán)限，用于在主從庫之間同步數(shù)據(jù)。

CREATE USER 'slave'@'%' IDENTIFIED BY '135246';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

和配置Master(主)一樣，在Slave配置文件my.cnf中添加如下配置：

[mysqld]
## 設(shè)置server_id,注意要唯一
server-id=101  
## 開啟二進(jìn)制日志功能，以備Slave作為其它Slave的Master時(shí)使用
log-bin=mysql-slave-bin   
## relay_log配置中繼日志
relay_log=edu-mysql-relay-bin 

同樣重啟從服務(wù)docker容器

獲取容器IP地址

在本機(jī)命令行執(zhí)行

docker inspect -f '{{.Name}} - {{.NetworkSettings.IPAddress }}' $(docker ps -aq)

設(shè)置主從同步

在Master進(jìn)入mysql，執(zhí)行

show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在Slave進(jìn)入mysql，設(shè)置其主數(shù)據(jù)庫

change master to master_host='172.17.0.2', master_user='slave', master_password='135246', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 155, master_connect_retry=30;

增加從數(shù)據(jù)庫連接帳號(hào)

mysql -uroot -p135246
CREATE USER 'user_r'@'％' IDENTIFIED BY '135246';
GRANT SElECT ON * .* TO 'user_r'@'％';
ALTER USER 'user_r'@'%' IDENTIFIED WITH mysql_native_password BY '135246';

在Slave 中的mysql終端執(zhí)行show slave status \G;用于查看主從同步狀態(tài)。
當(dāng)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
都為Yes時(shí)，表示正常同步了

show slave status \G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 23093
               Relay_Log_File: edu-mysql-relay-bin.000002
                Relay_Log_Pos: 322
        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: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 23093
              Relay_Log_Space: 534
              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: 100
                  Master_UUID: 12b2e6dd-bf46-11e9-ba82-0242ac110002
             Master_Info_File: mysql.slave_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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.00 sec)

ERROR: 
No query specified

從庫停止主從復(fù)制

STOP SLAVE IO_THREAD FOR CHANNEL ''