阅读 219

实现Mysql的主从复制

这是我参与8月更文挑战的第8天,活动详情查看:8月更文挑战

准备工作

配置网络

  • 通过VMware,复制三台虚拟机:master、slave1、slave2

  • 修改IP地址
# master: 192.168.200.11
# slave1: 192.168.200.12
# slave2: 192.168.200.13
# 打开配置文件
vim /etc/sysconfig/network-scripts/ifcfg-ens33

#IP的配置方法[none|static|bootp|dhcp](引导时不适用协议|静态分配IP|BOOTP协议|DHCP协议)
BOOTPROTO="static"
#IP地址
IPADDR=192.168.200.11
#网关
GATEWAY=192.168.200.2
#域名解析器
DNS1=192.168.200.2
复制代码
  • 重启网络服务或重启系统生效
#重启网络 
service network restart
#重启系统  
shutdown -r now 
#重启系统  
reboot
#查看ip
ifconfig
复制代码

安装mysql

  • 创建目录
#创建目录
mkdir /opt/software
#进入该目录
cd /opt/software
复制代码
  • 使用xftp将mysql5.7上传到该目录

  • 解压安装

#解压
tar -xvf mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar

复制代码

原理

  1. 当Master节点进行insert、update、delete操作时,会按顺序写入到binlog(二进制日志)中。
  2. salve从库连接master主库,Master有多少个slave就会创建多少个binlog dump线程。
  3. 当Master节点的binlog(二进制日志)发生变化时,binlog dump 线程会通知所有的salve节点,并将相应的binlog内容推送给slave节点。
  4. I/O线程接收到 binlog 内容后,将内容写入到本地的 relay-log(中继日志)。
  5. SQL线程读取I/O线程写入的relay-log,并且根据 relay-log 的内容对从数据库做对应的操作。

知道了主从复制的原理,我们也可以很清楚的知道,读写分离的写操作是必须要在master节点上进行,因为salve节点实现数据统一根据的是master节点的binlog,我们如果在slave节点进行写操作,在master节点进行读操作,二者数据是不会统一的,主从复制也就失去的意义。

主实例搭建

  • 修改mysql配置文件/etc/my.cnf
[mysqld]
## 设置server_id,同一局域网中需要唯一
server_id=11
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能
log-bin=mall-mysql-bin
## 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
复制代码
  • 修改完配置后重启实例:

    service mysqld restart
    复制代码
  • 创建数据同步用户:

# 连接数据库
mysql -uroot -proot

# 创建数据同步用户
# 为slave1创建同步账号
CREATE USER 'slave1'@'192.168.200.12' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave1'@'192.168.200.12';
# 为slave2创建同步账号
CREATE USER 'slave2'@'192.168.200.13' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave2'@'192.168.200.13';
复制代码

不出意外的话,这里mysql会报错:

原因是因为密码设置的过于简单会报错,MySQL有密码设置的规范,具体是与validate_password_policy的值有关,下图表明该值规则

我们可以看一下mysql的初始密码规则

SHOW VARIABLES LIKE 'validate_password%';
复制代码

修改密码的验证策略:

set global validate_password_policy=0;
复制代码

密码的长度和validate_password_length有关,我们将密码长度降低点

set global validate_password_length=4;
复制代码

这样就没问题了

从实例搭建

从实例配置都基本一致,这里只以一个从实例做讲解

  • 修改mysql配置文件/etc/my.cnf
[mysqld]
## 设置server_id,同一局域网中需要唯一
# 另一个从实例可以配置为103
server_id=102  
## 指定不需要同步的数据库名称
binlog-ignore-db=mysql
## 开启二进制日志功能,以备Slave作为其它数据库实例的Master时使用
# 另一个从实例可以配置为mall-mysql-slave2-bin 
log-bin=mall-mysql-slave1-bin 
## 设置二进制日志使用内存大小(事务)
binlog_cache_size=1M
## 设置使用的二进制日志格式(mixed,statement,row)
binlog_format=mixed
## 二进制日志过期清理时间。默认值为0,表示不自动清理。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=mall-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## slave设置为只读(具有super权限的用户除外)
read_only=1
复制代码
  • 修改完配置后重启实例:
service mysqld restart
复制代码

将主从数据库进行连接

  • 连接到主数据库的mysql客户端,查看主数据库状态:
show master status;
复制代码
  • 主数据库状态显示如下:

  • 连接从数据库的mysql的客户端
mysql -uroot -proot
复制代码
  • 在从数据库中配置主从复制
change master to master_host='192.168.200.11', master_user='slave1', master_password='123456', master_port=3306, master_log_file='mall-mysql-bin.000001', master_log_pos=1136, master_connect_retry=30;
复制代码

slave2 需将master_user、master_password替换为先前配置的第二个账号,做到一个库一个号

  • 主从复制命令参数说明:

    • master_host:主数据库的IP地址;
    • master_port:主数据库的运行端口;
    • master_user:在主数据库创建的用于同步数据的用户账号;
    • master_password:在主数据库创建的用于同步数据的用户密码;
    • master_log_file:指定从数据库要复制数据的日志文件,通过查看主数据的状态,获取File参数;
    • master_log_pos:指定从数据库从哪个位置开始复制数据,通过查看主数据的状态,获取Position参数;
    • master_connect_retry:连接失败重试的时间间隔,单位为秒。
  • 查看主从同步状态:

show slave status \G;
复制代码
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.200.11
                  Master_User: slave2
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000001
          Read_Master_Log_Pos: 1136
               Relay_Log_File: mall-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mall-mysql-bin.000001
             Slave_IO_Running: No		#表示还没开始同步
            Slave_SQL_Running: No		#表示还没开始同步
              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: 1136
              Relay_Log_Space: 154
              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: NULL
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: 0
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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)

复制代码
  • 开启主从同步:
start slave;
复制代码
  • 查看从数据库状态发现仍然没同步:
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.200.11
                  Master_User: slave2
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000001
          Read_Master_Log_Pos: 1136
               Relay_Log_File: mall-mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mall-mysql-bin.000001
             Slave_IO_Running: Connecting	
            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: 1136
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'slave2@192.168.200.11:3306' - retry-time: 30  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             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: 210811 22:01:39
     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.01 sec)
复制代码

现在插一句,先解决一部分小伙伴遇到的其他情况:Slave_IO_Running: No

哈哈,这其实是有个坑的,这一部分的小伙伴应该是先安装mysql,之后再进行复制的。

因为mysql 有个uuid , 然而uuid 是唯一标识的,所以克隆过来的uuid是一样的,只需要修改一下uuid 就ok了,找到auto.cnf 文件修改uuid

#退出mysql
mysql> exit;
#查询auto.cnf
find / -iname "auto.cnf"
#修改 这里写你找到的路径
vim /var/lib/mysql/auto.cnf

# 原内容
[auto]
server-uuid=0661e980-f858-11eb-9073-000c2965b894

# 调整后内容,自定义即可,把最后一位4替换为5
[auto]
server-uuid=0661e980-f858-11eb-9073-000c2965b895
#保存退出
#重启mysql
systemctl restart mysql
#上述命令不可以就使用这个(不同linux版本,命令不一样)
service mysqld restart

#登录mysql
mysql -uroot -p

#重启slave
stop slave;
start slave;
#查看状态
show slave status \G;
复制代码

这样,你可能就是我出现的这种情况了,哈哈哈。(如果你都是Yes的话就可以跳过这部分了

Slave_IO_Running: Connecting,Slave_SQL_Running:Yes出现这种情况,我想到的就是没访问到master虚拟机,难道是防火墙的问题?

查看一下master的防火墙状态

systemctl status firewalld
复制代码

果然,防火墙开着的,由于我防火墙没有放行3306端口(虽然别的端口也没放行)。

我就用比较暴力的方法了:关防火墙,你们别学我,你们还是老实放行端口吧

#关闭防火墙
systemctl stop firewalld
#查看防火墙状态
systemctl status firewalld
复制代码

防火墙关闭成功。

我们再次进入其中一个slave测试:

#重启slave
stop slave;
start slave;
#查看状态
show slave status \G;
复制代码
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.200.11
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mall-mysql-bin.000001
          Read_Master_Log_Pos: 1136
               Relay_Log_File: mall-mysql-relay-bin.000002
                Relay_Log_Pos: 325
        Relay_Master_Log_File: mall-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: 1136
              Relay_Log_Space: 537
              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: 11
                  Master_UUID: f8b4cd3b-f857-11eb-9172-000c293b13fb
             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)
复制代码

到此,成功了!!

主从复制测试

  • 在主实例中创建一个数据库mall

  • 在从实例中查看数据库,发现也有一个mall数据库,可以判断主从复制已经搭建成功。

至此,我们完成了mysql的主从复制。

再多嘴一句,配置好主从复制,我们如果使用windows上的连接工具(博主使用的是navicat)连接我们虚拟机的数据库时,可能会出现下面这些情况(要会举一反三)

注意观察:这里提示的时192.168.200.1连接出问题,而我连接的mysql地址是192.168.200.11(master),应该是权限不足,root权限只能在本地访问,我们可以修改root权限,也可以新建一个用户用来远程访问:

#创建一个新用户
grant all privileges on *.* to 用户名@localhost identified by “123456” ;
#设置用户可以在远程访问mysql
grant all privileges on *.* to 用户名@”%” identified by “123456” ;
复制代码

再次连接:

成功。

思考:这类问题,给我们的提示都是192.168.200.1出现问题,所以是这里出了问题,我们也没有给192.168.200.1分配账号,所以连接失败

如果想配置读写分离,请移步我的另一篇博客,导航栏搜索shardingjdbc,下拉文章到读写分离即可

文章分类
后端
文章标签