mysql 主从配置详解

最新推荐文章于 2024-05-22 10:29:47 发布
最新推荐文章于 2024-05-22 10:29:47 发布
阅读量400 收藏
点赞数
分类专栏： mysql 文章标签： msyql主从配置
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_34341290/article/details/103258979
版权

关于主从配置的好处和坏处我这里就不详细解说了，自己百度下就一大堆，我这就直入主题了

MySQL数据库复制操作大致可以分成三个步骤：

  1. 主服务器将数据的改变记录到二进制日志（binary log）中。
  2. 从服务器将主服务器的binary log events 复制到它的中继日志（relay log）中。
  3. 从服务器重做中继日志中的事件，将数据的改变与从服务器保持同步

首先，主服务器会记录二进制日志，每个事务更新数据完成之前，主服务器将这些操作的信息记录在二进制日志里面在事件写入二进制日志完成后主服务器通知存储引擎提交事务。

准备： 了解binlog日志，MySQL用户-权限

1. 创建复制账号

mysql服务器配置复制不难，但是因为场景不同可能会存在一定的差异化，总的来说分为一下几步：

  1. 在服务器上创建复制账号。
  2. 通知备库连接到主库并从主库复制数据。

对于主从复制，在本质上就是通过与从数据库复制与主数据库的binlog日志文件，通过重做实现的同步； 但是一定要注意尽量保证主从服务器上安装了相同的版本的数据库，设定主从的服务器ip地址为192.168.153.129从服务器的ip地址是192.168.153.131。

然后再主服务器上设置一个复制使用的账号，并授予replication slave权限。我们可以根据ip创建账号为127

/*
创建账号sql：
create user 'username'@'localhost' identified by 'password';

授权
grant [权限] on *.* to 'username'@'localhost' identified by 'password';
 */

mysql> create user 'repl_131'@'%' identified by 'repl_131';
Query OK, 0 rows affected (0.04 sec)

mysql> select user,host from mysql.user;

mysql> grant replication slave on *.* to 'repl_131'@'%' identified by 'repl_131';
Query OK, 0 rows affected, 1 warning (0.00 sec)

对于MySQL的主从复制来说最重要的主要就是binlog日志，所以我们就需要开启binlog日志，并设置server-id的值。需要重启服务器之后才生效

二进制日志，也就是我们常说的binlog。二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录日志在日志文件中，其中还包括没调语句所执行的时间和消耗的资源，以及相关的事务信息。默认情况下二进制日志功能是没有开启的，启动可以配置log-bin[=file_name]开启，

mysql> show global variables like '%log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /www/server/data/mysql-bin       |
| log_bin_index                   | /www/server/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
+---------------------------------+----------------------------------+
5 rows in set (0.03 sec)

作用就是

  1. 增量备份(不是所有数据备份，而是最近的写操作)
  2. 用于MySQL主从复制

解释为什么每次修改配置文件都用find...因为记不住

[root@localhost panel]# vi /etc/my.cnf

主要就是下配置文件中添加如下配置

[mysqld]
log-bin=mysql-bin
server-id=1

2 binlog数据恢复操作

查看MySQL的日志文件, 这是查询主服务器上当前的二进制日志名和偏移值。这个操作的母的是为了在从数据库启动以后，从这个点进行数据库的恢复。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000009 |      708 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

对于binlog来说mysql-bin.xxx就是binlog的节点日志文件，而mysql-bin.index这是日志文件的所以会记录的是所有日志文件的地址

[root@localhost data]# more mysql-bin.index
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009

我们可以看看binlog日志中的信息

-- 查看二进制日志信息
Mysqlbinlog filename
show binlog events in 'filename'\G;

查看binlog的信息,先设置mysqlbinlog为系统环境变量 

[root@localhost data]# vi ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin:/www/server/mysql/bin
export PATH

[root@localhost data]# source ~/.bash_profile
[root@localhost data]# cd /www/server/data
[root@localhost data]# mysqlbinlog ./mysql-bin.000009

日志中由 #at xxx 头， #at xxx ~~ /!/ 体两部分组成

-- 查看所有二进制文件信息
show binary logs;
-- 查看最新二进制文件
show master status;
-- 刷新日志
flush logs;
-- 清空所有的日志文件
reset master

添加一下测试的数据操作

CREATE DATABASE bin DEFAULT CHARACTER SET utf8;
use bin;
CREATE TABLE `t` (
  `id` int(10) ,
  `name` varchar(20)
) ;
INSERT INTO t VALUES(1, 'sixstar');
INSERT INTO t VALUES(2, 'shineyork');
INSERT INTO t VALUES(3, 'xxx');

这个时候我们可以看看这个binlog日志中的信息

mysql> show binlog events in 'mysql-bin.000001';
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                  |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
| mysql-bin.000001 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.27-log, Binlog ver: 4                                 |
| mysql-bin.000001 |  123 | Previous_gtids |         1 |         154 |                                                                       |
| mysql-bin.000001 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 |  219 | Query          |         1 |         310 | create database bin                                                   |
| mysql-bin.000001 |  310 | Anonymous_Gtid |         1 |         375 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 |  375 | Query          |         1 |         505 | use `bin`; CREATE TABLE `t` (
  `id` int(10) ,
  `name` varchar(20)
) |
| mysql-bin.000001 |  505 | Anonymous_Gtid |         1 |         570 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 |  570 | Query          |         1 |         647 | BEGIN                                                                 |
| mysql-bin.000001 |  647 | Query          |         1 |         753 | use `bin`; INSERT INTO t VALUES(1, 'sixstar')                         |
| mysql-bin.000001 |  753 | Xid            |         1 |         784 | COMMIT /* xid=2369 */                                                 |
| mysql-bin.000001 |  784 | Anonymous_Gtid |         1 |         849 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 |  849 | Query          |         1 |         926 | BEGIN                                                                 |
| mysql-bin.000001 |  926 | Query          |         1 |        1034 | use `bin`; INSERT INTO t VALUES(2, 'shineyork')                       |
| mysql-bin.000001 | 1034 | Xid            |         1 |        1065 | COMMIT /* xid=2370 */                                                 |
| mysql-bin.000001 | 1065 | Anonymous_Gtid |         1 |        1130 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                  |
| mysql-bin.000001 | 1130 | Query          |         1 |        1207 | BEGIN                                                                 |
| mysql-bin.000001 | 1207 | Query          |         1 |        1309 | use `bin`; INSERT INTO t VALUES(3, 'xxx')                             |
| mysql-bin.000001 | 1309 | Xid            |         1 |        1340 | COMMIT /* xid=2371 */                                                 |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------+
18 rows in set (0.00 sec)

接下来做一个操作就是对于t表中的数据进行删除所有的数据(这是模拟某些不小心的同学做的操作)；

mysql> delete from `t` where id > 1;
Query OK, 2 rows affected (0.02 sec)

mysql> select * from t;
+------+---------+
| id   | name    |
+------+---------+
|    1 | sixstar |
+------+---------+
1 row in set (0.00 sec)

现在来做恢复；根据数据情况找到数据的节点位置， 发现是从849开始到1340结束

[root@localhost data]# mysqlbinlog mysql-bin.000001 --start-position 849 --stop-position 1340  | mysql -u root -p
Enter password:

 

3. 配置从节点

注意：对于使用虚拟机的同学--注意克隆之后的系统你需要稍微修改一下系统的ip 地址

[root@localhost ~]# vi /etc/sysconfig/network-scripts/ifcfg-ens33
IPADDR=192.168.153.131
NETMASK=255.255.255.0
GATEWAY=192.168.153.2
[root@localhost ~]# systemctl restart network

注意还顺便也修改一下宝塔的nginx服务地址 -- 然后暂时就ok了把...

在进行配置之前，回顾一下过程

要先明确配置的架构Master-slave

1. 配置主节点
   1.1 配置账号
   1.2 开启binlog日志
2. 配置从节点
   2.1 配置同步日志
   2.2 指定主节点的ip， 端口， 用户..
   2.3 启动从节点

3.1 配置同步日志

修改配置

[root@localhost ~]# find / -name my.cnf
/etc/my.cnf
[root@localhost ~]# vi /etc/my.cnf
[root@localhost ~]# find / -name mysqld
/etc/rc.d/init.d/mysqld
/www/server/mysql/bin/mysqld
[root@localhost ~]# /etc/rc.d/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

在配置文件中添加

# 配置从节点
server-id = 2
relay_log = /www/server/data/mysql-relay-bin
relay_log-index = /www/server/data/mysql-relay-bin.index

log_slave_updates = 1
read_only = 1

参数介绍： server_id:这是服务id系统会自动命名的，但如果机器名边画画肯能回答导致问题。可以讲你主库和备库上的log-bin设置为相同的值。 relay_log:指定中继日志的位置和命名

3.2 指定主节点的ip，端口，用户

mysql> change master to master_host='192.168.153.129',master_port=3306,master_user='127',master_password='127',master_log_file='mysql-bin.000001',master_log_pos=0;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

3.3 启动从节点

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status \G;

对于我们来说其中的信息主要是关注

Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

reset slave all 清楚slave信息

测试的方法就是在主服务器中，添加一些数据测试观察从服务其中的数据变化情况。

进入slave服务器，运行：

mysql> show slave status\G

         .......
             Relay_Log_File: localhost-relay-bin.000535
              Relay_Log_Pos: 21795072
      Relay_Master_Log_File: localhost-bin.000094
           Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
      ......

 

解决办法一、

Slave_SQL_Running: No
1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：
解决办法：

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

解决办法二、

首先停掉Slave服务：slave stop
到主服务器上查看主机状态：
记录File和Position对应的值

进入master

mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 |              |                  | 
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

然后到slave服务器上执行手动同步：

mysql> change master to 
> master_host='master_ip',
> master_user='user', 
> master_password='pwd', 
> master_port=3306, 
> master_log_file=localhost-bin.000094', 
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> start slave ;
1 row in set (0.00 sec)

 

mysql> show slave status\G
*************************** 1. row ***************************
........
            Master_Log_File: localhost-bin.000094
        Read_Master_Log_Pos: 33768775
             Relay_Log_File: localhost-relay-bin.000537
              Relay_Log_Pos: 1094034
      Relay_Master_Log_File: localhost-bin.000094
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:

手动同步需要停止master的写操作！

yang洋PHPer
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
MySQL主从原理及配置详解
01-19
MySQL主从配置及原理，供大家参考，具体内容如下 一、环境选择： 1.Centos 6.5 2.MySQL 5.7 二、什么是MySQL主从复制 MySQL主从复制是其最重要的功能之一。主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。MySQL主从复制的基础是主服务器对数据库修改记录二进制日志，从服务器通过主服务器的二进制日志自动执行更新。 三、MySQL主从复制的类型 1.基于语句的复制 主服务器上面执行的语句在从服务器上面再执行一遍，在MySQL-3.23版本以
mysql配置主从
热门推荐
Tyler唐的博客
12-05 1万+
mysql配置主从
主从同步笔记
以码为梦
12-20 940
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。 原理：基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。 因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。
mysql 主从原理和配置教程
最新发布
完颜振江
05-22 274
MySQL 主从架构是一种常用的分布式数据库架构，它将MySQL服务器分为两个部分：主服务器（Master）和从服务器（Slave）。在这个架构中，主服务器负责写入数据，而从服务器负责读取数据。
Mysql 主从配置
strivew的博客
07-18 1823
mysql 主从配置
MySQL数据库主从配置
qq_59363371的博客
09-07 837
【代码】MySQL数据库主从配置。
实战MySQL主从的配置及介绍
qq_64989514的博客
09-08 1007
在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。
如何配置MySql主从库的配置方法
紅塵的博客
08-16 3241
原理我就不写了，我也不懂，就是记录一下是如何配置的 首先我们要有2个mySql数据库，我本地已经安装了2个数据库，并且测试连接成功 （1） MySql8-Master 主库 端口：3306 （2） MySql8-Slave 从库 端口：3308 // -------------------------------------------- 1、开始配置主库的配置文件 my.ini ## 如果设置主库，这个ID是主库的标识，代表唯一性，自定义...
MySQL 主从数据库配置
雨橙Orainge的博客
02-14 283
【代码】MySQL 主从数据库配置。
MySQL主从复制配置详解
SQL数据库开发
05-06 451
点击上方SQL数据库开发，关注获取SQL视频教程SQL专栏SQL数据库基础知识汇总SQL数据库高级知识汇总之前很多小伙伴想知道MySQL主从复制的配置步骤，今天它来了。带着你可能碰到的各...
【mysql】之主从配置详解
qq_41346335的博客
04-23 1123
文章目录一、MySQL主从1. 进入etc/my.cnf，修改如下配置2. 备份账户并授权3. 主进行锁表4. 主 找到 bin-log的位置 show master status5. 主备份数据6. 从配置 一、MySQL主从 1. 进入etc/my.cnf，修改如下配置 配置如下 主配置: 从配置 2. 备份账户并授权 命令详解 链接 命令: CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’; 说明： username：你
MySQL主从同步详解与配置
yygr的博客
04-26 2052
https://zhuanlan.zhihu.com/p/335142300 MySQL主从同步详解与配置 第一部分【原理解析】 * 应用背景* MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能。下图就描述了一个多个数据库间主从复制与读写分离的模型。web服务器(tomcat,apache,iis,weblogic等)从多个slave数据库进行读操作，从master数据库
Day05 03-MySQL主从-主主原理与搭建详解
Aidon博客
06-12 1971
在实际的生产环境中，数据量可能会非常庞大，这样单机服务的MySQL在使用的时候，性能会受到影响影响。并且单机服务的MySQL的数据安全性也会受到影响。因此在生产环境中，我们通常搭建MySQL的集群架构，来提高庞大数据量的基础上的高性能读写的需求。在常见的集群架构中，最常见的就是主从架构(Master-Slaves)MySQL的主从架构，又有一些其他的名称：主从复制、主主复制和集群等。 本文详细讲解：MySQL集群架构介绍、主从原理、主从复制搭建、主主原理和主主搭建等。
mysql主从配置实战记录
u012352392的博客
11-10 143
1111
mysql主从配置详细教程最新
pygodnet的博客
09-27 1万+
MySQL主从可以实现MySQL的读写分离，简言之，master上进行写操作，同步数据库，slave上进行读操作，可以极大的提升性能，降低服务器负载，在此不做赘述，下面以两台机器来做主从配置详解！ 两个主机 主 | 10.8.152.117 | master | 从 | 10.8.13.42 | slave | 准备工作 两台服务器 启动成功 关闭防火墙 相互ping 通...
详解Mysql数据库主从配置
weixin_40913898的博客
07-29 487
Mysql数据库主从配置 一、环境 主库： IP：172.12.7.40 操作系统：CentOS Linux release 7.8.2003 (Core) 数据库版本：Server version: 5.7.24 MySQL Community Server (GPL) 数据库：pm 数据库root用户密码mysqld 从库： IP：172.30.6.81 操作系统：CentOS Linux release 7.4.1708 (Core) 数据库版本：Server version: 5.7.24 MySQ
数据库的简单操作命令
wpb74521wrf的博客
05-25 358
关系型数据库核心元素数据行(记录)数据列(字段)数据表(数据行的集合)数据库(数据表的集合)[连接mysql]mysql -uroot -pmysql[退出登录]quit 和 exit 或 ctrl+d1.  [数据库操作概述]查看所有数据库show databases; 使用数据库use 数据库名; 查看当前使用的数据库select database(); 创建数据库create databas...
Mysql主数据库查看从数据库IP方法
qq_44692240的博客
02-03 2777
Mysql主数据库查看从数据库IP方法 show processlist 查看复制用户的连接 show slave hosts 查看所有从库的列表
mysql主从复制配置详解
04-02
以下是MySQL主从复制的配置详解： 1. 确认主服务器和从服务器的MySQL版本号一致。 2. 在主服务器上创建一个新用户，并授予该用户复制权限。 3. 在主服务器上开启二进制日志功能，并设置二进制日志文件名和位置。 ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值