常平镇网站建设公司,东莞长安网站设计公司,怎样做代刷网站长,连云港百度推广网站建设一、MYSQL主从同步概述 1、什么是MySQL主从同步#xff1f; 实现数据自动同步的服务结构 主服务器(master): 接受客户端访问连接
从服务器(slave)#xff1a;自动同步主服务器数据 2、主从同步原理 Maste#xff1a;启用binlog 日志 Slave#xff1a;Slave_IO: 复制master主… 一、MYSQL主从同步概述 1、什么是MySQL主从同步 实现数据自动同步的服务结构 主服务器(master): 接受客户端访问连接
从服务器(slave)自动同步主服务器数据 2、主从同步原理 Maste启用binlog 日志 SlaveSlave_IO: 复制master主机binlog 日志文件的SQL命令到本机的relay-log(中继日志) 文件里。 Slave_SQL: 执行本机 relay-log(中继日志) 文件里的SQL语句实现与 Master 数据一致。 Master(主服务器) 1》开启binlog日志记录所有除查询以外的SQL命令 Slave(从服务器): 1》从服务器上的I/O thread(读写线程) 负责读取主服务器binlog日志中的SQL命令并将其写入到 Relay log(中继日志中) 2》从服务器中的SQL thread(SQL 线程)读取中继日志中的SQL命令并将其写入到Slave的数据库中 3、主从同步结构模式 基本应用 单向复制一主 —— 一从 扩展应用 一主多从从 —— 主 —— 从 链式复制主 —— 从 —— 从 互为主从主 —— 主 4、MySQL主从同步配置 拓扑结构 5、实施 步骤一根据host50克隆虚拟机出host51,配置IP地址为192.168.4.51主机名为host51删除5152上面多余的数据库只保留默认的四个库 [roothost51 ~]# mysql -uroot -p123qqq...A
mysql drop database bbsdb;
[roothost52 ~]# mysql -uroot -p123qqq...A
mysql drop database db4; 步骤二host51配置主服务器 # 修改主配置文件开启binlog日志
[roothost51 ~]# vim /etc/my.cnf
[mysqld]
#binlog_formatmixed # 加上注释
server_id50 #指定id号默认与IP地址的主机位相同
log_binmaster51 #指定binlog日志名日志文件在/var/lib/mysql下 步骤三重启数据库服务让配置生效 [roothost51 ~]# systemctl restart mysqld
# 用户授权给replication slave复制数据的权限授权用户为: repluser
[roothost51 ~]# mysql -uroot -p123qqq...A
mysql grant replication slave on *.* to repluser% identified by 123qqq...A;
mysql show master status; 步骤四host52配置从服务器 # 修改主配置文件指定server_id号
[roothost52 ~]# vim /etc/my.cnf[mysqld]
server_id52 #指定id号默认与IP地址的主机位相同
[roothost52 ~]# systemctl restart mysqld
# 指定主服务器信息
mysql change master to master_host192.168.4.51, master_userrepluser, master_password123qqq...A, master_log_filemaster51.000001, master_log_pos441; 步骤五启动从服务器 mysql start slave;
mysql show slave status\G; #确认IO线程、SQL线程都是 Yes 状态 如果IO线程为NO错误提示如下 这是因auto.cnf 存放的是主服务器的uuid号 因为是克隆出的虚拟机uuid号都一样会进行冲突可以任意修改其中一个或多个字符重启数据库服务即可 [roothost52 ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuidd45877cc-f4ab-11ea-b2a1-000c291f7055
[roothost52 ~]# systemctl restart mysqld 步骤六host51测试主从同步 [roothost51 ~]# mysql -uroot -p123qqq...A
mysql create database db1;
mysql create table db1.user (name char(10));
mysql insert into db1.user values(tom); 步骤七在host52上查看 [roothost52 ~]# mysql -uroot -p123qqq...A
mysql show databases;
mysql select * from db1.user; 相关文件 存放在数据库目录下/var/lib/mysql/ 删除文件重启数据库服务可把主机恢复为独立的数据库服务器 记录主服务器的信息 [roothost52 ~]# cat /var/lib/mysql/master.info 查看中继日志信息 # host52-relay-bin.000001 是中继日志文件记录从主服务器拷贝过来的sql命令
# host52-relay-bin.index 是中继日志索引文件
[roothost52 ~]# ls /var/lib/mysql/host52*
# 查看中继日志文件内容
[roothost52 ~]# cd /var/lib/mysql
[roothost52 mysql]# cat relay-log.info
7
./host52-relay-bin.000004 #本机正在使用的中继日志文件
319 #中继日志记录主服务器sql命令的偏移量
master51.000001 #中继日志从哪个文件中拷贝sql命令(主服务器)
441 #此为主服务器最近的binlog日志的偏移量
# 查看中继日志索引文件有几个中继日志文件就记录几条
[roothost52 ~]# cd /var/lib/mysql
[roothost52 mysql]# cat host52-relay-bin.index 6、配置MySQL一主多从解决从服务器宕机之后无法备份数据问题 拓扑结构 步骤一准备数据库host53可以直接用之前的host53删除创建数据库保留默认的四个库 [roothost53 ~]# mysql -uroot -p123qqq...A
mysql show databases;
mysql drop database db4; 步骤二没有配置之前要确保从与主服务器数据一致 host51安装innobackupex相关软件包 [roothost52 ~]# scp libev-4.15-1.el6.rf.x86_64.rpm percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm 192.168.4.51:/root/
[roothost51 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
[roothost51 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
[roothost51 ~]# innobackupex --user root --password 123qqq...A --slave-info /allbak1 --no-timestamp
[roothost51 ~]# scp -r /allbak1/ 192.168.4.53:/opt/ host53使用innobackupex恢复数据 [roothost53 ~]# systemctl stop mysqld
[roothost53 ~]# rm -rf /var/lib/mysql/*
[roothost53 ~]# innobackupex --apply-log /root/allbak1/ #准备恢复数据
[roothost53 ~]# innobackupex --copy-back /root/allbak1/ #恢复数据
[roothost53 ~]# chown -R mysql:mysql /var/lib/mysql
[roothost53 ~]# systemctl start mysqld
[roothost53 ~]# mysql -uroot -p123qqq...A -e show databases # xtrabackup_binlog_info 文件记录的是binlog日志文件名和偏移量
# 此偏移量和主服务器的偏移量一致从服务器同步数据时从这个偏移量开始同步
# 可以查看host51的binlog日志状态偏移量相同
[roothost53 ~]# cat /opt/alldb/xtrabackup_binlog_info
master51.000001 1020 步骤三配置从服务器 # 修改主配置文件指定server_id号
[roothost53 ~]# vim /etc/my.cnf
[mysqld]
server_id53 #指定id号默认与IP地址的主机位相同
[roothost53 ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuidd45877cc-ffab-11ea-b2a1-000c291f7055
[roothost53 ~]# systemctl restart mysqld
[roothost53 ~]# mysql -uroot -p123qqq...A 步骤四指定主服务器信息 mysql change master to master_host192.168.4.51, master_userrepluser, master_password123qqq...A, master_log_filemaster51.000001, master_log_pos1020;
mysql start slave; #启动slave
mysql show slave status; #查看从服务器状态信息
host51插入数据
[roothost51 ~]# mysql -uroot -p123qqq...A
mysql insert into db1.user values(tomA),(tomB),(tomC); 步骤五host53测试主从同步 [roothost53 ~]# mysql -uroot -p123qqq...A
mysql select * from db1.user; 二、MYSQL主从从结构 配置MYSQL主从从结构 1、主从从结构 主从从结构优势 1》【host55】是【host54】的从服务器【host54】是【host53】的从服务器 2》当【host53】宕机以后用户访问的是从服务器【host54】的数据库 3》当【host54】宕机以后用户访问的就是从服务器【host55】的数据库 2、主从从结构实验环境准备根据host50克隆虚拟机host54和host55主机配置IP如下并还原数据库默认的四个库 主服务器 主机名host53 IP地址: 192.168.4.53
从服务器 主机名host54 IP地址: 192.168.4.54
从服务器 主机名host55 IP地址: 192.168.4.55 3、将host53恢复成独立的数据库服务器 [roothost53 ~]# cd /var/lib/mysql
[roothost53 mysql]# rm -rf master.info #删除连接主服务器的信息文件
[roothost53 mysql]# rm -rf host53-relay-bin.0* #删除所有的中继日志文件存放从主服务器拷贝过来的sql命令
[roothost53 mysql]# rm -rf host53-relay-bin.index #删除中继日志的索引文件按顺序记录所有的中继日志文件名
[roothost53 mysql]# rm -rf relay-log.info #删除中继日志文件记录中继日志信息
[roothost53 mysql]# systemctl restart mysqld 4、测试 [roothost53 ~]# mysql -uroot -p123qqq...A
mysql show slave status; #查看从服务器状态信息为Empty已经还原
mysql drop database db1; #删除db1库
mysql exit 5、host53配置主服务器 修改主配置文件开启binlog日志
[roothost53 ~]# vim /etc/my.cnf
[mysqld]
server_id53
log_binmaster53
[roothost53 ~]# systemctl restart mysqld
[roothost53 ~]# mysql -uroot -p123qqq...A 这一次的授权不需要操作因为之前53主机在做从库的时候已经同步用户了 mysql grant replication slave on *.* to repluser% identified by 123qqq...A;
mysql select user,host from mysql.user;
mysql show grants for repluser%;
mysql show master status; 6、host54配置从服务器 修改配置文件、用户授权、指定主库信息、启动slave进程 修改主配置文件指定server_id号 log_slave_updates 必须开启级联复制功能因为【host54】同步数据是从【host53】的binlog日志中获取的【host54】并没有直接执行sql命令所以在【host54】的binlog日志中并没有sql命令那么【host55】也就无法同步【host54】中的数据而开启级联复制功能则允许【host55】同步【host54】从【host53】同步过来的数据 [roothost54 ~]# vim /etc/my.cnf
[mysqld]
#binlog_formatmixed
server_id54
log_binmaster54
log_slave_updates #允许级联复制
[roothost54 ~]# vim /var/lib/mysql/auto.cnf # auto.cnf 存放的是主服务器的uuid号 因为是克隆出的虚拟机uuid号都一样会冲突可以任意修改其中一个或多个字符重启数据库服务即可
[auto]
server-uuidd45877cc-f4ab-12ea-b2a1-000c291f7055
[roothost54 ~]# systemctl restart mysqld 7、连接数据库指定主服务器信息 [roothost54 ~]# mysql -uroot -p123qqq...A
mysql change master to master_host192.168.4.53, master_userrepluser, master_password123qqq...A,master_log_filemaster53.000001,master_log_pos154;
mysql start slave;
mysql show slave status\G; 8、host54从服务器【host55】添加授权用户 mysql grant replication slave on *.* to jim% identified by 123qqq...A;
mysql show master status; 9、host55配置从服务器 [roothost55 ~]# vim /etc/my.cnf
[mysqld]
#binlog_formatmixed
server_id55
[roothost55 ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuidd45877cc-f4ab-12ea-b2a1-000c291f7055
[roothost55 ~]# systemctl restart mysqld 10、指定主服务器信息 [roothost55 ~]# mysql -uroot -p123qqq...A
mysql change master to master_host192.168.4.54,master_userjim, master_password123qqq...A,master_log_filemaster54.000001,master_log_pos1554;
mysql start slave;
mysql show slave status\G; 11、测试主从从同步在host53上建库,建表插入记录 [roothost53 ~]# mysql -uroot -p123qqq...A
mysql create database bbsdb;
mysql create table bbsdb.user(name char(11));
mysql insert into bbsdb.user values(bob);
mysql select * from bbsdb.user; 12、在host54上查看验证数据 [roothost54 ~]# mysql -uroot -p123qqq...A
mysql select * from bbsdb.user; 13、在host55上查看验证数据 [roothost55 ~]# mysql -uroot -p123qqq...A
mysql select * from bbsdb.user; 三、复制模式 复制模式介绍 异步复制 (默认的复制模式)Asynchronous replication 主服务器执行完一次事务后立即将结果返给客户端不关心从服务器是否已经同步数据。 案例【host50】(主服务器) —— 【host51】(从服务器) 用户在【host50】(主服务器)上执行插入更新删除等SQL命令时【host50】(主服务器)直接将结果返回给用户不关心【host51】(从服务器)是否同步数据成功 优点响应速度快用户体验很好 缺点主服务器宕机后有可能会存在从服务器数据丢失的情况 半同步复制Semisynchronous replication 主服务器在执行完一次事务后等待至少一台从服务器同步数据完成才将结果返回给客户端。 案例【host50】(主服务器) —— 【host51】(从服务器) 用户在【host50】(主服务器)上执行插入更新删除等SQL命令时【host50】(主服务器)不会立刻将结果返回给用户而是等待至少一个从服务器将数据同步写入到本机的数据库后才将结果返回给用户 优点主服务器宕机后至少有一台从服务器拥有和主服务器相同的数据数据安全度高 缺点响应速度下降用户体验度下降 配置半同步复制 (主从服务器都要配置) 主服务器 主机名host53 IP地址: 192.168.4.53
从服务器 主机名host54 IP地址: 192.168.4.54
从服务器 主机名host55 IP地址: 192.168.4.55 命令行加载模块 host53主服务器命令行加载半同步复制的master模块
mysql show databases;
mysql desc information_schema.PLUGINS; #查看默认库information_schema 下PLUGINS表(模块表)的表结构
加载 master 模块rpl_semi_sync_master模块类型semisync_master.so模块名称
mysql install plugin rpl_semi_sync_master SONAME semisync_master.so;
mysql select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like %semi%; host55安装slave模块 mysql install plugin rpl_semi_sync_slave SONAME semisync_slave.so;
host54因为是即做主又做从所以要安装 matser和slave模块
mysql install plugin rpl_semi_sync_master SONAME semisync_master.so;
mysql install plugin rpl_semi_sync_slave SONAME semisync_slave.so;
mysql select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME like %semi%; 启用模块 host53模糊查询半同步复制模块是否开启
mysql show variables like %semi%;
mysql set global rpl_semi_sync_master_enabled1; #全局设置开启半同步复制模块
mysql show variables like %semi%; #on开启 host54启动半同步复制的master和slave模块 mysql set global rpl_semi_sync_master_enabled1;
mysql set global rpl_semi_sync_slave_enabled1;
mysql show variables like %semi%; host55启动半同步复制的slave模块 mysql set global rpl_semi_sync_slave_enabled1;
mysql show variables like %semi%; 永久配置 主服务器永久安装和启用半同步复制的master模块 [roothost53 ~]# vim /etc/my.cnf
[mysqld]
plugin-loadrpl_semi_sync_mastersemisync_master.so #加载master模块
rpl_semi_sync_master_enabled1 #启用master模块
[roothost53 ~]# systemctl restart mysqld
[roothost54 ~]# mysql -uroot -p123qqq...A
mysql show variables like %semi%; 主从服务器永久安装和启用半同步复制的master和slave模块,在host54上操作 [roothost54 ~]# vim /etc/my.cnf
[mysqld]
plugin-loadrpl_semi_sync_mastersemisync_master.so;rpl_semi_sync_slavesemisync_slave.so #同时加载master和slave模块
rpl_semi_sync_master_enabled1 #启用master模块
rpl_semi_sync_slave_enabled1 #启用slave模块
[roothost54 ~]# systemctl restart mysqld
[roothost54 ~]# mysql -uroot -p123qqq...A
mysql show variables like %semi%; 从服务器永久安装和启用半同步复制的slave模块,在host55上操作 [roothost55 ~]# vim /etc/my.cnf
[mysqld]
plugin-loadrpl_semi_sync_slavesemisync_slave.so #加载master模块
rpl_semi_sync_slave_enabled1 #启用master模块
[roothost55 ~]# systemctl restart mysqld
[roothost55 ~]# mysql -uroot -p123qqq...A
mysql show variables like %semi%;
