当前位置:Gxlcms > 数据库问题 > mysql5.7.26做主从复制配置

mysql5.7.26做主从复制配置

时间:2021-07-01 10:21:17 帮助过:4人阅读

repl@% identified by Zaq1xsw@; Query OK, 0 rows affected, 1 warning (0.00 sec)
  1. mysql><span style="color: #000000;"> use mysql;
  2. Reading table information </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> completion of table and column names
  3. You can turn off this feature to get a quicker startup with </span>-<span style="color: #000000;">A
  4. Database changed
  5. mysql</span>> <span style="color: #0000ff;">select</span><span style="color: #000000;"> user,host from user;
  6. </span>+---------------+-----------+
  7. | user | host |
  8. +---------------+-----------+
  9. | repl | % |
  10. | root | % |
  11. | mysql.session | localhost |
  12. | mysql.sys | localhost |
  13. +---------------+-----------+
  14. <span style="color: #800080;">4</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

三、Master、Slave上分别设置不同的Server_id,主库上开启二进制日志

  1. mysql> show variables like <span style="color: #800000;">‘</span><span style="color: #800000;">%server_id%</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
  2. </span>+----------------+-------+
  3. | Variable_name | Value |
  4. +----------------+-------+
  5. | server_id | <span style="color: #800080;">0</span> |
  6. | server_id_bits | <span style="color: #800080;">32</span> |
  7. +----------------+-------+

如果配置文件没有设置server_id参数,则默认都是0

编辑/etc/my.cnf

添加service_id,它的值可以跟服务器的IP最后一位数字一样,这样就能保证内网中的服务器ID不重复。master上

  1. server_id=<span style="color: #800080;">103</span><span style="color: #000000;">
  2. log</span>-bin=<span style="color: #000000;">master
  3. binlog_format</span>=row

slave上

  1. server_id=<span style="color: #800080;">69</span>

四、将主库做一次全量备份,并恢复到从库上

在主库上操作

  1. mysql><span style="color: #000000;"> show databases;
  2. </span>+--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | mysql |
  7. | performance_schema |
  8. | sys |
  9. | test |
  10. +--------------------+
  11. <span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

主库有一个test数据库

  1. mysql><span style="color: #000000;"> show tables;
  2. </span>+----------------+
  3. | Tables_in_test |
  4. +----------------+
  5. | test |
  6. +----------------+
  7. <span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

有一个test表

  1. mysql> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from test;
  2. </span>+------+------+
  3. | <span style="color: #0000ff;">id</span> | name |
  4. +------+------+
  5. | <span style="color: #800080;">1</span> | aaaa |
  6. +------+------+
  7. <span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

表里有一条数据,开始备份主库

  1. [root@node2 data]# mysqldump -uroot -p --all-databases > /home/mysql-<span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span>/bak/bak.sql<br>[root@node2 data]# scp -P25601 /home/mysql-5.7.26/bak/bak.sql root@172.28.18.69:/home/mysql-5.7.26/bak/<br><br>在从库上操作,恢复主库数据
  1. [root@localhost log]# mysql -uroot -p < /home/mysql-<span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span>/bak/<span style="color: #000000;">bak.sql
  2. Enter password:
  3. [root@localhost log]# mysql </span>-uroot -<span style="color: #000000;">p
  4. Enter password:
  5. Welcome to the MySQL monitor. Commands end with ; or \g.
  6. Your MySQL connection </span><span style="color: #0000ff;">id</span> is <span style="color: #800080;">4</span><span style="color: #000000;">
  7. Server version: </span><span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span><span style="color: #000000;"> MySQL Community Server (GPL)
  8. Copyright (c) </span><span style="color: #800080;">2000</span>, <span style="color: #800080;">2019</span>, Oracle and/<span style="color: #000000;">or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and</span>/<span style="color: #000000;">or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  12. Type </span><span style="color: #800000;">‘</span><span style="color: #800000;">help;</span><span style="color: #800000;">‘</span> or <span style="color: #800000;">‘</span><span style="color: #800000;">\h</span><span style="color: #800000;">‘</span> <span style="color: #0000ff;">for</span> help. Type <span style="color: #800000;">‘</span><span style="color: #800000;">\c</span><span style="color: #800000;">‘</span> to <span style="color: #0000ff;">clear</span><span style="color: #000000;"> the current input statement.
  13. mysql</span>><span style="color: #000000;"> show databases;
  14. </span>+--------------------+
  15. | Database |
  16. +--------------------+
  17. | information_schema |
  18. | mysql |
  19. | performance_schema |
  20. | sys |
  21. | test |
  22. +--------------------+
  23. <span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
  24. mysql</span>>

 

此时,从库里test数据库有了,里面也有test数据表里记录

  1. mysql><span style="color: #000000;"> show databases;
  2. </span>+--------------------+
  3. | Database |
  4. +--------------------+
  5. | information_schema |
  6. | mysql |
  7. | performance_schema |
  8. | sys |
  9. | test |
  10. +--------------------+
  11. <span style="color: #800080;">5</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
  12. mysql</span>><span style="color: #000000;"> use test;
  13. Reading table information </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> completion of table and column names
  14. You can turn off this feature to get a quicker startup with </span>-<span style="color: #000000;">A
  15. Database changed
  16. mysql</span>> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from test
  17. </span>-><span style="color: #000000;"> ;
  18. </span>+------+------+
  19. | <span style="color: #0000ff;">id</span> | name |
  20. +------+------+
  21. | <span style="color: #800080;">1</span> | aaaa |
  22. +------+------+
  23. <span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.01</span> sec)

五、在slave上change msater 操作配置主从复制

首先获取主库日志文件名称和偏移量

  1. mysql><span style="color: #000000;"> show master status \G;
  2. </span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
  3. File: master.</span><span style="color: #800080;">000001</span><span style="color: #000000;">
  4. Position: </span><span style="color: #800080;">154</span><span style="color: #000000;">
  5. Binlog_Do_DB:
  6. Binlog_Ignore_DB:
  7. Executed_Gtid_Set:
  8. </span><span style="color: #800080;">1</span> row <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
  9. ERROR:
  10. No query specified
  11. mysql</span>>

在从库上执行

  1. mysql><span style="color: #000000;"> change master to
  2. </span>-> master_host=<span style="color: #800000;">‘</span><span style="color: #800000;">172.28.18.103</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
  3. </span>-> master_port=<span style="color: #800080;">3306</span><span style="color: #000000;">,
  4. </span>-> master_user=<span style="color: #800000;">‘</span><span style="color: #800000;">repl</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
  5. </span>-> master_password=<span style="color: #800000;">‘</span><span style="color: #800000;">Zaq1xsw@</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
  6. </span>-> master_log_file=<span style="color: #800000;">‘</span><span style="color: #800000;">master.000001</span><span style="color: #800000;">‘</span><span style="color: #000000;">,
  7. </span>-> master_log_pos=<span style="color: #800080;">154</span><span style="color: #000000;">;
  8. Query OK, </span><span style="color: #800080;">0</span> rows affected, <span style="color: #800080;">2</span> warnings (<span style="color: #800080;">0.21</span> sec)

启动从库

  1. mysql><span style="color: #000000;"> start slave;
  2. Query OK, </span><span style="color: #800080;">0</span> rows affected (<span style="color: #800080;">0.01</span> sec)

查看从库状态

  1. mysql><span style="color: #000000;"> show slave status\G;
  2. </span>*************************** <span style="color: #800080;">1</span>. row ***************************<span style="color: #000000;">
  3. Slave_IO_State: Waiting </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> master to send event
  4. Master_Host: </span><span style="color: #800080;">172.28</span>.<span style="color: #800080;">18.103</span><span style="color: #000000;">
  5. Master_User: repl
  6. Master_Port: </span><span style="color: #800080;">3306</span><span style="color: #000000;">
  7. Connect_Retry: </span><span style="color: #800080;">60</span><span style="color: #000000;">
  8. Master_Log_File: master.</span><span style="color: #800080;">000001</span><span style="color: #000000;">
  9. Read_Master_Log_Pos: </span><span style="color: #800080;">154</span><span style="color: #000000;">
  10. Relay_Log_File: localhost</span>-relay-bin.<span style="color: #800080;">000002</span><span style="color: #000000;">
  11. Relay_Log_Pos: </span><span style="color: #800080;">317</span><span style="color: #000000;">
  12. Relay_Master_Log_File: master.</span><span style="color: #800080;">000001</span><span style="color: #000000;">
  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: </span><span style="color: #800080;">0</span><span style="color: #000000;">
  22. Last_Error:
  23. Skip_Counter: </span><span style="color: #800080;">0</span><span style="color: #000000;">
  24. Exec_Master_Log_Pos: </span><span style="color: #800080;">154</span><span style="color: #000000;">
  25. Relay_Log_Space: </span><span style="color: #800080;">528</span><span style="color: #000000;">
  26. Until_Condition: None
  27. Until_Log_File:
  28. Until_Log_Pos: </span><span style="color: #800080;">0</span><span style="color: #000000;">
  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: </span><span style="color: #800080;">0</span><span style="color: #000000;">
  36. Master_SSL_Verify_Server_Cert: No
  37. Last_IO_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
  38. Last_IO_Error:
  39. Last_SQL_Errno: </span><span style="color: #800080;">0</span><span style="color: #000000;">
  40. Last_SQL_Error:
  41. Replicate_Ignore_Server_Ids:
  42. Master_Server_Id: </span><span style="color: #800080;">103</span><span style="color: #000000;">
  43. Master_UUID: ddbee8c3</span>-76da-11e9-<span style="color: #800080;">9174</span>-<span style="color: #000000;">90b11c15be09
  44. Master_Info_File: </span>/home/mysql-<span style="color: #800080;">5.7</span>.<span style="color: #800080;">26</span>/data/master.<span style="color: #0000ff;">info</span>

此时:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主从复制配置已经生效

六、测试数据

在主库插入一条数据

  1. mysql> insert test values(<span style="color: #800080;">2</span>,<span style="color: #800000;">‘</span><span style="color: #800000;">bbbb</span><span style="color: #800000;">‘</span><span style="color: #000000;">);
  2. Query OK, </span><span style="color: #800080;">1</span> row affected (<span style="color: #800080;">0.03</span> sec)

从库上查询

  1. mysql> <span style="color: #0000ff;">select</span> *<span style="color: #000000;"> from test;
  2. </span>+------+------+
  3. | <span style="color: #0000ff;">id</span> | name |
  4. +------+------+
  5. | <span style="color: #800080;">1</span> | aaaa |
  6. | <span style="color: #800080;">2</span> | bbbb |
  7. +------+------+
  8. <span style="color: #800080;">2</span> rows <span style="color: #0000ff;">in</span> set (<span style="color: #800080;">0.00</span> sec)

数据已经复制成功了。

mysql5.7.26做主从复制配置

标签:variable   aaa   base   不同的   查看   ref   也有   etc   hang   

人气教程排行