一、環(huán)境介紹
使用虛擬機(jī)搭的兩個(gè) linux 服務(wù)器，通過修改 ssh 端口來實(shí)現(xiàn)同個(gè) ip 兩個(gè)服務(wù)器
這里使用的是無歷史數(shù)據(jù)的主從復(fù)制搭建，主要參考官方文檔，有歷史數(shù)據(jù)的操作方法也在里面可以找到，有空再弄了
1. master 環(huán)境
- 操作系統(tǒng): centos x64
- 數(shù)據(jù)庫版本: 5.6.38
- ip: 116.7.40.10
- ssh port: 22
2. slaver 環(huán)境
- 操作系統(tǒng): centos x64
- 數(shù)據(jù)庫版本: 5.6.38
- ip: 116.7.40.10
- ssh port: 5000
3. 必要條件
做主從復(fù)制的所有 mysql 版本必須保持一致
二、設(shè)置主庫
修改 /etc/my.cnf 或 my.ini 文件，在 [mysqld] 模塊中添加 log-bin 和 server-id，其中 log-bin 必須以 mysql-bin 開頭。這里還有更多的參數(shù)可以設(shè)置，包括指定復(fù)制或不復(fù)制哪些庫等，自行查閱
[mysqld]
log-bin=mysql-bin-lujw
server-id=80
保存，重啟 mysql
三、設(shè)置從庫
同樣修改 /etc/my.cnf 或 my.ini 文件，添加 server-id，此 id 只要保證唯一就好
[mysqld]
server-id=81
保存，重啟 mysql
四、獲取主庫日志信息
在主庫中執(zhí)行 SHOW MASTER STATUS; 查看日志信息并記起來
mysql> SHOW MASTER STATUS;
+-----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------+----------+--------------+------------------+-------------------+
| mysql-bin-lujw.000001 | 120 | | | |
+-----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)
五、復(fù)制歷史數(shù)據(jù)（略）
六、從庫設(shè)置主庫信息
為了實(shí)現(xiàn)主從復(fù)制，肯定需要告訴從庫有關(guān)主庫的信息，才能夠成功連接，打開從庫 mysql 并執(zhí)行下列語句
mysql> change master to
-> master_host='116.7.40.10',
-> master_user='root',
-> master_password='password',
-> master_port=3305,
-> master_log_file='mysql-bin-lujw.000001',
-> master_log_pos=120,
-> master_connect_retry=3;
Query OK, 0 rows affected, 2 warnings (0.14 sec)
關(guān)于 log 文件的信息是從第四步中獲取到的，它決定了從庫從哪里開始復(fù)制主庫
七、開啟從庫復(fù)制
在從庫中執(zhí)行 start slave 來啟動(dòng)復(fù)制，然后執(zhí)行 show slave status\G 來查看是否啟動(dòng)成功
mysql> START SLAVE;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 116.7.40.10
Master_User: root
Master_Port: 3305
Connect_Retry: 3
Master_Log_File: mysql-bin-lujw.000001
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin-lujw.000001
Slave_IO_Running: No
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: 120
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 80
Master_UUID:
Master_Info_File: /var/lib/mysql/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: 171025 00:26:39
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)
是否啟動(dòng)成功主要看 Slave_IO_State、Slave_IO_Running和Slave_SQL_Running，這里可以看到我的從庫狀態(tài)為空，Slave_IO_Running 為 NO，肯定是出了問題
往下看 Last_IO_Error，可以看到報(bào)錯(cuò)
The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
出現(xiàn)這個(gè)錯(cuò)誤的原因是從庫的服務(wù)器是直接復(fù)制主庫服務(wù)器生成的，所以 UUID 重復(fù)了
通過查閱官方文檔可以得到錯(cuò)誤解決方法
通過修改 auto.cnf 文件中的 UUID 后重啟 mysql 即可
vi /var/lib/mysql/auto.cnf
再次查詢從庫狀態(tài)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 116.7.40.10
Master_User: root
Master_Port: 3305
Connect_Retry: 3
Master_Log_File: mysql-bin-lujw.000001
Read_Master_Log_Pos: 120
Relay_Log_File: localhost-relay-bin.000004
Relay_Log_Pos: 288
Relay_Master_Log_File: mysql-bin-lujw.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: 120
Relay_Log_Space: 465
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: 80
Master_UUID: c571f8b0-7890-11e7-aa60-000c29cf7ee0
Master_Info_File: /var/lib/mysql/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)
ERROR:
No query specified
這就表示主從復(fù)制配置成功了
八、測(cè)試
在主庫中創(chuàng)建一個(gè)數(shù)據(jù)庫
mysql> create database lujw_replication_test;
Query OK, 1 row affected (0.03 sec)
然后在從庫中查詢是否復(fù)制過來了
mysql> show databases;
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| db_forum |
| lujw_replication_test |
| mysql |
| performance_schema |
| test |
+-----------------------+
6 rows in set (0.00 sec)
測(cè)試成功。
