mysql傳統主從復制的部署過(guò)程

這篇文章主要講解了“傳統主從復制的部署過(guò)程”，文中的講解內容簡(jiǎn)單清晰，易于學(xué)習與理解，下面請大家跟著(zhù)小編的思路慢慢深入，一起來(lái)研究和學(xué)習“mysql傳統主從復制的部署過(guò)程”吧！

部署過(guò)程

一、保證Master庫和Slave庫的數據和結構一致
可以通過(guò)mysqldump或者XtraBackup工具對Master庫進(jìn)行全備，再把備份文件傳到Slave庫，Slave庫利用改備份進(jìn)行恢復，使得兩者一致。
我這里的Slave庫是直接復制Master庫生成的，所以不需要做這一步了。

二、配置兩個(gè)庫的參數文件

• Master庫需要打開(kāi)二進(jìn)制日志

• Master庫需要設置與Slave庫不同的server-id

1. [root@potato data]vi /etc/my.cnf
   

2. binlog_format = mixed
   

3. server-id = 203306
   

4. log-bin = mybinlog

• Slave庫只需配置server-id即可

1. [root@tomato data]vi /etc/my.cnf
   

2. server-id = 203307

三、創(chuàng )建用來(lái)進(jìn)行復制的用戶(hù)并且賦權

• 必須給予用戶(hù)replication slave權限，指定Slave庫的主機地址

點(diǎn)擊(此處)折疊或打開(kāi)

1. root@localhost:mysql.sock  05:02:32 [(none)]>grant replication slave on *.* to repl@'192.168.161.128' identified by 'repl';

四、讓Slave庫change到Master庫

• 查看Master庫此時(shí)的日志位置

點(diǎn)擊(此處)折疊或打開(kāi)

1. root@localhost:mysql.sock  05:02:32 [(none)]>show master status ;
   

2. +-----------------+----------+--------------+------------------+-------------------+
   

3. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
   

4. +-----------------+----------+--------------+------------------+-------------------+
   

5. | mybinlog.000004 | 331 | | | |
   

6. +-----------------+----------+--------------+------------------+-------------------+
   

7. 1 row in set (0.00 sec)

• 從庫開(kāi)始change到master庫上

點(diǎn)擊(此處)折疊或打開(kāi)

1. root@localhost:mysql.sock  04:29:15 [(none)]>change master to
   

2.     -> master_host='192.168.161.128',
   

3.     -> master_port=3306,
   

4.     -> master_user='repl',
   

5.     -> master_password='repl',
   

6.     -> master_log_file='mybinlog.000004',
   

7.     -> master_log_pos=331;
   

8. ERROR 2006 (HY000): MySQL server has gone away
   

9. No connection. Trying to reconnect...
   

10. Connection id:    2
    

11. Current database: *** NONE ***
    

12. 
    

13. Query OK, 0 rows affected, 2 warnings (0.17 sec)

五、Slave庫執行開(kāi)始復制命令

點(diǎn)擊(此處)折疊或打開(kāi)

1. root@localhost:mysql.sock  04:40:11 [(none)]>start slave;

到此，主從復制就已經(jīng)配置完畢了

六、對搭建的環(huán)境進(jìn)行測試測試

• 主庫開(kāi)始插入數據測試

點(diǎn)擊(此處)折疊或打開(kāi)

1. root@localhost:mysql.sock  04:40:11 [(none)]>use lala;
   

2. root@localhost:mysql.sock  04:51:23 [(lala)]>create table haha(id int);
   

3. root@localhost:mysql.sock  04:51:23 [(lala)]>insert into haha values(1);

• 從庫是否接受到數據

點(diǎn)擊(此處)折疊或打開(kāi)

1. root@localhost:mysql.sock  04:52:55 [(none)]>select * from lala.haha;
   

2. +------+
   

3. | id |
   

4. +------+
   

5. | 1 |
   

6. +------+
   

7. 1 row in set (0.00 sec)

• 查看Slave狀態(tài)

點(diǎn)擊(此處)折疊或打開(kāi)

1. root@localhost:mysql.sock  04:57:23 [(none)]>show slave status\G
   

2. *************************** 1. row ***************************
   

3.                Slave_IO_State: Waiting for master to send event
   

4.                   Master_Host: 192.168.161.128
   

5.                   Master_User: repl
   

6.                   Master_Port: 3306
   

7.                 Connect_Retry: 60
   

8.               Master_Log_File: mybinlog.000004
   

9.           Read_Master_Log_Pos: 620
   

10.                Relay_Log_File: mysql-relay-bin.000003
    

11.                 Relay_Log_Pos: 571
    

12.         Relay_Master_Log_File: mybinlog.000004
    

13.              Slave_IO_Running: Yes
    

14.             Slave_SQL_Running: Yes
    

15.               Replicate_Do_DB:
    

16.           Replicate_Ignore_DB:
    

17.            Replicate_Do_Table:
    

18.        Replicate_Ignore_Table:
    

19.       Replicate_Wild_Do_Table:
    

20.   Replicate_Wild_Ignore_Table:
    

21.                    Last_Errno: 0
    

22.                    Last_Error:
    

23.                  Skip_Counter: 0
    

24.           Exec_Master_Log_Pos: 620
    

25.               Relay_Log_Space: 744
    

26.               Until_Condition: None
    

27.                Until_Log_File:
    

28.                 Until_Log_Pos: 0
    

29.            Master_SSL_Allowed: No
    

30.            Master_SSL_CA_File:
    

31.            Master_SSL_CA_Path:
    

32.               Master_SSL_Cert:
    

33.             Master_SSL_Cipher:
    

34.                Master_SSL_Key:
    

35.         Seconds_Behind_Master: 0
    

36. Master_SSL_Verify_Server_Cert: No
    

37.                 Last_IO_Errno: 0
    

38.                 Last_IO_Error:
    

39.                Last_SQL_Errno: 0
    

40.                Last_SQL_Error:
    

41.   Replicate_Ignore_Server_Ids:
    

42.              Master_Server_Id: 203306
    

43.                   Master_UUID: af3609cd-b426-11e6-a997-000c29d55626
    

44.              Master_Info_File: /data/mysql/mytest_3306/data/master.info
    

45.                     SQL_Delay: 0
    

46.           SQL_Remaining_Delay: NULL
    

47.       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
    

48.            Master_Retry_Count: 86400
    

49.                   Master_Bind:
    

50.       Last_IO_Error_Timestamp:
    

51.      Last_SQL_Error_Timestamp:
    

52.                Master_SSL_Crl:
    

53.            Master_SSL_Crlpath:
    

54.            Retrieved_Gtid_Set:
    

55.             Executed_Gtid_Set:
    

56.                 Auto_Position: 0
    

57. 1 row in set (0.00 sec)

主從復制部署測試成功