mysql主从复制操作方法

admin · 发表于 2018-7-25 11:26:09

购买主题本主题需向作者支付 5 金钱 才能浏览

admin · 发表于 2024-8-12 09:52:42

AB复制(重点)
一、什么是主从复制?
1、主从同步也叫AB复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

2、主从复制的作用

1.做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。
2.架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率,提高单个机器的I/O性能。
3.读写分离，使数据库能支撑更大的并发。
1--在从服务器可以执行查询工作(即我们常说的读功能)，降低主服务器压力;（主库写，从库读，降压）
2--在从服务器进行备份，避免备份期间影响主服务器服务;（确保数据安全）

为什么要搞主从同步?
因为只有一个服务器上面跑着mysql 万一发生故障数据会丢失业务访问无法正常运行，业务量过大的情况下，对服务器的压力也是很大的，所以要考虑到服务要高可用，既降低服务器压力又提高可用性

写库(写入)专门放一个数据库主库负责写入
查询(select)在专门放一个库从库负责查询

AB复制也叫读写分离

二、主从同步原理(面试题)
主服务器把数据修改之后，会放入到二进制日志中(binary log)中，然后通过内部进程机制把日志传递到从服务器(从服务器在通过IO线程写入到中继日志)中并表示为中继日志(中继日志在通过sql线程传输给从服务器并执行)，从而达到了主从一致
从服务器有俩个线程(I/O线程和SQL线程)I/O线程负责把主服务器上的日志拉取过来，slq线程是负责把日志执行的，从服务器干的活最多
主服务器写入数据，从服务器就写入，主服务器删除数据，从服务器就会接着删除数据

从服务器不能写入数据

主从同步也叫异步模式，时间会有0.几毫秒延时，

主服务器做好授权从服务器具有授权的凭证进行把日志传递

丛库可以写数据库但是主库是不会有的

三、M-S 架构GTID 基于事务ID复制
1、什么是GTID？
全局事务标识：global transaction identifiers
是用来代替传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。

2、GTID工作原理
1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
2、slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

3、部署主从复制
-A, --all-databases #备份所有库
-B, --databases  #备份多个数据库
-F, --flush-logs #备份之前刷新binlog日志
--default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。
--no-data，-d #不导出任何数据，只导出数据库表结构。
--lock-tables #备份前，锁定所有数据库表
--single-transaction #保证数据的一致性和服务的可用性
-f, --force #即使在一个表导出期间得到一个SQL错误，继续。

对主库已有的数据库不会进行自动同步(可选操作两边的库要一样)
逻辑备份先暂时关闭gtid
--single-transaction 做一次性检查
--all-databases 导出所有数据库

mkdir /dackupdb
mysqldump -uroot  -p'123456'  --set-gtid-purged=OFF --single-transaction  --all-databases  > /data/backupdb/all_$(date +%Y%m%d%H%M%S).sql

在从库上导入单个数据库。
mysql -uroot -p'' 库名 < /path/dbname.bak
mysql -uroot -p'123456' company < all_20240524195401.sql

在从库上导入多个数据库
mysql -uroot -p'123456'  < all_20240524195401.sql

部署主从复制详细流程
开始执行第一步
master操作：
cd /usr/local/mysql/
mkdir bin-log
chown -R mysql:mysql bin-log

[root@mysql-master ~]# vim /etc/my.cnf
#在[mysqld]下添加如下内容
server-id=1
log-bin = /usr/local/mysql/bin-log/binlog  //前面是路径后面是文件名
gtid_mode = on
enforce_gtid_consistency=1
#强制gtid
sync_binlog = 1

[root@mysql-master ~]# systemctl restart mysqld

检查一下bin-log文件夹(此时二进制文件已经生成好了)
cd /usr/local/mysql/
cd bin-log/ ll

replication  slave  //同步到从服务器
reload //重新加载
super //超级权限
slave //账号
% //从服务器的id地址和主机
openssl rand -base64 40 | cut -c 2-15
+GVUrydHxuf4FK

在主服务器创建授权账户：
mysql> grant replication  slave,reload,super on *.*  to 'slave'@'%' identified by '+GVUrydHxuf4FK';
#注:生产环境中密码采用高级别的密码，实际生产环境中将'%'换成slave的ip
mysql> flush privileges;
mysql> show master status; //查看状态

注意:如果不成功删除以前的binlog日志
replication slave：拥有此权限可以查看从服务器，从主服务器读取二进制日志。
super权限：允许用户使用修改全局变量的SET语句以及CHANGE  MASTER语句
reload权限：必须拥有reload权限，才可以执行flush  [tables | logs | privileges]

slave操作(从服务器)：
[root@mysql-slave ~]# vim /etc/my.cnf  #添加如下配置

relay-log-info-repository=TABLE //要不要这个表的数据记录

server-id=2
gtid_mode = ON
enforce_gtid_consistency=1
master-info-repository=TABLE
relay-log-info-repository=TABLE

在重启服务
[root@mysql-slave ~]# systemctl restart mysqld

检查一下有没有错误的配置文件
cat /usr/local/mysql/mysql.log

master_host='' //主服务器是多少
master_user='' //账号是多少
master_password='+GVUrydHxuf4FK', //主服务器的密码是多少
master_auto_position=1; //主服务器的标识是几
[root@mysql-slave ~]# mysql -uroot -p'123456' #登陆mysql

mysql> change master to
master_host='192.168.171.15',
master_user='slave',
master_password='+GVUrydHxuf4FK',
master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave; #真实启动slave(从)角色
mysql> show slave status\G  #查看状态，验证sql和IO是不是yes。
Slave_IO_Running: Yes  //表示主从同步已经配置成功
Slave_SQL_Running: Yes

4、测试同步
在主库新建数据库，在从库查看同步

回到主服务器上
mysql> show master status;
创建数据库
mysql> create database test_db;
主服务器状态码会发生变化为773
mysql> show master status;
回到从服务器上状态码也会发生变化为773
mysql> show slave status\G
查看test_db库是否存在
mysql> show databases;

在主服务器上创建库shuihu
mysql -uroot -p'123456' -e"create database shuihu";
回到从服务器上查看shuihu库是否存在
mysql> show databases;
查看动态日志
tail -f /usr/local/mysql/mysql.log

5、重设从库
什么场景需要重设从库，比如网络发生异常，主库地址(position)会发生变化,从库上方网络ok就可以做一次从连

从库重设#全在从库执行(断开于主库之间的通信，脱离主从关系)
mysql>stop slave;
mysql>reset slave;
mysql>reset master;
#从库的binlog已经无效了，所以要执行这个命令清空binlog

mysql> change master to
master_host='192.168.171.15',
master_user='slave',
master_password='+GVUrydHxuf4FK',
master_auto_position=1;

mysql> start slave; #启动slave角色
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G  #查看状态，验证sql和IO是不是yes。

6、常见错误
ERROR 3081 (HY000): This operation cannot be performed with running replication threads; run STOP SLAVE FOR CHANNEL '' first
如果遇到这种错误(是因为之前做主从同步的时候有残留)

要先断开服务
从库重设#全在从库执行(断开于主库之间的通信，脱离主从关系)
mysql>stop slave;
mysql>reset slave;
mysql>reset master;
在进行重启就ok了
systemctl stop mysqld
systemctl start mysqld

常见故障1
Slave has more GTIDs than the master has,using the master's SERVER_UUID
该问题代表从库获取到的GTID超过了主库，比如主库在未指定binlog文件名的同时修改了系统主机名，导致binlog全部被修改，从库就会判断失败；或者主库未配置双1参数时断电，导致从库提前获取到了还未执行的GTID，解决方法如下：
重设从库
stop slave;
reset slave;
reset master;
#从库的binlog已经无效了，所以要执行这个命令清空binlog
change master to
master_host='10.36.107.10',
master_port=3306,
master_user='slave',
master_password='Qf@12345!',
master_auto_position=1;

常见故障2
如果从库未指定relaylog的同时修改了系统主机名，只需要在从库重新执行一次同步
stop slave;
reset slave;
change master to
master_host='10.36.107.10',
master_port=3306,
master_user='slave',
master_password='Qf@12345!',
master_auto_position=1;

常见故障3
Master_has_purged_require_gtids
主库提前删除了还未同步完成的binlog
在主库上查看master信息
mysql> show master status\G;
*************************** 1. row ***************************
         File: mylog.000001
      Position: 465
   Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 402c0020-4012-11ed-8d7e-000c292b8f0e:1-2
1 row in set (0.00 sec)
#在从库上手动指定二进制日志文件master_log_file和位置master_log_pos与master上的一致
mysql > stop slave;
mysql > change master to
master_host='10.36.107.10',
master_user='slave',
master_password='Qf@12345!',
master_log_file='mylog.000001',
master_log_pos=465,
master_auto_position=0;
mysql > start slave;

6、故障切换
丛库可以创数据库但是主库是不会有的

mysql主从，主服务器发生故障，如何进行切换？
主机故障，要把从服务器替换成主服务器

1)在从服务器执行：(脱离主从关系)
mysql> stop slave;
mysql> reset master;

2)查看是否只读模式:(此时只读模式是关闭模式 OFF)
show variables like 'read_only';
关闭只读模式
vim /etc/my.cnf
read-only=1
并重启mysql服务
systemctl restart mysqld

或者不重启使用命令关闭只读，但下次重启后失效：set global read_only=off;
3)查看show slave status \G;
4)在程序中将原来主库IP地址改为现在的从库IP地址，测试应用连接是否正常

		自动登录	找回密码
密码			注册

mysql主从复制操作方法

浏览过的版块