mysql主从复制优化_MySQL性能优化：实现主从复制

1 主从复制的介绍

概念：使用两台或两台以上的数据库服务器，分别设置主服务器(master)和从服务器(slave)，对主服务器的任何操作都会同步到从服务器上。

主要作用：

(1)当主服务器出现问题时，可以切换到从服务器，避免数据丢失。

(2)可以在从服务器上查询数据，降低主服务器压力。

2.实现原理

mysql会将所有修改过数据库的sql语句记录在二进制bin-log日志中。主从复制实际是将多台服务器bin日志的开启，然后主服务器(master)会把执行过的sql语句记录到bin日志中，之后从服务器(slave)读取该日志，在从服务器再把bin日志中记录的sql语句同样的执行一遍。这样从服务器(slave)上的数据就和主服务器(master)同步了。

3 实现步骤：

两台服务器：

主服务器：ip地址：192.168.2.17，系统：Centos，配置文件：my.cnf

从服务器：ip地址：192.168.2.4，系统：Windows，配置文件：my.ini

注意：因为本人是实验用，所以主服务器是虚拟机上配置的linux，从服务器使用的是windows，可以根据自身需求选择linux，配置方式大体一致。

3.1 在主服务器上修改 my.cnf 配置文件

vim /etc/my.cnf

server-id=1#这是数据库ID,此ID是唯一的，主库默认为1，其他从库以此ID进行递增，ID值不能重复，否则会同步出错；

log-bin=mysql-bin#用来设置二进制文件的存储位置和文件名称，如果不指定存储位置，则和数据库文件存储在同一位置

注意：在my.cnf配置文件里面，配置的区域在[mysqld]与[mysql]之间配置。

3.2 修改完成mysql的配置后，要重启mysql服务使之生效

service mysql restart

3.3 授权一个账号，让从服务器通过该账号读取log-bin日志里面的内容

grant replication slave on *.* to 'lily'@'%' identified by '123456'

3.4 赋予从库权限账号，允许用户在主库上读取日志，也就是Slave机器读取File权限

grant FILE on *.* to 'lily'@'%' identified by '123456';

3.5 查看最新的log-bin日志，记录主服务器里面的最新的二进制的名称和pos位置

show master status

注意：到这一步就禁止对主服务器执行增删改的操作，避免产生新的日志，从服务器配置成功再对主服务器进行操作。

4 配置从服务器

4.1 修改从服务器的数据库配置文件

注意：这里从服务器使用的是windows操作系统，mysql的配置文件是my.ini。server-id 该值不能和主服务器的相同

修改完成mysql的配置后，要重启mysql服务使之生效

net stop mysql

4.2 停止从服务器

stop slave

4.3 开始配置

在mysql命令行中输入如下指令：

CHANGE MASTER TO

MASTER_HOST='192.168.2.17',#主服务器的ip地址

MASTER_USER='lily',#主服务器授权用户的账号名

MASTER_PASSWORD='123456',#主服务器授权用户的密码

MASTER_LOG_FILE='mysql-bin.000002',#上文中记录的二进制日志文件的名称

MASTER_LOG_POS=897#上文中记录的pos位置

4.4 启动从服务器

start slave

4.4 验证配置是否成功

show slave status\G;

若下面红框处两个地方皆为yes，则配置成功！

5 测试主从复制

5.1 在主服务器创建一个数据库，并添加一张表，插入数据

create database demo_lily;

use demo_lily;

insert into user values(1, 'lily'), (2, 'cily');

5.2 在从服务器上面查看是否有插入的数据记录

6 常见问题

(1)执行授权账户命令grant replication slave on . to 'lily'@'%' identified by '123456' 的时候出现如下提示：

这时候只需要执行如下命令，刷新一下权限表：

flush privileges

之后再次执行授权命令即可。

(2)出现 Slave_IO_Running：Connecting 的情况：

造成这个提示的原因比较多，而本人是因为linux防火墙开启，直接拦截了3306端口，导致连接不上。这时候可以设置主服务器防火墙放行3306端口：

sudo firewall-cmd --list-all#查看防火墙端口开放情况

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

或者直接关闭防火墙：

systemctl stop firewalld.service