Mysql5.7配置主从实际操作记录

news/2024/12/27 9:08:58/

👀 什么是MySQL主从配置

指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。

对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。

一句话表示就是，主数据库做什么，从数据库就跟着做什么。

⛄️ 主从复制的工作原理

1️⃣ Master 数据库只要发生变化，立马记录到Binary log 日志文件中。
2️⃣ Slave数据库启动一个I/O thread连接Master数据库，请求Master变化的二进制日志。
3️⃣ Slave I/O获取到的二进制日志，保存到自己的Relay log 日志文件中。
4️⃣ Slave 有一个 SQL thread定时检查Realy log是否变化，变化那么就更新数据。

🍄 三种复制方式

（1）STATEMENT模式：基于语句的复制，在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选择基于行的复制。

（2）ROW模式：基于行的复制，把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从MySQL5.0开始支持。

（3）MIXED模式：混合类型复制，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

📕 主从配置的优点

提高数据库系统的可用性。
读写分离，提高查询访问性能，有效减少主数据库访问压力。
数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。
实时灾备，主数据库出现故障时，可快速切换到从数据库。

🚀 环境准备

建议本机安装虚拟机，准备两个MySQL，具体环境详情如下：

宿主机 centos7
MySQL版本 5.7
MySQL Master: 192.168.191.130
MySQL Slave : 192.168.191.131

🅰️ 主库配置

① 修改主库的 my.cnf 文件，添加如下配置。

vim /etc/my.cnfserver-id=1       #服务器id
log-bin=mysql-bin #打开日志，master需要打开binlog-do-db=db_ly #这里写你需要同步的数据库名！这里一定要写对你要同步的数据库名
binlog-ignore-db=mysql #忽略不需要同步给从库的数据库名
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
expire_logs_days=7 #自动清理7天前的log文件,可根据需要修改

tips ：修改完成后按esc 按：wq 回车 保存并退出！

② 重启mysql服务，输入以下命令。

service mysqld restart

③ 登录MySQL查看测试log_bin是否成功开启 （ON 代表已开启）

mysql -uroot -proot  # 登录mysql

执行以下命令：

show variables like '%log_bin%';

[root@jenkins /]# mysql -uroot -proot 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35-log MySQL Community Server (GPL)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)

④ 创建master的数据库中建立备份账号：root 为用户名，%表示任何远程地址。
如下表示密码为 root 的任何远程地址的root都可以连接master主机。

grant replication slave on *.* to 'root'@'%' identified by 'root';

⑤ 查看刚刚创建的账号是否存在。

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | %         |
+---------------+-------------------------------------------+-----------+
5 rows in set (0.00 sec)

⑥ 查看主服务器上当前的二进制日志名。

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000001 |      439 | master       | mysql,information_schema,performance_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)

Tips: 到此MySQL master的设置就设置完毕！

🅱️ 从库设置

① 修改从库的 my.cnf 文件，添加如下配置。

vim /etc/my.cnfserver_id=2 #从库的服务器id，不能和主库的一样！切记！！！
master_info_repository=TABLE
relay_log_info_repostitory=TABLE

② 登录MySQL，设置上面主库对应的参数。

[root@localhost /]# mysql -uroot -proot

执行以下命令：

 change master to master_host='192.168.191.130',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=439;

mysql> stop slave #记得先关闭-> ;
Query OK, 0 rows affected, 1 warning (0.04 sec)mysql>  change master to master_host='192.168.191.130',->  master_user='root',master_password='root',->  master_log_file='mysql-bin.000001',master_log_pos=439;
Query OK, 0 rows affected, 2 warnings (0.04 sec)mysql> start slave;#设置好了之后记得从新start slave

③ 查看slave 的状态，执行一下命令。

show slave status \Gmysql> show slave status \G
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.191.130Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000008Read_Master_Log_Pos: 847Relay_Log_File: localhost-relay-bin.000013Relay_Log_Pos: 1060Relay_Master_Log_File: mysql-bin.000008Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 847Relay_Log_Space: 1437Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_UUID: 99e35046-6492-11ec-b188-000c2902ba69Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 
1 row in set (0.01 sec)Slave_IO_Running: YesSlave_SQL_Running: Yes

看到 Slave_IO_Running 和 Slave_SQL_Running 均为YES 则说明我们配置的主从已经成功了！

🆎 测试主从

① 切换至主库，创建主库中指定的 db_ly 数据库测试一下。

mysql> use db_ly;
Database changed
mysql> 
mysql> 
mysql> 
mysql> create table `tb_001`(`id` int(11) not null,`tb_user` int(11) not null,primary key(`id`)) engine=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)mysql> insert into tb_001 value('1','1');
Query OK, 1 row affected (0.09 sec)mysql>

② 查看同步后的数据。
在这里插入图片描述

在这里插入图片描述

🆒 到此主从配置就成功了！

常见问题集合

⁉️ 常见问题一： ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.191.130’ (113)

✔️ 解决方法：
① 尝试远程连接数据库 (通常都是这个原因无法远程连上MySQL)
mysql -h192.168.191.130 -uroot -proot

如果无法登录 则使用下面命令：
#重新复权

grant all privileges on *.* to 'root'@'%' identified by 'root';
Query OK, 0 rows affected (0.00 sec)#这条命令也别忘记了
flush privileges;
Query OK, 0 rows affected (0.00 sec)

然后可以再次尝试远程登录MySQL。

② 测试能否ping通远程IP

ping 192.168.191.130

③ 如果IP是通的，则测试能否访问到端口。

telnet 192.168.191.130 3306

如果通不了，则说明防火墙是开启的，直接关闭防火墙即可（生产环境不推荐这么玩啊~）。

④ 查看my.cnf文件是不是没有注释 bind-address = 127.0.0.1

⁉️ 常见问题二：Last_IO_Errno: 1593

① 可能是server-id重复。

✔️ 解决方法：
任意选一个服务器修改对应的server-id 即可，
输入命令如下，找到对应的 server-id 修改保存即可。
vim /etc/my.cnf

② 可能是server-uuid重复。
一般导致这个问题就是在虚拟机中装好MySQL之后，直接copy导致的。

✔️ 解决方法：
先登录MySQL使用命令，产生一个uuid。

mysql> select  uuid();
+--------------------------------------+
| uuid()                               |
+--------------------------------------+
| 0eec5819-78a2-11e9-84d0-525400534aff |
+--------------------------------------+
1 row in set (0.04 sec)然后修改其中一个MySQL的auto.cnf配置文件，修改完成后保存退出。
[root@centos-cluster-s19423 ~]# vim  /var/lib/mysql/auto.cnf 
[auto]
server-uuid=b1bfa5f4-6a95-11e9-8049-525400534aee
~
~

重启这个MySQL即可。

🚩 tips: 如果主从配置有问题通常 Last_IO_Errno 和 Last_IO_Error 会有对应提示的错误码和错误信息。

The end.


http://www.ppmy.cn/news/1558487.html

相关文章

【面试系列】深入浅出 Spring

【面试系列】深入浅出 Spring

熟悉Spring，对IOC、AOP、Bean生命周期、循环依赖等有深入了解。 面试题整理 描述 Spring Context 初始化的流程介绍 Bean 的生命周期及作用域Bean 的构造方法、 PostConstruct注解、InitializingBean、init-method 的执行顺序？Spring 如何解决循环依赖&…
阅读更多...
C语言从入门到放弃教程

C语言从入门到放弃教程

C语言从入门到放弃 1. 介绍1.1 特点1.2 历史与发展1.3 应用领域 2. 安装2.1 编译器安装2.2 编辑器安装 3. 第一个程序1. 包含头文件2. 主函数定义3. 打印语句4. 返回值 4. 基础语法4.1 注释4.1.1 单行注释4.1.2 多行注释 4.2 关键字4.2.1 C语言标准4.2.2 C89/C90关键字&#xf…
阅读更多...
【机器学习案列】车牌自动识别系统：基于YOLO11的高效实现

【机器学习案列】车牌自动识别系统：基于YOLO11的高效实现

🧑 博主简介：曾任某智慧城市类企业算法总监，目前在美国市场的物流公司从事高级算法工程师一职，深耕人工智能领域，精通python数据挖掘、可视化、机器学习等，发表过AI相关的专利并多次在AI类比赛中获奖。CSDN…
阅读更多...
Metricbeat安装教程——Linux——Metricbeat监控ES集群

Metricbeat安装教程——Linux——Metricbeat监控ES集群

Metricbeat安装教程——Linux 一、安装 下载安装包： 官网下载地址：https://www.elastic.co/cn/downloads/beats/metricbeat 上传包到linux 切换到安装目录下 解压：tar -zxvf metricbeat-7.17.1-linux-x86_64.tar.gz 重命名安装文件夹 mv met…
阅读更多...
【Compose multiplatform教程07】多平台常用组件和重要组件目录

【Compose multiplatform教程07】多平台常用组件和重要组件目录

一、基础交互与显示组件 Text 查看示例 功能说明：用于在界面上显示文本内容，支持设置字体、大小、颜色、样式（如加粗、斜体、下划线）等属性，满足不同的文本展示需求，可传达各种信息给用户。示例场景&#…
阅读更多...
大模型日报 2024-12-20

大模型日报 2024-12-20

大模型日报 2024-12-20 大模型资讯 标题： OpenAI发布季第十一天：ChatGPT深度集成Mac应用，从Chatbot变身AI Agent 摘要：本文报道了OpenAI在其发布季第十一天推出的ChatGPT与Mac应用的深度集成，标志着ChatGPT从单一的会话…
阅读更多...
使用Python获取PDF文本和图片的精确位置

使用Python获取PDF文本和图片的精确位置

在处理和分析PDF文档时，获取文本和图片在页面上的精确位置是一个重要的操作。通过确定这些元素的具体坐标，我们可以实现对PDF内容的更精细控制和理解，这对于自动化文档处理、信息提取以及内容重组等工作流程尤为关键。通过Python编程语言&…
阅读更多...
流年运势API接口_解析个人命理十年大运PHP实现方法返回json数据

流年运势API接口_解析个人命理十年大运PHP实现方法返回json数据

介绍 流年运势与十年大运的 API 接口提供了一种高效的方式来分析个体的命理特征。通过输入个人的基本信息（包括姓、名、性别、出生日期和时间），用户可以获取详细的命理分析，包括五行数量、生肖、八字、十神、命宫等信息。这些数据…
阅读更多...
最新文章

Copyright @ 2022~2023