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 # 服务器idbinlog_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: 0Slave_Non_Transactional_Groups: 0    Slave_Transactional_Groups: 11 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: NULLProgress: 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: NULLProgress: 0.000*************************** 3. row ***************************      Id: 19    User: root    Host: localhost      db: mydb Command: Query    Time: 0   State: starting    Info: show processlistProgress: 0.0003 rows in set (0.000 sec)

同步测试

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

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