mysql互为主从失效,重新同步

一、分别登录服务器A和服务器B的mysql

mysql -u root -p 123456789

二、分别查看数据库状态信息,下边两项参数有一项为NO就表示同步异常

Slave_IO_Running:从服务器（Slave）中的 I/O 线程的运行状态

Slave_SQL_Running:从服务器上的 SQL 线程是否正在运行

mysql> show slave status\G                                                
Slave_IO_Running: Yes
Slave_SQL_Running: No

三、修复异常,重新同步

数据库A

1、停止同步操作

mysql> stop slave;

2、进行锁表，防止数据写入

mysql> flush tables with read lock;

3、备份数据库数据、同步数据库数据

4、查看log日志的同步点位

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

数据库B

5、停止同步操作

mysql> stop slave;

6、备份数据库数据、同步数据库数据

7、设置从库同步，注意该处的同步点，就是数据库Ashow master status信息里的| File| Position两项

mysql> change master to master_host = '192.168.128.100', master_user = 'rsync',  master_port=3306, master_password='', master_log_file =  'mysqld-bin.000001', master_log_pos=3260;

8、打开数据库同步状态

mysql> start slave;

9、参看同步状态信息

Slave_IO_Running、Slave_SQL_Running数据都为yes表示同步成功,为no时可能为7中的File| Position两项配置不正确

mysql> show slave status\G  

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

10、查看log日志的同步点位,供数据库A使用

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |     3260 |              | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

数据库A

11、数据库A同步B

mysql> change master to master_host = '192.168.128.100', master_user = 'rsync',  master_port=3306, master_password='', master_log_file =  'mysqld-bin.000001', master_log_pos=3260;

12、打开从库同步状态

mysql> start slave;

13、查看同步状态信息

mysql> show slave status\G  

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

14、解除表锁定 

UNLOCK TABLES;

四、crontab定时检查数据库同步状态,发现异常时发送消息到钉钉群,脚本如下

<?php

function httpsPost($url = '', $data = []){
	$curl = curl_init();
	
	// 设置请求头
	$headers = array(
			"Content-Type: application/json"
	);
	curl_setopt($curl, CURLOPT_HTTPHEADER, $headers);
	
	curl_setopt($curl, CURLOPT_URL, $url);
	if (stripos($url, "https://") !== false) {
			curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
			curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, false);
			curl_setopt($curl, CURLOPT_SSLVERSION, 1); //CURL_SSLVERSION_TLSv1
	}
	if (!empty($data)) {
			curl_setopt($curl, CURLOPT_POST, 1);
			curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data));
	}
	curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
	$output = curl_exec($curl);
	if (curl_error($curl) != 0) {
			curl_error($curl);
			curl_close($curl);
			return false;
	} else {
			curl_close($curl);
			return $output;
	}
}
	
try {
   
	$dsn = 'mysql:host=127.0.0.1;dbname=数据库名';
	$username = '数据库账号';
	$password = '数据库密码';
	
	$pdo = new PDO($dsn, $username, $password);
	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
	$sql = 'show slave status';
	$stmt = $pdo->prepare($sql);
	$id = 1; // 示例值
	$stmt->bindParam(':id', $id); // 绑定参数以防止SQL注入
	$stmt->execute(); // 执行查询
	$result = $stmt->fetch(PDO::FETCH_ASSOC); // 获取单行结果

	
	if($result["Slave_SQL_Running"] != "Yes"){
		$data = [
			'msgtype' => 'text',
			'text'    =>
				[
					'content' => "@所有人 同学快来:数据库同步异常了"
				],
		];
		//监控群
		$url="钉钉监控群消息接口地址";
		//测试环境
		httpsPost($url,$data);
	}
    echo "数据库同步正常";
		
} catch (PDOException $e) {
    die("Query failed: " . $e->getMessage());
}