MySQL主从结构搭建

﹏ヽ暗。殇╰゛Y 2022-05-14 06:15 182阅读 0赞

昨天按公司要求搭建了一套MySQL主从结构，趁现在还没有忘记，做个笔记巩固学习；

由于保密协议，本文所涉及的参数都是自己本机的测试参数：

配置环境：

两台新的服务器主机，本次系统是centos7.4；

ip1：172.168.0.40   ip2：172.168.0.41

主从的概念不做说明：简单结构是一个主机做MySQL服务的主机，另一个主机来做MySQL服务的从机，从机目的的容灾；服务正常情况都在主机上跑；

简单搭建原理：

1、两个主机上都安装好MySQL服务

2、配置主机的MySQL配置文件、配置从机的MySQL配置文件 使得他们相关的配置文件信息能够在链接的时候对接上；

3、确保主机ip和从机ip在同一网段，即在局域网内；

4、在从机上执行链接信息命令，让从机主动连接主机，由于配置文件的信息校验通过，主机允许从机访问主机MySQL服务从而实现主从结构；

具体步骤：

1、分别在两个主机上安装相同版本的MySQL，本次安装版本是MySQL5.7；

方法一，我用的是本地安装，确保安装包一致；步骤是先把Linux版本的MySQL安装包现在到电脑，通过ftp软件将文件上传到主机中自己新建的/data/package文件夹，执行命令：yum localinstall +安装包名进行安装；两个机器操作方法一样；

方法二，通过yum安装

2、两个机器安装完后，启动MySQL服务，设置开机自动重启，查看MySQL状态，命令是：

启动：systemctl start mysqld
    加入开机启动：systemctl enable mysqld 
                systemctl daemon-reload
    查看状态：systemctl status mysqld

查看状态后，status如下图代表在正常运行：

![70][]

2、进入MySQL修改密码和相关配置：

MySQL安装完自带root密码，这个root只允许当前本机进行登录；密码是系统自动生成的，放在了日志文件中，我们需要去找到root密码登入MySQL进行相关调整：

日志存放在：/var/log/mysqld.log

查看日志命令：more /var/log/mysqld.log

![70 1][]

截图就是默认的密码，复制密码后通过命令：

mysql -uroot -p

进入MySQL

修改登录密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

我修改为123456，访问root账户还是只用本机访问，因为企业对于数据库链接一般都会有跳板机/堡垒机这种中间件去链接服务器，为了安全，root账户我也只设定到跳板机上才能使用，远程本地机器要用的账户，我们可以再新建一个角色账户并授权所有权限即可；

具体create user 和grant 授权命令不做介绍，这里我新建了一个admin账户授权all权限；

以上的步骤两台服务器操作相同；

接下来开始配置主从信息，这里开始两个机器的差异产生；

两个机器的配置文件都是放在/etc/my.cnf

所以编辑配置文件命令都是 ：vi /etc/my.cnf

主机和从机配置字符格式都是utf-8这个是一样的，所有的配置信息也都放在 \[mysqld\] 以下；

character_set_server=utf8
    init_connect='SET NAMES utf8'
    这两条是设置utf-8字符格式，两个主机配置相同

不同点：

主机（master）添加配置文件信息：

#要给从机同步的库（如果不写，默认全部同步）
    binlog-do-db=db01
    #不给从机同步的库(多个写多行)
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
    binlog-ignore-db=performance_schema
    binlog-ignore-db=sys
    #自动清理 7 天前的log文件,可根据需要修改
    expire_logs_days=7
    # 启用二进制日志
    log-bin=master-bin
    # 服务器唯一ID，一般取IP最后一段
    server-id=40
    
    log_bin_index = master-bin.index

从机添加的配置信息：

server-id = 41
    #加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错
    read_only = 1
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    relay-log = slave-relay-bin
    
    #主服务器I/O日志读取、记录及存放
    relay-log-index = slave-relay-bin.index

以上就是主从结构搭建的配置，配置完成后保存退出，运行并进入各个主机的MySQL；

--------------------

9.4更新：修改完配置文件后需要重启服务才生效，不管是MySQL还是其他Linux应用程序，重启的方式有两种：

1、进入服务启动文件夹进行启动

2、service命令：如MySQL的命令是：service mysqld restart

--------------------

master服务器查看MySQL状态：

show master status；

![70 2][]

这里查看到的信息即是主服务器准许接入的信息，我们要在从服务器MySQL语句中执行接入语句需要用到主服务器允许接入参数，这里的file和position非常重要；

运行slave服务器MySQL（从服务器）：执行接入master服务器MySQL服务语句：

change master to master_host='主服务器ip地址',
    master_port=3306,master_user='链接主服务器账户',
    master_password='链接主服务器角色账户密码',
    master_log_file='主服务器file参数',
    master_log_pos=主服务器positon参数

注意以上语句不要断开，我这里隔行是为了方便观看，执行的时候请一行无空格敲完；除了端口和pos其他参数都要有引号；

以上我们就映射完成，并在理论上完成了搭建；

核实工作：

slave上运行sql语句：

show slave status\G;

结果如下：

![70 3][]

代表从slave运行正常。

测试：

分别用navicat链接master和slave；在master上创建数据库和表并插入数据最后删除库，每一步观察slave上是否同步，经测试数据库同步无异常，搭建完成：

![70 4][]

遇见问题：

1、修改mater配置文件的时候文件信息改错了导致restart mysql服务报错：

(code=exited, status=1/FAILURE)

解决方案：不知道改动了配置文件哪里，后来直接替换了一份配置文件就可以启动成功；

教训，更改配置文件前最好先备份一下；

--------------------

9.4更新：

MySQL服务报错，可以去错误日志里面查看报错原因，大多数情况在错误日志中能看得到，所有相关的日志配置，都可以在配置文件中查看得到；

--------------------

2、配置好slave后没有成功同步，经查询slave状态：show slave status\\G；显示：Slave\_SQL\_Running: No

产生原因：

1、如果mater服务器和slave服务器有数据差异，且相关数据库还有同步服务的时候，mater上一旦操作了差异数据的调整，slave无法找到数据，slave会自动关闭running。

2、slave上进行了写入操作使得master和slave产生差异；

长期解决方案：还在学习中；

临时解决方案：

Slave\_SQL\_Running: No  
1.程序可能在slave上进行了写操作   2.也可能是slave机器重起后，事务回滚造成的.  
一般是master差异且进行了差异数据执行造成的：  
解决办法：  
mysql> stop slave ;  
mysql> set GLOBAL SQL\_SLAVE\_SKIP\_COUNTER=1;  
mysql> start slave ;

反思：是否应该使salve库不允许写入操作？

--------------------

9.4更新：

如果master的允许接入参数file和position进行了变更，则slave上的接入配置信息需要重置，否则会导致数据不同步，且在start slave语句时会报错无法启动slave；

产生原因：

接入参数变更

解决方案：

重新执行接入语句，步骤必须是：

1、stop slave （停止slave同步）

2、reset slave （重置slave同步）

3、执行更新slave参数语句

change master to
    master_host='',
    master_user='',
    master_password='slave',
    master_port=3306,
    master_log_file='',
    master_log_pos=;

4、start slave

大多数修改完配置文件后无法启动MySQL服务的原因都是配置文件修改信息不完整，如少打一个字母，误修改其他参数，或者是文件夹MySQL没有权限；

解决方案：

1、建议在修改配置文件前先进行备份，或直接修改备份确认后再替换原文件

2、授权问题，如果我们在变更MySQL默认配置的文件夹，如我想把默认的数据存放文件指定到自己的文件夹，那么在我新建自己的文件夹的时候，只有当前用户或是root账户有该文件夹操作权限，一般作为root账户去新建文件夹时，只有root角色能修改文件夹，MySQL没有权限去读写文件夹时会启动报错，这时候我们要对MySQL进行文件夹权限授权，命令是：

chown -R MySQL：MySQL +文件夹目录；
    chown -R MYSQL:MYSQL /data/mysql/dir 代表把dir文件夹读写权限给到MySQL，-R是代表文件夹操作

--------------------

9.5更新：

问题：MySQL变更文件夹后启动报错：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

报错信息告诉我无法链接到MySQL，因为找不到sock文件；额外笔记中有介绍sock文件作用，详情去后文查看；

这里要说明的一点是，一般我们改配置文件的时候都是改：\[mysqld\] 下面的信息，里面有socket指定到我更改后的目录但是通过报错发现还是在默认目录去调取sock文件。本身这是个坑的，这里做些讲解避免新人懵逼；

原因：

\[mysqld\]有指定路径，重启服务后不去指定路径找sock，而是去默认路径找sock；

原理：

\[mysqld\] 字段是服务端的信息字段，配置客户端信息字段不是在这下面，客户端信息想要生效，需要配置到 \[client\] 字段下面；

解决方案：

在\[client\] 字段下面添加socket = /目录/mysql.sock 信息，重启服务

如果在my.cnf中没有找到\[client\]字段，直接在\[mysqld\]字段信息后手工添加一个\[client\]字段即可

--------------------

额外笔记，MySQL配置文件参数代表意义：

以下文件夹路径是我举例路径，z默认安装路径可以自行百度，这里主要描述参数意义。

[mysqld]
    
    ###以下参数配置都可以按需求修改
    
    #服务名称，不做介绍....
    user=mysql 
    
    #端口，不做介绍.....
    port=3306
    
    #MySQL启动的配置文件，这是不可缺少的文件，跟随数据存放目录，变更数据存放目录也要变更socket目录
    socket=/data/mysql/mysql.sock
    
    #MySQL数据库及数据存放目录，默认是在/var/lib/mysql中，可自行变更
    datadir=/data/mysql
    
    #记录当前MySQL进程的process id目录，启动MySQL时会去检查pid，如果存在且占用状态则会启动报错，如果存#在不占用会删除该pid，没有pid后启动MySQL会新建一个pid记录MySQL进程，主要目的是：数据文件同一份的情#况下不同端口，防止同一个数据库被启动多次
    pid-file=/data/mysql/mysql.pid
    
    
    #MySQL的安装路径，不做多余解释....
    basedir=/data/mysql/dase
    
    
    #MySQL的错误日志存放路径，非常重要，查看错误日志进该文件夹读取日志文件
    log-error=/data/mysql/errorlog
    
    #非常重要的MySQL日志文件，该日志记录除了select以外的操作记录，如update/delete/create/等待，如果要#求回滚数据，通过特定工具读取该日志进行逆向操作进行回滚
    log-bin=/data/mysql/log/mysql-bin
    
    #MySQL服务id，不做过多解释.....
    server-id=40
    
    #MySQL集群时主允许同步的数据库，如果不设置，则默认全部同步
    binlog-do-db=db01，db02.......
    
    #MySQL集群时不允许同步的数据库
    binlog-ignore-db=db01，db02....

--------------------

9.10更新：

slave上写入数据会导致主从关系中断的情况，目前决定把slave机器上的权限改为只读；

上锁方式：

--查看全局只读状态是否开打
    show global variables like “%read_only%”; 
    
    --锁定表用户更新，限定普通账户不解锁情况下不能写入数据
    flush tables with read lock; 
    
    --打开全局只读开关
    set global read_only=1;
    
    --再次查看开关是否打开
    show global variables like “%read_only%”;

如果特殊需求要解锁写入，方法是：

--解除表锁定
     unlock tables; 
    
    --关闭只读开关
    set global read_only=0;

注意点：

1、锁定账户命令只对普通用户生效，如果是超极用户，如root等，不起作用的；

2、只读不会影响主从同步，所以查看slave同步状态，开关都是打开的，无需担心主从同步会中断；

[70]: /images/20220514/f38d245c052d415e85cb6c067fb1f0dd.png
[70 1]: /images/20220514/9f3280e0b9a64745bd39bddcd7ad180e.png
[70 2]: /images/20220514/f9c84a968bb542ab984b600c61a51163.png
[70 3]: /images/20220514/54afe308126242498e8e5fdbdb1cb69a.png
[70 4]: /images/20220514/25cb667ff4dd44d6933245527363dcd1.png