mysql数据库管理

多列主键：复合主键

事务隔离级别
1.读未提交
2.读已提交
3.可重复读 解决幻读
什么是幻读，一个事务种，第一次查询和第二次查询不一样

SELECT @@GLOBAL.transaction_isolation; #mysql查询事务隔离级别的方法
for update-----排他锁
begin;
select * from table where id >=1 and id < =5 for update ; 间隙所，加上了行锁，其他会话不能对id1-5修改
会显示执行中，会话2插入id=5不成功

MVCC 多版本并发控制

当前读---- 会触发行锁 排他锁
写的操作
insert delete update， for update
快照读----没有加锁
普通select 查询

快照读-》当前读》快照读》 会产生幻读

mysql 安装

curl -O https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.33-linux-glibc2.28-x86_64.tar.gz


mysqld --initialize --user=mysql --datadir=/data/mysql 
    cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start
/etc/init.d/mysqld status

安装脚本

cat install_offline_mysql5.7or8.0_for_centos.sh 
#!/bin/bash

#********************************************************************
#MySQL Download URL: https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
#http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
#http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz
#. /usr/lib/tuned/functions
#. /etc/init.d/functions 
SRC_DIR=`pwd`
MYSQL=mysql-8.0.33-linux-glibc2.28-x86_64.tar.gz
#MYSQL='mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz'
#MYSQL='mysql-8.0.23-linux-glibc2.12-x86_64.tar.xz'
MYSQL_ROOT_PASSWORD=123456


COLOR='echo -e \E[01;31m'
END='\E[0m'


check (){
echo $UID
if [ $UID -ne 0 ]; then
  action "当前用户不是root,安装失败" false
  exit 1
fi

cd  $SRC_DIR


if [ !  -e $MYSQL ];then
        $COLOR"缺少${MYSQL}文件"$END
		$COLOR"请将相关软件放在${SRC_DIR}目录下"$END
        exit
elif [ -e /usr/local/mysql ];then
        action "数据库已存在，安装失败" false
        exit
else
	return
fi
} 

install_mysql(){
    $COLOR"开始安装MySQL数据库..."$END
    yum  -y -q install libaio numactl-libs
    tar xf $MYSQL -C /usr/local/
    MYSQL_DIR=`echo $MYSQL| sed -nr 's/^(.*[0-9]).*/\1/p'`
    ln -s  /usr/local/$MYSQL_DIR /usr/local/mysql
    chown -R  root.root /usr/local/mysql/
    id mysql &> /dev/null || { useradd -s /sbin/nologin -r  mysql ; action "创建mysql用户"; }
        
    echo 'PATH=/usr/local/mysql/bin/:$PATH' > /etc/profile.d/mysql.sh
    .  /etc/profile.d/mysql.sh
	ln -s /usr/local/mysql/bin/* /usr/bin/
    cat > /etc/my.cnf <<-EOF
[mysqld]
server-id=`hostname -I|cut -d. -f4`
log-bin
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
EOF
    [ -d /data ] || mkdir /data
	mysqld --initialize-insecure --user=mysql --datadir=/data/mysql 
    cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
    chkconfig --add mysqld
    chkconfig mysqld on
    service mysqld start
    [ $? -ne 0 ] && { $COLOR"数据库启动失败，退出!"$END;exit; }
    #MYSQL_OLDPASSWORD=`awk '/A temporary password/{print $NF}' /data/mysql/mysql.log`
    #mysqladmin  -uroot -p$MYSQL_OLDPASSWORD password $MYSQL_ROOT_PASSWORD &>/dev/null
	sleep 3
    mysqladmin  -uroot  password $MYSQL_ROOT_PASSWORD &>/dev/null
    action "数据库安装完成" 
}


check

install_mysql

SQL语句

命令

help contents  #查看分类
help  Data Manipulation


SHOW CHARACTER SET;

查看当前字符集
 show variables like 'character%';

utf8mb4

show warnings;  #查看告警提示

字符串类型

varchar和char的使用
长度相差不大，比如都是3-4个长度，用char
varchar 节约空间

变量

mysql> set @luo=333;  #定义变量
Query OK, 0 rows affected (0.01 sec)

mysql> select @luo;  #查看变量


mysql> insert into num values (@luo);#使用变量
Query OK, 1 row affected (0.00 sec)

mysql> select * from num;

表的创建

create table student (id tinyint unsigned primary key auto_increment ,name  char(4) not null ,gender char(1) ,age tinyint unsigned);

show table status\G 查看当前所有的表
show table status like 'student' \G
insert student (name,age,gender)values('xiao',18,'男') ;
insert student values(2,'zhan','女',20);
insert student (name,age,gender)values('li',22,'男') ;
delete from student where id='3'


 select * from student limit 1,2; 跳过前 1条，显示后面2条


count 只统计非空的

select distinct gender from student;  #去重

select avg(age)  from student;# 平均值
select sum(age)  from student; 总和


select gender,avg(age)  from student  group by gender ;  #分组统计

分组查询只能出现分组字段和聚合函数


分组后过滤条件用having
mysql> select gender,avg(age),sum(age)  from student  group by gender having gender='男' ;
+--------+----------+----------+
| gender | avg(age) | sum(age) |
+--------+----------+----------+
| 男     |  24.0000 |       48 |
+--------+----------+----------+
1 row in set (0.00 sec



或者
select gender,avg(age),sum(age)  from student where  gender='男'  group by gender ;




排序
 select * from student order by  age  asc,id   ;

多表查询

联合查询  union
select stuid,name,age,gender from  students union select * from teachers;

union会去重  ，union all 保留重复的


交叉连接
cross join
select * from   students cross join teachers;

内连接
 select s.stuid,s.name,t.name from   students s  inner join teachers t  on s.TeacherID=t.tid;

多表联合查询

select  *    from  students s   inner join   teachers  t on s.TeacherID=t.tid  inner join classes c  on s.classid=c.classid;

左外连接：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外连接：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col

左外连接
select *  from   students s  left join teachers t  on s.TeacherID=t.tid;

右外连接
select *  from   students s  right   outer join teachers t  on s.TeacherID=t.tid;

内连接：
等值连接：让表之间的字段以"等值"建立连接关系

多表联合查询
select st.Stuid,st.name,co.course from students st inner join scores sc on st.StuID=sc.id inner join courses co on sc.courseid=co.courseid;

导入hellodb.sql生成数据库
1. 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄 
select  name,age from students where age>25 and gender='M';

2. 以ClassID为分组依据，显示每组的平均年龄
select avg(age),classid   from students group by classid;
3. 显示第2题中平均年龄大于30的分组及平均年龄
 select avg(age),classid   from students group by classid having avg(age) >30;
4. 显示以L开头的名字的同学的信息
select * from students where name like 'L%';
5. 显示TeacherID非空的同学的相关信息
select * from students where teacherid is not null;
6. 以年龄排序后，显示年龄最大的前10位同学的信息
select * from students order by  age  desc   limit 10 ;
7. 查询年龄大于等于20岁，小于等于25岁的同学的信息
 select * from students where age>=20 and age <=25;
 select * from students where age between 20 and  25;
8. 以ClassID分组，显示每班的同学的人数
 select  count(*) as total_num,classid  from students group by  ClassID;

9. 以Gender分组，显示其年龄之和
select sum(age),gender   from students group by gender;
10. 以ClassID分组，显示其平均年龄大于25的班级
select avg(age)   from  students  group by classid having avg(age) > 25;
11. 以Gender分组，显示各组中年龄大于25的学员的年龄之和
12. 显示前5位同学的姓名、课程及成绩
13. 显示其成绩高于80的同学的名称及课程
select s.name,co.Course,sc.Score from students as  s   inner join scores  as sc  on s.stuid=sc.stuid inner join courses co  on sc.CourseID=co.CourseID where Score>80;
15. 取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩
16. 显示每门课程课程名称及学习了这门课的同学的个数
17. 显示其年龄大于平均年龄的同学的名字
18. 显示其学习的课程为第1、2，4或第7门课的同学的名字
19. 显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
20. 统计各班级中年龄大于全校同学平均年龄的同学
参考https://blog.csdn.net/weixin_33893473/article/details/93028346

创建用户 create user wordpress;
create user wordpress2@'192.168.1.%'  ;
  create user wordpress10@'192.168.1.%'  identified by '123456';
  修改密码
alter user wordpress@'%' identified by '123456';
重命名
rename user wordpress10@'192.168.1.%'   to wordpress222@'%';

删除用户
delete from mysql.user where user='wordpress5'；
drop user luohuiwen@'120.79.60.105';

授权
grant all on wordpress.* to wordpress@'%' ;
 flush privileges;

清空密码
 update mysql.user  set  authentication_string=''  where user='root';
忘记密码

查看变量
show variables like ‘%char%’;
select @@character_set_client;

mysqld --print-defaults 查看当前的选项

索引

create index luo_linux on student (name); 为已有表创建索引

以什么开头，可以走索引
*www 以什么结尾，或者包含不会走索引
走索引

explain

查看是否走索引

mysql> explain select * from students where stuid=22;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+------

不走索引看key，null 不走索引

ysql> explain select * from students where name="X*";
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system >
const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >
range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

key

profiling

查看执行命令的时间

SHOW PROFILE：SHOW PROFILE语句可以启用查询分析器，收集关于查询执行的详细信息。可以使用SHOW PROFILES查看已经收集到的查询分析数据，包括每个阶段的执行时间和资源消耗。

set profiling=1  #开启
show profiles;#查看
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00010875 | select count(*) form mysql.user |
|        2 | 0.00128500 | select count(*) from mysql.user |
+----------+------------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

创建索引
create index idx_name on students(name);

删除索引
how indexes from 表名 #查看索引
drop index salary on testlog;
drop index 索引名 on 表名；

mysql 默认会自动提交事务，修改不用加commit
mysql> select  @@autocommit; #查看是否开启自动提交

原子性
一致性
隔离性
持久性

事务的四个隔离级别

READ UNCOMMITTED 
可读取到未提交数据，产生脏读
READ COMMITTED
可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次
读取数据不一致

数据库用那个隔离级别？
可重复读 不能产生ddL，ddl也不能隔离

mysql的日志

事务日志

所有事务的操作过程

慢查询日志

slow_query_log=ON      
long_query_time=1   #查询超过1秒记录
log_queries_not_using_indexes=ON  #没有使用索引也记录

二进制日志（）

单独存放目录
服务器挂了
同步binlog到远程

基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下），日志量较少基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式
混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

mysql> show variables like 'binlog_format'; #查看二进制格式
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

sql_log_bin=ON|OFF：#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=/PATH/BIN_LOG_FILE：#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED：#二进制日志记录的格式，mariadb5.5默认STATEMENT
max_binlog_size=1073741824：#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m #此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m #限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘expire_logs_days=N：#二进制日志可以自动删除的天数。默认为0，即不自动删除

全局读锁，只能读，不能写

mysql备份与恢复

cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
mysqlbackup：热备份， MySQL Enterprise Edition 组件
mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、
FLUSH TABLES和cp或scp来快速备份数据库

#重新安装mysql
sudo apt-get remove --purge mysql-server mysql-client mysql-common
sudo apt-get autoremove  #卸载所有自动安装且不再使用的软件包
sudo apt-get autoclean
sudo apt-get install mysql-server

mysqldump

#备份数据库，可以备份多个数据库
mysqldump  -uroot -B  hellodb  >/backup/hellodb2.sql
#备份所有数据库
mysqldump  -uroot -A  >/backup/all.sql



--master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-data
#此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复
制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
mysqldump  -uroot -A  --source-data=2  >/backup/all_A.sql




-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，
配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--singletransaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志

–single-transaction
#此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启
事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表
（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储
文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，
RENAME TABLE，TRUNCATE TABLE,此选项和–lock-tables（此选项隐含提交挂起的事务）选项是相互
排斥,备份大型表时，建议将–single-transaction选项和–quick结合一起使用

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob  >${BACKUP}/fullbak_${BACKUP_TIME}.sql

mysqldump -uroot -F -E -R --single-transaction --source-data=2 --default-character-set=utf8 -q -B hellodb

单库备份脚本

cat backup.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
#PASS=
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot   -F -E -R   --single-transaction --source-data=2 --default-character-set=utf8 -q -B ${DB} | gzip > ${DIR}/${DB}_${TIME}.sql.gz

分库备份

mysql -uroot -e 'show databases'|sed -rn  '/^(Database|information_schema|performance_schema|sys)$/!s#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash

误删故障使用mysqldump恢复

当发现数据库误删除，立即停止数据库对外访问，准备一个测试数据库
通过二进制日志找出全备后的二进制日志合并到一个文件后，找出删除的语句并删除，
传输到测试主机，首先进行全备导入，然后导入全备后的二进制日志
对数据库进行验证测试，测试后没问题，可以当生产数据库使用

每天2.30全备 10点误删 10.10发现

必须开启二进制日志
mysql> select @@sql_log_bin;;

mysql> show master logs; #查看位置

全备

mysqldump -uroot -p -A -F --single-transaction --source-data=2 > /backup/allbackup_`date +%F_%T`.sql
-F 刷新binlog认证
--source-data=2  记录blog file信息
查看日志，完全备份的日志文件和pos
-- CHANGE MASTER TO MASTER_LOG_FILE='luohuiwen.000303', MASTER_LOG_POS=157;

2:30-10点数据更新

mysql> use hellodb;
Database changed
mysql>  insert students (name,age,gender) values('rose',20,'f');
Query OK, 1 row affected (0.00 sec)

mysql> insert students (name,age,gender) values('jack',22,'M');

mysql误删数据
mysql> drop table students;

删除数据表后其他表更新
insert teachers (name,age,gender)values(‘wang’,30,‘M’);

insert teachers (name,age,gender)values(‘1806’,30,‘M’);

10点10分发现故障，暂停用户访问（防火墙）

#从完全备份中，找到二进制位置
-- CHANGE MASTER TO MASTER_LOG_FILE='luohuiwen.000316', MASTER_LOG_POS=157;

找出从完全备份后的二进制日志

mysqlbinlog --start-position=157 /data/mysql/luohuiwen.000316 > inc.sql
root@server01:~# mysqlbinlog   /data/mysql/luohuiwen.000316 > inc.sql

打印出误删除的语句，然后删除

root@server01:~# sed  -n  '/DROP/p' inc.sql 
DROP TABLE `students` /* generated by server */
删除误删除的语句
 sed  -i  '/DROP TABLE/d' inc.sql 
 
再过滤找不到
sed  -n  '/DROP TABLE/p' inc.sql 

找一个测试数据库
将数据传输到测试主机

登录mysql
mysql> set sql_log_bin=0;#停止二进制日志

分别导入全备，和二进制日志文件
source /allbackup_2023-08-14_22:05:10.sql
source /inc.sql
还原后开启
set sql_log_bin=1;

验证数据，已经恢复
select *from students;
select * from teachers;

将测试数据地址改为生产地址；或者全备导入生产数据库

mysqldump不支持增量备份

利用 xtrabackup 实现完全备份及还原

下载https://www.percona.com/downloads

安装
dpkg -i percona-xtrabackup-80_8.0.33-28-1.jammy_amd64.deb

在原主机做完全备份到/backup
xtrabackup -uroot --backup --target-dir=/backup/base

拷贝到恢复的主机
scp -rp /backup/base/ 172.16.141.0:/backup

在目标主机上还原

1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
xtrabackup --prepare --target-dir=/backup/base


2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
 xtrabackup --copy-back --target-dir=/backup/base datadir=/var/lib/mysql/
 #datadri= 指定数据目录 ，如果备份和恢复节点数据目录不同可不写，不同要指定数据目录
3）还原属性
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
4）启动服务
[root@centos8 ~]#service mysqld start

测试数据和原主机数据一样

xtrabackup 增量备份

xtrabackup -uroot -p123456 --backup --target-dir=/backup/base

数据更新1
insert students (name,age,gender) values('rose',20,'f');

xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

--incremental-basedir  基于谁进行增量
数据更新2
insert students (name,age,gender) values('jack',22,'M');



xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

预准备
xtrabackup --prepare --apply-log-only --target-dir=/backup/base

--apply-log-only   不回滚
合并第1次增量备份到完全备份：
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
xtrabackup  --datadir=/var/lib/mysql --copy-back --target-dir=/backup/base

mysql主从

注意：安装mysql后克隆虚拟机做主从会出现问题，会提示Fatal error: The replica I/O thread stops because source and replica have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
因为uuid一样
cat /var/lib/mysql/auto.cnf
[auto]
server-uuid=948c9b34-3c2c-11ee-bf72-000c29d9b16f

主节点mysql配置
server-id=67
log_bin=/data/mysql/luohuiwen

create user repluser@‘%’ identified by ‘123456’;
grant replication slave on . to repluser@‘%’;
导出全备
mysqldump -uroot -p123456 -A -F --single-transaction --source-data=1 > /backup/fullbackup_date +%F_%T.sql
–source-data=1 记录 备份是的binlog文件和pos

从节点
server-id=222
read-only=1 #只读。只能对普通用户生效

scp /backup/fullbackup_2023-08-15_20:34:25.sql 172.16.141.0:/root/

修改sql文件更改change master 行，添加主节点信息

CHANGE MASTER TO MASTER_HOST='172.29.21.67',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='luohuiwen.000304',MASTER_LOG_POS=157;

重节点还原备份
set sql_log_bin=0;
mysql -uroot -p123456 < allbackup_2023-08-14_22:05:10.sql
set sql_log_bin=1;

start slave; 启动线程
主

从

Slave_IO_Running: no
Slave_SQL_Running: Yes

主节点flush privileges;

Seconds_Behind_Master: 0 #比主节点差多少秒

插入数据丛库同步
mysql> insert into teachers (name,age) values(“li2”,25);

重节点关机，开机后会同步关机中的数据

slave更改连接主节点的信息
首先在master上记录pos和file

stop slave ;8.0.22弃用改用stop replica
mysql> STOP REPLICA;
Query OK, 0 rows affected (0.00 sec)
mysql> reset slave all ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G
Empty set, 1 warning (0.00 sec)

停止期间发生数据更新insert into teachers (name,age,gender) values(“song”,35,‘M’);

指定
mysql> CHANGE MASTER TO MASTER_HOST=‘172.29.21.67’,MASTER_USER=‘repluser’,MASTER_PASSWORD=‘123456’,MASTER_PORT=3306,MASTER_LOG_FILE=‘luohuiwen.000310’,MASTER_LOG_POS=630;
Query OK, 0 rows affected, 9 warnings (0.01 sec)
get_master_public_key=1
mysql> start slave;

从库出现 Last_IO_Error: Error connecting to source 'repluser@172.29.21.67:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

添加get_master_public_key=1
1.从库执行 stop slave;

2.清除从库配置：reset slave all;

3.重新配置主库信息
CHANGE MASTER TO MASTER_HOST='172.29.21.67',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='luohuiwen.000315',MASTER_LOG_POS=1104,get_master_public_key=1;

从节点添加记录
insert into teachers (name,age,gender) values(“song”,35,‘M’);

主节点添加记录，从节点会产生冲突，主从数据不一致
insert into teachers (name,age,gender) values(“haha”,38,‘M’);

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘ANONYMOUS’ at source log luohuiwen.000310, end_log_pos 1201. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

解决方法一： 跳过这条错误
stop slave; #停止主从关系
mysql> set global sql_slave_skip_counter=1; #1跳过一条错误，all跳过所有
start slave;#启动

slave_skip_errors=1007 跳过代码

会数据不一致，手动改为主节点一样的
update teachers set name=‘haha’,age=‘38’ where Tid=9;
解决方法二： 重新做主从

备份单表
mysqldump -uroot -p hellodb teachers > /backup/teachers.sql

lock table teachers read;锁表
unlock tables ;

多个slave怎么选从提升为主

看show slave status \， log_pos 谁大
Master_Log_File: luohuiwen.000310
Read_Master_Log_Pos: 2130

其他指向新主

从节点二进制不会记录主节点传来的中继日志更新数据，要开启log_slave_updates,8.0默认开启，作为级联复制的中点位置机器好开启

双主

环境：搭建好主从
在一个节点复制

由于从节点的数据和主一样的，有授权账号，如果主只是单独ip授权，则要在从创建账号和授权

从节点查看：

mysql> show  master  status  ;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000010 |  7164771 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

主节点指定从节点信息

CHANGE MASTER TO MASTER_HOST=‘172.16.141.0’,MASTER_USER=‘repluser’,MASTER_PASSWORD=‘123456’,MASTER_PORT=3306,MASTER_LOG_FILE=‘binlog.000010’,MASTER_LOG_POS=7164771;

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status \G
在各个节点插入数据都可以同步数据，验证成功

不要在二个数据同时写数据

               Last_SQL_Errno: 1062
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log luohuiwen.000310, end_log_pos 3893. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

跳过错误代码：slave_skip_errors=1062

主节点端口是127.0.0.1,开启成功后，要stop slave;重启

置这些参数决定了自增长列的增长步长和初始值。
mysql> SET @@auto_increment_increment = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@auto_increment_offset = 2

create user rep@‘192.168.1.%’ identified by ‘123456’;

mysql> grant replication slave on . to rep@‘192.168.1.%’;

半同步复制

MySQL半同步复制是一种数据库复制技术，用于提高数据的可靠性和一致性。在半同步复制中，主数据库将事务写入二进制日志（binary log），然后等待至少一个从数据库将该事务成功写入其本地日志后，才认为该事务已经提交。这样可以确保至少有一个从数据库与主数据库保持一致。

半同步复制的原理如下：

1. 主数据库接收到一个事务后，将其写入二进制日志（binary log）。
2. 主数据库等待至少一个从数据库将该事务成功写入其本地日志。
3. 从数据库接收到主数据库的事务后，将其写入本地日志。
4. 从数据库将该事务应用到自己的数据文件，使其与主数据库保持一致。
5. 从数据库向主数据库发送一个确认消息，表示该事务已经成功写入本地日志。
6. 主数据库收到从数据库的确认消息后，认为该事务已经提交。

通过使用半同步复制，可以提高数据的可靠性，因为只有在至少一个从数据库成功写入事务后，主数据库才认为该事务已经提交。这样可以避免主数据库和从数据库之间的数据不一致问题。然而，半同步复制也会增加主数据库的写入延迟，因为主数据库需要等待从数据库的确认消息。

请注意，半同步复制在MySQL中是一个可选的功能，需要在配置文件中进行相应的设置。

当超过超时时间从库还没有写入，直接返回成功

环境：ubuntu20.04 部署好主从复制
192.168.1.147 主
192.168.1.147 备
192.168.1.148 备
查看半同步插件

find / -name semis*
/usr/lib/mysql/plugin/semisync_master.so
/usr/lib/mysql/plugin/semisync_replica.so
/usr/lib/mysql/plugin/semisync_slave.so

centos要先安装插件再启用插件，ubuntu直接可添加配置文件启动插件

2.4.1、主库配置
直接在配置文件中设置永久配置

修改 vi /etc/mysql/mysql.conf.d/mysqld.cnf 配置在[mysqld]下面添加对应配置

设置半同步插件
plugin-load="rpl_semi_sync_master=semisync_master.so"
#添加或修改下行,需要先安装semisync_master.so插件后,再重启,否则无法启动
rpl_semi_sync_master_enabled=ON 
#加或修改下行,设置3s内无法同步，也将返回成功信息给客户端
rpl_semi_sync_master_timeout=3000
2.4.2、从库配置
修改 vi /etc/mysql/mysql.conf.d/mysqld.cnf 配置在[mysqld]下面添加对应配置

 设置半同步插件
plugin-load="rpl_semi_sync_slave=semisync_slave.so"
 #添加或修改下行,需要先安装semisync_master.so插件后,再重启,否则无法启动
rpl_semi_sync_slave_enabled= 1

查看插件
show plugins;
pl_semi_sync_master            | ACTIVE   | REPLICATION        | semisync_master.so | GPL  
| rpl_semi_sync_slave             | ACTIVE   | REPLICATION        | semisync_slave.so | GPL 

查看超时时间
mysql> select @@rpl_semi_sync_master_timeout;

查看半同步状态有二个客户端连接
 SHOW GLOBAL STATUS LIKE '%semi%';

测试半同步复制：
停止任一个重节点，可以很快同步
停止所有的从节点，插入数据要等待3秒才能返回，因为设置了超时时间3秒，没有同步也返回成功
insert teachers (name,age,gender)values(“huihui”,“25”,“M”);

错误一：

Error connecting to source 'rep@192.168.1.147:3306'. This was attempt 30/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
以去主库mysql上查询当前用户信息

SELECT plugin FROM `user` where user = 'repluser';

原来是主库repluser的plugin是caching_sha2_password 导致连接不上，修改为mysql_native_password即可解决。
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

产生错误二：
错误一解决后产生错误二

 Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log luohuiwen.000052, end_log_pos 453. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

到主库查询luohuiwen.000052文件，发现更新了用户信息，但丛库没有，所以保存

ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

mysqlbinlog luohuiwen.000052

配置跳过1396代码
slave_skip_errors=1396

开启主从复制前要把主库数据导入到丛库，这样才能一致，不会产生主库有的数据丛库没有

复制过滤器

从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库(特定表)相关的事件并应用于本
地
缺点：会造成网络及磁盘IO浪费,在所有从节点都要配置
优点: 不影响二进制备份还原
从服务器上的复制过滤器相关变量：
replicate_ignore_db= #指定复制库黑名单
replicate_do_table= #指定复制表的白名单
replicate_ignore_table= #指定复制表的黑名单
replicate_wild_do_table= foo%.bar% #支持通配符

GTID复制

主从节点添加
gtid_mode=ON
enforce_gtid_consistency
重节点
CHANGE MASTER TO MASTER_HOST='192.168.1.147',MASTER_USER='rep',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
 start slave;
 show slave  status \G
 主库添加配置
 

gtid复制批量跳过错误

STOP SLAVE;
global gtid_purged=“f776edc7-3c2a-11ee-9cd7-000c29420734:11-10000”;#跳过11-10000
start SLAVE;

跳过一条
SET GTID_NEXT=“7d72f9b4-8577-11e2-a3d7-080027635ef5:5”;

MHA

架构

192.168.1.147 mysql主
192.168.1.148 从
192.168.1.149 从
192.168.1.150 MHA
工作原理：

1. MHA利用 SELECT 1 As Value 指令判断master服务器的健康性,一旦master 宕机,MHA 从宕机崩溃
   的master保存二进制日志事件（binlog events）
2. 识别含有最新更新的slave
3. 应用差异的中继日志（relay log）到其他的slave
4. 应用从master保存的二进制日志事件（binlog events）到所有slave节点
5. 提升一个slave为新的master
6. 使其他的slave连接新的master进行复制
7. 故障服务器自动被剔除集群(masterha_conf_host),将配置信息去掉
8. MHA是一次性的高可用性解决方案,Manager会自动退出

部署

提前做好主从，
所有节点安装mha-node，管理节点安装mha-manager
mha4mysql-node_0.58-0_all.deb
mha-manager只能在centos7，而且要安装mha-node，安装失败可用yum update
150安装mha-manager和mha-node
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm

所有节点做好密钥同步，同步到其他节点
ssh-keygen
ssh-copy-id 127.0.0.1
rsync -av .ssh 192.168.1.147:/root/

manage端配置文件
cat /etc/mastermha/app1.cnf
[server default]
user=mha
password=123456
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=rep
repl_password=123456
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
check_repl_delay=0
master_binlog_dir=/var/lib/mysql
[server1]
hostname=192.168.1.147
port=3306
[server2]
hostname=192.168.1.148
port=3306
[server3]
hostname=192.168.1.149
port=3306
chmod +x /etc/mastermha/app1.cnf
chmod +x /usr/local/bin/master_ip_failover

主mysql配置vip1.122

检查mha环境

  268  masterha_check_ssh --conf=/etc/mastermha/app1.cnf  #检查ssh免密要登录环境
  269  masterha_check_repl --conf=/etc/mastermha/app1.cnf
[root@localhost ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 is stopped(2:NOT_RUNNING)

启动mha

#开启MHA,默认是前台运行,生产环境一般为后台执行

nohup masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf --ignore_last_failover &> /dev/null

[root@localhost ~]# masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf --ignore_last_failove
Wed Nov 15 21:00:18 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Nov 15 21:00:18 2023 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Wed Nov 15 21:00:18 2023 - [info] Reading server configuration from /etc/mastermha/app1.cnf..

master 在147上

[root@localhost ~]# masterha_check_status --conf=/etc/mastermha/app1.cnf
app1 (pid:8429) is running(0:PING_OK), master:192.168.1.147

模拟故障
#模拟故障
[root@master ~]#systemctl stop mysqld
#当 master down机后,mha管理程序自动退出

148 成为master ，149 设置148为主

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.1.148

MHA日志，会停止
[root@localhost ~]# masterha_manager --conf=/etc/mastermha/app1.cnf --remove_dead_master_conf --ignore_last_failove
Wed Nov 15 21:00:18 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Nov 15 21:00:18 2023 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Wed Nov 15 21:00:18 2023 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
	LANGUAGE = (unset),
	LC_ALL = (unset),
	LC_TIME = "en_DK.UTF-8",
	LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to a fallback locale ("en_US.UTF-8").
  Creating /data/mastermha/app1 if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /var/lib/mysql, up to luohuiwen.000071
Wed Nov 15 21:02:35 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Nov 15 21:02:35 2023 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Wed Nov 15 21:02:35 2023 - [info] Reading server configuration from /etc/mastermha/app1.cnf..

查看mha配置文件会,将master剔除

[root@localhost ~]#  cat /etc/mastermha/app1.cnf
[server default]
check_repl_delay=0
manager_log=/data/mastermha/app1/manager.log
manager_workdir=/data/mastermha/app1/
master_binlog_dir=/var/lib/mysql
master_ip_failover_script=/usr/local/bin/master_ip_failover
password=123456
ping_interval=1
remote_workdir=/data/mastermha/app1/
repl_password=123456
repl_user=rep
ssh_user=root
user=mha

[server2]
hostname=192.168.1.148
port=3306

[server3]
hostname=192.168.1.149
port=3306

主库宕机后如何恢复？

mha管理节点也可以查看
[root@localhost mastermha]# grep 'CHANGE MASTER TO'  /data/mastermha/app1/manager.log
Fri Aug 18 09:13:01 2023 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.148', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
Wed Nov 15 21:02:38 2023 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.148', MASTER_PORT=3306, MASTER_LOG_FILE='mysql2-bin.000003', MASTER_LOG_POS=197, MASTER_USER='rep', MASTER_PASSWORD='xxx';
Mon Nov 27 14:11:45 2023 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.147', MASTER_PORT=3306, MASTER_LOG_FILE='luohuiwen.000075', MASTER_LOG_POS=197, MASTER_USER='rep', MASTER_PASSWORD='xxx';

登录宕机的旧库，show master status\G 查看pos
指定新主

 CHANGE MASTER TO MASTER_HOST='192.168.1.147', MASTER_PORT=3306, MASTER_LOG_FILE='luohuiwen.000075', MASTER_LOG_POS=197, MASTER_USER='rep', MASTER_PASSWORD='xxx';

测试：在新主上插入数据，丛库查询有数据
use test；
insert into testlog values(29759,“luohuiwen”,“888888”);

select * from testlog;

错误

masterha_check_repl --conf=/etc/mastermha/app1.cnf
检测中会遇到很多错误

错误1
 masterha_check_ssh --conf=/etc/mastermha/app1.cnf 
Fri Aug 18 07:58:39 2023 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
/etc/mastermha/app1.cnf:
 at /usr/share/perl5/vendor_perl/MHA/SSHCheck.pm line 148.
app1.cnf中文件多加了东西

错误2
[root@localhost ~]#  masterha_check_repl --conf=/etc/mastermha/app1.conf
Fri Aug 18 08:08:12 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln283] Configuration file /etc/mastermha/app1.conf not found!
Fri Aug 18 08:08:12 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/bin/masterha_check_repl line 48.
Fri Aug 18 08:08:12 2023 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Fri Aug 18 08:08:12 2023 - [info] Got exit code 1 (Not master dead).
解决办法
mysql5 默认加密方式是 my_native_password
8.0用户默认加密方式是caching_sha2_password，修改为5.6版本的加密方式
ALTER USER 'mha'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
还要测试其他机器能连接mha用户

select user,host,plugin from mysql.user;

错误三：

连接不了数据库
测试主从用户148和149主从用户连接不了，发现148和149没有创建主从用户rep，
创建并授权

启动mha

主库批量插入数据，停止主服务器，会发生切换，主库切换到1.148，vip切换到1.148，从库1.149的主服务器变为1.148，数据一致

PXC

配置源

[root@localhost ~]# cat  /etc/yum.repos.d/pxc.repo 
[percona]
name=percona_repo
baseurl = https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0

拷贝到其他节点
scp /etc/yum.repos.d/pxc.repo 192.168.1.52:/etc/yum.repos.d
scp /etc/yum.repos.d/pxc.repo 192.168.1.152:/etc/yum.repos.d

yum makecache

yum install Percona-XtraDB-Cluster-57 -y

修改配置文件

vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://192.168.1.151,192.168.1.152,192.168.1.153
wsrep_node_address=192.168.1.151#当前ip地址

wsrep_cluster_name=pxc-cluster-n80

#If wsrep_node_name is not specified,  then system hostname will be used
wsrep_node_name=pxc-cluster-node-1 #节点名，每个节点不一样

拷贝到其他节点

启动
启动命令不一样

第一个节点
systemctl start mysql@bootstrap.service


grep "temporary password" /var/log/mysqld.log
2023-08-18T21:27:35.642836Z 1 [Note] A temporary password is generated for root@localhost: EqmoWgoni4)w
#修改root密码
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
#创建相关用户并授权
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';



SHOW VARIABLES LIKE 'wsrep%'\G

启动其他集群节点

systemctl start mysql

创建数据库和表，可以同步
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> use db1;
Database changed
mysql> create table t1(id int auto_increment primary key,mame char(20));
insert into t1 values(‘4’,‘a’);

pxe的优势：所有节点可以同时插入数据
添加节点
所有节点配置文件wsrep_cluster_address选项添加ip，和节点名
新节点启动
systemctl start mysql

Mycat

tail /apps/mycat/logs/wrapper.log
mysql -uroot -p123456 -h 127.0.0.1 -P8066

vim /apps/mycat/conf/server.xml

主库
mysql> create user admin@‘192.168.1.%’ identified by ‘123456’;
Query OK, 0 rows affected (0.03 sec)

mysql> grant all on hellodb.* to admin@‘192.168.1.%’;