超详细 linux（ubuntu）手把手教你搭建 MySQL 8.0 主从库

目录

前言

（1）环境配置

（2）附上ubuntu 安装 mysql 的命令

 （3）主库 数据库配置

（a）修改主库环境配置

（b）修改主库远程操作配置

 （c）创建 从库 账号密码

（d）查看账号授权情况

（e）查看主库状态 

（f）创建账号可能的错误列表

（4）从库 数据库配置

（a）修改从库环境配置

（b）创建 mysql 账号密码

（c）查看账号授权情况

（d）从库启动复制命令

1 创建复制命令配置

2 复制命令

 3 查看复制状态

（e）从库复制错误列表

1 主库创建的账号没有办法登录

2 主库的操作从库没有办法复制

---

前言

        在开发项目时，总是会遇到设计数据库，而怎么设计高并发数据库，不仅仅是要提高单个数据库的配置，也会有很多数据库的设计，如主从库机制，分布式数据库等等。而这一篇则是主要讲解主从库的搭建。

        主从库，主库（读写），从库（读），分离读写操作，降低主库的压力，也进一步保护了主库的数据的纯净度，从而达到提高数据库查询效率。

（1）环境配置

（a）linux：ubuntu 22.04 LTS 64位；

（b）mysql：mysql 8.0.31

（2）附上ubuntu 安装 mysql 的命令

apt-get install mysql-server // 安装数据库服务端

apt-get install mysql-client  // 安装数据库客户端

 （3）主库 数据库配置

（a）修改主库环境配置

进入 mysql 环境配置 mysql.cnf

vim /etc/mysql/mysql.conf.d/mysql.cnf

# 添加以下环境配置
[mysqld]

#[必须]主服务器唯一ID
server-id=1

#[必须]启用二进制日志,无后缀的文件名。也可以是本地的路径/log/bin-log
log-bin=bin-log

#[可选]设置需要复制的数据库名称,默认全部记录。
binlog-do-db=master_db

#[可选]设置不要复制的数据库
binlog-ignore-db=test

#[可选] 0(默认)表示读写(主库),1表示只读(从库)
read-only=0

#设置日志文件保留的时长,单位是秒
binlog_expire_logs_seconds=6000

#控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M

#[可选]设置binlog格式(STATEMENT是基于sql语句的复制，ROW是基于行的复制，MIXED是混合模式)
binlog_format=STATEMENT

（b）修改主库远程操作配置

进入数据库环境配置 mysqld.cnf

vim /etc/mysql/mysql.conf.d/mysqld.cnf

bind-address = 127.0.0.1    // 这里表示只能本机操作

bind-address = 0.0.0.0        // 这里表示所有IP地址都可以操作

 环境配置修改好了之后，使用以下命令重启数据库服务端

systemctl restart mysql.service

 （c）创建 从库 账号密码

（1）进入 mysql 命令行界面

mysql -u账号 -p密码

（2）创建 从库 操作账号密码 slave2 

create user 'slave2'@'%' identified by '111111';

flush privileges;

PS: host 为 'localhost' 和 '127.0.0.1' 时，只允许本机登录，而 host 为 '%' 或 'IP地址' 则允许远程账号登录

（3）修改 host 命令

use mysql

update user set host='localhost' where user = 'slave2';

flush privileges;

（4）给 slave1 账号 授予权限

grant replication slave on master_db.* to 'slave2'@'%';

flush privileges;

（d）查看账号授权情况

 show grants for 'slave2'@'%';

slave2 授权情况

（e）查看主库状态 

show master status;

主库状态

记住记录文件名 和 binlog日志偏移量 ，这在后面的从库创建复制命令中是必要的参数！

（f）创建账号可能的错误列表

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

在mysql 8之后，这里可能会授权失败，并提示错误：

1. 拼写错误 '... near identified by ...'，所以将 identified by '111111' 取消就可以了
2. 无权创建 '... no allow to create ...'，这是因为创建账号之后，没有执行 flush privileges 导致的。

到这里之后就可以退出登录账号试试看了

（4）从库 数据库配置

（a）修改从库环境配置

进入 mysql 环境配置文件中

vim /etc/mysql/mysql.conf.d/mysql.cnf

# 添加以下配置
[mysqld]
#[必须]从服务器唯一ID
server-id=2

#[可选]启用中继日志
relay-log=mysql-relay

#[可选] 0(默认)表示读写(主机),1表示只读(从机)
read-only=1

#[可选] 选择复制的数据库，不填则默认复制主库授权可以复制的数据库
replicate-do-db=master_db

#[可选] 表示不随着数据库启动而启动复制
skip-slave-start

环境配置修改好了之后，使用以下命令重启数据库服务端

systemctl restart mysql.service

（b）创建 mysql 账号密码

 （1）进入 mysql 命令行界面

mysql -u账号 -p密码

（2）创建操作账号密码 rdonly

create user 'rdonly'@'localhost' identified by '111111';

flush privileges;

（3）给 rdonly 账号 授予权限，只授予 select 权限

grant select on master_db.* to 'rdonly'@'%';

flush privileges;

PS：只授予 select 权限，可以避免从库数据被修改而导致主从库数据不一致，进而导致下一次复制失败

（c）查看账号授权情况

（d）从库启动复制命令

1 创建复制命令配置

change master to 
master_host = '主库IP地址',
master_port = 主库端口,
master_user = 'slave2',
master_password = '111111',
master_log_file = 'bin-log.000005',
master_log_pos = 997;

2 复制命令

start slave    // 启动从库复制命令

stop slave    // 停止从库复制命令

reset slave   // 重置从库复制命令

 3 查看复制状态

show slave status\G;

slave status

 上图中，如果已经启动复制命令成功了，那么 slave_io_running 和 slave_sql_running 都是 yes。

（e）从库复制错误列表

显示错误的字段

 如果复制没有生效，具体可以看上图这几个参数，笔者也遇到几个问题：

1 主库创建的账号没有办法登录

1. 检查主库的服务器安全组端口号是否开放；
2. 检查主库的服务器IP地址是否限制了；
3. 检查主库的环境配置中 是否限制了只能本地操作，具体看 （3）-（b）；
4. 检查主库防火墙是否限制了端口号；
5. 检查主库的账号权限是否有问题，replication 权限才能复制到从库中；
6. 检查上面的步骤是不是操作失误了。

2 主库的操作从库没有办法复制

1. 主库的库、表和数据记录都已经存在了，但是从库没有相应的库、表和数据记录原来是没有的，所以复制失败。

主从库机制，还有半同步、增强式半同步等模式可以提高复制效率，且进一步提高主库的操作效率。感兴趣的可以自行搜索，后续笔者进一步搭建的时候会再更新。