MySQL数据库集群技术主从复制 一主一从详细讲解

时间:2024-02-25 10:18:45

集群技术

集群概述 MySQL复制技术

集群目的

  • 负载均衡

    • 解决高并发

  • 高可用HA

    • 服务可用性

  • 远程灾备

    • 数据有效性

 

类型

  • 一主一从

  • 一主双从

  • 双主双从

原理

 

 

  • 概念

    • 在主库上把数据更改(DDL DML DCL)记录到二进制日志(Binary Log)中。

    • 备库I/O线程将主库上的日志复制到自己的中继日志(Relay Log)中。

    • 备库SQL线程读取中继日志中的事件,将其重放到备库数据库之上。

  • 环境

    • 全新服务器-互相通信

    • 全新安装mysql80-分别安装

    • 配置域名解析

 

master1:192.168.142.139
slave1:192.168.142.140
[root@service ~]# vim /etc/hosts
192.168.142.139 master1
192.168.142.140 slave1

主服务器配置

  • 部署一台新mysql服务器。准备好域名解析。

  • 准备数据

mysql> create database master1db;
Query OK, 1 row affected (0.05 sec)

mysql> use master1db;
Database changed
mysql> create table master1db.master1tab(name char(50));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into master1db.master1tab values (1111);
Query OK, 1 row affected (0.02 sec)

mysql> insert into master1db.master1tab values (2222);
Query OK, 1 row affected (0.00 sec)
  • 开启二进制日志

[root@service ~]# vim /etc/my.cnf
server-id=1
log_bin
[root@service ~]# systemctl restart mysqld
  • 创建复制用户

mysql> create user 'rep'@'192.168.142.%' identified by 'Wyxbuke00.';
Query OK, 0 rows affected (0.03 sec)

mysql> grant replication slave,replication client on *.* to 'rep'@'192.168.142.%' ;
Query OK, 0 rows affected (0.00 sec)
  • 备份master数据库的数据

[root@service ~]# mysqldump -uroot -p'Wyxbuke00.' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F` -mysql-all.sql
[root@service ~]# ll
总用量 444848
-rw-r--r--. 1 root root         0  2月 22 16:12 2024-02-22
-rw-r--r--. 1 root root   1288580  2月 22 16:10 2024-02-22-mysql-all.sql
观察二进制日志分割点
-- CHANGE MASTER TO MASTER_LOG_FILE='service-bin.000003', MASTER_LOG_POS=157;
  • 准备数据2

mysql> insert into master1db.master1tab values (33333333);
Query OK, 1 row affected (0.00 sec)

mysql> insert into master1db.master1tab values (44444);
Query OK, 1 row affected (0.00 sec)

从服务器配置

  • 测试rep用户是否可用

[root@server ~]# mysql -urep -pWyxbuke00. -h master1
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
  • 启动服务器序号

[root@server ~]# systemctl stop firewalld
[root@server ~]# setenforce 0
[root@server ~]# vim /etc/my.cnf
不用在从设备上开启二进制日志,没有人向从设备请求日志。

server-id=2
[root@server ~]# systemctl restart mysqld
  • 手动同步数据

从主服务器发送
[root@service ~]# scp 2024-02-22-mysql-all.sql slave1:/tmp/
root@slave1's password: 
2024-02-22-mysql-all.sql
到从服务器查看
[root@server ~]# ls /tmp
2024-02-22-mysql-all.sql  

[root@server ~]# mysql -u root -p
Enter password:
mysql> set sql_log_bin=0;\
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/2024-02-22-mysql-all.sql ;
Query OK, 0 rows affected (0.00 sec)

mysql> use master1db;
Database changed
mysql> select *from master1db.master1tab;
+------+
| name |
+------+
| 1111 |
| 2222 |
+------+
2 rows in set (0.00 sec)
  • 设置主服务器

mysql> change master to master_host='master1', master_user='rep', master_password='Wyxbuke00.',master_log_file='localhost-bin.000004',master_log_pos=157;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
CHANGE MASTER TO MASTER_LOG_FILE=‘mysqld-bin.000004’,MASTER_LOG_POS=157;
  • 启动从设备

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
  • 查看启动状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: master1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: localhost-bin.000003
          Read_Master_Log_Pos: 157
               Relay_Log_File: server-relay-bin.000004
                Relay_Log_Pos: 126
        Relay_Master_Log_File: binlog.000038
             Slave_IO_Running: No
            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: 126
              Relay_Log_Space: 337
              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: 13114
                Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: d5ddcb15-d063-11ee-a4cc-000c2904e4be
             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: 240222 16:42:23
     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

此时,发生报错 Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Could not find first log file name in binary log index file';

解决方案

到从服务器输入
mysql> stop slave
    -> ;
到master机器登陆mysql:

记录master的bin的位置,例如:mysql> show master status;
mysql> show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| service-bin.000004 |      157 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
必要时使用flush log;进行刷新
到从数据库修改:

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_log_file='service-bin.000004',master_log_pos=157;
Query OK, 0 rows affected, 3 warnings (0.01 sec)

mysql> change master to master_host='master1',master_user='rep',master_password='Wyxbuke00.',master_log_file='service-bin.000004';
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: master1
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: service-bin.000004
          Read_Master_Log_Pos: 157
               Relay_Log_File: server-relay-bin.000002
                Relay_Log_Pos: 328
        Relay_Master_Log_File: service-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: 157
              Relay_Log_Space: 539
              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: d5ddcb15-d063-11ee-a4cc-000c2904e4be
             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)
问题解决!
  • 返回主服务器(master1)更新数据,在从服务器(slave1)观察是否同步。

主服务器添加数据
mysql> insert into master1db.master1tab values (55555);
Query OK, 1 row affected (0.00 sec)
从服务器查看数据是否同步
mysql> select * from master1db.master1tab;
+-------+
| name  |
+-------+
| 1111  |
| 2222  |
| 55555 |
+-------+
3 rows in set (0.00 sec)