[MySQL] 记录一次实践 MySQL 主从数据库

2,362 阅读7分钟

前言

之前一直在研究 MySQL 复制问题,所以最近就想动动手将 MySQL 的主从服务器搭一下。MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。

准备工作

  1. MySQL 5.7
  2. Linux
  3. 准备两台服务器两端口(如果是单机版本准备两端口)。我这里是单机版,两端口是 33063307

实现效果

双机或单机主从复制,一主一从。

步骤

下文我会称主服务器为 Master,从服务器为 Slave

下载 MySQL 5.7

  1. 通过 下载地址 下载 MySQL 的安装包
  2. 解压安装包 tail zvxf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
  3. 复制两份,命名为 MasterSlave
cp -r 解压后的MySQL文件目录 ./master

cp -r 解压后的MySQL文件目录 ./slave

创建数据目录

然后我们创建数据目录。目前我自己创建的目录是在 /var/lib/mysql/ 路径下的,所以我们在此路径创建两个名为 MasterSlave 的文件夹。

目前文件夹是为空的,所以我们需要进行初始化数据目录。所以我们在 Master 和 Slave 的两个安装目录下以下命令

## Master
./bin/mysqld --initialize --user=root --datadir=/var/lib/mysql/master/

如果成功执行的情况之下,你可以看到一串输出,里面包括一句话,上面说明了临时密码。这个密码用于临时登陆 MySQL,然后再修改密码的。

2019-11-12T08:43:49.391486Z 1 [Note] A temporary password is generated for root@localhost: %Cx0w5aWACrf

接下来执行的是 Slave 的数据目录初始化

## Slave
./bin/mysqld --initialize --user=root --datadir=/var/lib/mysql/slave/

初始化完毕之后,我们可以走到下一步了

创建配置文件 my.cnf 并编辑

有人可能不知道 my.cnf 文件是什么。其实 my.cnf 就是一个配置文件,主要配置 MySQL 的服务端,客户端等配置信息。

所以我们需要分别为 Master(主) 和 Slave(从) 复制两份放进各自 MySQL 文件夹,这个是为了区别开各个 MySQL 的配置文件。然后我们编辑 Mastermy.cnf 文件,填入以下内容(需要特别注意的是我们应该核对好主从数据库的数据目录)

my.cnf

[mysqld]
server-id=1
log-bin=binlog_name
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-do-db=test
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
user=root
port=3307
datadir=/var/lib/mysql/master
socket=/var/lib/mysql/master/mysql.sock

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[client]
default-character-set=utf8
port=3306
socket=/var/lib/mysql/master/mysql.sock

[mysql]
default-character-set=utf8
socket=/var/lib/mysql/master/mysql.sock

然后编辑 Slavemy.cnf

[mysqld]
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
user=root
port=3307
datadir=/var/lib/mysql/slave
socket=/var/lib/mysql/slave/mysql.sock

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[client]
default-character-set=utf8
port=3306
socket=/var/lib/mysql/slave/mysql.sock

[mysql]
default-character-set=utf8
socket=/var/lib/mysql/slave/mysql.sock

集中说一下上面配置文件是什么意思

  1. server-id 是作为 MySQL 的唯一标识
  2. binlog-ignore-db 是指二进制日志会忽略掉数据库
  3. replicate-do-db 发生改变会被复制的数据库
  4. replicate-ignore-db 发生改变被忽略的数据库
  5. socket socket 文件,用于通信
  6. port MySQL 端口
  7. datadir 数据目录地址
  8. log-bin 开启了二进制日志

启动 MySQL

因为我们是单机版主从数据库搭建,所以我们需要指定 my.cnf 来启动 MySQL。下面是启动的命令行,++我们需要分别在 Master 和 Slave 安装目录下执行这句命令++

Master

./bin/mysqld --defaults-file=my.cnf -uroot

Slave

./bin/mysqld --defaults-file=my.cnf -uroot

配置 MySQL 的主从信息

连接 Master 服务端

❤️ 还记得上面的临时密码吗?现在服务端已经启动好了,现在我们需要拿来登陆服务端。

./bin/mysqld --defaults-file=my.cnf -uroot -p临时密码

登陆成功后,我们首要的事情是修改密码,因为在临时登陆情况下服务端不允许进行任何操作。

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

首先我们通过命令行或者客户端连接上 Master 的 MySQL 服务器,然后查看是否开启了 binlog 二进制日志。

show master status;

如果开启状态的话会有以下结果

+--------------------+----------+--------------+--------------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+--------------------+----------+--------------+--------------------------+-------------------+
| binlog_name.000005 |      154 |              | information_schema,mysql |                   |
+--------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

关于上面的结果我解释一下

  • File 指的是当前二进制日志记录的文件名
  • Position 指的是二进制日志记录的位置
  • Binlog_Do_DB 是指需要被复制的数据库
  • Binlog_Ignore_DB 是指被忽略复制的数据库

现在我们记住 FilePosition,下面在连接 Slave 的时候我们要配置 Master 和 Slave 的信息对称问题。

❤️ 然后我们创建一个 test 数据库,再创建一个 sys_account 表,用于测试主从信息同步的表。

CREATE TABLE `test`.`sys_account`  (
  `id` int(11) NOT NULL,
  `username` varchar(255) NULL,
  PRIMARY KEY (`id`)
);

❤️ 最后我们需要创建一个账号,因为主从数据库的实质++是 Slave 通过特定的账号登陆 Master 获取二进制日志文件,然后通过记录同步到 Slave 数据库当中++。

// 创建一个用户名为 slave_user,密码是 slave_pass ,服务器地址为 slave_host 的账号,
create user 'slave_user'@'slave_host' identified by 'slave_pass';

// 然后授予所有数据库的复制权限给新建的账号
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_host';

至此 Master 的所有操作已经结束了。

连接 Slave 服务端

❤️ 同样,现在我们需要临时密码登陆服务端,然后修改密码。
❤️ 因为 Slave 只是拿来单纯的同步来自于 Master 的信息,数据库里边是空的,所以我们先要对其和 Master 进行一个数据结构和数据的同步,保证在开始的时候,它们是处于信息对称的状态。我们可以通过 navicat 或者命令行进行同步,这个我就不在这里展开。

❤️ 现在我们要设置 Slave 数据库的主服务器,所以你们需要根据实际情况填写命令的信息

CHANGE MASTER TO MASTER_HOST='127.0.0.1',  MASTER_USER='slave_user', MASTER_PORT=3306,  MASTER_PASSWORD='slave_pass',  MASTER_LOG_FILE='binlog_name.000005', 
MASTER_LOG_POS=154

参数说明

  • MASTER_HOST 指的是主数据库的 IP 地址
  • MASTER_USER 指的是刚刚在上面 Master 为 Slave 创建的账号名
  • MASTER_PORT 指的是主数据库监听的端口
  • MASTER_PASSWORD 指的是刚刚在上面 Master 为 Slave 创建的账号密码
  • MASTER_LOG_FILE 指的是主服务器当前二进制的文件名
  • MASTER_LOG_POS 指的是主服务器当前二进制的记录的位置

常见问题

无法登陆 MySQL

如果新装的 MySQL 无法登陆,例如提示以下信息

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

那么我们先修改 my.cnf,加上下面这句话

[mysqld]
skip-grant-tables

然后我们可以直接无账号密码登陆 MySQL 服务端,然后我们通过 SQL 命令行修改命令

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');

修改完后我们就可以将上面添加的 skip-grant-tables 注释掉,重新使用新密码登陆。

远程登陆问题

有可能你们在远程服务器进行搭建的,所以新建的 MySQL 需要开启远程连接的选项

# 使所有远程用户都可以使用 root 账号 root 密码登陆 mysql
grant all privileges on *.* to 'root'@'%' identified by 'root' with grant option;
flush privileges;

关注我

有兴趣的可以关注我,我会持续发布关于后端、数据库、消息队列中间件、高并发等知识!