MySQL 主从设定

使用时机：
1. 资料库效能慢的时候
2. 就是想读写分离的时候

主从分别叫做Master, Slave，通常用Master来做写入／变更／删除；也就是会改变资料内容的命令，而Slave只用来做查询；也就是不会改变资料的命令，而简单理解Master/Slave之间的同步过程，就是master被修改时会传送事件给所有的replicas，让replicas也做一次相同的操作，即完成同步。

本次实作流程如下：
先创建mysql作为master，经过日常操作後多了一些资料，之後想要做主从时，先在master做一些设定，然後再创建一个mysql作为slave，也在slave做一些设定，经过双边的设定後，最後测试在master上的增删改是否会即时通知给slave同步。
本次范例使用docker实作mysql，故会将m/s分别创建两个容器，并使用内部局网互通

创建network

创建docker内部网路，命名为db，并设置网段为172.20.0.0/16
docker network create custom-db --subnet 172.20.0.0/16

创建mysql

vi docker-compose.yml

version: '3.1'

services:

  db-master:
    image: mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 1qaz@WSX
    volumes:
      - '/etc/localtime:/etc/localtime:ro'
      - ./db-master:/var/lib/mysql
    ports:
      - 33061:3306
    networks:
      custom-db: # 使用内部网路db
        ipv4_address: 172.20.0.10 


networks:
  custom-db: # 引入内部网路db
    external: true

docker-compose 运行
docker-compose up -d

日常操作

创新表并随意插入两笔资料

+----+--------+----------+
| id | color  | plate    |
+----+--------+----------+
|  1 | red    | RMB-3312 |
|  2 | yellow | RCD-9013 |
+----+--------+----------+
2 rows in set (0.000 sec)

Master设定启用log-bin 这是主从的起手式

修改配置档
vi /etc/mysql/mariadb.cnf

[mysqld]:
log-bin=mysql-bin # 使用mysql-bin做纪录
server-id=1 # 服务器id
binlog_do_db=mydb # 需要同步的资料库名称
max_binlog_size=100M # binlog档案最大容量

重启
/etc/init.d/mariadb restart

然後需要添加一个新帐号，叫做bak给slave使用
grant replication slave on *.* to "bak"@'%' identified by "12345";
上面的replication slave 是关键字
更新权限
flush privileges;
锁定资料表避备份过程中被修改
use mydb;
FLUSH TABLES WITH READ LOCK;
查看当自己身为master的状态

MariaDB [mydb]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      328 | mydb         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

上方的log file以及position要记得，等下slave会需要用到
将资料库导出
mysqldump -u root -p1qaz@WSX --opt mydb > /mydb.sql
解除只读锁
unlock tables;

锁能确保备份的当下mysql-bin.000003的position 328不会改变，实现slave再导入资料时，接着这个位置同步，能不移失资料。

创建mysql (slave)

修改原本的docker-compose.yml 添加db-slave

version: '3.1'

services:
  db-master:
    ...
    
  db-slave:
    image: mariadb
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: 1qaz@WSX
    volumes:
      - '/etc/localtime:/etc/localtime:ro'
      - ./db-slave:/var/lib/mysql
      - ./db-slave-conf:/etc/mysql
    ports:
      - 33062:3306
    networks:
      custom-db: # 使用内部网路db
        ipv4_address: 172.20.0.20 


networks:
  custom-db: # 引入内部网路db
    external: true

Slave设定

先汇入master的资料（记得先create database mydb之後才能导入资料）
mysql -u root -p1qaz@WSX mydb < mydb.sql

修改配置档
vi /etc/mysql/mariadb.cnf

[mysqld]:
log-bin=mysql-bin # 使用mysql-bin做纪录
server-id=2 # 服务器id （别跟master以及其他slave重复即可）
binlog_do_db=mydb # 需要同步的资料库名称
max_binlog_size=100M # binlog档案最大容量

重启
/etc/init.d/mariadb restart
设定自己的master_host为db-master位置，并将bak使用者写入
CHANGE MASTER TO MASTER_HOST='172.20.0.10', MASTER_USER='bak', MASTER_PASSWORD='12345', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=328;
开始扮演slave脚色
start slave
此时/var/lib/mysql/master.info这个档案会自动被产生，若要变更master_host等相关资讯时，建议先手动stop slave，然後手动将这个档案移除後，再CHANGE MASTER ... 然後再重新start slave

捡查master与slave状态

show master status;
show slave status\G;

MariaDB [mydb]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.20.0.10
                   Master_User: bak
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 551
                Relay_Log_File: mysqld-relay-bin.000004
                 Relay_Log_Pos: 850
         Relay_Master_Log_File: mysql-bin.000004
              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: 551
               Relay_Log_Space: 1459
               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: 1
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 1
1 row in set (0.000 sec)

show processlist;

MariaDB [mydb]> show processlist\G;
*************************** 1. row ***************************
      Id: 5
    User: system user
    Host:
      db: NULL
 Command: Slave_IO
    Time: 7547
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 6
    User: system user
    Host:
      db: NULL
 Command: Slave_SQL
    Time: 1548
   State: Slave has read all relay log; waiting for more updates
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 19
    User: root
    Host: localhost
      db: mydb
 Command: Query
    Time: 0
   State: starting
    Info: show processlist
Progress: 0.000
3 rows in set (0.000 sec)

同步测试

在master对mydb做insert/update/delete之後，slave能查到修改後的资料。

若是相互同步，也就是两个都要完整的CRUD操作，就是彼此互为主从即可。双方都做一样的设定，开一个帐号给对方使用，只有server-id仍然不同即可。