数据库:mysql的主从复制实战
目录
一、主从复制
1、主从复制作用
2、主从复制原理
3、三个重要线程
4、主从复制实战实现
5、主从复制实战实现场景2
6、主从复制错误解决方法
二、级联主从复制
1、级联复制原理
2、级联复制实战实现
一、主从复制
1、主从复制作用
主从复制:主设备通过二进制日志传输到从设备,从设备通过二进制日志和主同步数据。
作用:负载均衡读操作,备份(实时备份,不能替换手动的备份),高可用和故障切换,数据分布,Mysql升级。
2、主从复制原理
①从数据库处理用户读操作,主数据库处理用户写操作。
②用户写入数据到主数据库时,主数据库更新并写入binlog二进制日志中。
③主数据库开启dump线程一边读取binlog日志一边通过网络将日志传输给从数据库。
④从数据库通过io线程接收binlog日志并保存为中继日志(即binlog日志只是换了名称)。
⑤从数据库开启sql线程将中继日志写入从数据库主从数据库复制完成。
3、三个重要线程
dump线程:由主数据库开启,用于读取主的二进制日志并传输给从数据库 。
io线程:由从数据库开启,用于接收二进制日志并保存为中继日志。
sql线程:由从数据库开启,用于将中继日志写入备数据库中完成主从复制。
4、主从复制实战实现
①环境准备:2台安装了相同版本mysql数据库的服务器
主服务器(pc1):192.168.30.11
从服务器(pc2):192.168.30.12
②首先关闭2台服务器的firewalld以及selinux,重置mysql密码为Admin@123
pc1、pc2执行:
systemctl stop firewalld.service
setenforce 0
systemctl start mysqld
#开启数据库
grep password /var/log/mysqld.log
#在错误日志中找到mysql初始密码,如下图
mysql -uroot -p'初始密码'
#登录数据库
alter user root@'localhost' identified by 'Admin@123';
quit;
#修改root默认密码并退出
③主节点配置:
vim /etc/my.cnf
[mysqld]下一行添加内容:
server_id=11
#serverid建议配置为ip地址最后8位
log-bin=/data/mysql/mysql-bin
#指定二进制日志文件保存位置为/data/mysql/,保存后退出
mkdir -p /data/mysql
#创建保存二进制文件的目录
chown mysql:mysql -R /data/
#目录的属主属组都给到mysql用户
systemctl restart mysqld
#重启数据库
mysql -uroot -p'Admin@123'
#登录数据库
grant replication slave on *.* to test@'192.168.30.%' identified by 'Admin@123';
#创建复制用户只要是192.168.30开头的地址都可使用用户名test密码Admimin@123登录数据库。
show master status;
#查看主从复制从那个二进制日志的那个初始节点开始,从服务器配置需要依赖此条查看结果,如下图
④从节点配置:
vim /etc/my.cnf
[mysqld]下一行添加内容:
server_id=12
#serverid建议配置为ip地址最后8位
log-bin=/data/mysql/mysql-bin
#指定二进制日志文件保存位置为/data/mysql/
relay-log=relay-log-bin#开启中继日志
relay-log-index=slave-relay-bin.index#添加,定义中继日志文件的位置和名称,保存后退出
mkdir -p /data/mysql
#创建保存二进制文件的目录
chown mysql:mysql -R /data/
#目录的属主属组都给到mysql用户
systemctl restart mysqld
#重启数据库
mysql -uroot -p'Admin@123'
#登录数据库
help change master to
#可以查看配置帮助复制出来修改,或直接复制下面模板
CHANGE MASTER TO
MASTER_HOST='192.168.30.11',
#主服务器主机ip
MASTER_USER='test',
#主从复制登录用户名
MASTER_PASSWORD='Admin@123',
#主从复制登录用户名的密码
MASTER_PORT=3306,
#主服务器数据库端口
MASTER_LOG_FILE='mysql-bin.000001',
#从那个二进制日志开始复制,show master status;看到的结果
MASTER_LOG_POS=448;
#从二进制日志的那个at节点开始复制,show master status;看到的结果
start slave;
#开启主从复制
show slave status\G
#查看主从复制状态,找到Slave_IO_Running: Yes Slave_SQL_Running: Yes 此2个参数为yes则主从复制开启完成。
⑤实验结果验证:
主数据库:
create databases test1;
#创建一个test1测试库
备数据库:
show databases;
#查看是否同步到test1库
5、主从复制实战实现场景2
①实现背景:主从复制开始前主数据库已有一部分数据,现在要做主从复制。
②实验环境 :
环境准备:2台安装了相同版本mysql数据库的服务器
主服务器(pc1):192.168.30.11
从服务器(pc2):192.168.30.12
②首先关闭2台服务器的firewalld以及selinux,重置mysql密码为Admin@123。
pc1、pc2执行:
systemctl stop firewalld.service
setenforce 0
systemctl start mysqld
#开启数据库
grep password /var/log/mysqld.log
#在错误日志中找到mysql初始密码,如下图
mysql -uroot -p'初始密码'
#登录数据库
alter user root@'localhost' identified by 'Admin@123';
quit;
#修改root默认密码并退出
③主节点配置:
pc1:
mysql -uroot -pAdmin@123
#登录数据库
create database test;
use test;
create table lhj(id int,name char(11));
insert lhj() values(1,'abc'),(2,'xyz');
quit
#创建test库进入test库创建lhj表并写入2条数据到lhj表中,退出数据库
vim /etc/my.cnf
[mysqld]下一行添加内容:
server_id=11
#serverid建议配置为ip地址最后8位
log-bin=/data/mysql/mysql-bin
#指定二进制日志文件保存位置为/data/mysql/,保存后退出
mkdir -p /data/mysql
#创建保存二进制文件的目录
chown mysql:mysql -R /data/
#目录的属主属组都给到mysql用户
systemctl restart mysqld
#重启数据库
mysqldump -uroot -p'Admin@123' -A -F --master-data=1 --single-transaction > /data/all.sql
#备份主数据已有内容到/data/all.sql文件中
#-A --all-databases(表示所有库)
#-F 刷新日志
#--master-data=1 会将 master 的修改语句写入dump文件中
#--single-transaction 已开启事务的方式备份
scp /data/all.sql 192.168.30.12:/
#将主数据库备份的文件all.sql传输到从数据库的/目录下
mysql -uroot -pAdmin@123
grant replication slave on *.* to test@'192.168.30.%' identified by 'Admin@123';
show master status;
#查看主从复制从那个二进制日志的那个节点开始,从服务器配置需要依赖此条查看结果
quit;
#登录数据库创建主从复制登录用户后退出
④从节点配置:
vim /etc/my.cnf
[mysqld]下一行添加内容:
server_id=12
#serverid建议配置为ip地址最后8位
log-bin=/data/mysql/mysql-bin
#指定二进制日志文件保存位置为/data/mysql/
relay-log=relay-log-bin#开启中继日志
relay-log-index=slave-relay-bin.index#添加,定义中继日志文件的位置和名称,保存后退出
mkdir -p /data/mysql
#创建保存二进制文件的目录
chown mysql:mysql -R /data/
#目录的属主属组都给到mysql用户
systemctl restart mysqld
#重启数据库
vim /all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
#找到此行,在MSATER TO 后添加 主服务器ip,远程账户密码端口内容如下:
CHANGE MASTER TO
MASTER_HOST='192.168.30.13',
MASTER_USER='test',
MASTER_PASSWORD='Admin@123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=154;
#内容写完后保存退出,此备份文件可以理解为一个脚本
mysql -uroot -pAdmin@123
#登录数据库
source /all.sql;
start slave;
#执行根下的all.sql脚本并开启主从复制
⑤实战结果:
登录从服务器验证结果:
select * from test.lhj;
#查看是否备份到未开启主从备份时主数据库中的表
6、主从复制错误解决方法
方法1:临时跳过错误 stop slave; set global sql_slave_skip_counter=1; start slave;
方法2:永久跳过错误修改配置文件 vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL
重启数据库
二、级联主从复制
1、级联复制原理
①一级主是二级主的主数据库,二级主是从数据库的主服务器
②配置时二级主的主服务器ip日志远程用户等配置一级主的信息,二级主将一级主的二进制日志转发给从数据库
③配置从数据库时主服务器ip日志远程用户等配置二级主的信息
2、级联复制实战实现
①环境准备:3台安装了相同版本mysql数据库的服务器
一级主服务器(pc1):192.168.30.11
二级主服务器(pc2):192.168.30.12
从服务器(pc3):192.168.30.13
②首先关闭3台服务器的firewalld以及selinux,重置mysql密码为Admin@123
③一级主配置:与主从复制主服务器配置完全相同
④二级主配置:与主从复制从服务器配置相同,只需要在修改配置文件时多添加一条:log_slave_updates(此条为开启log转发更新即将一级主的binlog转发给从服务器)
⑤从服务器配置:与主从复制从服务器配置相同,注意从服务器的主是二级主,所以配置主机ip,远程用户密码以及端口和日志位置时要写二级主的信息。