单机实现 MySQL 主从同步读写分离

Question

数据库的主从同步读写分离一般都是在多台服务器上部署，但是咱没那么多服务器可用，但是应用的瓶颈主要在数据库上，那么我们就可以在单台服务器上配置多个从数据库，并行读取数据提高应用响应速度。

复制出第二个 MySQL 数据库

1，将你的 MySQL 文件夹直接拷贝到另一个目录。如我的拷贝到了 D:\work\mysql\MySQL5

2，修改 my.ini 文件，修改以下参数：

两个位置需要重新设置端口：

port=3307

路径配置：

basedir="D:/work/mysql/MySQL5"
datadir="D:/work/mysql/MySQL5/data"

3，将 MySQL 配置到环境变量

4，在 MySQL5 路径下 shift+右击打开cmd，输入如下：

mysqld --install mysql_slave --defaults-file="D:\work\mysql\MySQL5\my.ini"

5，右击计算机，去“服务”里打开mysql_slave服务，如果报1067错误，查看属性的路径是否正确

6，属性的路径不正确则去注册表修改，home+r，输入regedit，打开注册表，根据路径：

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\...

修改路径，可以仿照原先的mysql路径写法

7，再次去“服务”里启动即可。

注：Win 系统的如果是 5.7 以上的 MySQL 版本，data 文件夹默认在 C:\ProgramData\MySQL 下，其中 ProgramData 是隐藏文件夹。

修改 MySQL 配置

1，主库的 my.ini 末尾添加如下：

#主为1，如果需要增加Slave库则，此id往后顺延； 
server-id = 1
#要同步的数据库 
binlog-do-db=community_ms
#要生成的二进制日记文件名称 
log-bin=mysql-bin

第二行配置可以省略，则所有库同步，这里笔者只指定了需要同步的库

2，从库 my.ini 末尾添加如下：

#主为1，如果需要增加Slave库则，此id往后顺延； 
server-id = 10
#要同步的数据库 
replicate-do-db=community_ms
#要生成的二进制日记文件名称 
log-bin=mysql-bin

注：这里主要是标记从库的服务 ID，不能和主库一样，添加时请先查询全部内容，看配置文件是否默认有配置 server-id 有则直接修改，没有则添加。

3，打开主库添加用户，并赋予对应权限：

create user 'forslave'@'127.0.0.1' identified by '123456';
GRANT REPLICATION SLAVE,super,reload,file ON *.* TO 'forslave'@'127.0.0.1';
//或
grant replication slave,super,reload,file on *.* to forslave@127.0.0.1 identified by '123456';

注：授权格式：grant 权限 on 数据库 .* to 用户名@登录主机 identified by "密码";

4，在主库运行：

show master status;

记录 File 和 Position 的值。

5，把需要同步的数据库，从主库备份到从库。

6，打开从库，运行如下：

change master to master_host='127.0.0.1',
master_port=3306,
master_user='forslave',
master_password='123456',
master_log_file='mysql-bin.xxx',
master_log_pos=xxx;

注：file 和 pos 即上面记录的值，若提示 salve 未停止，执行如下：

stop slave;

7，执行完上诉的 change 后启动 slave：

start slave;

8，查看是否配置成功：

show slave status;

若如下两个参数都为 YES 则表示两个库从属关系配置完成：

注：若后一个为 NO，一般情况是 slave 没有启动。若前一个为 NO 一般是 my.ini 配置出错，或者执行以上命令时，没有按笔者指定的库。如 change 在主库执行，就会报 ID 重复。若修改了 my.ini 请重启服务，使其生效。

配置成功后可以对主库执行 insert 操作，查看插入后否从库也一样新增了数据，但是不能对从库进行任何写入操作，不然主从同步将失效。

9，其他常用命令如下：

• 查看服务 ID：show variables like 'server_id';
• 临时设置服务 ID：set global server_id=2;
• 查看 MySQL 库用户：SELECT User, Host, Password FROM mysql.user;