MySQL主主復(fù)制（雙主復(fù)制）配置過(guò)程介紹
一、修改配置文件my.cnf
服務(wù)器A（172.16.16.70）配置如下
server_id = 70
socket = /tmp/MySQL.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
binlog-ignore-db=mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 1
服務(wù)器B（172.16.16.71）配置如下:
server_id = 71
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
replicate-ignore-db = mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 2
兩臺(tái)服務(wù)器都重啟，使配置生效:
# service mysqld restart
說(shuō)明:auto-increment-offset是用來(lái)設(shè)定數(shù)據(jù)庫(kù)中自動(dòng)增長(zhǎng)的起點(diǎn)的，回為這兩臺(tái)服務(wù)器都設(shè)定了一次自動(dòng)增長(zhǎng)值2，所以它們的起點(diǎn)必須要不同，這樣才能避免兩臺(tái)服務(wù)器數(shù)據(jù)同步時(shí)出現(xiàn)主鍵沖突.
replicate-do-db 指定同步的數(shù)據(jù)庫(kù)，本例為ixinnuo_sjcj庫(kù)，另外，建議兩臺(tái)服務(wù)器的硬件配置也都一樣。
二、同步數(shù)據(jù),建立復(fù)制賬號(hào):
在服務(wù)器A（172.16.16.70）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.71' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在服務(wù)器B（172.16.16.71）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.70' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
三、執(zhí)行change master命令同步:
在服務(wù)器A（172.16.16.70）上:
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000047 | 411 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
在服務(wù)器B（172.16.16.71）上:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 618 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在服務(wù)器A上執(zhí)行:
mysql> change master to master_host='172.16.16.71',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=618;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
在服務(wù)器B上執(zhí)行:
mysql> change master to master_host='172.16.16.70',master_user='slave',master_password='123456',master_log_file='mysql-bin.000047',master_log_pos=411;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
在兩服務(wù)器都執(zhí)行以下命令:
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
四.查看狀態(tài):
A服務(wù)器（172.16.16.70）狀態(tài)如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 618
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
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: 618
Relay_Log_Space: 460
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: 71
Master_UUID: 32197cd9-2957-11e7-a5c4-525400e493a4
Master_Info_File: /usr/local/mysql/data/master.info
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_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
B服務(wù)器（172.16.16.71）狀態(tài)如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.70
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 411
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000047
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB: mysql,information_schema
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: 411
Relay_Log_Space: 460
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: 70
Master_UUID: f1366940-266c-11e7-92c2-525400f39f79
Master_Info_File: /usr/local/mysql/data/master.info
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_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
說(shuō)明已經(jīng)配置成功了
測(cè)試:
在服務(wù)器A(172.16.16.70)上ixinnuo_sjcj庫(kù)里創(chuàng)建表chenfeng:
mysql> use ixinnuo_sjcj
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table chenfeng (name varchar(10));
Query OK, 0 rows affected (0.23 sec)
mysql> insert into chenfeng values('duansf');
Query OK, 1 row affected (0.13 sec)
mysql> insert into chenfeng values('liuyb');
Query OK, 1 row affected (0.02 sec)
在服務(wù)器B上查看服務(wù)器A上創(chuàng)建的表chenfeng
mysql> select * from chenfeng;
+--------+
| name |
+--------+
| duansf |
| liuyb |
+--------+
2 rows in set (0.00 sec)
mysql> desc chenfeng;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
在服務(wù)器B(172.16.16.71)上ixinnuo_sjcj庫(kù)里創(chuàng)建表duansf:
mysql> create table duansf (name varchar(20));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into duansf values('duansf');
Query OK, 1 row affected (0.02 sec)
mysql> insert into duansf values('liuky');
Query OK, 1 row affected (0.03 sec)
在服務(wù)器上A上查看服務(wù)器B上創(chuàng)建的表duansf
mysql> select * from duansf;
+--------+
| name |
+--------+
| duansf |
| liuky |
+--------+
2 rows in set (0.00 sec)
mysql> desc duansf;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
測(cè)試成功，說(shuō)明MySQL主主復(fù)制配置成功.
服務(wù)器A（172.16.16.70）配置如下
server_id = 70
socket = /tmp/MySQL.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
binlog-ignore-db=mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 1
服務(wù)器B（172.16.16.71）配置如下:
server_id = 71
socket = /tmp/mysql.sock
innodb_buffer_pool_size = 10G
character-set-server=utf8
log_bin=mysql-bin
expire_logs_days=3
replicate-do-db=ixinnuo_sjcj
replicate-ignore-db = mysql,information_schema
auto-increment-increment = 2
auto-increment-offset = 2
兩臺(tái)服務(wù)器都重啟，使配置生效:
# service mysqld restart
說(shuō)明:auto-increment-offset是用來(lái)設(shè)定數(shù)據(jù)庫(kù)中自動(dòng)增長(zhǎng)的起點(diǎn)的，回為這兩臺(tái)服務(wù)器都設(shè)定了一次自動(dòng)增長(zhǎng)值2，所以它們的起點(diǎn)必須要不同，這樣才能避免兩臺(tái)服務(wù)器數(shù)據(jù)同步時(shí)出現(xiàn)主鍵沖突.
replicate-do-db 指定同步的數(shù)據(jù)庫(kù)，本例為ixinnuo_sjcj庫(kù)，另外，建議兩臺(tái)服務(wù)器的硬件配置也都一樣。
二、同步數(shù)據(jù),建立復(fù)制賬號(hào):
在服務(wù)器A（172.16.16.70）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.71' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在服務(wù)器B（172.16.16.71）上:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'172.16.16.70' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
三、執(zhí)行change master命令同步:
在服務(wù)器A（172.16.16.70）上:
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000047 | 411 | | mysql,information_schema | |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)
在服務(wù)器B（172.16.16.71）上:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 618 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在服務(wù)器A上執(zhí)行:
mysql> change master to master_host='172.16.16.71',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=618;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
在服務(wù)器B上執(zhí)行:
mysql> change master to master_host='172.16.16.70',master_user='slave',master_password='123456',master_log_file='mysql-bin.000047',master_log_pos=411;
Query OK, 0 rows affected, 2 warnings (0.34 sec)
在兩服務(wù)器都執(zhí)行以下命令:
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
四.查看狀態(tài):
A服務(wù)器（172.16.16.70）狀態(tài)如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.71
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 618
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
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: 618
Relay_Log_Space: 460
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: 71
Master_UUID: 32197cd9-2957-11e7-a5c4-525400e493a4
Master_Info_File: /usr/local/mysql/data/master.info
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_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
B服務(wù)器（172.16.16.71）狀態(tài)如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.16.70
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 411
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000047
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: ixinnuo_sjcj
Replicate_Ignore_DB: mysql,information_schema
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: 411
Relay_Log_Space: 460
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: 70
Master_UUID: f1366940-266c-11e7-92c2-525400f39f79
Master_Info_File: /usr/local/mysql/data/master.info
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_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
說(shuō)明已經(jīng)配置成功了
測(cè)試:
在服務(wù)器A(172.16.16.70)上ixinnuo_sjcj庫(kù)里創(chuàng)建表chenfeng:
mysql> use ixinnuo_sjcj
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table chenfeng (name varchar(10));
Query OK, 0 rows affected (0.23 sec)
mysql> insert into chenfeng values('duansf');
Query OK, 1 row affected (0.13 sec)
mysql> insert into chenfeng values('liuyb');
Query OK, 1 row affected (0.02 sec)
在服務(wù)器B上查看服務(wù)器A上創(chuàng)建的表chenfeng
mysql> select * from chenfeng;
+--------+
| name |
+--------+
| duansf |
| liuyb |
+--------+
2 rows in set (0.00 sec)
mysql> desc chenfeng;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
在服務(wù)器B(172.16.16.71)上ixinnuo_sjcj庫(kù)里創(chuàng)建表duansf:
mysql> create table duansf (name varchar(20));
Query OK, 0 rows affected (0.18 sec)
mysql> insert into duansf values('duansf');
Query OK, 1 row affected (0.02 sec)
mysql> insert into duansf values('liuky');
Query OK, 1 row affected (0.03 sec)
在服務(wù)器上A上查看服務(wù)器B上創(chuàng)建的表duansf
mysql> select * from duansf;
+--------+
| name |
+--------+
| duansf |
| liuky |
+--------+
2 rows in set (0.00 sec)
mysql> desc duansf;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
測(cè)試成功，說(shuō)明MySQL主主復(fù)制配置成功.
新聞名稱：MySQL主主復(fù)制（雙主復(fù)制）配置過(guò)程介紹
當(dāng)前路徑：http://chinadenli.net/article4/gohiie.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站內(nèi)鏈、網(wǎng)站策劃、建站公司、ChatGPT、網(wǎng)站制作、動(dòng)態(tài)網(wǎng)站
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 綿陽(yáng)網(wǎng)站制作公司：受歡迎的建站公司往往具備這些特點(diǎn)！ 2021-08-09
- 企業(yè)網(wǎng)站建設(shè)前需要準(zhǔn)備哪些資料給建站公司 2015-12-17
- 為什么企業(yè)網(wǎng)站建設(shè)應(yīng)該找專業(yè)建站公司 2015-09-18
- 深圳網(wǎng)站建設(shè)哪家好？挑選建站公司的方法 2015-11-01
- 贛州專業(yè)建站公司：你了解什么是虛擬主機(jī)嗎？ 2021-10-12
- 建網(wǎng)站為何要找建站公司而不是個(gè)人？ 2023-03-15
- 大連做網(wǎng)站哪家知名?大連網(wǎng)站做的知名的建站公司 2022-07-27
- 成都中小型企業(yè)開(kāi)發(fā)網(wǎng)站怎么找建站公司 2013-07-04
- 成都建站公司怎樣建設(shè)網(wǎng)站更高效 2022-06-27
- 建站公司究竟有沒(méi)有前途 2017-08-26
- 建站公司提醒：網(wǎng)站建設(shè)一定要讓文本易于閱讀 2015-01-15
- 怎樣挑選建站公司 2021-10-30