一、分別登錄服務器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());
}