分库分表 3：主从复制（从入门到精通）

2024-02-02 20:07:22

狂创客圈经典图书：《Netty Zookeeper Redis 高并发实战》面试必备 + 面试必备 + 面试必备【博客园总入口】
疯狂创客圈经典图书：《SpringCloud、Nginx高并发核心编程》大厂必备 + 大厂必备 + 大厂必备【博客园总入口】
入大厂+涨工资必备：高并发【亿级流量IM实战】实战系列【 SpringCloud Nginx秒杀】实战系列【博客园总入口】

《SpringCloud Nginx 高并发核心编程》环境搭建 - 系列

组件	链接地址
【必须】虚拟机Linux 开发环境准备	https://www.cnblogs.com/crazymakercircle/p/14194688.html
Linux openresty 安装	Linux openresty 安装
【必须】Linux Redis 安装（带视频）	Linux Redis 安装（带视频）
【必须】Linux Zookeeper 安装（带视频）	Linux Zookeeper 安装, 带视频
Windows Redis 安装（带视频）	Windows Redis 安装（带视频）
RabbitMQ 离线安装（带视频）	RabbitMQ 离线安装（带视频）
ElasticSearch 安装, 带视频	ElasticSearch 安装, 带视频
Nacos 安装（带视频）	Nacos 安装（带视频）

【必须】Eureka	Eureka 入门，带视频
【必须】springcloud Config 入门，带视频	springcloud Config 入门，带视频
【必须】SpringCloud 脚手架打包与启动	SpringCloud脚手架打包与启动
Linux 自启动假死自启动定时自启	Linux 自启动假死启动

Sharding-JDBC 从入门到精通 - 目录

组件	链接地址
准备一：在window安装虚拟机集群	vagrant+java+springcloud+redis+zookeeper镜像下载(&制作详解)）
而且：在虚拟机上需要安装 mysql	centos mysql 笔记（内含vagrant mysql 镜像）

分库分表 Sharding-JDBC 从入门到精通之一	Sharding-JDBC 入门实战
分库分表 Sharding-JDBC 从入门到精通之二	Sharding-JDBC 基本原理
分库分表 Sharding-JDBC 从入门到精通之三	MYSQL主从复制
分库分表 Sharding-JDBC 从入门到精通之四	读写分离

分库分表 Sharding-JDBC 从入门到精通之源码	git

MYSQL主从复制配置

练习的场景

主从实战的数据库场景：

建议有三虚拟机：cdh1、 cdh2、 cdh3（cdh3可以不开启）。

cdh1 上的 user_db为主，cdh1 上的 user_db为从，仅仅开启这两个库的复制，其他的库如store，不开启主从复制。
有关学习环境的快速搭建，请参考本系列博客的准备一：在window安装虚拟机集群。

配置主数据库

登录主库

mysql  ‐uroot ‐p123456

创建用于主从复制的账号

set global validate_password_policy=0;
set global validate_password_length=1;
create user db_sync identified by '123456';

创建库和表


mysql> CREATE DATABASE user_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use user_db;
Database changed

mysql> create table t_user_0( id bigint , name varchar(40) );
Query OK, 0 rows affected (0.02 sec)

授权主备复制专用账号

GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync';

刷新权限

FLUSH PRIVILEGES;

修改 MySQL 配置文档 /etc/my.cnf，在 [mysqld] 段添加以下配置：

[mysqld] 
#开启日志，启用二进制日志
log‐bin = mysql‐bin 

#设置服务器标识ID，主从不能一致 ，每台服务器唯一
server‐id = 1      

#设置需要同步的数据库 
binlog‐do‐db=user_db 

#屏蔽不需要同步的数据库 
binlog‐ignore‐db=mysql
binlog‐ignore‐db=information_schema
binlog‐ignore‐db=performance_schema
binlog‐ignore‐db=dolphinscheduler
binlog‐ignore‐db=sys
binlog‐ignore‐db=store

参数说明：

log-bin
　　该参数只要配置就表示开启了MySQL的bin log日志功能，注意改参数的值是我们自定义的，我们自定义的值将作为bin log的名称的前缀信息哟，我们可以使用MySQL命令"show variables like '%log_bin%';"查看咱们的配置。

server-id
　　该参数可以指定数据库服务器的唯一标识。在同一个复制组下的所有实例的server_id都必须是唯一的，而且取值必须是正整数，取值范围是1~(232)−1

可以通过show databases，查看所有的库，然后找出不需要同步的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dolphinscheduler   |
| mysql              |
| performance_schema |
| store              |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

完整配置如下：

[root@cdh1 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
log-bin=user-mysql-bin
server-id=1

binlog-do-db=user_db
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=dolphinscheduler
binlog-ignore-db=sys
binlog-ignore-db=store

skip-name-resolve
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

配置从服务器

登录从库

mysql  ‐uroot ‐p123456

创建用于主从复制的账号

set global validate_password_policy=0;
set global validate_password_length=1;
create user db_sync identified by '123456';

创建库和表

mysql> CREATE DATABASE user_db DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use user_db;
Database changed

mysql> create table t_user_0( id bigint , name varchar(40) );
Query OK, 0 rows affected (0.02 sec)

授权主备复制专用账号

GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY '123456';

刷新权限

FLUSH PRIVILEGES;

修改 MySQL 配置文档 /etc/my.cnf，在 [mysqld] 段添加以下配置：

[mysqld] 

log‐bin=mysql‐bin  #开启日志，启用二进制日志
server‐id=2 #设置服务器标识ID，主从不能一致 ，每台服务器唯一

#设置需要同步的数据库 
replicate_wild_do_table=user_db.% 
#屏蔽系统库同步 
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=dolphinscheduler.%
replicate_wild_ignore_table=sys.%
replicate_wild_ignore_table=store.%

重启数据库

systemctl restart mysqld

完整的配置文件如下：

[root@cdh2 ~]# vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
log-bin=user-mysql-bin
server-id=101

replicate_wild_do_table=user_db.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=dolphinscheduler.%
replicate_wild_ignore_table=sys.%
replicate_wild_ignore_table=store.%

skip-name-resolve
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

主从复制的配置

设置主库

首先连接 master db，为从库的账号授予复制的权利

GRANT REPLICATION SLAVE ON . TO 'db_sync'@'%' IDENTIFIED BY '123456';

如果权限不够，就执行一下下面的两句：

set global validate_password_policy=0;
set global validate_password_length=1;

刷新权限

FLUSH PRIVILEGES;

在主库查看同步的起点，记录下文件名以及起点位置
show master status;

查看数据库状态：

记录 File 的值（ user-mysql-bin.000001 ）和 Position 的值（154），等会配置 slave 服务器的时候要用。

设置从库

接下来在 slave db上，配置主从复制：

先停止同步

STOP SLAVE;

修改从库的master配置，指向到主库，配置过程中，需要使用上一步记录的文件名以及位点

mysql>change master to
master_host='cdh1',
master_user='db_sync',
master_log_file='user-mysql-bin.000001',
master_log_pos=154,
master_port=3306,
master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

解释下配置参数

master_host='x.x.x.x' // 这里填 master 主机 ip
master_log_file='user-mysql-bin.000001', // 这里填写 File 的值
master_log_pos=154,// 这里填写 Position 的值。

启动同步

START SLAVE;

查看同步

show slave status\G;

结果如下：

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: cdh1
                  Master_User: db_sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: user-mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: cdh2-relay-bin.000004
                Relay_Log_Pos: 377
        Relay_Master_Log_File: user-mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: user_db.%
  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,dolphinscheduler.%,sys.%,store.%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 2531
              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: fb8d483c-47eb-11eb-ba17-0800276c3e95
             Master_Info_File: /var/lib/mysql/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:
1 row in set (0.00 sec)

查看Slave_IO_Runing和Slave_SQL_Runing字段值都为Yes，表示同步配置成功。如果不为Yes，请排
查相关异常。

如果不小心配置错, 输入 mysql> stop slave; 然后重新录入一遍就可以了。

问题：

问题1：由于是克隆的虚拟机，所以mysql的uuid相同，报错如下：

The slave I/O thread stops because master and slave have equal MySQL server UUID

原因：主从复制的mysql实例的uuid不能相同。

修改方法：在从库上，从my.cnf找到data目录，然后定位到auto.cnf目录，找到 auto.cnf文件，进行uuid的调整。

具体如下：

$ vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=fb8d483c-47eb-11eb-ba17-0800276c3e96

修改之后：

[root@cdh2 ~]#  cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=fb8d483c-47eb-11eb-ba17-0800276c3e96

最终，确保两个mysql 实例的uuid不同。

然后，重启改动过的实例。

主从复制测试

1 将主库的t_user_0表复制一份t_user_0_copy1，稍等一会，刷新从库，发现从库也有了一个t_user_0_copy1表，表明从库复制了主库的t_user_0_copy1表。

2 修改主库的t_user_0_copy1，名称为t_user_1，大约过去10s，刷新从库，从库的t_user_0_copy1名称改为t_user_1

回到◀疯狂创客圈▶

疯狂创客圈 - Java高并发研习社群，为大家开启大厂之门

码农公寓

《SpringCloud Nginx 高并发核心编程》 环境搭建 - 系列

Sharding-JDBC 从入门到精通 - 目录

MYSQL主从复制配置

练习的场景

配置主数据库

配置从服务器

主从复制的配置

设置主库

设置从库

问题：

主从复制测试

回到◀疯狂创客圈▶

相关文章

《SpringCloud Nginx 高并发核心编程》环境搭建 - 系列