mysql优化 - 主从复制 - 《数据库教程》

主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）.

MySQL中复制的优点包括：

横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。
数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。
分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。
远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。

实例 :
配置一主一从两台mysql服务

| | 后台运行 | port | # slaveof

| | —- | —- | —- | —- | | Master | yes | 3306 |

| | Slaver | yes | 3307 | 127.0.0.1 3306 |

除了phpstudy_pro集成的mysql以外,我们还要单独再安装一个mysql服务

将mysql复制过来之后,需要创建数据文件夹data和my.ini配置项

[mysqld]
port = 3307
basedir=D:/apps/mysql
datadir=D:/apps/mysql/data
max_connections=200
character-set-server=utf8
default-storage-engine=INNODB
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 主从配置
server-id=2
[mysql]
default-character-set=utf8

端口设置为3307 , server-id 设置为 2 和主服务器区分开

使用管理员身份运行cmd
安装mysql服务命名为mysqld,并初始化
mysqld -install mysqld
mysqld —initialize-insecure —user=mysql
win+r 输入services.msc 打开系统服务,找到刚刚创建的mysqld

点击启动mysqld,实现mysql服务双开

此时打开Navicat,连接第二个3307端口的mysql服务,默认密码为空

此时新mysql中只有系统自带的库,创建一个test22的库和主数据库同步

此时我们开始配置主数据库,打开my.ini文件

添加以下配置项

# 主从配置
server-id=1
log-bin=mysql-bin
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
binlog-do-db=test22

配置server-id为1 和从数据库区分开来
log-bin=mysql-bin 设置同步方式为log-bin日志方式
replicate-ignore-db 为不需要同步的库
binlog-do-db为需要同步的库,同时其他库不同步,这里我们同步test22这个库
配置完毕后重启主mysql服务

打开查询,输入show master status 查看主服务器状态

此步骤得到的file和position下一个步骤会使用到

此时打开3307服务器的查询 , 配置主从同步

change master to 
master_host='127.0.0.1',
master_user='root',
master_password='root',
master_log_file='mysql-bin.000001',
master_log_pos=1580;

其中master_log_file 为上一步得到的file
master_log_pos为上一步得到的position
配置完毕后开启主从同步 : start slave;
开启完毕之后查看开启状态 : show slave status;

可以看到读写和sql执行全部同步,为了防止从服务器出现同步失败情况
设置从服务器只读状态 :
set global read_only=1;

现在主从已经配置完毕,体验一下
主服务器新建一张表 ,名字设置为new, 添加一条数据

此时打开从服务器,发现表和数据已经出现

如果因为操作原因导致主从同步暂时失效,可以使用以下命令重置同步

stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
SHOW SLAVE STATUS;