MySQL 主从配置

• 主从数据库原理
  • 主库 master
  • 从库 slave
• 一、云服务器配置
  • 1.1 腾讯云
  • 1.2 阿里云
• 二、MySQL 主机 master 配置
  • 2.0 创建主机 MySQL 数据库
  • 2.1 修改 MySQL 配置文件
  • 2.2 创建一个 MySQL 数据库用户，提供给从机 slave 访问
  • 2.3 给刚才创建的用户授权
  • 2.4 赋予从机复制主机数据的权力
  • 2.5 重启主机 MySQL 服务
  • 2.6 登录主机 MySQL，检查主机 master 的状态：
• 三、MySQL 从机 slave 配置
  • 3.1 修改从机 MySQL 配置文件
  • 3.2 重启从机 MySQL 服务
  • 3.3 登录从机 MySQL，配置与主服务器 master 的连接
  • 3.4 在从服务器上创建同名从数据库和同名用户
  • 3.5 重启 从服务器（slave）上的 MySQL 服务
  • 3.6 登录从机 MySQL，启用 slave 数据同步

主从数据库原理

主库 master

当 master 每做一次 write 操作，就会将操作的命令记录到 master 的 Binary-log（bin-log）日志中。

从库 slave

slave 中默认会有两条线程

IO Thread：读取 master 中的 bin-log 内容，写入到 slave 中的 relay-log。

SQL Thread：slave 读取 relay-log，将操作写入到 slave 数据库。

一、云服务器配置

1.1 腾讯云

（1）在腾讯云控制台上，设置主数据库外网访问权限

（2）修改 MySQL配置文件，设置 3306 端口外部访问权限

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

将 bind_address 的值由 '127.0.0.1' 改成你腾讯云内网IP地址。

重启 MySQL 服务器，然后输入 netstat -anpt|grep 3306 检查3306端口是否显示腾讯云的内网IP。

1.2 阿里云

（1）修改主机 MySQL 配置文件：

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

将 bind_address 一行注释掉。

（2）然后在阿里云 ECS 控制台上，为主机添加网络安全组规则：

入方向
协议类型选择 MySQL(3306)
授权类型改为地址段访问
授权对象输入：0.0.0.0/0

从机可以不用对外开放端口 3306。

二、MySQL 主机 master 配置

2.0 创建主机 MySQL 数据库

创建主机数据库：mytest

create database mytest default character set utf8;

2.1 修改 MySQL 配置文件

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

在 [mysqld] 模块下添加如下内容（默认是注释的，解除注释即可）：

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = mytest # 需要做主从分离，主服务器 MySQL 的数据库名
binlog_ignore_db        = mysql # 在主服务器上需要忽略的 MySQL 数据库，避免从服务器对主服务器的安全性影响

数据表名忽略大小写，在 [mysqld] 模块下添加如下内容：

lower_case_table_names=1

修改完之后，记得要重启 MySQL 服务：

/etc/init.d/mysql restart;

2.2 创建一个 MySQL 数据库用户，提供给从机 slave 访问

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 

说明:

• username - 你将创建的用户名

• host - 指定该用户在哪个主机上可以登陆。如果是本地用户可用 localhost， 如果想让该用户可以从任意远程主机登陆，可以使用通配符 % 。

• password - 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器.。

举例：

CREATE USER 'mytest'@'%' IDENTIFIED BY 'mytest123!@#';

2.3 给刚才创建的用户授权

grant all privileges on *.* to 'mytest'@'%'identified by 'mytest123!@#' with grant option;
flush privileges;  -- 更新权限，使之生效

• all privileges 表示授权全部的权限。如果单独指定权限，可以替换成：insert、select、update、delete 等等；

• *.* 表示这些权限是对所有的 数据库.表 等生效。如果要单独指定，可以按照此格式 databasename.tablename ；

• with grant option 表示创建的这个用户还可以为其他用户继续赋予这些权限。

2.4 赋予从机复制主机数据的权力

从机可以通过主机创建的这个用户连接上主机数据库，然后复制主机的数据到从机数据库。

GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'%' IDENTIFIED BY 'mytest123!@#';

2.5 重启主机 MySQL 服务

sudo /etc/init.d/mysql restart

2.6 登录主机 MySQL，检查主机 master 的状态：

show master status;

会显示如下内容（例子）：

   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      154 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

注意记录 File 和 Position 的值，在从机 slave 配置时会用到该属性。

注：执行完此步骤后不要再操作主服务器 MySQL，防止主服务器 master 状态值变化。如果对主机数据库做了增删改操作，会导致 Position 值发生变化。

如果是在主节点开启主从配置之前就已经创建的表，是不会同步到从节点中。

三、MySQL 从机 slave 配置

3.1 修改从机 MySQL 配置文件

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

在 [mysqld] 模块中添加如下内容：

server-id = 2
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index

主机中是 server-id = 1，而从机与主机不一样，注意区分。

3.2 重启从机 MySQL 服务

sudo /etc/init.d/mysql restart

3.3 登录从机 MySQL，配置与主服务器 master 的连接

使用 root 帐号登录到从机 MySQL，执行以下命令，将从机与主机关联：

-- 例子
change master to master_host='120.77.219.39',master_port=3306,master_user='mytest',master_password='mytest123!@#',master_log_file='mysql-bin.000001',master_log_pos=154;


change master to master_host='101.201.66.147',master_port=3306,master_user='mytest',master_password='mytest123!@#',master_log_file='mysql-bin.000005',master_log_pos=154;

说明：

• master_host 对应主服务器的外网IP地址 120.77.219.39
• master_port 对应主服务器的端口（3306）
• master_log_file 对应 show master status 显示的 File 列：mysql-bin.000001
• master_log_pos 对应前面记录主机中显示的 Position 的值，否则有可能出现同步失败。

3.4 在从服务器上创建同名从数据库和同名用户

该数据库名称需要与主服务器上 MySQL 配置文件中定义的 binlog_do_db = mytest 名称一致：

create database mytest default character set utf8;

创建与主数据库同名的用户（非必需）：

CREATE USER 'mytest'@'%' IDENTIFIED BY 'mytest123!@#'; 
grant all privileges on *.* to 'mytest'@'%'identified by 'mytest123!@#' with grant option;
flush privileges;  -- 更新权限，使之生效

注意：“创建同名用户” 这一步是非必需的，但为了方便读写，建议对 master 或 slave 数据库访问采用同样的一套用户名密码。

3.5 重启 从服务器（slave）上的 MySQL 服务

/etc/init.d/mysql restart

3.6 登录从机 MySQL，启用 slave 数据同步

登录 MySQL 后，执行如下命令：

start slave;

如果出现错误：

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository

说明 relay log 即 mysql.slave_relay_log_info 表中保留了以前的主从复制信息，导致新从库启动时无法找到对应文件，那么我们只需要清理掉该表中的记录就可以了。注意，不要手动删该表数据，MySQL 已经提供了解决放哪广发，输入命令：

reset slave;

执行成功后，再来执行一遍启动 slave 同步：

start slave;

补充几个个与 slave 相关的命令：

1. 停止当前在运行的 slave

stop slave;

2. 显示当前 slave 的状态

show slave status;

关于 relay log 的问题，我参考了这篇文章：
[Relay log 导致主从复制启动失败]:

关于 MySQL 采用 Amoeba 实现读写分离，可以参考 青衫lys 的这篇文章，写的很详细：