《Mysql學習mysql數據庫主從同步》要點：
本文介紹了Mysql學習mysql數據庫主從同步，希望對您有用。如果有疑問，可以聯系我們。
導讀：當時想的試一試能不能用mysql自帶的功能做數據庫災備,后來發現mysql數據庫主從同步會有一些問題.第一個不好腳本化的東西是在同步之前需保證兩邊的數據庫初始信息一樣
環境：
Mater:?? CentOS7.1? 5.5.52-MariaDB? 192.168.108.133
Slave:?? CentOS7.1? 5.5.52-MariaDB? 192.168.108.140
1.導出主服務數據,將主備初始數據同步master:
//從master上導出需要同步的數據庫信息
mysqldump -u*** -p*** --database test > test.sql
//將master上的備份信息傳輸到slave上
scp /root/test.sql root@192.168.108.140:/opt/
slave:
//進入slave的數據庫
mysql -u*** -p***
//清空test數據庫
drop database test
//導入master的test數據庫信息
source /opt/test.sql
2.配置master和slave上的mysql數據庫master:
//修改master的my.cnf文件
vim /etc/my.cnf
//master配置如下,在[mysqld]下添加如下配置
#log-bin
server-id????????? =?? 1
log_bin??????????? =?? master-bin
expire_logs_days?? =?? 10
max_binlog_size??? =?? 100M
binlog-do_db?????? =?? test
binlog_ignore_db?? =?? mysql
//重啟mysql數據庫
service mysqld restart
//如果安裝的是mariadb可以重啟mariadb
systemctl restart mariadb.service
slave:
//修改slave的my.cnf文件
vim /etc/my.cnf
//slave配置如下,在[mysqld]下添加如下配置
server-id???????? =?? 2
//重啟mysql數據庫
service mysqld restart
//如果安裝的是mariadb可以重啟mariadb
systemctl restart mariadb.service
簡單說明一下參數配置,保證主備server-id唯一.在master上需要開啟mysql的binlog,log_bin=master_bin,指定binlog文件的名稱.
3.創建一個復制用戶,具有replication slave 權限,能保證slave能把master的數據同步過去master:
grant replication slave on *.* to 'replication'@'192.168.108.140' identified by 'replication';
4.獲取master的binlog位置master：
//進入mysql數據庫
mysql -u*** -p***
//設置讀鎖
flush tables with read lock;
//獲取mysql的binlog文件信息和偏移量
show master status;
+-------------------+----------+--------------+------------------+
| File????????????? | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000010 |???? 3713 | test???????? | mysql??????????? |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
//解鎖
unlock tables;
5.設置備端數據庫
//進入mysql數據庫
mysql -u*** -p***
//停止slave
stop slave;
//設置對應master的binlog信息
MariaDB [(none)]> change master to
??? -> master_host='192.168.108.133',
??? -> master_user='replication',
??? -> master_password='replication',
??? -> master_log_file='master-bin.000010',
??? -> master_log_pos=3713;
//啟動slave
start slave;
6.查看備端狀態
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
?????????????? Slave_IO_State: Waiting for master to send event
????????????????? Master_Host: 192.168.108.133
????????????????? Master_User: replication
????????????????? Master_Port: 3306
??????????????? Connect_Retry: 60
????????????? Master_Log_File: master-bin.000010
????????? Read_Master_Log_Pos: 3881
?????????????? Relay_Log_File: mariadb-relay-bin.000002
??????????????? Relay_Log_Pos: 698
??????? Relay_Master_Log_File: master-bin.000010
???????????? Slave_IO_Running: Yes
??????????? Slave_SQL_Running: Yes
????????????? Replicate_Do_DB:
????????? Replicate_Ignore_DB:
?????????? Replicate_Do_Table:
?????? Replicate_Ignore_Table:
????? Replicate_Wild_Do_Table:
? Replicate_Wild_Ignore_Table:
?????????????????? Last_Errno: 0
?????????????????? Last_Error:
???????????????? Skip_Counter: 0
????????? Exec_Master_Log_Pos: 3881
????????????? Relay_Log_Space: 994
????????????? Until_Condition: None
?????????????? Until_Log_File:
??????????????? Until_Log_Pos: 0
?????????? Master_SSL_Allowed: No
?????????? Master_SSL_CA_File:
?????????? Master_SSL_CA_Path:
????????????? Master_SSL_Cert:
??????????? Master_SSL_Cipher:
?????????????? Master_SSL_Key:
??????? Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
??????????????? Last_IO_Errno: 0
??????????????? Last_IO_Error:
?????????????? Last_SQL_Errno: 0
?????????????? Last_SQL_Error:
? Replicate_Ignore_Server_Ids:
???????????? Master_Server_Id: 1
1 row in set (0.00 sec)
ERROR: No query specified
如果：Slave_IO_Running: Yes,Slave_SQL_Running: Yes則為配置成功,配置錯誤重復上面操作即可.如果解決不了可通過查看mysql日志分析處理.
vim /var/log/mariadb/mariadb.log
7.測試.
其實測試沒啥好寫的,配置成功之后直接連到主從數據庫,在master上改變表、字段、數據,slave會同步變化.
寫在最后：當時想的試一試能不能用mysql自帶的功能做數據庫災備,后來發現mysql數據庫主從同步會有一些問題.第一個不好腳本化的東西是在同步之前需保證兩邊的數據庫初始信息一樣,因為備端配置的mysql-binlog位置只是當前主數據庫信息的位置,在該位置之前的數據只能通過人工導入.第二個就是mysql主從同步時,只能進行數據庫的增量同步,不能進行全量同步；還有就是如果備端出現臟數據,多了一條數據,當主那邊新增一條主鍵相同的數據,則同步失敗.之后我會嘗試的能不能把這些操作腳本化,發現mysql自帶的同步功能限制性很大,并且手工干預的東西太多了.
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/5791.html
