Mysql雙主模式

1、Mysql雙主模式安裝mysqlyum list mysql-serveryum install mysql-serverservice mysqld start修改mysql密碼： mysql set password for rootlocalhost = password(123); Centos6.5搭建Mysql雙主模式環(huán)境：Centos6.5 mysql 5.*設(shè)置同步賬號密碼及權(quán)限D(zhuǎn)b1：mysql grant all privileges on *.* to root39 identified by root with grant option;mysq

2、l flush privileges;Db2：grant all privileges on *.* to root39 identified by root with grant option;mysql flush privileges;測試：mysql -h 33 -usroot -prootmysql -h 34 -usroot -proot修改MySQL配置文件修改 vim /etc/f以root登陸DBS1，vim /etc/f 文件：Vim /etc/f在mysqld的配置項中增加如下配置:clientpor

3、t = 3306socket = /var/lib/mysql/mysql.sockmysqlddatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0port=3306skip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 25

4、6sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 2log-bin=mysql-binrelay-log=mysql-relay-logbinlog_format=mixedserver-id = 1auto-increment-increment=10auto-increment-offset=1以root用戶登錄ServDB

5、2，修改ServDB2的f文件:clientport = 3306socket = /var/lib/mysql/mysql.sockmysqldport=3306datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0skip-external-lockingkey_buffer_size = 256Mmax_allowed_packet

6、= 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 2log-bin=mysql-binbinlog_format=mixedserver-id = 2auto-increment-increment=10auto-increment-offset=2mysqldumpquickm

7、ax_allowed_packet = 16Mmysqlno-auto-rehashmyisamchkkey_buffer_size = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2M分別啟動數(shù)據(jù)庫:分別查看binlog日志位置SDB1 上 mysql show master status;SDB2 上 mysql show master status;兩臺數(shù)據(jù)庫設(shè)置連接命令：SDB1：CHANGE MASTER TO MASTER_HOST=33,MASTER_USER=root,MASTER_

8、PASSWORD=root,MASTER_LOG_FILE=mysql-bin.000007,MASTER_LOG_POS=106;疑難小結(jié)：（有的機器可能遇到這個問題）ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log解決辦法：stop slave;reset slave;SDB2：CHANGE MASTER TO MASTER_HOST=34,MASTER_USER=root,MA

9、STER_PASSWORD=root,MASTER_LOG_FILE=mysql-bin.000003,MASTER_LOG_POS=106;mysql start slave; 啟動從服務(wù)器mysql SHOW SLAVE STATUSG; 查看這兩項是否為YES，yes為正常。Slave_IO_Running:YesSlave_SQL_Running:YesCentos6.5搭建Mysql5.6雙主模式環(huán)境：centos 6.5 mysql 5.6 service DB1: 34 service DB2: 491.設(shè)置同步賬號密碼及權(quán)限S

10、ervice DB1mysqlgrantallprivilegeson*.*tosync49identifiedby123withgrantoption;QueryOK,0rowsaffected(0.02sec)mysqlflushprivileges;QueryOK,0rowsaffected(0.02sec)Service DB2mysqlgrantallprivilegeson*.*tosync34identifiedby123withgrantoption;QueryOK,0rowsaffected(0.02sec)mysqlflu

11、shprivileges;QueryOK,0rowsaffected(0.02sec)測試：ServiceDB1:mysql-h49-usync-p123ServiceDB2:mysql-h34-usync-p1232.修改mysql的配置文件：以root用戶登錄ServDB1，修改ServDB1的f文件vi /etc/f在mysqld的配置項中增加如下配置：clientport = 3306socket = /tmp/mysql.sockmysqldport = 3306socket = /tmp/mysql.sockbasedir=/us

12、r/local/mysqldatadir=/data/mydataskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 2log-bin=mysql-binre

13、lay-log=mysql-relay-logbinlog_format=mixedserver-id = 1auto-increment-increment=10auto-increment-offset=1以root用戶登錄ServDB2，修改ServDB2的f文件clientport = 3306socket = /tmp/mysql.sockmysqldport = 3306socket = /tmp/mysql.sockbasedir=/usr/local/mysqldatadir=/data/mydataskip-external-lockingkey_buffer_size =

14、256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 2log-bin=mysql-binbinlog_format=mixedserver-id = 2auto-increment-increment=10auto-increment-

15、offset=2mysqldumpquickmax_allowed_packet = 16Mmysqlno-auto-rehashmyisamchkkey_buffer_size = 128Msort_buffer_size = 128Mread_buffer = 2Mwrite_buffer = 2M分別啟動數(shù)據(jù)庫12rootbogondata#servicemysqldstartStartingMySQL. OK 3.分別查看binlog日志位置：Server DB1 上：mysqlshowmasterstatus;+-+-+-+-+-+|File|Position|Binlog_Do_D

16、B|Binlog_Ignore_DB|Executed_Gtid_Set|+-+-+-+-+-+|mysql-bin.000004|120|+-+-+-+-+-+1rowinset(0.00sec)ServerDB2上：mysqlshowmasterstatus;+-+-+-+-+-+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|+-+-+-+-+-+|mysql-bin.000008|120|+-+-+-+-+-+1rowinset(0.00sec)4.兩臺數(shù)據(jù)庫設(shè)置連接Server DB1:CHANGEMAST

17、ERTOMASTER_HOST=49,MASTER_USER=sync,MASTER_PASSWORD=123,MASTER_LOG_FILE=mysql-bin.000004,MASTER_LOG_POS=120;mysql start slave; 啟動從服務(wù)器mysql SHOW SLAVE STATUSG; 查看這兩項是否為YES，yes為正常。Slave_IO_Running:YesSlave_SQL_Running:Yes* 1. row * Slave_IO_State: Waiting for master to send event Master_H

18、ost: 49 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 219 Relay_Log_File: mysql-relay-log.000002 Relay_Log_Pos: 382 Relay_Master_Log_File: mysql-bin.000008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replic

19、ate_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: 219 Relay_Log_Space: 555 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: M

20、aster_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_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 Master_UUID: 44d1d14b-884e-11e5-865a-000c29c70f2e Master

21、_Info_File: /data/mydata/ SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpat

22、h: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)ERROR:No query specifiedServer DB2:CHANGE MASTER TOMASTER_HOST=34,MASTER_USER=sync,MASTER_PASSWORD=123,MASTER_LOG_FILE=mysql-bin.000008,MASTER_LOG_POS=120;mysql start slave; 啟動從服務(wù)器mysql SHOW SLAVE STATUSG;

23、查看狀態(tài)* 1. row * Slave_IO_State: Waiting for master to send event Master_Host: 34 Master_User: sync Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 120 Relay_Log_File: test2-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000

24、004 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: 120 Relay_Log_Space: 456 Until_Condition: None Until

25、_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: 0Master_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 Master_UUID: 15c66da4-a39b-11e5-b85e-000c297221e8 Master_Info_File: /data/mydat