mysql 主從複製搭建詳細步驟

 
 
 
 
 


 
 
 
 
 


 
 
 
 
 

主機名稱	IP地址	埠號	版本資訊	作業系統
Master	192.168.111.21	3306	5.6.17	Redhat5.4
Slave	192.168.111.22	3306	5.6.23	Redhat5.4

配置成功的前置條件

版本一致或是從伺服器的版本要高於主服務

初始化表，並在後臺啟動mysql

主從伺服器安裝並啟動成功

修改root的密碼

主伺服器上授權（master）

修改主機服務的配置檔案（/etc/my.cnf）

新增如下的內容

[root@master etc]# cat 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

log-bin           = mysql-bin

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/，如需轉載，請註明出處，否則將追究法律責任。