Mysql数据库----4高级（运维进阶）篇------

　　mysql优化之磁盘io-----------------------------------------

[mysql]
default_character_set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#lower_case_table_names=1 忽略大小写
character_set_server=utf8
server_id=2
log_bin=mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default_character_set = utf8
[mysql.server]
default_character_set = utf8
[client]
default_character_set = utf8

mysql数据库备份,我们主要采用3种策略
1 使用mysqldump,
优点是mysql自带,不需要安装,可以备份单库单表对备份的数据可以选择.
缺点是备份的时候会锁表,导致数据库长时间不能写入,可能导致平台业务的暂时中断
用途主要是用来备份数据库表结构和测试数据,用于新环境的搭建和测试

2 使用xtrabackup,
优点备份速度快,锁表时间较短,可以远程备份,
缺点无法只备份表结构,innodb引擎下无法备份单表
用途主要是用来进行数据库的日常备份,用于数据库被攻击或彻底损坏时的数据恢复,备份全量数据,备份策略是根据数据量大小决定的,如使用定时任务加脚本的方式,进行每周一次全备份,每天一次增量备份.

3 实时同步备份,
优点数据实时备份,出现故障时丢失数据极少
缺点当主库数据被人为删除时,会同步主库导致备份数据也同时删除了这些数据
主要是用来实现数据库的快速切换和高可用,如MHA,PXC,Xenon等数据库高可用工具.

Mysql高可用架构之HMA：https://blog.csdn.net/hahaxixi131/article/details/122282665

gtid数据恢复和原理：https://www.css3er.com/p/260.html

[root@db01 app]# mysql -e "select @@enforce_gtid_consistency ; 
[root@dbo1 app]# mysql -e "select @@gtid_mode ; "

[root@db01 data]# mysql -e "set global enforce_gtid_consistency =warn; "

mysq1 -e "set global enforce-gtid_consistency = on; "
mysql -e "set global gtid_mode = off_permissive ; 
mysq1 -e "set global gtid_mode=on_permissive ; "
mysq1 -e "show status like 'ongoing_anonymous_transaction_count ' ; "
mysql -e "flush logs ; "

mysq1 -e "stop slave; change master to master_auto_position=1;start slave; "
mysql -e "show slave status /G"
#修改配置文件永久生效
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

---------------------------半同步和增强半同步

开启半同步教程：https://blog.csdn.net/JavaShark/article/details/125124748

　　　　　　　　　https://www.cnblogs.com/fengjian2016/p/12357379.html

半同步复制

从MySQL5.5开始，引入了半同步复制，此时的技术暂且称之为传统的半同步复制，因该技术发展到MySQL5.7后，已经演变为增强半同步复制(也成为无损复制)。在异步复制时，主库执行Commit提交操作并
写入BINLOG日志后即可成功返回客户端，无需等待BINLOG日志传送给从库，如图所示。

而半同步复制时，为了保证主库上的每一个BINLOG事务都能够被可靠地复制到从库上，主库在每次事务成功提交时，并不及时反馈给前端应用用户，而是
等待至少一个从库(详见参数rpl_semi_sync_master_wait_for_slave_count)也接收到BINLOG事务并成功写入中继日志后，主库才返回Commit操
作成功给客户端(不管是传统的半同步复制，还是增强的半同步复制，目的都是一样的，只不过两种方式有一个席位地方不同，将在下面说明)

半同步复制保证了事务成功提交后，至少有两份日志记录，一份在主库的BINLOG日志上，另一份在至少一个从库的中继日志Relay Log上，从而更进一步保证了
数据的完整性。

半同步复制分为传统的半同步复制和增强半同步复制（也称无损复制）

在传统的半同步复制中，主库写数据到BINLOG，且执行Commit操作后，会一直等待从库的ACK，即从库写入Relay Log后，并将数据落盘，返回给主库消息，通知主库可以返回前端应用操作成功，
这样会出现一个问题，就是实际上主库已经将该事务Commit到了事务引擎层，应用已经可以看到数据发生了变化，只是在等待返回而已，如果此时主库宕机，有可能从库还没能写入Relay Log，就会发生主从库不一致。
增强半同步复制就是为了解决这个问题，做了微调，即主库写数据到BINLOG后，就开始等待从库的应答ACK，直到至少一个从库写入Relay Log后，并将数据落盘，然后返回给主库消息，通知主库可以执行Commit操作，
然后主库开始提交到事务引擎层，应用此时可以看到数据发生了变化。增强半同步复制的大致流程如下图所示。

mysql5.7以后半同步都使用增强半同步，5.6之前使用传统半同步

5.3 主从复制延迟问题

------------------------------多sql线程组提交并行回放

这里为了增加一组事务中的事务数量，提高刷盘收益，MySQL使用两个参数控制获取队列事务组的时机：
 
        binlog_group_commit_sync_delay=N：在等待N μs后，开始事务刷盘(图中Sync binlog)
 
        binlog_group_commit_sync_no_delay_count=N：如果队列中的事务数达到N个，就忽视binlog_group_commit_sync_delay的设置，直接开始刷盘(图中Sync binlog)
 
Sync阶段队列的作用是支持binlog的组提交
 
如果在这一步完成后数据库崩溃，由于协调者binlog中已经有了事务记录，MySQL会在重启后通过Flush 阶段中Redo log刷盘的数据继续进行事务的提交

　　 具体组复制原理：https://mp.weixin.qq.com/s/_LK8bdHPw9bZ9W1b3i5UZA

--------------------------延时从库的应用【延时复制】（做备份数据库，延时时间由业务以及到场的时间来定，一般3-6小时）

sql_delay   #延时执行sql的时间，秒为单位
sql_remaining_delay    #最近一次事务执行所需的剩余时间

一、第一章

1.1 过滤复制

replicate_do_db=test              #库级别白名单
replicate_ignore_db=             #库级别黑名单

replicate_do_tab1e=wor1d.city       #表级别白名单
replicate_ignor e_tab1e=           #表级别黑名单

replicate_wild_do_table=wor ld.t*      #支持通配符模式匹配
replicate_wild_ignore_table=

　　　　　　1.2 多源复制

　　　　　　　　　　　　　　　　　　　　　----------8.0之前是不支持通配符匹配的。

　　　　　　　　1.3 MGR组复制

　　　　　　　　——————————MGT的搭建

cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysq1/datadir=/data/3306/data
socket=/tmp/mysq1.sock
server_id=51
port=3306
secure-file-priv=/tmp
1og_bin=/data/3306/binlog/mysq1-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
skip_name_resolve
master_info_repository=TABLE       #数据库状态信息以数据表的形式保存，而不是以文件
relay_log_info_repository=TABLE
report_host=10.0.0.51             #作为从库是需要提交自己的ip信息
report_port=3306　　　　　　　　　　　
socket=/tmp/mysq1.sock
defau1t_authentication_plugin=mysql_native_password
binlog_checksum=NONE
binlog_transaction_dependency_tracking = WRITESET    #sql多线程复制以及MGR复制要打开的参数
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="ca842376-1c50-42ac-bb57-a5adc7da7a12"  #uuid，
loose-group_replication_start_on_boot=OFF                               #MGR是否开启
loose-group_replication_local_address= "10.0.0.51:33061"             #本地节点的ip加端口 
loose-gr oup_replication_group_seeds= "10.0.0.51:33061,10.0.0.52:33062,10.0.0.53:33063"    #整个集群的所有节点ip
loose-group_replication_bootstrap_group=OFF           #是否初始化集群
[mysql]
prompt = dbo1 [\\d]>
EOF

g．设置本地root用户密码和密码插件（所有节点)
mysql -s /tmp/mysql.sock -e "ALTER USER 'root '@ 'loca1host' IDENTIFIED WITH mysql_native_password BY '123';"
h．安装MGR插件(所有节点)
mysq1 -uroot -p123 -s /tmp/mysq1.sock -e "INSTALL PLUGIN group_replication SONAME 'group_replication.so';"
i.设置复制账号(所有节点)
[root@db03 83306]# mysql -uroot -p123 -s /tmp/mysq1.sock
SET SQL_LOG_BIN=0;
CREATE USER repl@'%’IDENTIFIED BY '123';
CREATE USER repl@'localhost' IDENTIFIED BY '123';
CREATE USER rep1@'127.0.0.1' IDENTIFIED BY '123';
GRANT REPLICATION SLAVE,replication client ON *.* TO repl@'%';
grant replication slave,replication client on *.* to repl@'localhost';
grant replication slave,replication client on *.* to repl@'127.0.0.1';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;　　

j．启动MGR单主模式
#启动MGR，在主库(10.0.0.51)上执行
CHANGE MASTER TO MASTER_USER='repl'，MASTER_PASSWORD='123' FOR CHANNEL 'group_replication_recovery';
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_gr oup=OFF;
SELECT * FROM performance_schema.replication_group_members;

补充:8.0
SELECT group_replication_set_as_primary('17f0cf8d-65fd-11ec-9aef-000c2932383b');   #切换主节点
SELECT group_replication_switch_to_single_primary_mode();
SELECT group_replication_switch_to_multi_primary_mode();