CentOs7中mysql5.7如何实现主从复制配置

这篇文章主要介绍CentOs7中MySQL5.7如何实现主从复制配置,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!

我们提供的服务有:成都网站设计、网站建设、微信公众号开发、网站优化、网站认证、浏阳ssl等。为近1000家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务,是有科学管理、有技术的浏阳网站制作公司

MySQL5.7主、从配置具体步骤:

点击(此处)折叠或打开

  1. 1.两台MySQL服务器

  2. 主:192.168.253.138  端口:1221

  3. 从:192.168.253.139  端口:1221

  4. 2.修改主从配置参数

  5. 2.1 主服务器

  6. ##添加入下内容

  7. vi /etc/my.cnf

  8. [mysqld]

  9. log-bin = mysql-bin ##必须启用二进制格式日志

  10. server-id=138  ##server-id必须唯一,一般为IP的尾数

  11. 2.2 从服务器

  12. ##添加入下内容

  13. vi /etc/my.cnf

  14. [mysqld]

  15. log-bin = mysql-bin ##启用二进制格式日志,可选配置

  16. server-id=139  ##server-id必须唯一,一般为IP的尾数

  17. 3.重启主、从服务器使修改生效

  18. 主服务器(138)

  19. [root@my01 ~]# service mysql restart

  20. Shutting down MySQL.. SUCCESS!

  21. Starting MySQL. SUCCESS!

  22. 从服务器(139)

  23. [root@my02 ~]# service mysql restart

  24. Shutting down MySQL.. SUCCESS!

  25. Starting MySQL. SUCCESS!

  26. 4.在主服务器上创建复制账号并授权slave

  27. [root@my01 ~]# mysql -uroot -p

  28. Enter password:

  29. Welcome to the MySQL monitor. Commands end with ; or \g.

  30. Your MySQL connection id is 5

  31. Server version: 5.7.19-log MySQL Community Server (GPL)

  32. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

  33. Oracle is a registered trademark of Oracle Corporation and/or its

  34. affiliates. Other names may be trademarks of their respective

  35. owners.

  36. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  37. mysql> grant replication slave on *.* to 'mysync'@'%' identified by 'mysql123';

  38. Query OK, 0 rows affected, 1 warning (0.02 sec)

  39. mysql>

  40. 5.查看主服务器状态

  41. ##记录File与Position的值,配置从服务器时使用,之后主服务器就不要做任何操作了,避免值的改变使得从服务器配置失败

  42. mysql> show master status;

  43. +------------------+----------+--------------+------------------+-------------------+

  44. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

  45. +------------------+----------+--------------+------------------+-------------------+

  46. | mysql-bin.000001 | 1306 | | | |

  47. +------------------+----------+--------------+------------------+-------------------+

  48. 1 row in set (0.00 sec)

  49. 6.配置、启动从服务器

  50. [root@my02 ~]# mysql -u root -p

  51. Enter password:

  52. Welcome to the MySQL monitor. Commands end with ; or \g.

  53. Your MySQL connection id is 7

  54. Server version: 5.7.19-log MySQL Community Server (GPL)

  55. Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

  56. Oracle is a registered trademark of Oracle Corporation and/or its

  57. affiliates. Other names may be trademarks of their respective

  58. owners.

  59. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  60. mysql>

  61. mysql> CHANGE MASTER TO MASTER_HOST='192.168.253.138', MASTER_PORT=1221, MASTER_USER='mysync', MASTER_PASSWORD='mysql123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1306;

  62. Query OK, 0 rows affected, 2 warnings (0.01 sec)

  63. mysql> start slave;

  64. Query OK, 0 rows affected (0.01 sec)

  65. 7.查看从服务器复制状态

  66. ##最主要的是查看Slave_IO_Running: Yes,Slave_SQL_Running: Yes 这两项是不是YES,是则代表主从配置成功,否则失败

  67. mysql> show slave status\G

  68. *************************** 1. row ***************************

  69.                Slave_IO_State: Waiting for master to send event

  70.                   Master_Host: 192.168.253.138

  71.                   Master_User: mysync

  72.                   Master_Port: 1221

  73.                 Connect_Retry: 60

  74.               Master_Log_File: mysql-bin.000001

  75.           Read_Master_Log_Pos: 1306

  76.                Relay_Log_File: my02-relay-bin.000003

  77.                 Relay_Log_Pos: 320

  78.         Relay_Master_Log_File: mysql-bin.000001

  79.              Slave_IO_Running: Yes

  80.             Slave_SQL_Running: Yes

  81.               Replicate_Do_DB:

  82.           Replicate_Ignore_DB:

  83.            Replicate_Do_Table:

  84.        Replicate_Ignore_Table:

  85.       Replicate_Wild_Do_Table:

  86.   Replicate_Wild_Ignore_Table:

  87.                    Last_Errno: 0

  88.                    Last_Error:

  89.                  Skip_Counter: 0

  90.           Exec_Master_Log_Pos: 1306

  91.               Relay_Log_Space: 526

  92.               Until_Condition: None

  93.                Until_Log_File:

  94.                 Until_Log_Pos: 0

  95.            Master_SSL_Allowed: No

  96.            Master_SSL_CA_File:

  97.            Master_SSL_CA_Path:

  98.               Master_SSL_Cert:

  99.             Master_SSL_Cipher:

  100.                Master_SSL_Key:

  101.         Seconds_Behind_Master: 0

  102. Master_SSL_Verify_Server_Cert: No

  103.                 Last_IO_Errno: 0

  104.                 Last_IO_Error:

  105.                Last_SQL_Errno: 0

  106.                Last_SQL_Error:

  107.   Replicate_Ignore_Server_Ids:

  108.              Master_Server_Id: 138

  109.                   Master_UUID: dfb01359-857f-11e7-8ed4-000c2997411c

  110.              Master_Info_File: /data/db/mysql/1221/master.info

  111.                     SQL_Delay: 0

  112.           SQL_Remaining_Delay: NULL

  113.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

  114.            Master_Retry_Count: 86400

  115.                   Master_Bind:

  116.       Last_IO_Error_Timestamp:

  117.      Last_SQL_Error_Timestamp:

  118.                Master_SSL_Crl:

  119.            Master_SSL_Crlpath:

  120.            Retrieved_Gtid_Set:

  121.             Executed_Gtid_Set:

  122.                 Auto_Position: 0

  123.          Replicate_Rewrite_DB:

  124.                  Channel_Name:

  125.            Master_TLS_Version:

  126. 1 row in set (0.00 sec)

  127. mysql>

  128. 8.测试主、从情况

  129. ##主服务器

  130. mysql> show databases;

  131. +--------------------+

  132. | Database |

  133. +--------------------+

  134. | information_schema |

  135. | mysql |

  136. | performance_schema |

  137. | sys |

  138. +--------------------+

  139. 4 rows in set (0.01 sec)

  140. mysql> create database test;

  141. Query OK, 1 row affected (0.02 sec)

  142. mysql> show databases;

  143. +--------------------+

  144. | Database |

  145. +--------------------+

  146. | information_schema |

  147. | mysql |

  148. | performance_schema |

  149. | sys |

  150. | test |

  151. +--------------------+

  152. 5 rows in set (0.00 sec)

  153. mysql> use test;

  154. Database changed

  155. mysql> show tables;

  156. Empty set (0.00 sec)

  157. mysql> create table t(id int,name varchar(10));

  158. Query OK, 0 rows affected (0.04 sec)

  159. mysql> insert into t values (1,"zhang san");

  160. Query OK, 1 row affected (0.07 sec)

  161. mysql> show tables;

  162. +----------------+

  163. | Tables_in_test |

  164. +----------------+

  165. | t |

  166. +----------------+

  167. 1 row in set (0.00 sec)

  168. mysql> select * from

  169.     -> t;

  170. +------+-----------+

  171. | id | name |

  172. +------+-----------+

  173. | 1 | zhang san |

  174. +------+-----------+

  175. 1 row in set (0.00 sec)

  176. mysql> insert into t values (2,"li si");

  177. Query OK, 1 row affected (0.01 sec)

  178. mysql>

  179. ###从服务器

  180. mysql> show databases;

  181. +--------------------+

  182. | Database |

  183. +--------------------+

  184. | information_schema |

  185. | mysql |

  186. | performance_schema |

  187. | sys |

  188. | test |

  189. +--------------------+

  190. 5 rows in set (0.00 sec)

  191. mysql> use test;

  192. Reading table information for completion of table and column names

  193. You can turn off this feature to get a quicker startup with -A

  194. Database changed

  195. mysql> show tables;

  196. +----------------+

  197. | Tables_in_test |

  198. +----------------+

  199. | t |

  200. +----------------+

  201. 1 row in set (0.00 sec)

  202. mysql> select * from t;

  203. +------+-----------+

  204. | id | name |

  205. +------+-----------+

  206. | 1 | zhang san |

  207. | 2 | li si |

  208. +------+-----------+

  209. 2 rows in set (0.00 sec)

  210. mysql>

  211. ####恭喜成功了!!!

  212. ##注意:主服务器权限配置,如下:

  213. mysql> update user set user.Host='%' where user.User='root';

  214. Query OK, 1 row affected (0.00 sec)

  215. Rows matched: 1  Changed: 1  Warnings: 0

  216. mysql> flush privileges;

  217. Query OK, 0 rows affected (0.01 sec)

  218. ###此配置保证其它服务器能够连接到主服务器,否则后面的从服务器配置复制时会失败!!!

以上是“CentOs7中mysql5.7如何实现主从复制配置”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注创新互联行业资讯频道!


本文名称:CentOs7中mysql5.7如何实现主从复制配置
转载源于:http://scyanting.com/article/jjhjsj.html