mysql 删除操作加的是什么锁

转载

mob64ca1405664d 2024-11-07 21:43:51

文章标签 mysql 删除操作加的是什么锁 mysql 同步复制测试数据 文章分类 MySQL 数据库

【0】需求

　　前提，已经假设好基于传统异步复制的主库和从库1.

　　【0.1】传统异步切换成基于GTID的无损模式

　　【0.2】增加特殊要求的从库

【1】操作环境

【2】构建复制-》半同步复制实践操作

　　【2.1】搭建传统异步复制（带配置文件）
　　【2.2】构建测试数据
　　【2.3】在线异步模式改成无损半同步模式

【3】半同步与GTID模式的相互在线切换
　　【3.1】前置条件
　　【3.2】半同步切换成 GTID 模式操作步骤
　　【3.3】GTID切换成半同步

【4】增加节点 203 为只复制 test 库的从库
　　【4.1】在主库 mysqldump 备份 test 库（也可以在从）
　　【4.2】修改配置文件（只复制test库）
　　【4.3】开始主从（203从，201主）
　　【4.4】核验

正文：

【0】需求

　　前提，已经假设好基于传统异步复制的主库和从库1.

【0.1】传统异步切换成基于GTID的无损模式

　　　　由于早期架构没有设置好，用的是非GTID的传统异步模式。

　　　　为了安全与性能，需要在线切换至基于GTID的无损模式，且不能停止业务，需在线处理。

【0.2】增加特殊要求的从库

　　　　由于业务需要，增加2个从库，从库2只需要同步一个库，从库3只需要同步某2张表。

　　　　且从库3在使用一段时间后，不再使用，需要删除。

【1】操作环境

　　操作系统：CentOS linux 7.5

　　数据库版本：5.7.24

　　数据库架构：主从复制，主库用于生产，从库用于数据容灾和主库备机，采用默认传统的异步复制。

　　主库IP：192.168.1.201　　端口：3306

　　从库1_IP：192.168.1.202　　端口：3306 全库

　　从库2_IP：192.168.1.203　　端口：3306 test数据库

　　从库3_IP：192.168.1.204　　端口：3306 test.test4

【2】构建复制-》半同步复制实践操作

【2.1】搭建传统异步复制（带配置文件）

#replication_new
log_bin=/mysql/log/3306/mysql-bin #开启binlog
log_bin_index=/mysql/log/3306/mysql-bin.index
binlog_format=row
binlog_rows_query_log_events=on
max_binlog_size=2048

bind-address=0.0.0.0
server_id=2013306
expire_logs_days=7    #超过7天的binlog清理
innodb_support_xa=1
binlog_cache_size=1M
log_bin_trust_function_creators=1    #同步存储过程、函数、触发器
innodb_flush_log_at_trx_commit=1
sync_binlog=1
transaction-isolation=read-committed

relay_log=/mysql/log/3306/relaylog/mysql-relay.log
log-slave-updates=1
#read_only=1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master_info_repository=table #master_info 会记录到 mysql.slave_master_info
relay_log_info_repository=table #relay_log 会记录到，mysql.slave_relay_log_info
relay_log_recovery=1
slave_skip_errors=ddl_exist_errors
slave_preserve_commit_order=1

#plugin_dir=/mysql/app/mysql/lib/plugin/
#plugin_load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so

#如果是5.7，参数前面加上loose_，如下列，如果是5.6 则直接使用 rpl_semi_sync_master_enabled=1 之类的就好了。
#我这里是5.7就直接做增强半同步了（loseless Semisynchronous ）
#loose_rpl_semi_sync_master_enabled=1 #MySQL开启主的半同步复制（rpl_semi_sync_master_enabled）
#loose_rpl_semi_sync_slave_enabled=1 #MySQL5.6开启从的半同步复制
#loose_rpl_semi_sync_master_timeout=5000 #超时5秒，切回异步
#rpl_semi_sync_master_wait_for_slave_count=1 #至少收到1个slave发会的ack
#rpl_semi_sync_master_wait_point=AFTER_SYNC #MySQL 5.7的方法，AFTER_SYNC（default，增强半同步） & AFTER_COMMIT（传统半同步）

#gtid_mode=1
#enforce_gtid_consistency=1

　　搭建参考（这里就不再写了）：异步复制

【2.2】构建测试数据

-- 【2.2.1】构造test库和test库下的test1,test2,test3表。test4表用于模拟业务一直在运行
create database test;
use test;
create table test1(id int);
insert into test1 values(1);
create table test2(id int);
insert into test2 values(2);
create table test3(id int);
insert into test3 values(3);
commit;
create table test4(id int);
insert into test4 values(4);
commit;

-- 【2.2.2】构造存储过程sp_test4来循环插入test4表，模拟业务运行
use test;
drop procedure if exists sp_test4;
delimiter $$
create procedure sp_test4()
begin
declare n int;
set n=11;
while(n<=20)
do
insert into test.test4 values(n);
commit;
set n=n+1;
end while;
end $$
delimiter ;

-- 【2.2.3】构造事件，来调度sp_test4过程
use test;
set global event_scheduler=1;

delimiter $$
create event if not exists event_test4
on schedule every 5 second
on completion preserve
enable
do
begin
call sp_test4();
end $$
delimiter ;

-- 为了防止测试数据量累计导致卡顿，我这里5小时做一次truncate

delimiter $$
create event if not exists event_truncate_test4
on schedule every 5 hour
on completion preserve
enable
do
begin
truncate table test.test4;
end $$
delimiter ;

【2.3】在线异步模式改成无损半同步模式

参考：半同步复制

【2.3.1】在线开启半同步
在主库从库均：

install plugin rpl_semi_sync_master soname 'semisync_master.so';
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_master_enabled=1; #MySQL开启主的半同步复制（rpl_semi_sync_master_enabled）
set global rpl_semi_sync_slave_enabled=1; #MySQL5.6开启从的半同步复制
set global rpl_semi_sync_master_timeout=5000; #超时5秒，切回异步
set global rpl_semi_sync_master_wait_for_slave_count=1; #至少收到1个slave发会的ack
set global rpl_semi_sync_master_wait_point=AFTER_SYNC;

-- 主从均核验状态
show plugins; -- 查看插件是否装好
show variables like 'rpl%'; -- 查看半同步参数设置及状态
show status like 'rpl%'; -- 查看半同步复制的状态

在从库（确认主从同步后）：

stop slave io_thread; -- stop slave ;
start slave io_thread; -- start slave;

【2.3.2】修改my.cnf 重启mysql启用半同步

#plugin_dir=/mysql/app/mysql/lib/plugin/
plugin_load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so

#如果是5.7，参数前面加上loose_，如下列，如果是5.6 则直接使用 rpl_semi_sync_master_enabled=1 之类的就好了。
#我这里是5.7就直接做增强半同步了（loseless Semisynchronous ）
loose_rpl_semi_sync_master_enabled=1 #MySQL开启主的半同步复制（rpl_semi_sync_master_enabled）
loose_rpl_semi_sync_slave_enabled=1 #MySQL5.6开启从的半同步复制
loose_rpl_semi_sync_master_timeout=5000 #超时5秒，切回异步
rpl_semi_sync_master_wait_for_slave_count=1 #至少收到1个slave发会的ack
rpl_semi_sync_master_wait_point=AFTER_SYNC #MySQL 5.7的方法，AFTER_SYNC（default，增强半同步） & AFTER_COMMIT（传统半同步）

【3】半同步与GTID模式的相互在线切换

【3.1】前置条件

　　（1）mysql5.7.6以上版本，才支持GTID

　　（2）当前gtid_mode=off

　　（3）下面的步骤必须按顺序执行，不能跳着执行。

　　　　gtid_mode 在线打开切换过程：

　　　　　　off >> off_permissive >> on_permissive >> on

　　　　gtid_mode 在线关闭切换过程：

　　　　　　on >> on_permissive >> off_permissive >> off

　　（4）相关释义信息：　

　　　　#gtid_mode = 　

　　　　　　#（1）on：产生GTID,slave只接受带GTID的事务　　

　　　　　　#（2）on_permissive：产生GTID，slave接受不带GTID的事务也接受带GTID的事务

　　　　　　#（3）off：不产生GTID，slave只接受不带参GTID的事务　　

　　　　　　#（4）off_permissive：不产生GTID，slave接受带GTID也接受不带GTID的事务

　　　　# enforce_gtid_consistency=on　　#on：当发现语句/事务不支持GTID时，返回错误信息　　WARN：发现不支持返回警告　　#off：不检查

【3.2】半同步切换成 GTID 模式操作步骤

参考官网：https://dev.mysql.com/doc/refman/8.0/en/replication-mode-change-online-enable-gtids.html

-- 在所有mysql上运行~~~~

-- 【3.2.1】提交事务、设置GTID强制连续选项
commit;
set @@global.enforce_gtid_consistency=warn;
show variables like '%enforce_gtid%';

set @@global.enforce_gtid_consistency=on;

-- 【3.2.2】设置 gtid_mode
set @@global.gtid_mode=off_permissive;
set @@global.gtid_mode=on_permissive;
show variables like '%gtid_mode%';

-- 【3.2.3】查看是否还有匿名事务没有提交
show status like '%anonymous%';

-- 【3.2.4】所有的mysql清理旧的二进制日志
flush logs;

-- 【3.2.5】开启GTID，设置gtid_mode=on
set @@global.gtid_mode=on;
show variables like '%gtid_mode%';

-- 这就算配完了


-------------------------------------
#【3.2.6】为了下次重启永久生效，设置好my.cnf
[mysqld]
gtid_mode=1
enforce_gtid_consistency=1
log_slave_update=1

-- 【3.2.7】在从库运行，重配基于GTID的复制
stop slave;

change master to 
master_host='192.168.1.201',
master_port=3306,
master_user='rpl',
master_password='123456',
master_auto_position=1;

start slave;
show slave status\G

-- 查看测试数据，是否同步
select count(1) from test.test4;

【3.3】GTID切换成半同步

-- 先主库

-- 【3.3.1】修改gtid_mode
commit;
set @@global.gtid_mode=on_permissive;
commit;
show variables like '%gtid_mode%';
set @@global.gtid_mode=off_permissive;
commit;
set @@global.gtid_mode=off;
commit;
show variables like '%gtid_mode%';

【报错】ERROR 1766 (HY000): The system variable gtid_mode cannot be set 
       when there is an ongoing transaction.
【解决】commit;  -- 提交一下空事务即可

-- 【3.3.2】从库运行

commit;
set @@global.gtid_mode=on_permissive;
commit;
show variables like '%gtid_mode%';
set @@global.gtid_mode=off_permissive;
commit;

-- 【3.3.3】从库停止复制，禁止使用master_auto_position，用file于pos重启复制

-- 在从库
stop slave;
show slave status\G -- 查看一下io线程读取到主库的哪个Binlog文件及其 pos位置

-- (如果账号密码等没有变化，可以只写最后3行，即 change master to master_log.....)
change master to 
master_host='192.168.1.201',
master_port=3306,
master_user='rpl',
master_password='123456',
master_auto_position=0,
master_log_file='mysql-bin.000550',
master_log_pos=1754;

start slave;
show slave status\G

-- 最后再
commit;
set global gtid_mode=off;
show variables like 'gtid_mode';

-- 【3.3.4】检查测试数据是否一致
select count(1) from test.test4;

-- 到这里就算完成了
-------------------

-- 【3.3.5】配置my.cnf，以便下次重启也能生效，注释掉GTID参数即可

[mysqld]
#gtid_mode=1
#enforce_gtid_consistency=1
#slave_log_update=1

-- 【3.3.6】另外一种办法

-- 【3.3.1】从库停止复制，禁止使用master_auto_position，用file于pos重启复制
-- 在从库
stop slave;
show slave status\G -- 查看一下io线程读取到主库的哪个Binlog文件及其 pos位置

-- (如果账号密码等没有变化，可以只写最后3行，即 change master to master_log.....)
change master to 
master_host='192.168.1.201',
master_port=3306,
master_user='rpl',
master_password='123456',
master_auto_position=0,
master_log_file='mysql-bin.000550',
master_log_pos=1754;

start slave;
show slave status\G
-- 这里必须确认数据同步情况，因为当前主从gtid_mode都是on，还可以运行GTID事务，等待把GTID事务都跑完同步完。
-- 【3.3.2】主从都跑--修改gtid_mode
commit;
set @@global.gtid_mode=on_permissive;
commit;
show variables like '%gtid_mode%';
set @@global.gtid_mode=off_permissive;
commit;
set @@global.gtid_mode=off;
commit;
show variables like '%gtid_mode%';

【报错】ERROR 1766 (HY000): The system variable gtid_mode cannot be set 
       when there is an ongoing transaction.
【解决】commit;  -- 提交一下空事务即可
-- 【3.3.3】检查测试数据是否一致
select count(1) from test.test4;

-----------

【4】增加节点 203 为只复制 test 库的从库

【4.1】在主库 mysqldump 备份 test 库（也可以在从）

mysqldump --databases test --single_transaction --master-data=2  -R -uroot -p123456  -E >/mysql/backup/test.bak
# --dump-slave可以获取主库的binlog文件名和位置及pos
# 如果是在从库上，加上 --dump-slave=2 即可（但要去掉--master-data=2）

【4.2】修改配置文件（只复制test库）

#-- 最初配置中的全部要加上
#-- 包含主、从、半同步、gtid 参数
[mysqld]

replicate_do_db=test
replicate_wild_do_table=test.%

【4.3】开始主从（203从，201主）

stop slave;

change master to 
master_host='192.168.1.201',
master_port=3306,
master_user='rpl',
master_password='123456',
master_auto_position=1;

start slave;
show slave status\G

【4.4】核验

（1）test库数据：

　　　　select count(1) from test.test4;

（2）其他库数据；

-- 《1》主库 201 执行与查看
create database test_2;
create table test_2.test2_1(id int);
insert into test_2.test2_1 values(1);
commit;
-- -------------
select * from test_2.test2_1;

mysql 删除操作加的是什么锁_mysql

-- 《2》从库202 查看

select * from test_2.test2_1;　

mysql 删除操作加的是什么锁_mysql 删除操作加的是什么锁_02

-- 《3》我们新增的从库 203 查看

select * from test_2.test2_1;

show databases;

　　表不存在，新建的库也不存在，实验成功；

mysql 删除操作加的是什么锁_同步复制_03

【5】增加节点204，只复制表test.test4

　　从库3_IP：192.168.1.204　　端口：3306 复制主库201的：test.test4

【5.1】备份

-- 在203上备份
mysqldump -uroot -p123456 --single-transaction -R -E --dump-slave=2 -B test  >/mysql/backup/test.bak

【5.2】配置参数

[mysqld]
replicate_do_table=test.test4
replicate_wild_do_table=test.test4

【5.3】开始操作配置

# 还原
echo 'create database test;'|mysql -uroot -p123456
mysql -uroot -p123456 test </mysql/backup/test4.bak

#复制
change master to 
master_host='192.168.1.201',
master_user='rpl',
master_password='123456',
master_auto_position=1;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。