共計 4910 個字符，預計需要花費 13 分鐘才能閱讀完成。
這篇文章給大家分享的是有關如何實現 mysql 主從復制及數據備份恢復的內容。丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，一起跟隨丸趣 TV 小編過來看看吧。
1、主從服務器分別作以下操作：
1.1、版本一致 1.2、初始化表，并在后臺啟動 mysql 1.3、修改 root 的密碼
2、修改主服務器 master:
#vi /etc/my.cnf [mysqld] log-bin=mysql-bin //[必須] 啟用二進制日志 server-id=222 //[必須] 服務器唯一 ID，默認是 1，一般取 IP 最后一段
3、修改從服務器 slave:
#vi /etc/my.cnf [mysqld] log-bin=mysql-bin //[不是必須] 啟用二進制日志 server-id=226 //[必須] 服務器唯一 ID，默認是 1，一般取 IP 最后一段
4、重啟兩臺服務器的 mysql
systemctl restart mysqld
5、在主服務器上建立帳戶并授權 slave:
#/usr/local/mysql/bin/mysql -uroot -pmttang mysql GRANT REPLICATION SLAVE ON *.* to 賬戶 @ 從服務器的 ip identified by 密碼
一般不用 root 帳號，% 表示所有客戶端都可能連，只要帳號，密碼正確，此處可用具體客戶端 IP 代替，如 192.168.145.226，加強安全。
6、登錄主服務器的 mysql，查詢 master 的狀態
mysql show master status; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000004 | 308 | | | +——————+———-+————–+——————+ 1 row in set (0.00 sec) 注：執行完此步驟后不要再操作主服務器 MYSQL，防止主服務器狀態值變化
7、配置從服務器 Slave：
mysql change master to master_host= 192.168.71.128 ,master_user= root ,master_password= root ,master_log_file= mysql-bin.000004 ,master_log_pos=308; // 注意不要斷開，308 數字前后無單引號。Mysql start slave; // 啟動從服務器復制功能
8、檢查從服務器復制功能狀態：
mysql show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.222 // 主服務器地址 Master_User: mysync // 授權帳戶名，盡量避免使用 root Master_Port: 3306 // 數據庫端口，部分版本沒有此行 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 600 //# 同步讀取二進制日志的位置，大于等于 Exec_Master_Log_Pos Relay_Log_File: ddte-relay-bin.000003 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes // 此狀態必須 YES Slave_SQL_Running: Yes // 此狀態必須 YES ……
注：Slave_IO 及 Slave_SQL 進程必須正常運行，即 YES 狀態，否則都是錯誤的狀態 (如：其中一個 NO 均屬錯誤)。
以上操作過程，主從服務器配置完成。
9、主從服務器測試：
主服務器 Mysql，建立數據庫，并在這個庫中建表插入一條數據：
mysql create database hi_db; Query OK, 1 row affected (0.00 sec) mysql use hi_db; Database changed mysql create table hi_tb(id int(3),name char(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into hi_tb values(001, bobu Query OK, 1 row affected (0.00 sec) mysql show databases; +——————–+ | Database | +——————–+ | information_schema | | hi_db | | mysql | | test | +——————–+ 4 rows in set (0.00 sec)
從服務器 Mysql 查詢：
mysql show databases; +——————–+ | Database | +——————–+ | information_schema | | hi_db | //I M here，大家看到了吧 | mysql | | test | +——————–+ 4 rows in set (0.00 sec) mysql use hi_db Database changed mysql select * from hi_tb; // 查看主服務器上新增的具體數據 +——+——+ | id | name | +——+——+ | 1 | bobu | +——+——+ 1 row in set (0.00 sec)
10、完成： 編寫一 shell 腳本，用 nagios 監控 slave 的兩個 yes（Slave_IO 及 Slave_SQL 進程），如發現只有一個或零個 yes，就表明主從有問題了，發短信警報吧。
MySQL 主從同步故障-Slave_SQL_Running: No
故障現象：進入 slave 服務器，運行：
mysql show slave statusG ……. Relay_Log_File: localhost-relay-bin.000535 Relay_Log_Pos: 21795072 Relay_Master_Log_File: localhost-bin.000094 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: ……
解決辦法一、
首先停掉 Slave 服務：
mysql stop slave;
到主服務器上查看主機狀態：記錄 File 和 Position 對應的值 進入 master
mysql show master status;+———————-+———-+————–+——————+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+———————-+———-+————–+——————+| localhost-bin.000094 | 33622483 | | | +———————-+———-+————–+——————+1 row in set (0.00 sec)
然后到 slave 服務器上執行手動同步：
mysql change master to master_log_file= loccalhost-bin.000094 ,master_log_pos=33622483;mysql start slave;// 開啟同步
再查詢狀態看是否正常
mysql show slave statusG*************************** 1. row ***************************…….. Master_Log_File: localhost-bin.000094 Read_Master_Log_Pos: 33768775 Relay_Log_File: localhost-relay-bin.000537 Relay_Log_Pos: 1094034 Relay_Master_Log_File: localhost-bin.000094 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB:
注意: 手動同步需停止主庫的寫操作!
解決辦法二、
mysql stop slave;mysql set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;mysql start slave;
說明：Slave_IO_Running：連接到主庫，并讀取主庫的日志到本地，生成本地日志文件 Slave_SQL_Running: 讀取本地日志文件，并執行日志里的 SQL 命令。
數據備份:
格式：mysqldump - h 鏈接 ip -P(大寫) 端口 - u 用戶名 - p 密碼數據庫名 d:XX.sql(路勁)
示例：mysqldump -uroot -p8888 htgl d:htgl.sql 如出現報錯 Warning: Using a password on the command line interface can be insecure. 則需要修改配置文件:
vim /etc/mysql/my.cnf[mysqldump]user=your_backup_user_namepassword=your_backup_password
修改完配置文件，重啟 MyQL 后，只需要執行 mysqldump 腳本就可以了。備份腳本中不需要涉及用戶名密碼相關信息。示例:mysqldump htql d:htql.sql
數據恢復:
1、系統命令行
格式：mysql - h 鏈接 ip -P(大寫) 端口 - u 用戶名 - p 密碼 數據庫名 d:XX.sql(路勁)
mysql -uusername -ppassword db1 tb1tb2.sql
2、或 mysql 命令行
mysql user db1;source tb1_tb2.sql;
3、恢復整個數據庫的方法：
mysql -u b_user -h 101.3.20.33 -p H_password -P3306 all_database.sql
感謝各位的閱讀！關于“如何實現 mysql 主從復制及數據備份恢復”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！
