MySQL主从数据库测试（CentOS 7）

MySQL主从数据库测试（CentOS 7）

测试环境说明

操作系统：CentOS 7x64（最小化安装，进安装调试工具、开发工具、安全性工具、系统管理工具）

数据库版本：MySQL5.7

硬件配置：1个处理器、 1GB内存、 10GB硬盘，共两台（一主一从）

测试人：think

测试时间：2021/08/27

配置CnetOS 7

更新安装源

yum update -y

安装网络工具net-tools

yum install net-tools -y

安装编辑器vim

yum install vim -y

安装下载工具wget

yum install wget -y

备份安装源

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup

下载国内安装源

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

清除缓存

yum clean all

生成缓存

yum makecache

安装工具lsof

yum install lsof -y

安装MySQL5.7

参考文章：Linux 之 CentOS 7安装MySQL5.7 - 念月_xy - 博客园 (cnblogs.com)

下载MySQL5.7的资源包

wget http://repo.mysql.com/mysql57-community-release-el7-10.noarch.rpm

安装MySQL5.7源

rpm -Uvh mysql57-community-release-el7-10.noarch.rpm

安装MySQL服务端

yum install mysql-community-server -y

启动MySQL

systemctl start mysqld.service

查看MySQL状态

systemctl status mysqld.service

image-20210827091305919

将MySQL设置为自启动

systemctl enable mysqld.service

配置MySQL

获取生成的默认随机密码

grep 'temporary password' /var/log/mysqld.log

image-20210827093154955

登录MySQL修改密码

mysql -uroot -p

image-20210827093410612

修改密码

注意默认情况下MySQL有自己的密码规则，如需修改则要另行配置。

ALTER USER 'root'@'localhost' IDENTIFIED BY '{yourpassword}';

开启远程连接

mysql> use mysql;#%代表所有连接mysql> update user set Host = '%' where Host = 'localhost' and User='root';#刷新权限表
mysql> flush privileges;

首先切换到mysql数据库，其次输入更新命令，最后记得刷新权限表

注意：如果远程连接不成功，则使用授权命令允许远程连接，授权完成后如仍然无法远程连接，则可重启服务器在进行尝试

mysql> GRANT ALL PRIVILEGES ON *.* TO '{username}'@'%' IDENTIFIED BY '{yourpassword}' WITH GRANT OPTION; // %:表示从任何主机连接到mysql服务器mysql> FLUSH PRIVILEGES;

image-20210827094032438

设置字符集

vim /etc/my.cnf

执行之前记得退出mysql，只需输入exit即可

修改后的内容如下，修改之后保存退出：

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[mysql]default-character-set=utf8[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

重启MySQL使配置生效

systemctl restart mysqld

测试MySQL远程连接

放行MySQL的远程连接端口3306

#放行3306端口firewall-cmd --zone=public --add-port=3306/tcp --permanent

重启防火墙

#重启防火墙firewall-cmd --reload

使用Navicat测试

连接成功

image-20210827102921533

主数据库配置

参考文章：面试官你好，我已经掌握了MySQL主从配置和读写分离，你看我还有机会吗？ - 风的姿态 - 博客园 (cnblogs.com)

启用bin_log，并配置server_id、binlog_format、binlog_do_db、expire_logs

打开MySQL配置文件

vim /etc/my.cnf

MySQL 配置文件默认位置在如下位置，从上下到下优先级降低：

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. /usr/local/etc/my.cnf
  4. ~/.my.cnf

配置内容如下：

[mysqld]log_bin=mysql-binserver_id=102binlog_format=mixedbinlog_do_db=db1expire_logs-days=7

参数说明：

binlog_format

bin_log 日志的格式，支持下面三种，推荐使用 mixed 。
statement：会将对数据库操作的sql语句写入到binlog中。
row：会将每一条数据的变化写入到binlog中。
mixed：statement 与 row 的混合。MySQL 决定什么时候写 statement 格式，什么时候写 row 格式。

binlog_do_db

配置要同步的数据库，如果不配置默认为全部数据库。

binlog_do_db=db1binlog_do_db=db2

binlog_ignore_db

配置不需要同步的数据库。

binlog_ignore_db=db3

expire_logs_days

bin_log 日志保存天数，保存天数越久占用空间越大。

配置完成之后重启MySQL服务，登录MySQL之后输入以下两条命令查看配置是否生效

show variables like 'log_bin';show variables like 'server_id';

img

查看 master 状态

show master status;

image-20210827115154975

从数据库配置

打开从数据库的配置文件，并且添加如下配置：

server-id=108

image-20210827115935301

重启从数据库

systemctl restart mysqld

配置主从同步

在从数据库服务器中输入如下命令：

change master to master_host='192.168.20.6',master_user='root',master_password='{yourpassword}',master_log_file='mysql-bin.000001' ,master_log_pos=154;

其中 master_host表示主服务器 IP，master_user和master_password分别是主服务器的用户名和密码，master_log_file和master_log_pos在主服务器中通过show master status语句可以查到。

开启同步线程

start slave;

关闭同步线程

stop slave;

查看从数据库同步状态

**注意：**建议使用Navicat等工具查看，否则页面输出显示不大友好

show slave status;

显示结果：

image-20210827150142341

查看主数据库服务状态

在主数据库服务器中输入以下命令

show master status；

显示结果：

image-20210827150815883

使用 show processlist;语句可查看线程状态，可以看到第二个线程的 State 是 「Master has sent all binlog to slave; waiting for more updates」，说明同步线程一直在运行中。

使用show processlist;命令的结果：

image-20210827151016284

测试同步是否生效

  1. 在主数据库建立一个数据库。

    create database test;

    结果如下：

    image-20210827154146293

  2. 在主数据库中建立一张表。

    use test;create table test(	test varchar(255))

    结果如下：

    image-20210827154753693

  3. 利用图形化界面往test表中插入数据并查询test表。

    主数据库查询结果如下：

    image-20210827155110173

    从数据库查询结果如下：

    image-20210827155146874

更新以及删除都可以实现同步，说明MySQL主从数据库同步测试成功！

易错点总结

  • 远程连接配置

    远程连接这一块我遇到的问题是授权所有主机远程访问之后mysql服务并没有对3306/tcp进行监听，反倒是监听3306/tcp6，防火墙已经放行3306端口，重启了防火墙之后还是无法远程访问，mysql服务并没有监听3306/tcp，重启数据库服务之后仍然无法解决，最后重启服务器之后就能够正常远程访问了。。。

  • 数据库配置文件修改

    修改数据库配置文件时遇到一个参数是binlog_do_db，那时候看岔了，以为是以后要写的从数据库服务器名或者标识之类的，结果最后同步测试的时候发现同步没成功，逐一排查之后发现这个参数的值写的是主数据库中允许主从复制的数据库名称。读者根据自己的需求自行修改。

  • 从数据库修改连接主数据库连接信息前或者后忘记关闭或者开启同步线程

    从数据库修改连接主数据库连接信息前需要关闭同步线程，命令为：

    stop slave;

    从数据库修改连接主数据库连接信息后需要开启同步线程，命令为：

    start slave;

    当无法同步的时候注意查看主从数据库的进程信息，利用show processlist;命令进行查看。同时还需要查看主从数据库服务器的状态。

版权声明：本文为MZS2254399401原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。