MySQL实现主从复制
前言
windows 下的mysql主从复制
一、什么是主从复制?
将主数据库中的DDL和DML操作通过二进制日志(BINLOG)传输到从数据库上,然后将这些日志重新执行(重做);从而使得从数据库的数据与主数据库保持一致。
二、主从复制的作用
1、主数据库出现问题,可以切换到从数据库。
2、可以进行数据库层面的读写分离,
3、可以在从数据库上进行日常备份
三、复制过程
第一步:master在每个事务更新数据完成之前,将该操作记录串行地写入到binlog文件中。
第二步:salve开启一个I/O Thread,该线程在master打开一个普通连接,主要工作是binlog dump process。如果读取的进度已经跟上了master,就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。
第三步:SQL Thread会读取中继日志,并顺序执行该日志中的SQL事件,从而与主数据库中的数据保持一致。
四、主机操作
首先按照常规方式按照一个mysql实例:
将下载后的mysql压缩包解压如下图:
在根目录下创建my_3306.ini配置文件,配置内容如下:
#设置3306端口
port=3306
#设置mysql的安装目录
basedir=D:\\dev\\mysql-8.0.20-winx64
#设置mysql数据库的数据的存放目录
datadir=D:\\dev\\mysql-8.0.20-winx64\\Data_3306
#允许最大连接数
max_connections=200
#允许连接失败的次数。
max_connect_errors=10
#服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
#服务器 id
server-id=3306
#二进制文件存放路径log_3306文件夹需要手动创建
log-bin=D:\\dev\\mysql-8.0.20-winx64\\log_3306\\mysql-bin
#待同步的数据库(根据需要修改)
binlog-do-db=testdb
#不需要备份的数据库 (根据需要修改)
#binlog-ignore-db=mysql
binlog-ignore-db = information_schema
#二进制文件记录格式
binlog_format = STATEMENT
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4
在根目录下创建install_3306.bat自动安装执行脚本,配置内容如下
cd /d %~dp0\bin
mysqld --defaults-file="D:\dev\mysql-8.0.20-winx64\my_3306.ini" --initialize-insecure --user=mysql --console
mysqld install mysql_3306 --defaults-file="D:\dev\mysql-8.0.20-winx64\my_3306.ini"
net start mysql_3306
pause
在根目录下创建remove_3306.bat自动卸载执行脚本,配置内容如下:
cd /d %~dp0\bin
net stop mysql_3306
mysqld -remove mysql_3306
pause
执行安装操作:
配置mysql环境变量,右键以管理员身份运行install_3306.bat,如需卸载请执行remove_3306.bat
如上图显示安装成功并启动成功,默认密码为空
执行以下操作
-- 切换到mysql数据库
use mysql;
-- 修改root用户的可连接的host %代表所有
UPDATE USER SET HOST='%' WHERE USER='root'; -h IP地址
-- 查看是否修改成功
SELECT HOST,USER FROM USER;
-- 刷新MySQL的系统权限相关表
FLUSH PRIVILEGES;
-- 修改root用户密码
ALTER USER 'root'@'%' IDENTIFIED BY 'root';
-- 在主机创建一个用户
create user 'slave'@'%' identified by 'slave';
-- 给创建的而用户赋予复制的权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
-- 展示主机状态
show master status;
执行完查看主机状态之后不要再操作以免数据变动
五、从机的具体操作
安装第二个mysql实例:
复制install_3306.bat,my_3306.ini,remove_3306.bat三个文件在同一目录,并分别改名为install_3307.bat,my_3307.ini,remove_3307.bat,并将三个3307文件中的所有3306改为3307,修改完成后按照上面的执行安装操作
my_3307.ini配置
[mysqld]
#设置3307端口
port=3307
#设置mysql的安装目录
basedir=D:\\dev\\mysql-8.0.20-winx64
#设置mysql数据库的数据的存放目录
datadir=D:\\dev\\mysql-8.0.20-winx64\\Data_3307
#允许最大连接数
max_connections=200
#允许连接失败的次数。
max_connect_errors=10
#服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
#服务器 id
server-id=3307
#开启二进制日志(从服务器不是必须要开启二进制日志log_3307文件夹需手动创建)
log-bin=D:\\dev\\mysql-8.0.20-winx64\\log_3307\\mysql-bin
#执行同步操作的数据库(根据需要修改)需要和master数据库要同步的数据库名称保持一致
replicate-do-db=testdb
#复制的数据库
binlog-ignore-db=information_schema
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3307
default-character-set=utf8mb4
执行安装.bat
安装成功并启动成功,默认密码为空
执行以下操作
-- 切换到mysql数据库
use mysql;
-- 修改root用户的可连接的host %代表所有
UPDATE USER SET HOST='%' WHERE USER='root'; -h IP地址
-- 查看是否修改成功
SELECT HOST,USER FROM USER;
-- 刷新MySQL的系统权限相关表
FLUSH PRIVILEGES;
-- 修改root用户密码
ALTER USER 'root'@'%' IDENTIFIED BY 'root';
-- 与主机连接
change master to master_host='主机ip地址',
master_port=主机数据库端口号,
master_user='主机分配的复制用户',
master_password='主机分配的复制用户的密码',
master_log_file='主机执行show master status 之后得到的值',
master_log_pos='主机执行show master status 之后得到的值';
-- 开启复制
start slave;
-- 查看状态
show slave status\G;
-- 当结果中的这两个值为yes则一主一从搭建完成
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试在主库创建表,插入数据;从库查看表,查看数据,数据相同则大功告成
与redis主从复制不同
mysql从接入点开始复制
redis从头开始复制
六、关闭主从复制
七、MySql Host is blocked because of many connection errors 解决方法
八、MySQL8.0中binlog的深入讲解