MySQL主从同步_表与表之间数据同步,如何判断没有同步的数据-CSDN博客

本文链接：https://blog.csdn.net/m0_37642477/article/details/114417052

单台服务器在企业业务不断发展中必然会遇到访问压力与单点故障，主从复制的架构在一定程度上解决了这个问题。一方面可以实现流量分流，可以让外部请求直接访问主库，内部人员访问从库；另一方面，从库可以当做备份服务器实现主库故障时的手动切换，以满足正常运行的基本要求。

主从同步基本原理

主库打开binlog日志记录功能，主从同步就是从库去主库上获取这个binlog日志，并且将binlog日志中记录执行的SQL语句在从库上执行一次
从库上执行start slave命令即可开始主从同步，主从的数据复制就开始了
主从同步开始进行后，从库上的I/O线程就会发送验证请求到主库请求建立连接，并指定位置（这个位置是在从库上执行chage master时指定的）后的binlog日志
主库在收到从库的验证请求后会返回从库请求的内容，这些内容包括binlog日志的内容，主库中新纪录binlog日志的名称以及新的binlog日志中下一个指定位置点等信息
从库收到主库发送来的binlog日志后会将其存在relay log中，并将新的binlog文件名以及位置点信息存储在master.info文件中
从库的SQL线程会实时查看本地relay log线程新增的内容，然后把relay log文件中的内容解析为SQL语句并顺序的在从库一一执行，最后将当前服务器中的中继日志的文件名及位置点信息存储在relay-log.info中以便下次同步需要

主从同步部署

1、服务器环境配置：

IP	角色	操作系统
192.168.11.128	主数据库	CentOS7.7
192.168.11.129	从数据库	CentOS7.7

2、数据库安装

3、主数据库开启binlog日志

/etc/my.cnf配置文件添加如下配置

log_bin=/opt/sudytech/mysql/data/mysql-bin

重启数据库后会发现在/opt/mysql/data目录中会生成两个文件mysql-bin.000001和mysql-bin.index,mysql-bin.000001是记录binlog日志的文件，而index是存放mysql-bin文件名的文件

$ cat mysql-bin.index 
/opt/sudytech/mysql/data/mysql-bin.000001
/opt/sudytech/mysql/data/mysql-bin.000002

实际同步中，为了安全考虑，主数据库的用户授权信息是不会同步给从库的，这就必须显式的在配置文件中告诉mysql在不要同步mysql库。如果binlog-format是ROW模式，那么忽略同步mysql库的语法是replicate-wild-ignore-table=mysql.%(该配置加在从库数据库的配置文件中);如果是STATEMENT模式，语法为replicate-ignore-db=mysql

binlog 三种格式

ROW

ROW格式会记录每行记录修改的记录，这样可能会产生大量的日志内容,比如一条update语句修改了100条记录，那么这100条记录的修改都会被记录在binlog日志中，这样造成binlog日志量会很大，这种日志格式会占用大量的系统资源，mysql5.7和myslq8.0安装后默认就是这种格式。

STATEMENT

记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）所以大大减少了binlog日志量，节约磁盘IO，提高性能,看上面的图解可以很好的理解row和statement 两种模式的区别。但是STATEMENT对一些特殊功能的复制效果不是很好，比如：函数、存储过程的复制。由于row是基于每一行的变化来记录的，所以不会出现类似问题

MIXED

实际上就是前两种模式的结合。在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.11 sec)

4、主库配置同步用户并对从库授权

mysql> GRANT REPLICATION SLAVE ON *.* To 'root'@'192.168.11.129' IDENTIFIED BY 'Sudy@12344';               
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

查看主库状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     1372 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5、配置从库

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.11.128',
    -> MASTER_PORT=3306,
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='Sudy@12344',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=1372;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

查看从库同步状态

mysql> show slave status\G;
*************************** 1. row ***************************
*************************** 略***************************
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
*************************** 略***************************
1 row in set (0.00 sec)`

Slave_IO_Running: No表示存在错误，查看从库日志错误日志

2020-07-25T14:18:43.822184Z 6 [ERROR] Slave I/O for channel '': Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593

大意为两台数据库的server_id一致。修改从库/etc/my.cnf配置文件中server_id =2 重启从库数据库。

重新查看从库同步状态，发现两个参数的值均为YES时，成功。

主从同步测试

主库创建数据库并写入数据

mysql> create database db_test;
Query OK, 1 row affected (0.00 sec)

mysql> use db_test;
Database changed
mysql> CREATE TABLE employees (
    ->     emp_no      INT             NOT NULL COMMENT '主键',
    ->     birth_date  DATE            NOT NULL COMMENT '生日',
    ->     first_name  VARCHAR(14)     NOT NULL COMMENT '用户-姓',
    ->     last_name   VARCHAR(16)     NOT NULL COMMENT '用户-名',
    ->     gender      ENUM ('M','F')  NOT NULL COMMENT '性别',
    ->     hire_date   DATE            NOT NULL COMMENT '入职时间',
    ->     PRIMARY KEY (emp_no)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO employees VALUES
    -> (10001,'1953-09-02','Georgi','Facello','M','1986-06-26'),
    -> (10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21'),
    -> (10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

登录从库查看数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use db_test;

mysql> show tables;`
+-------------------+`
| Tables_in_db_test |`
+-------------------+`
| employees         |`
+-------------------+`
1 row in set (0.00 sec)`

mysql> select * from employees;`
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |

10 rows in set (0.00 sec)