MYSQL主从复制

1. 环境

主机地址	端口号	描述
本机 (192.168.8.114)	13306	主
本机 (192.168.8.114)	23306	从

2. 授权外部访问

use mysql;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

update user set Host='%' where user='root';

ERROR 1062 (23000): Duplicate entry '%-root' for key 'user.PRIMARY' // 不影响

flush privileges;

Query OK, 0 rows affected (0.00 sec)

3. 异步主从

文件位置：/etc/my.cnf

1. 主机配置

# [必选] 指定mysql唯一标识
server-id=1

# [可选] 指定binlog文件名称
log-bin=mysql-bin

# [可选,建议填写] binlog的格式 (mixed/statement/row)
binlog_format=mixed

# [可选] 设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=sys

# [可选] 设置要复制的数据库
# binlog-do-db=需要复制的数据库
# binlog-do-db=需要复制的数据库

2. 从机配置

# [必选] 指定mysql唯一标识
server-id=2

# [可选] 指定binlog文件名称
# log-bin=mysql-bin

# [可选] 配置中继日志
relay_log=mysql-slave-bin

# [可选,建议填写] binlog的格式 (mixed/statement/row)
binlog_format=mixed

# [可选] 设置不要复制的数据库
# binlog-ignore-db=mysql
# binlog-ignore-db=sys
# [可选] 设置要复制的数据库
# binlog-do-db=需要复制的数据库
# binlog-do-db=需要复制的数据库

[!IMPORTANT]
修改配置后需要重启mysql服务

3. 主机创建用户同步的账号

CREATE USER 'salave1'@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'salave1'@'%';

# 不加这句 后面需要加上一句
ALTER USER 'salave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

flush privileges;

4. 查询主机Master状态

show master status;

记录File，Position
File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
mysql-bin.000001 1150 mysql,sys
1 row in set (0.00 sec)
[!WARNING]
执行完后不要再操作主数据库

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000001	1150		mysql,sys

5. 从机配置同步指令

CHANGE MASTER TO
MASTER_HOST='主服务器IP', 
MASTER_USER='主服务器用户名',
MASTER_PORT='3306', # 默认3306不用填写
MASTER_PASSWORD='主服务器密码',
MASTER_LOG_FILE='mysql-bin.xxxxx',
MASTER_LOG_POS=XXX;

# GET_SOURCE_PUBLIC_KEY=1; 如果不设置为mysql_native_password,则需要加上这句

# 例如:
CHANGE MASTER TO MASTER_HOST='192.168.8.114', MASTER_PORT=13306, MASTER_USER='salave1', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1150;

Query OK, 0 rows affected, 9 warnings (0.02 sec)

启动

start slave;

Query OK, 0 rows affected, 1 warning (0.01 sec)

停止

#从库:
stop slave;
#主库:
reset master;

Query OK, 0 rows affected, 1 warning (0.01 sec)

查看状态

show slave status\G;

需要保证下面两项为Yes

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

1. row **

        Slave_IO_State: Waiting for source to send event
           Master_Host: 192.168.8.114
           Master_User: salave1
           Master_Port: 13306
         Connect_Retry: 60
       Master_Log_File: mysql-bin.000001
   Read_Master_Log_Pos: 1150
        Relay_Log_File: mysql-slave-bin.000002
         Relay_Log_Pos: 326
 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: 1150
       Relay_Log_Space: 536
       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_UUID: e363fcec-96ca-11f0-8795-2a4e6aa1306f
      Master_Info_File: mysql.slave_master_info
             SQL_Delay: 0
   SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Replica 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, 1 warning (0.00 sec)

ERROR:
No query specified

验证

在主数据库中新增数据库/表/数据, 查看同步状态

其他提示

[!CAUTION]
当从数据库新增数据有冲突后无法正常同步
先查看状态
查看输出中的这些字段，它们会告诉你SQL线程为什么停止：
Last_SQL_Error
Last_SQL_Errno
Last_SQL_Error_Timestamp
原因A：重复的主键或数据冲突
STOP SLAVE;
SET GLOBAL sql_replica_skip_counter = 1;
START SLAVE;

# 方法B：在配置文件中设置自动跳过重复键错误
#编辑 my.cnf 文件：
#[mysqld]
#replica_skip_errors = 1062
原因B：从库上手动修改了数据导致冲突
-- 重新初始化从库数据
STOP REPLICA;
RESET REPLICA ALL;

-- 重新导入主库的数据快照，然后重新配置复制
原因C：表不存在或结构不一致
-- 检查具体的错误信息，然后手动创建缺失的表
预防措施
为了避免未来的重复键错误：
确保从库初始数据与主库一致
避免手动在从库上修改数据
使用GTID复制（可以更好地处理冲突）
-- 在主库和从库上启用GTID
[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON

-- 然后使用自动定位
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.8.114',
  SOURCE_PORT=13306,
  SOURCE_USER='master1',
  SOURCE_PASSWORD='123456',
  SOURCE_AUTO_POSITION = 1,
  GET_SOURCE_PUBLIC_KEY=1;

4. 半同步主从(生成环境)

文件位置：/etc/my.cnf

1. 主机配置

[mysqld]
# 加载插件
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

# [必选] 指定mysql唯一标识
server-id=1

# [可选] 指定binlog文件名称
log-bin=mysql-bin

# 插件配置
rpl_semi_sync_master_enabled = 1  # 在master开启半同步模式
rpl_semi_sync_master_timeout = 1000  # 主机在某次事务中的等待时间, 默认10000ms   超时后降为异步同步

# [可选,建议填写] binlog的格式 (mixed/statement/row)
binlog_format=mixed

# [可选] 设置不要复制的数据库
binlog-ignore-db=mysql
binlog-ignore-db=sys

# [可选] 设置要复制的数据库
# binlog-do-db=需要复制的数据库
# binlog-do-db=需要复制的数据库

2. 从机配置

[mysqld]
# 加载插件
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# [必选] 指定mysql唯一标识
server-id=2

# [可选] 指定binlog文件名称
# log-bin=mysql-bin

# [可选] 配置中继日志
relay_log=mysql-slave-bin

rpl_semi_sync_slave_enabled = 1

# [可选,建议填写] binlog的格式 (mixed/statement/row)
binlog_format=mixed

# [可选] 设置不要复制的数据库
# binlog-ignore-db=mysql
# binlog-ignore-db=sys
# [可选] 设置要复制的数据库
# binlog-do-db=需要复制的数据库
# binlog-do-db=需要复制的数据库

# 查询插件状态
SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';

3. 查询主机Master状态

show master status;

File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
mysql-bin.000002 157 mysql,sys
1 row in set (0.00 sec)
[!WARNING]
执行完后不要再操作主数据库

File	Position	Binlog_Do_DB	Binlog_Ignore_DB	Executed_Gtid_Set
mysql-bin.000002	157		mysql,sys

4. 从机配置同步指令

CHANGE MASTER TO
MASTER_HOST='主服务器IP', 
MASTER_USER='主服务器用户名',
MASTER_PORT='3306', # 默认3306不用填写
MASTER_PASSWORD='主服务器密码',
MASTER_LOG_FILE='mysql-bin.xxxxx',
MASTER_LOG_POS=XXX;

# GET_SOURCE_PUBLIC_KEY=1; 如果不设置为mysql_native_password,则需要加上这句

# 例如:
CHANGE MASTER TO MASTER_HOST='192.168.8.114', MASTER_PORT=13306, MASTER_USER='salave1', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157;

Query OK, 0 rows affected, 9 warnings (0.02 sec)

启动

start slave;

Query OK, 0 rows affected, 1 warning (0.01 sec)

停止

#从库:
stop slave;
#主库:
reset master;

Query OK, 0 rows affected, 1 warning (0.01 sec)

查看状态

show slave status\G;

需要保证下面两项为Yes

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

1. row **

        Slave_IO_State: Waiting for source to send event
           Master_Host: 192.168.8.114
           Master_User: salave1
           Master_Port: 13306
         Connect_Retry: 60
       Master_Log_File: mysql-bin.000001
   Read_Master_Log_Pos: 1150
        Relay_Log_File: mysql-slave-bin.000002
         Relay_Log_Pos: 326
 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: 1150
       Relay_Log_Space: 536
       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_UUID: e363fcec-96ca-11f0-8795-2a4e6aa1306f
      Master_Info_File: mysql.slave_master_info
             SQL_Delay: 0
   SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Replica 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, 1 warning (0.00 sec)

ERROR:
No query specified

验证

在主数据库中新增数据库/表/数据, 查看同步状态

5. 读写分离

1. 引入时机

大多数业务往往读多写少, 当达到业务瓶颈后, 需要使用到读写分离, 需要创建主从库, 主库用来写, 从库用来读

主机: 写 , (读)

从机: 读

优化: 主库只写的话，可以不加索引，提升写的效率，从库增加多个索引
读写分离架构可以消除读写锁的冲突从而提升数据库的读写性能
但是需要注意：主从同步延时和读写分配机制的问题

2. 主从同步延时

优化方案:

写后立刻读主库 (例如: 写入后5s从主库读, 之后从从库读)
二次查询策略 (先从从库里面读, 读不到再去主库里读)
业务特殊处理 (实时性要求高的放主库里读写, 次要业务全部从从库读)

3. 落地

基于编程实现 (应用层)
1. 优点: 灵活, 规则可自定义;
2. 缺点: 有入侵性, 多个从机选择规则(随机/权重/轮询),需要自定义
基于数据库代理实现(数据网关)
目前性能不错的数据库中间件
- MySQL Proxy：官方提供的MySQL中间件，实现负载均衡、读写分离等
- MyCat：阿里云开源Cobar
- ShardingSphere：
- Atlas：

6. MySQL-Proxy的使用

官网下载地址

1. 复制到容器中

docker cp E:\Downloads\mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz 4c7981af5e1e:/home

2. 容器中解压

tar -xvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz

3. 配置mysql-proxy

touch /etc/mysql-proxy.cnf
vi /etc/mysql-proxy.cnf

[mysql-proxy]
user=root  # 运行用户
admin-username=root  # 主库从库的账户名
admin-password=root  #  主库从库的密码
log-level=debug  # 日志级别 从高到底 (error|warning|info|message|debug)
log-file=/var/log/mysql-proxy.log  
proxy-address=127.0.0.1:4040  # 运行端口, 默认4040
proxy-backend-addresses=192.168.8.144:13306 #,192.168.8.114:13307     # 指定后端主机Master写入数据,多个用,隔开,下同
proxy-read-only-backend-addresses=192.168.8.114:23306     # 指定后端从salve读取数据
proxy-lua-script=/home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua
admin-lua-script=/home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/admin-sql.lua
daemon=true   # 是否采用后台模式运行
keepalive=true  # 当mysql-proxy崩溃时尝试重启

使用中不建议加入中文注释,以下是简版

[mysql-proxy]
user=root
admin-username=root
admin-password=root
log-level=debug
log-file=/var/log/mysql-proxy.log  
proxy-address=127.0.0.1:4040
proxy-backend-addresses=192.168.8.144:13306
proxy-read-only-backend-addresses=192.168.8.114:23306
proxy-lua-script=/home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua
admin-lua-script=/home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/admin-sql.lua
daemon=true
keepalive=true

[!CAUTION]
rw-splitting.lua 中设置了最小达到4个连接数的时候才开启读写分离, 测试时可做修改

vim /home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua

找到
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 4,
max_idle_connections = 8,
is_debug = false
}
end
将min_idle_connections 设置为1

4. 相关命令

cd /home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/bin
ls

mysql-binlog-dump mysql-myisam-dump mysql-proxy

5. 启动服务命令

./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

#./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --daemon
## 或使用后台模式
#nohup ./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf > /var/log/mysql-proxy.log 2>&1 &

6. 查询是否启动

1. 查看日志

cat /var/log/mysql-proxy.log

2. 查看进程

ps -ef | grep mysql

1. 环境

2. 授权外部访问

3. 异步主从

1. 主机配置

2. 从机配置

3. 主机创建用户同步的账号

4. 查询主机Master状态

5. 从机配置同步指令

启动

停止

查看状态

验证

其他提示

4. 半同步主从(生成环境)

1. 主机配置

2. 从机配置

3. 查询主机Master状态

4. 从机配置同步指令

启动

停止

查看状态

验证

5. 读写分离

1. 引入时机

2. 主从同步延时

3. 落地

6. MySQL-Proxy的使用

1. 复制到容器中

2. 容器中解压

3. 配置mysql-proxy

4. 相关命令

5. 启动服务命令

6. 查询是否启动

1. 查看日志

2. 查看进程

评论 (0)