mysql主从同步

 mysql主从同步原理

 

MySQL主从同步的核心在于二进制日志（Binary Log）的复制。当主服务器上的数据发生变更时（如INSERT、UPDATE、DELETE等操作），这些变更会被记录到二进制日志(Binary log)中。

随后，从服务器会连接到主服务器，请求并接收这些二进制日志的内容，并将其写入到自己的中继日志（Relay Log）中。

最后，从服务器会读取中继日志中的事件，并重新执行这些事件，以保证从服务器上的数据与主服务器保持一致。

主机名	IP地址	资源
mysql-master	192.168.88.20
mysql-slave	192.168.88.21

1.安装mysql

[root@mysql-master ~]# yum install -y mysql mysql-server
Last metadata expiration check: 0:02:29 ago on Wed 11 Oct 2023 08:40:52 AM CST.
Package mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!
[root@mysql-master ~]# systemctl enable --now mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.

2.mysql-master开起binlog日志

[root@mysql-master ~]# cat /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
server-id=20
log-bin=mysql-master20
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

3.创建同步用户并授权

#创建了一个名为"tongbu"的用户，并设置了密码为"123"。用户的访问权限限定为可以从任何主机（通过通配符"%"表示）连接到MySQL服务器。
mysql> create user tongbu@"%" identified by "123";
Query OK, 0 rows affected (0.06 sec)
#使tongbu用户可以作为从服务器进行复制
mysql> grant replication slave on *.* to tongbu@"%";
Query OK, 0 rows affected (0.06 sec)

4.查看mysql当前日志偏移量

[root@mysql-master ~]# mysql -uroot -p123

mysql> show master status;
+-----------------------+----------+--------------+------------------+-------------------+
| File                  | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysql-master20.000001 |      667 |              |                  |                   |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5.mysql-slave安装mysql服务并设置server-id

[root@mysql-master ~]# yum install -y mysql mysql-server

Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@mysql-slave ~]# vim /etc/my.cnf.d/mysql-server.cnf 
[root@mysql-slave ~]# systemctl restart mysqld.service 
[root@mysql-slave ~]# cat /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
server-id=21
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

[root@mysql-slave ~]# systemctl enable --now mysqld

5.从服务器认主

mysql> change master to master_host="192.168.88.20" , master_user="tongbu" , master_password="123" ,master_log_file="mysql-master20.000001" , master_log_pos=667;
Query OK, 0 rows affected, 8 warnings (0.80 sec)

6.开起线程并查看同步状态

mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.88.20
                  Master_User: tongbu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-master20.000001
          Read_Master_Log_Pos: 969
               Relay_Log_File: mysql-slave-relay-bin.000002
                Relay_Log_Pos: 329
        Relay_Master_Log_File: mysql-master20.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 7.验证数据同步

主库操作
mysql> create database gamedb;
Query OK, 1 row affected (0.17 sec)
mysql> create table gamedb.user(name varchar(30),level int(10));
Query OK, 0 rows affected, 1 warning (0.35 sec)
mysql> insert into gamedb.user values('十里坡剑神',30);
#主库查看插入数据
mysql> select * from gamedb.user;
+-----------------+-------+
| name            | level |
+-----------------+-------+
| 十里坡剑神      |    30 |
+-----------------+-------+
1 row in set (0.00 sec)

从库查询数据是否同步
mysql> select * from gamedb.user;
+-----------------+-------+
| name            | level |
+-----------------+-------+
| 十里坡剑神      |    30 |
+-----------------+-------+
1 row in set (0.01 sec)

8.如果同步输入错误，导致同步不成功，如何进行重新同步？（若有重要数据，请先备份）

#从库操作
mysql> reset master;
Query OK, 0 rows affected (0.12 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.30 sec)
#主库中重新查询日志偏移量

  mysql> show master status;
  +-----------------------+----------+--------------+------------------+-------------------+
  | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  +-----------------------+----------+--------------+------------------+-------------------+
  | mysql-master20.000001 | 969 | | | |
  +-----------------------+----------+--------------+------------------+-------------------+

 #从库重新认主

  mysql> change master to master_host="192.168.88.20" , master_user="tongbu" , master_password="123" ,master_log_file="mysql-master20.000001" , master_log_pos=969;