MySQL主主復(fù)制（雙主復(fù)制）配置過程介紹

發(fā)布時(shí)間：2020-08-07 11:27:16 來源：ITPUB博客閱讀：153 作者：chenfeng 欄目：MySQL數(shù)據(jù)庫

一、修改配置文件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

說明:auto-increment-offset是用來設(shè)定數(shù)據(jù)庫中自動(dòng)增長的起點(diǎn)的，回為這兩臺(tái)服務(wù)器都設(shè)定了一次自動(dòng)增長值2，所以它們的起點(diǎn)必須要不同，這樣才能避免兩臺(tái)服務(wù)器數(shù)據(jù)同步時(shí)出現(xiàn)主鍵沖突.
replicate-do-db 指定同步的數(shù)據(jù)庫，本例為ixinnuo_sjcj庫，另外，建議兩臺(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)

說明已經(jīng)配置成功了

測(cè)試:
在服務(wù)器A(172.16.16.70)上ixinnuo_sjcj庫里創(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庫里創(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è)試成功，說明MySQL主主復(fù)制配置成功.

向AI問一下細(xì)節(jié)

MySQL主主復(fù)制（雙主復(fù)制）配置過程介紹

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽