Linux搭建MYSQL主从

一、简介

背景：

MySQL 主从复制的背景是，当一个 MySQL 数据库需要处理大量的读操作和写操作时，可能会出现性能瓶颈。为了解决这个问题，可以使用主从复制技术，将读操作分散到多个从服务器上，从而减轻主服务器的负载，提高数据库的性能。

主从复制还可以提高数据库的可用性和可靠性。如果主服务器出现故障，从服务器可以接管主服务器的工作，从而保证了数据库的可用性和可靠性。此外，通过将数据复制到多个从服务器上，可以提高数据的安全性和可靠性，从而保护数据免受意外删除、破坏或损坏的风险。

MySQL 主从复制是一种成熟的数据库复制技术，已经被广泛应用于各种场景，如电子商务、社交网络、游戏等。

优点：

高可用性：主数据库异常可切换到从数据库

负载均衡：实现读写分离

备份：进行日常备份

复制过程：

Binary log：主数据库的二进制日志；Relay log：从服务器的中继日志。

  （1）主数据库在每次事务完成前，将该操作记录到binlog日志文件中；

  （2）从数据库中有一个I/O线程，负责连接主数据库服务，并读取binlog日志变化，如果发现有新的变动，则将变动写入到relay-log，否则进入休眠状态；

  （3）从数据库中的SQL Thread读取中继日志，并串行执行SQL事件，使得从数据库与主数据库始终保持一致。

注意事项：

（1）涉及时间函数时，会出现数据不一致。原因是，复制过程的两次IO操作和网络、磁盘效率等问题势必导致时间戳不一致；

  （2）涉及系统函数时，会出现不一致。如：@@hostname，获取主机名称，主从数据库服务器名称不一致导致数据不一致；

二、环境准备

 1 购买服务器：

主：这里使用的aws公有云服务器，1C2G,磁盘20G大小就可以。

从：这里使用的aws公有云服务器，1C1G,磁盘20G大小就可以。

MySQL数据库的版本我这里是5.7

登录aws的官网，然后注册账户，在服务搜索EC2这个产品，如下https://cn-northwest-1.console.amazonaws.cn/ec2/v2/home?region=cn-northwest-1#Instances:sort=keyName

 启动新实例，自定义你服务器的名称，选择你要什么系统例如ubuntu、linux等，选择你的套餐和架构，新手建议一般选择默认

 实例类型这个就是选择服务器的型号了或者说性能，一般满足你业务的你就自己评估一下，避免资源浪费，我这里1c2g和1c1g就满足业务了，然后记得要提前创建密钥对，自己命名然后他会自动下载保存，你要记得路径，后续远程登录的时候会使用到，安全组也需要你创建一下，有默认的安全组，但是建议自己创建一个，直接点击创建然后选择任何位置，先简单设置后续会进一步设置

 最后存储就类似于磁盘大小，还是根据业务来，有免费的30G，我这里20G就满足了。设置完之后点击启动实例，然后等待2-3分钟初始化

 然后根据业务来设置入站端口，我这里为了方便都设置为全通，但是建议只设置一点点业务访问的端口，为了安全着想。

 验证：本地win+r 输入cmd，然后ping + 公有ipv4地址，就是通的了。

 2 远程连接服务器

2.1 xshell远程连接

   看了官网有很多方法，这里选择两种，xshell连接和putty连接，首先到xshell官网下载免费的xshell和xftp，然后一直下一步安装就好，在Xshell中选择“文件”—>“新建”，弹出如下对话框：复制公有云DNS的地址到Xshell新建对话框的主机处，名称自定义

 点击“用户身份认证”，方法选择“Public Key”，用户名写ec2-user，用户密钥选择刚刚导入的密钥对(创建密钥对自动下载的)；点击确定，大功告成，通过点击Xshell文件下面的文件夹，选择你的服务器就可以登陆了

 2.2 putty远程连接服务器

同理首先官网下载putty，直接解压msi包就可以，如下有参考链接，先把pem文件转换为ppk的密钥文件，https://blog.csdn.net/weixin_42638946/article/details/122022592，方法是打开puttygen.exe，点击load，选择all files，，然后选择你要转换的密钥对，然后点击Save private key 保存下来就可以。然后就可以打开putty.exe，同理选择会话，SSH填写ip、端口、以及密钥对，然后点击open就可以连接了，第一次有个警告，确认就可以，远程连接结束。如下操作图

 3 安装MYSQL

两台机器都需要操作，首先进入opt目录，执行下面命令进行下载：cd /opt/，进入opt目录，

wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm 下载安装包

执行安装命令

rpm -ivh mysql57-community-release-el7-9.noarch.rpm

执行完成后会在/etc/yum.repos.d/目录下生成两个repo文件mysql-community.repo mysql-community-source.repo 必须进入到 /etc/yum.repos.d/目录后再执行安装，如下：

cd /etc/yum.repos.d/

yum install -y mysql-server --nogpgcheck      //nogpgcheck表示不校验key

安装完毕，启动数据库：

systemctl start mysqld     #启动数据库

systemctl enable mysqld   #开机自启动

 其他命令：

# 关闭开机启动

systemctl stop mysqld

# 关闭MySQL

systemctl stop mysqld

# 重启MySQL

systemctl restart mysqld

# 查看运行状态

systemctl status mysqld （active running 表示运行状态）ctrl+z 可以退出这个界面

 4 设置root密码

1、进入MySQL数据配置文件进行修改

vi /etc/my.cnf  再按字母【i】进入编辑模式，然后将下面这一行拷贝进去，放到最后一行，含义是免密码登录  skip-grant-tables ，然后按【Esc】后再输入【:wq】，表示保存并退出，

然后重启：systemctl restrt mysqld.service，直接免密登录，mysql -uroot -p此时提示要你输入密码，什么都不用输入，直接回车就可以进入数据库了，如下图

你可以使用以下命令来重置root账户的密码：（我这里设置的是AaBb123456，设置最好不要设置的太简单）,要先登录到数据库页面之后输入如下命令

use mysql;  #有分号

5.7以上版本运行:

update user set password= password("AaBb123456") where user='root';   #有分号

5.7以下版本运行:

update user set authentication_string= password("AaBb123456") where user='root'; #有分号

改了之后记得删除免密设置，按照刚才的步骤，进入【my.cnf】文件中，将【skip-grant-tables】代码删除，并保存退出，再重启MySQL服务器即可，这样以后就可以使用AaBb123456这个密码进行登录了，记得登录测试一下

5 主库（在master主节点服务器操作）

5.1 修改my.cnf

vi /etc/my.cnf

然后按字母【i】进入编辑模式，将下面内容拷贝进去即可：（拷贝到【mysqld】节点下面）

[mysqld]

#开启log-bin二进制日志

log-bin=mysql-bin

#配置唯一的服务器ID，一般使用IP最后一位

server-id=94

#添加，允许从服务器更新二进制日志

log-slave-updates=true

#需要同步的数据库，如果有多个，复制多份指定数据库名即可

binlog_do_db=testdb

#不需要同步的数据库，如果有多个，复制多份指定数据库名即可

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

#下面不是必须配置

#主要是为了使用带事务的InnoDB进行复制设置时尽可能提高持久性和一致性

innodb_flush_log_at_trx_commit = 1

#使binlog在每1次binlog写入后与硬盘同步

sync_binlog = 1

#只保留7天的二进制日志，以防磁盘被日志占满

expire-logs-days=7

然后按【Esc】后，再按【:wq】进行保存并退出操作

5.2 重启数据库

修改完配置文件后需要重启数据库，并查看配置是否生效，执行下面命令进行重启：

systemctl restart mysqld.service

然后在输入下面命令查看MySQL重启后的运行状态：systemctl status mysqld ，到此主库设置完毕

5.3 检验设置是否成功

mysql -uroot -pAaBb123456 登录验证一下，登录成功后输入下面命令检查：show variables like 'server_id';

可能会报错，ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.，执行下面的命令修改密码

mysql> ALTER USER USER() IDENTIFIED BY 'winkin';#winkin为密码

如果报错 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

执行如下命令：

mysql> set global validate_password_policy=LOW;

mysql> set global validate_password_length=6;

mysql> ALTER USER USER() IDENTIFIED BY 'winkin';#winkin为密码

FLUSH PRIVILEGES;   #刷新系统权限

show variables like 'server_id';

再输入命令：show variables like 'log_bin';

再输入命令：

#skip_networking默认是OFF关闭状态，启用后，主从服务器将无法相互通信，所以这里必须是OFF

show variables like '%skip_networking%';

检查完毕，设置成功。

5.4 建立主从复制的账号

mysql -uroot -pwinkin

同样先登录MySQL之后，执行下面命令：

GRANT REPLICATION SLAVE ON *.* to 'slavetest'@'%' identified by 'root@123456';

上面SQL的作用是创建一个用户【slavetest】密码为【root@123456】并且给【slavetest】用户授予REPLICATION SLAVE权限，常用于建立复制时所需要用到的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过该用户复制，如下：

#查看master的状态

show master status\G

上面结果中的File和Position的值需要记录一下，待会要用到，主库的配置就搞完了，都已经做好准备了，下面我们开始搞从库

File: mysql-bin.000001

Position: 854

6 从库配置（另一台数据库配置）

6.1 测试连接

先在从库上执行下面语句检查下刚刚建立的账号是否可以连接

#IP是主库IP

mysql -uslavetest -p'root@123456' -h120.26.60.94

6.2 配置my.cnf

vi /etc/my.cnf

然后在【mysqld】节点下面新增下面内容：

#配置唯一的服务器ID，一般使用IP最后一位

server-id=160

然后重启数据库，并查看刚配置的是否生效：

systemctl restart mysqld

mysql -uroot -pAaBb123456

show variables like 'server_id';

show variables like 'log_bin';

报错：ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

执行：ALTER USER USER() IDENTIFIED BY 'Admin2023!';  密码改为了Admin2023!

FLUSH PRIVILEGES; 就可以了

6.3  配置复制参数

配置同步，注意 MASTER_LOG_FILE 和 MASTER_LOG_POS 的值要与Master的一致，登录MySQL后运行下面语句：这里的file和pos要和master的一样，就是之前保存的

CHANGE MASTER TO MASTER_HOST='68.79.40.46',MASTER_USER='slavetest',MASTER_PASSWORD='root@123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=854;

主的如下：

如果执行失败，就执行：reset slave; 我这里执行成功了，就可以直接执行下面语句了：

start slave;

 

6.4 查看从库状态

执行下面命令查看从库状态：show slave status\G

主要看红色部分是否为yes

# 负责与主机的io通信

Slave_IO_Running: Yes

# 负责自己的slave mysql进程

Slave_SQL_Running: Yes

若出错，则清理掉之前的配置，执行以下命令：

mysql> stop slave;

mysql> reset slave all;

7 测试

在我本地用Navicat工具连接主库和从库：

连接可能会说账户密码或者服务器不允许连接，步骤如下：

主从数据库（服务器）：mysql -uroot -p   输入密码，登录成功后执行下面的命令

GRANT ALL PRIVILEGES ON *.* TO 'slavetest'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

然后就可以了。用户名：slavetest  密码：123456

目前两个库都是一样的库，我现在在主库上面新建一个【testdb】数据库，然后从库直接刷新，就可以看到这个数据库了，同样的，你在主库上新建什么表，添加什么数据，刷新从库后，会发现，数据都在了，说明测试成功了，现在主从复制功能就实现了：右键新建数据库

刷新一下，发现从库也同步过来了，就实现了主从。

当然如果修改了从服务器的数据，主服务器不会自动修改。主从复制是单向的，只有主服务器的更改会被复制到从服务器，而从服务器的更改不会被复制到主服务器。

如果您需要在从服务器上进行更改，并希望这些更改能够同步到主服务器和其他从服务器，您可以考虑使用 MySQL 群集（Cluster）或 MySQL GTID 复制（GTID Replication）等技术。

MySQL 群集是一种多主复制技术，它可以将多个 MySQL 服务器组成一个群集，每个服务器都可以接受读写请求，并将更改同步到其他服务器。这样，如果您在任何一个服务器上进行更改，这些更改都会被同步到其他服务器。

MySQL GTID 复制是一种基于全局事务标识符（GTID）的复制技术，它可以确保每个事务只被执行一次，并且可以在主服务器和从服务器之间自动切换。这样，如果您在任何一个服务器上进行更改，这些更改都会被同步到其他服务器。

需要注意的是，MySQL 群集和 MySQL GTID 复制都需要更高的配置和管理成本，因此在选择使用这些技术之前，需要仔细评估您的需求和资源。

8 注意

最后，如果你设置都已经设置好了，确保没有问题了，但是本机windows就是无法远程连接Linux中的MySQL服务器的话，那就需要检查下：

端口是否放行

防火墙是否关闭

防火墙相关命令：

远程访问linux时，需要关闭防火墙，否则访问linux上的tomcat，mysql等服务可能会失败；

查看防火墙状态： systemctl status firewalld.service（绿的running表示防火墙开启）

执行关闭命令（立即关闭 - 立即生效）： systemctl stop firewalld.service

再次执行查看防火墙命令：systemctl status firewalld.service

执行开机禁用防火墙自启命令（永久关闭 - 重启生效）： systemctl disable firewalld.service

开启防火墙：systemctl start firewalld.service

一般情况下，需要检查下Linux服务器的防火墙是否关闭，然后检查端口是否放行（不放行的话其他服务器上连接不成功的）

Slave_IO_Running: No

一般出现这样的问题有以下几种情况，逐一检查下即可：

网络不通

my.cnf 配置有问题

密码、file 文件名、pos 偏移量不对

防火墙没有关闭