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定时检查数据库同步状态,发现异常时发送消息到钉钉群,脚本如下
<?phpfunction 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());
}