mysql传统主从复制的部署过程

这篇文章主要讲解了“mysql传统主从复制的部署过程”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“mysql传统主从复制的部署过程”吧！

部署过程

一、保证Master库和Slave库的数据和结构一致
可以通过mysqldump或者XtraBackup工具对Master库进行全备，再把备份文件传到Slave库，Slave库利用改备份进行恢复，使得两者一致。
我这里的Slave库是直接复制Master库生成的，所以不需要做这一步了。

二、配置两个库的参数文件

• Master库需要打开二进制日志

• 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

三、创建用来进行复制的用户并且赋权

• 必须给予用户replication slave权限，指定Slave库的主机地址

点击(此处)折叠或打开

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库此时的日志位置

点击(此处)折叠或打开

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)

• 从库开始change到master库上

点击(此处)折叠或打开

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库执行开始复制命令

点击(此处)折叠或打开

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

到此，主从复制就已经配置完毕了

六、对搭建的环境进行测试测试

• 主库开始插入数据测试

点击(此处)折叠或打开

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);

• 从库是否接受到数据

点击(此处)折叠或打开

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状态

点击(此处)折叠或打开

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)

主从复制部署测试成功

感谢各位的阅读，以上就是“mysql传统主从复制的部署过程”的内容了，经过本文的学习后，相信大家对mysql传统主从复制的部署过程这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！