MYSQL主从同步

MYSQL主从同步

1 主从简介

1.1 原理

1. master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中
2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
3. 同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

也就是说：

• 库会生成两个线程,一个I/O线程,一个SQL线程
• I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
• 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
• SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行;

1.2 特点

• 读写分离
• 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换
• 高可用HA
• 架构扩展

1.3 主从形式

• 一主一从
• 一主多从
• 多主一从
• 双主复制
• 联级复制

2 mysql主从同步部署

为确保从数据库与主数据库里的数据一样

//先查看主库有哪些库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
//再查看从库有哪些库
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.004 sec)

在主数据库创建一个同步账号授权给从数据库使用
MariaDB [(none)]> create user 'sjk'@'192.168.136.227' identified by '1';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant replication slave on *.* to 'sjk'@'192.168.136.227';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

配置主数据库

[root@master ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

log-bin = mysql_bin  //启用binlog日志
server-id = 10   //数据库服务器唯一标识符，主库的server-id值必须比从库的小

[root@master ~]# service mysqld restart

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)

从数据库
[root@slave ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
skip-name-resolve

server-id = 20      //设置从库的唯一标识符，从库的server-id值必须大于主库的该值
relay-log = mysql_relay      //启用中继日志relay-log

[root@slave ~]# service mysqld restart

//配置并启动主从复制
mysql> change master to 
    -> master_host='192.168.235.135',
    -> master_user='sjk',
    -> master_password='1',
    -> master_log_file='mysql_bin.000001',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.136.227
                  Master_User: sjk
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql_relay.000003
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql_bin.000004
             Slave_IO_Running: Yes  //此处必须为Yes
            Slave_SQL_Running: Yes  //此处必须为Yes

验证

在主数据库创建zz

mysql> create database zz;
Query OK, 1 row affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema | 
| zz                 |
+--------------------+
5 rows in set (0.02 sec)

在从服务器操控是否同步

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| zz                 |
+--------------------+

主从配置（主数据库有数据）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| zz                 |
+--------------------+
5 rows in set (0.03 sec)

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | yx    |   20 |
|  2 | xsssx |   23 |
+----+-------+------+
2 rows in set (0.03 sec)


全备主从数据库
全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.14 sec)

//备份主库并将备份文件传送到从库
[root@master ~]# mysqldump -uroot -p1 --all-databases > /opt/all-`date +%F`.sql

[root@master ~]# ls /opt/
all-2021-08-30.sql 
[root@master ~]# scp /opt/all-2021-08-30.sql  root@192.168.136.227:/opt/
root@192.168.136.227's password: 
all-2021-08-30.sql                       100%    0     0.0KB/s   00:00    
[root@master ~]# 

[root@slave ~]# mysql -uroot -p1 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| zz                 |
+--------------------+


在主数据库创建账号同步给从数据库使用
mysql> create user 'cs'@'192.168.136.227' identified by "1";
Query OK, 0 rows affected (0.05 sec)

mysql> grant  replication slave on *.* to  'cs'@'192.168.235.145';
Query OK, 0 rows affected (0.04 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

配置主从数据库
[root@master ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin = mysql_bin
server-id = 10

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000003 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.02 sec)

mysql> change master to
    -> master_host='192.168.136.227',
    -> master_user='cs',
    -> master_password='1',
    -> master_log_file='mysql_bin.000003',
    -> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.136.227
                  Master_User: cs
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql_relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: YES
            Slave_SQL_Running: YES

验证

删除数据库yy
mysql> drop database zz;
Query OK, 1 row affected (0.06 sec)

在从数据库中查看数据是否同步：
[root@slave ~]# mysql -uroot -p1 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+