Mysql主主同步详细操作过程
?
此處是在公司線上部署mysql雙主時(shí)所記錄的文檔，為安全，IP都改為內(nèi)網(wǎng)IP。
版本信息：
#mysql -V
mysql? Ver 14.14 Distrib 5.5.37, fordebian-linux-gnu (x86_64) using readline 6.2
?
1、主庫開啟bin-log功能，配置server-id
修改my.cf配置文件，開啟bin-log功能，配置server-id。
#cat /etc/mysql/my.cnf
[myqld]
server-id?????????????? = 1
log_bin???????????????? =/var/log/mysql/
slave-net-timeout??? ???= 60???
#salve-net-timeout默認(rèn)是3600秒，縮短時(shí)間是為了防止雙YES的假象
#（事實(shí)上我已遇到，參考地址：http://www.cnblogs.com/billyxp/p/3470376.html）
如果要指定同步或不同步哪些庫，可使用如下參數(shù)
#binlog-do-db=osyunweidb? ?#需要同步的數(shù)據(jù)庫名，如果有多個(gè)數(shù)據(jù)庫，可重復(fù)此參數(shù)，每個(gè)數(shù)據(jù)庫一行
#binlog-ignore-db=mysql??? #不同步mysql系統(tǒng)數(shù)據(jù)庫
?
2、確認(rèn)bin-log與server-id是否開啟：
查看命令 show variables like 'log_bin'; show variables like 'server_id';
mysql> show variables like 'log_bin';
+---------------+-------+
|Variable_name | Value |
+---------------+-------+
|log_bin?????? | ON???|
+---------------+-------+
1 rowin set (0.00 sec)
mysql>show variables like 'server_id';
+---------------+-------+
|Variable_name | Value |
+---------------+-------+
|server_id???? | 1 ????|
+---------------+-------+
1 rowin set (0.00 sec)
?
3、創(chuàng)建復(fù)制授權(quán)用戶
mysql>?grant replication slave on *.* to replication@'%'identified by '123456'; #授權(quán)該用戶對所有表都能進(jìn)行復(fù)制
mysql>flush privileges; ?#刷新權(quán)限
?
4、鎖表，記錄log-bin文件名和位置
mysql>flush tables with read lock;???? #鎖定所有表，此時(shí)數(shù)據(jù)庫不能寫入數(shù)據(jù)
QueryOK, 0 rows affected (0.05 sec)
?
mysql>show master status;? ????#查看最新bin-log文件及位置
+------------------------+------------+-------------------+-------------------------+
|File?????? ????????????| Position ??| Binlog_Do_DB ?????| Binlog_Ignore_DB ???????|
+------------------------+------------+-------------------+-------------------------+
|mysql-bin.000001????? ?|? 26314????|????????? ?????????|????????????????????????|
+------------------------+------------+-------------------+-------------------------+
1row in set (0.00 sec)
?
5、鎖表狀態(tài)全備mysql數(shù)據(jù)
由于退出當(dāng)前mysql登陸窗口，鎖表功能就失效，需克隆一個(gè)會(huì)話進(jìn)行全備。
#mysqldump-uroot -p -A -B?>/tmp/mysql_bak_2014_10_30.sql.gz
?
看下備份數(shù)據(jù)大小，確認(rèn)備份成功。
#ls? -l /tmp/mysql_bak_2014_10_30.sql.gz
-rw-r--r--1 root root 339222 10月 3011:01 /tmp/mysql_bak_2014_10_30.sql.gz
?
6、解除鎖表
mysql>unlock tables;
或直接quit退出即可。
?
7、從庫開啟bin-log功能，配置server-id
從庫開啟bin-log功能后，待會(huì)在主上在配置同步，互為主從就完成了。
#cat /etc/mysql/my.cnf
[myqld]
server-id?????????????? = 2
log_bin???????????????? =/var/log/mysql/mysql-bin.log
slave-net-timeout??? ???= 60???
#salve-net-timeout默認(rèn)是3600秒，縮短時(shí)間是為了防止雙YES的假象
#（參考地址：http://www.cnblogs.com/billyxp/p/3470376.html）
#/etc/init.d/mysqlrestart
?
8、確認(rèn)從庫bin-log與server-id是否開啟
查看命令 show variables like 'log_bin'; show variables like 'server_id';
mysql>show variables like 'log_bin';
+---------------+-------+
|Variable_name | Value |
+---------------+-------+
|log_bin?????? | ON? ??|
+---------------+-------+
1row in set (0.00 sec)
mysql>show variables like 'server_id';
+---------------+-------+
|Variable_name | Value |
+---------------+-------+
|server_id???? | 2 ????|
+---------------+-------+
1row in set (0.00 sec)
?
9、從庫導(dǎo)入主庫的全備數(shù)據(jù)
在從庫上解壓數(shù)據(jù)。
#gzip-d mysql_bak_2014_10_30.sql.gz
登陸mysql導(dǎo)入數(shù)據(jù)
mysql>source /root/mysql_bak_2014_10_30.sql
?
10、記錄從庫bin-log信息
因?yàn)樵趶膸鞂?dǎo)入全備數(shù)據(jù)時(shí)，此時(shí)主庫與從庫的內(nèi)容是一致的，但是bin-log位置不一定一致。
mysql>show master status;? ????#查看最新bin-log文件及位置
+------------------------+------------+-------------------+-------------------------+
|File?????? ????????????|Position ??| Binlog_Do_DB ?????| Binlog_Ignore_DB ???????|
+------------------------+------------+-------------------+-------------------------+
|mysql-bin.000003? ?????|? 2328055 ??|???????????? ??????|????????????????????????|
+------------------------+------------+-------------------+-------------------------+
1row in set (0.00 sec)
?
11、從庫設(shè)置同步主庫
此處binlog文件與位置狀態(tài)，是主庫在步驟4鎖表時(shí)show master status查看的位置狀態(tài)。
?
CHANGE MASTER TO?
MASTER_HOST='10.0.0.2',
MASTER_PORT=8306,
MASTER_USER='replication',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=26314;
?
12、開啟從庫同步并確認(rèn)同步是否成功
使用start slave開啟同步功能，使用show slave status\G查看同步是否成功
mysql>start slave;
QueryOK, 0 rows affected (0.00 sec)
mysql>show slave status\G? ?#\G不按表格輸出
***************************1. row ***************************
?????????????? Slave_IO_State: Waiting formaster to send event
????????????????? Master_Host: 10.0.0.2
????????????????? Master_User: replication
????????????????? Master_Port: 8306
??????????????? Connect_Retry: 60
????????????? Master_Log_File: mysql-bin.000001
????????? Read_Master_Log_Pos: 136270
?????????????? Relay_Log_File:mysqld-relay-bin.000002
??????????????? Relay_Log_Pos: 72697
??????? Relay_Master_Log_File: mysql-bin.000001
???????????? 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: 98758
????????????? Relay_Log_Space: 110366
????????????? 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: 622?? #查看主從同步延遲，延遲大則可能需要優(yōu)化
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
1row in set (0.00 sec)
?
#sql線程與IO線程都是YES，slave配置成功。
?
13、主庫設(shè)置同步從庫
由于從庫是全備導(dǎo)入，原先在主庫上配置的復(fù)制帳戶也同樣導(dǎo)入，所以這里不用在從庫上新授權(quán)復(fù)制用戶。
從庫上的binlog文件與位置狀態(tài)，是從庫在剛導(dǎo)入時(shí)show master status查看到的位置狀態(tài)。
?
CHANGEMASTER TO?
MASTER_HOST='172.16.0.2',
MASTER_PORT=3306,
MASTER_USER='replication',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=2328055;
#修改相應(yīng)信息，直接把這些配置在mysql中粘貼即可。
?
?
14、開啟同步并確認(rèn)同步是否成功
使用start slave開啟同步功能，使用show slave status\G查看同步是否成功
mysql>start slave;
QueryOK, 0 rows affected (0.00 sec)
?
mysql>show slave status\G
***************************1. row ***************************
?????????????? Slave_IO_State: Waiting formaster to send event
????????????????? Master_Host: 172.16.0.2
????????????????? Master_User: replication
????????????????? Master_Port: 3306
??????????????? Connect_Retry: 60
????????????? Master_Log_File: mysql-bin.000007
????????? Read_Master_Log_Pos: 107
?????????????? Relay_Log_File:mysqld-relay-bin.000006
??????????????? Relay_Log_Pos: 253
??????? Relay_Master_Log_File: mysql-bin.000007
?????????? ??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: 107
????????????? Relay_Log_Space: 556
????????????? 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: 2
1row in set (0.00 sec)
?
#IO線程與sql線程都是正常。
?
15、互為主從測試
在兩臺mysql各創(chuàng)建一個(gè)庫，看兩邊是否都能進(jìn)行同步。
分別在主庫上執(zhí)行 create database test01;
從庫上執(zhí)行create database test02;
看兩臺數(shù)據(jù)庫上執(zhí)行show databases;，看是否都有test01表和test02表。
我的經(jīng)過測試，雙主測試成功。
?
?
總結(jié)
以上是生活随笔為你收集整理的Mysql主主同步详细操作过程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Apache开启Gzip压缩设置(转)
- 下一篇： jQuery.fn.load调用时给ur