mysql双主不同步问题

1：碰到的问题

mysql双主数据库数据不同步
错误提示类似于：1032等，不仅1032我跳过后还有其他的各种问题
查询网上后，基本是两种解决方案
		1：直接跳过这一步错误，但是因为不同步太多了，跳过之后还会有，可以尝试此方法，但一般无法解决问题
		2：重新备份

1：1032问题解释

 MySQL主从同步的1032错误，一般是指要更改的数据不存在，SQL_THREAD提取的日志无法应用故报错，造成同步失败

（Update、Delete、Insert一条已经delete的数据）。

 1032的错误本身对数据一致性没什么影响，影响最大的是造成了同步失败、同步停止。

2：网络解决方法：

方法1：跳过错误Event

先跳过这一条错误(event),让主从同步恢复正常。（或者N条event，一条一条跳过）

　　stop slave;

　　set global sql_slave_skip_counter=1;

　　start slave;
　　
方法2：重新备份

3：mysql各种错误提示：

2：问题分析

可能是因为当初改配置文件时有个参数写错：在my.cnf中参数server-id两个机器写重复引起的数据不同步。

1：查询service-id相关：

1、 mysql的同步的数据中是包含server-id的，用于标识该语句最初是从哪个server写入的，所以server-id一定要有的

2、 每一个同步中的slave在master上都对应一个master线程，该线程就是通过slave的server-id来标识的；每个slave在master端最多有一个master线程，如果两个slave的server-id 相同，则后一个连接成功时，前一个将被踢掉。 这里至少有这么一种考虑：

    slave主动连接master之后，如果slave上面执行了slave stop；则连接断开，但是master上对应的线程并没有退出；当slave start之后，master不能再创建一个线程而保留原来的线程，那样同步就可能有问题；

3、 在mysql做主主同步时，多个主需要构成一个环状，但是同步的时候有要保证一条数据不会陷入死循环，这里就是靠server-id来实现的

3：前期信息收集

1：查询show slave status\G；命令出现的问题

里面着重关注的点是以下三个文件：

l Master_Log_File，Read_Master_Log_Pos 记录了IO thread读到的当前master binlog文 件和位置， 对应master的binlog文件和位置。

l Relay_Log_File，Relay_Log_Pos记录了SQL thread执行到relay log的那个文件和位置，对应的是slave上的relay log文件和位置。

l Relay_Master_Log_File，Exec_Master_Log_Pos记录的是SQL thread执行到master binlog的文件和位置，对应的master上binlog的文件和位置。

2：借鉴以前的主备方案得出步骤如下：

1. 分别在主库和从库登录数据库：

   $ mysql -uroot -p -h192.168.11.23 -P23306

   停止当前数据同步

   MySQL>stop slave；

2. 备份备库数据

   $ mysqldump -uroot –p –h127.1 –P3306 --all-databases -–events --routines–-single-transactions -–quick –-flush-logs –-master-data=2 > allbackup_20211209.sql

3. 清理备库

   MySQL> drop database LCDMP3;

   MySQL> drop database AIDDI;

   MySQL> drop database AIIPAM;

   停止备库数据库服务

4. $ mysql.server stop

   清理备库上的二进制日志和同步日志(切换到)

   $ cd /lcdmp3_mysqldata/mysqldata

   $ rm bin-log.* relay-log.*（根据实际文件名调整）

5. 修改数据库配置文件my.cnf, 在[mysqld]节点下增加如下内容：

   slave-skip-errors=1032,1062

   并在my.cnf临时关闭二进制日志，如

   \#log-bin = /home/mysql2/mysql/log/binlogs/binlog

6. 导出主库数据

   mysqldump -uroot –p –h127.1 –P3306 –all-databases –events --routines–-single-transactions -–quick –-flush-logs –-master-data=2 > all_new_20211209.sql

7. 将数据上传到备库后，进行数据导入

   nohup mysql–uroot –p –h127.1 –P3306 < all_new_20211209.sql &

8. 在备库配置数据同步

   mysql –uroot –p –h127.1 –P3306 

9. 查看从库数据同步状态

   MYSQL> show slave status\G;

   从库基本同步(Seconds_Behind_Master小于10或为0)后，在备库开启bin-log相关配置,并重启数据服务

10. 1） 修改my.cnf文件如下：

    \#----------------bin-log----------------#

    log-bin = /home/mysql2/mysql/log/binlogs/binlog

    binlog_format = mixed

    expire_logs_days = 7

    binlog_cache_size = 64k

    binlog_checksum = NONE

    log_slave_updates = ON

    重启备库上的数据库服务

3：根据以上方案排版自己的方案如下：

第一个错误：停止slave，暂停同步。

stop slave;只停止了主库的slave，因为当时数据库已经显示不同步了，甚至认为不用停止也没关系。所以只停止了一个，但是在后面备份完备库后，直接drop了备库的数据库，但是过了会发现主库的数据库也被drop掉了？？？

**反省分析：**mysql当出现数据不同步的时候具体分几方面？

因为当时a库的：I/O 线程和SQL线程都是正常的。说明它可以正常从b库使用I/O线程读取二进制文件，然后放到本地中继日志。再使用SQL线程读取本地的中继日志。
所以当时我只是停止了b库的I/O 线程和SQL线程，只是使b库无法再从主库来读取。
但是后来我drop database是在备库进行的，所以主库可以正常获取并执行。

得出结论:

对数据库进行操作时，一定要将两个库分别进行停止同步的操作：stop slave

---

第二个错误：备份一个数据库

不可以完全按照以上方式进行，因为我的数据库是主主同步，或者说是因为我的上面误操作致使两个数据库全部都没有了。

反省分析：

正常应该是暂停备库，然后删除备库的二进制文件和中继日志。
是因为正常情况下有个主库并不会去备库读取二进制文件和中继日志。
但是我的两个库a和b都会互相读取。如果我将有错误的那个备库b清理掉，那么恢复后，a库要根据记录去新的b库寻找文件，但是却没有了，又会产生新的错误。

得出结论：

双主数据库出现不同步的错误，操作要双向进行。

4：制定正确的操作方案

• 停止keepalived，禁止数据写入。先停备库再停主库

systemctl stop keepalived

• 分别登录两个mysql，停止同步，先停备库，再停主库

stop slave；

• 备份主库数据库,使用mysqldump。mysqldump有些参数说明，但我没有加，具体链接如下

mysqldump -uroot -p jhpt > /export/jhpt.sql

https://blog.51cto.com/243662875/2125757

• 清理主库和备库

drop database jhpt；

• 停止主库和备库数据库服务

systemctl stop mysqld

• 清理两个库的二进制文件和中继日志

$ cd /lcdmp3_mysqldata/mysqldata
$ rm bin-log.* relay-log.*（根据实际文件名调整）

• 修改两个数据库的my.cnf文件，增加内容

slave-skip-errors=1032,1062（跳过错误1032，1062）


MySQL主从同步的1032错误，一般是指要更改的数据不存在，SQL_THREAD提取的日志无法应用故报错，造成同步失败
(Update、Delete、Insert一条已经delete的数据)。

• 并在my.cnf中临时关闭二进制日志

如：#log-bin = /home/mysql2/mysql/log/binlogs/binlog（根据实际情况调整，类似于注释掉二进制日志）

• 分别重启两个服务，导入数据

进入两个数据库，分别进行以下操作
source /export/jhpt.sql；

但是以上操作数据量过大，需要挂到后台进行。正确应该用nohup比如：
nohup mysql–uroot –p –h127.1 –P3306 < all_new_20211209.sql &
但是出现报错，未解决，只能使用source来进行，进入数据库：
source /export/jhpt.sql；
ctrl+z
（终止当前操作，并放到后台）我们可以jobs -l在当前session看到！！（所有在此session挂到后台的任务只有此session可以查询看到）
bg 1
（1是当前后台任务编码），但是正常就是在后台运行了，mysql的语句却会跳到前台进行，我们可以忽略，也不用管这个session。
一段时间后这个session就会断开连接，然后我们重新开一个session，可以用ps和top命令来查看，该任务是挂在后台在运行的。

• 在两个库中配置同步

因为配置文件没有修改，可以直接进行以下操作
stop slave;
reset slave;
start slave;

reset slave 将使slave 忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。
使用reset slave之前必须使用stop slave 命令将复制进程停止。

reset slave all其他功能和reset slave 一样，唯一区别是：会立即清理内存里的同步配置信息：即比如 master host, master port, master user, or master password等

• 查看两个库数据同步状态

show slave status\G;

当两个库同步，不报错，Seconds_Behind_Master小于10或为0

• 修改两个库的my.cnf配置文件，将二进制日志加进来，然后先后重启主备服务

systemctl restart mysqld

• 重启keepslived服务

systemctl restart keepalived

• 至此数据库备份已经完成。

5：索引的重要性

• 介绍索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。本节将详细讲解索引的含义、作用和优缺点。

通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

• 索引的优点

优点
索引的优点如下：
通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
可以给所有的 MySQL 列类型设置索引。
可以大大加快数据的查询速度，这是使用索引最主要的原因。
在实现数据的参考完整性方面可以加速表与表之间的连接。
在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

• 索引的缺点

缺点
增加索引也有许多不利的方面，主要如下：
创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

• 索引的使用感触

因为使用的这个数据库查询使用的where比较多，所以添加索引后他的速度会很快。本来需要7分钟多可能现在也就几秒钟。根据查询语句适当使用索引可以提高数据库的效率。