mysql 主從複製搭建詳細步驟
mysql 主從複製搭建詳細步驟
|
主機名稱 |
IP地址 |
埠號 |
版本資訊 |
作業系統 |
|
Master |
192.168.111.21 |
3306 |
5.6.17 |
|
|
Slave |
192.168.111.22 |
3306 |
5.6.23 |
Redhat5.4 |
配置成功的前置條件
版本一致或是從伺服器的版本要高於主服務
初始化表，並在後臺啟動mysql
主從伺服器安裝並啟動成功
修改root的密碼
主伺服器上授權（master）
修改主機服務的配置檔案（/etc/my.cnf）
新增如下的內容
[mysqld]
log-bin=mysqsl-bin
server-id=101
修改後必須重新啟動被修改的伺服器才可以生效
mysql>
show master status;
Empty
set (0.00 sec)
如果還是空，就檢視二進位制日誌是否開啟
mysql>
show variables like '%log_bin%';
+---------------------------------+-------+
|
Variable_name | Value |
+---------------------------------+-------+
|
log_bin |
OFF |
|
log_bin_basename | |
|
log_bin_index | |
|
log_bin_trust_function_creators | OFF |
|
log_bin_use_v1_row_events |
OFF |
|
sql_log_bin | ON |
+---------------------------------+-------+
6
rows in set (0.00 sec)
那說明二進位制日誌沒有開啟，因此對配置檔案檢視，尤其檢視配置檔案的名稱my.cnf（我寫成了my.conf），所以沒有配置上去。
授權給從資料庫伺服器（slave server）
[root@master ~]# pwd
/root
mysql> grant replication slave on *.* to
'replication'@'192.168.111.22' identified by 'qilin';
Query OK, 0 rows affected (0.07 sec)
查詢主資料庫狀態
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqsl-bin.000001 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
從伺服器上的配置（slave）
修改從伺服器的配置檔案（/etc/my.cnf)
# add mian by liqilin
server-id = 201
relay-log = mysql-relay-bin
log-slave-updates
= 1
read-only = 1
從新啟動伺服器，啟動MYSQL
執行同步SQL語句
mysql> change master to
-> master_host='192.168.111.21',
-> master_user='replication',
-> master_password='qilin',
-> master_log_file='mysqsl-bin.000001',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.04
sec)
主從同步檢查
mysql> show slave status\G
*************************** 1. row
***************************
Slave_IO_State:
Master_Host: 192.168.111.21
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
mysqsl-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File:
mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysqsl-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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)
mysql>
啟動Slave同步程式
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
再次檢視狀態
mysql> show slave status\G
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send
event
Master_Host: 192.168.111.21
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
mysqsl-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File:
mysql-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqsl-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: 120
Relay_Log_Space: 457
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: 101
Master_UUID:
75fc94ca-e819-11e4-b1ac-000c2969fa00
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)
mysql>
變化了專案
Seconds_Behind_Master
Slave_SQL_Running_State
I/O執行緒建立的連線(SLAVE)
mysql> show processlist \G
*************************** 1. row
***************************
Id: 3
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row
***************************
Id: 4
User: system user
Host:
db: NULL
Command: Connect
Time: 443
State: Waiting for master to send event
Info: NULL
*************************** 3. row
***************************
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 443
State: Slave has read all relay log; waiting for the slave I/O thread to
update it
Info: NULL
3 rows in set (0.00 sec)
I/O執行緒建立的連線(master)
mysql> show processlist \G
*************************** 1. row
***************************
Id: 1
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
*************************** 2. row
***************************
Id: 2
User: replication
Host: 192.168.111.22:37333
db: NULL
Command: Binlog Dump
Time: 377
State: Master has sent all binlog to slave; waiting for binlog to be
updated
Info: NULL
2 rows in set (0.03 sec)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20976446/viewspace-1595778/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用XtraBackup搭建mysql主從複製的操作步驟MySql
- mysql主主複製（雙主複製）配置步驟MySql
- mysql主從複製搭建MySql
- MYSQL主從複製的搭建MySql
- MySQL的主從複製、半同步複製、主主複製詳解MySql
- MySQL主從複製的詳細過程介紹MySql
- Mysql主從複製原理及搭建MySql
- 簡單搭建MySQL主從複製MySql
- MySQL（14）---Docker搭建MySQL主從複製(一主一從)MySqlDocker
- Redis 主從複製詳細解讀Redis
- MySQL5.7主從複製-半同步複製搭建MySql
- rman複製資料庫詳細的步驟資料庫
- mysql主從複製的理解和搭建MySql
- mysql 5.7 主從複製搭建及原理MySql
- MySQL主從複製與主主複製MySql
- MySQL主從複製中的“show slave status”詳細含義MySql
- MySQL資料庫複製操作的詳細實現步驟介紹MySql資料庫
- Oracle 高階複製配置步驟詳細說明Oracle
- Redis主從複製工作原理和步驟介紹Redis
- MySQL的主從複製與MySQL的主主複製MySql
- mysql5.7主從複製，主主複製MySql
- MySQL主從複製、半同步複製和主主複製MySql
- MYSQL主主複製的搭建MySql
- mysql複製--主從複製配置MySql
- MySQL 主從複製MySql
- 【MySql】主從複製MySql
- MySQL主從複製MySql
- docker-compase搭建mysql主從複製DockerMySql
- MySQL-主從複製之搭建主資料庫MySql資料庫
- MySQL主從複製之GTID模式詳細介紹鞴嬈MySql模式
- MySQL主從複製、半同步複製和主主複製概述MySql
- MySQL-主從複製之搭建從資料庫MySql資料庫
- MySQL主從複製_複製過濾MySql
- MySQL 5.5 主主複製搭建流程MySql
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- MySQL 5.7 基於GTID搭建主從複製MySql
- Docker Compose搭建MySQL主從複製叢集DockerMySql
- MySQL主從複製配置步驟如何操作？學linux要什麼基礎MySqlLinux