17mysql主从(搭建)

MySql主从(搭建)

一、搭建mysql主从

1.1 拉取镜像

docker pull mysql:5.7
docker run -p 3339:3306 --name my-master -e MYSQL_ROOT_PASSWORD&＃61;123456 -d mysql:5.7
docker run -p 3340:3306 --name my-slave -e MYSQL_ROOT_PASSWORD&＃61;123456 -d mysql:5.7


1.2 配置master

• 进入容器修改配置&＃xff0c;先更新apt-get &＃xff0c;安装vim

apt-get update
apt-get install vim
cd /etc/mysql
vi my.cnf


• my.cnf配置信息如下&＃xff1a;注意不要漏了 [mysqld]和server-id&＃xff0c;不配置server-id 启动也会失败&＃xff1a;

[mysqld]
## 同一局域网内注意要唯一
server-id&＃61;100
## 开启二进制日志功能&＃xff0c;可以随便取&＃xff08;关键&＃xff09;&＃xff0c;配置次项表示开启bin-log日志&＃xff0c;配置值代表bin-log文件名称
log-bin&＃61;mysql-bin


• 修改完配置后重启master

docker start my-master


• 配置slave账号

root&＃64;d1e4a1eb0bed:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.28 MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.mysql>
mysql> CREATE USER &＃39;slave&＃39;&＃64;&＃39;%&＃39; IDENTIFIED BY &＃39;123456&＃39;;
Query OK, 0 rows affected (0.01 sec)mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO &＃39;slave&＃39;&＃64;&＃39;%&＃39;;
Query OK, 0 rows affected (0.00 sec)


• 查询 master 的 master_log_pos,这里查到的 Position 在后面配置slave的时候要用&＃xff0c;

mysql> show master status;
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;-------------------&＃43;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;-------------------&＃43;
| mysql-bin.000001 | 2425 | | | |
&＃43;------------------&＃43;----------&＃43;--------------&＃43;------------------&＃43;-------------------&＃43;
1 row in set (0.00 sec)


1.3 配置Slave

• my.cnf 配置信息如下&＃xff1a;

[mysqld]
## 设置server_id,注意要唯一
server-id&＃61;101
## 开启二进制日志功能&＃xff0c;以备Slave作为其它Slave的Master时使用
log-bin&＃61;mysql-slave-bin
## relay_log配置中继日志
relay_log&＃61;edu-mysql-relay-bin


• 配置 slave 的主机

root&＃64;d8a4501c94b7:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28-log MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.mysql> change master to master_host&＃61;&＃39;172.17.0.2&＃39;, master_user&＃61;&＃39;slave&＃39;, master_password&＃61;&＃39;123456&＃39;, master_port&＃61;3306, master_log_file&＃61;&＃39;mysql-bin.000001&＃39;, master_log_pos&＃61; 2830, master_connect_retry&＃61;30;
Query OK, 0 rows affected, 2 warnings (0.08 sec)start slave; --- 注意这里启动slaveshow slave status\G;


• 需要注意的是 change master 这条命令需要正确&＃xff0c;命令中的 master_host&＃xff0c;master_user &＃xff0c;master_password &＃xff0c;master_port 容易理解分别是主机的ip&＃xff0c;端口以及之前 master 上配置的slave同步账号&＃xff0c;master_log_pos主要正确配置&＃xff0c;就是前面在master 中查到的 Position&＃xff0c;master_log_file 是同步的binlog文件&＃xff1b;

• 如果 change master 有问题&＃xff0c;那么show slave status\G; 可能会提示错误&＃xff0c;正常情况show slave status\G;输出大概如下&＃xff1a;

mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 172.17.0.2Master_User: slaveMaster_Port: 3306Connect_Retry: 30Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 2425Relay_Log_File: edu-mysql-relay-bin.000002Relay_Log_Pos: 2091Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 2425Relay_Log_Space: 2302Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100Master_UUID: 5722550e-2319-11ea-b15e-0242ac110002Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
1 row in set (0.01 sec)


• 这里的 Seconds_Behind_Master 显示的是Slave 落后 Master 的时间&＃xff0c;已经扣除了两台机器的时钟差距&＃xff0c;显示的就是主从同步延迟时间&＃xff1b;

1.4 验证

• 如果大致没问题&＃xff0c;在主库创建数据库&＃xff0c;即可在从库看到了&＃xff1b;

二、Slave只读

2.1 设置只读的考虑

• 设置只读可以防止备库上执行修改语句&＃xff0c;导致主备不一致&＃xff1b;
• 防止切换过程由bug&＃xff0c;比如双写&＃xff0c;导致主备不一致
• 用readOnly来判断节点角色

状态查询&＃xff1a;show global variables like “%read_only%”;

show global variables like "%read_only%"; 查询只读状态&＃xff0c;如果set global read_only&＃61;1;&＃xff0c;
则read_only为ON&＃xff0c;
set global read_only&＃61;0; read_only 为OFF


• 注意 read_only 不会影响主从同步&＃xff0c;因为同步是超级用户&＃xff0c;只读限制的是非超级用户&＃xff0c;(设置后root用户是可以写的)

2.2 readOnly设置

• 创建用户&＃xff0c;授权增删改查到指定数据库

grant select,insert,update,delete on db_test.* to &＃39;readUser&＃39;&＃64;&＃39;%&＃39; identified by &＃39;123456&＃39;;


• set global read_only &＃61; 1 ; 全局设置只读后&＃xff0c;再插入数据就会提示为只读&＃xff0c;插入失败&＃xff1a;

• 然后执行 set global read_only &＃61; 0 ; 后&＃xff0c;就能继续插入了&＃xff1b;

• 下面附上一段代码&＃xff0c;read_only 是ON

public static void main(String[] args) throws Exception {conn &＃61; DriverManager.getConnection(DB_URL, USER, PASS);String sql &＃61; "show global variables like \"%read_only%\";";Statement statement &＃61; conn.createStatement();ResultSet resultSet &＃61; statement.executeQuery(sql);while (resultSet.next()) {String key &＃61; resultSet.getString("Variable_name");String value &＃61; resultSet.getNString("Value");System.out.println(key &＃43; " ---> " &＃43; value);}}innodb_read_only ---> OFF
read_only ---> ON
super_read_only ---> OFF
transaction_read_only ---> OFF
tx_read_only ---> OFF


参考

• [1] 基于Docker的Mysql主从复制搭建
• [2] MySQL权限整理及授权命令
• [3] mysql授权
• [4] MySQL设置只读模式
• [5] Mysql的read_only 只读属性说明 (运维笔记)