MySQL

MySQL
MySQL是一个关系型数据库管理系统。

MySQL的存储引擎
1.InnoDB存储引擎
InnoDB是MySQL5.5开始默认的存储引擎，被设计用来处理大量的短期事物；有自动崩溃恢复的特性。
2.MyISAM存储引擎
在MySQL5.5，MyISAM是默认的存储引擎。MyISAM提供了全文检索、压缩、空间函数等特性，但MyISAM不支持事物和行级锁，而且崩溃后无法安全恢复。

隔离级别（MySQL默认是可重复读）
1.读未提交
事务中的修改，即使没有提交，对其他事务也都是可见的。会导致脏读、不可重复读、幻读
2.读已提交
会导致不可重复读、幻读
3.可重复读
会导致幻读，InnoDB通过多版本并发控制（MVCC）解决幻读问题
4.串行化
避免了脏读、可重复读、幻读

事务的四个特性：ACID
原子性(Atomicity)：原子性是指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。
一致性(Consistency)：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。换一种方式理解就是：事务按照预期生效，数据的状态是预期的状态。
隔离性(Isolation)：并发访问数据库时，多个事务之间要相互隔离，不受其它事务的干扰。
持久性(Durability)：事务一旦被提交，它对数据库中数据的改变是永久性的，即使数据库发生故障也不应该对其有影响。

InnoDB逻辑存储结构
在InnoDB存储引擎中，所有数据都被逻辑地存放在表空间(tablespace)里面。表空间又由段(segment)、区(extent)、页(page)组成。
表空间：InnoDB存储引擎逻辑结构的最高层，默认为ibdata1。
段：常见的段有数据段，索引段，回滚段等。
区：每64个连续的页组成区，因此区大小正好为1M。
页：页是InnoDB存储引擎中最小的磁盘单位，默认大小为16K。

常见的页类型有
数据页(B-tree Node)
Undo页(Undo Log Page)
系统页(System Page)
事务数据页(Transaction system Page)
插入缓冲位图页(Insert Buffer Bitmap)
插入缓冲空闲列表页(Insert Buffer Free List)
未压缩的二进制大对象页(Uncompressed BLOB Page)
压缩的二进制大对象页(Compressed BLOB Page)

InnoDB数据页组成部分
File Header(文件头)
Page Header(页头)
Infimun + Supremum Records
User Records(用户记录，即行记录)
Free Space(空闲空间)
Page Directory(页目录)
File Trailer(文件结尾信息)

索引
索引按数据结构划分(show index from 表名;可通过该语句查询索引类型)
1.B+树索引
B+树是一个平衡多叉查找树，左右子树的高度之差不超过1。B+树对索引列进行排序存储，因此很适合查找范围数据。

2.哈希索引
哈希索引把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置。但是哈希索引不支持范围查询、不支持索引排序、不支持联合索引的最左匹配规则。

3.R-Tree(空间数据索引)
R-Tree用作地理数据存储，必须使用MySQL的GIS相关函数来维护。很少使用。

4.全文索引
MySQL5.6开始，InnoDB也支持全文索引。全文索引是一种特殊类型的索引，它查找的是文本中的关键字，而不是直接比较索引中的值。全文索引更类似于搜索引擎做的事情，而不是简单的where条件匹配。

索引按表现形式划分
主键、唯一、全文、普通、组合
alter table a add primary key(column);
create unique index index_a on a(column);
create fulltext index index_a on a(column);
create index index_a on a(column);
create index index_a on a(column,column);

MySQL数据库优化
一.SQL语句优化
1.1避免使用*号，需要什么列就返回什么列。
1.2where和order by涉及的列上考虑建立索引。
1.3尽量避免在where子句中对列进行null值判断。
1.4尽量避免在where子句中使用!=或<>操作符。
1.5尽量避免在where子句中使用or来连接条件。
1.6尽量避免在where子句中对列进行函数操作。
1.7避免使用in和not in，否则会导致全表扫描，对于连续的数值，可用between代替in。
1.8使用like模糊查询时，“%”放在前面会导致索引失效。
1.9尽量用exists代替in。
1.10尽量使用“>=”，不要使用“>”。
1.11使用union all替代union。union会排除重复记录。

二.索引优化
索引类型:主键、唯一、全文、普通、组合
alter table a add primary key(column);
create unique index index_a on a(column);
create fulltext index index_a on a(column);
create index index_a on a(column);
create index index_a on a(column,column);

索引使用场合
1.频繁作为查询条件的列应该创建索引。
2.频繁更新的列不适合创建索引。
3.唯一性太差的列不适合创建索引。

MySQL索引失效的场景
1.使用函数。
2.使用不等于操作符。
3.使用了null判断。
4.使用like时从前面匹配。
5.使用or时其中一个字段没有索引。
6.类型转换。
7.复合索引时没有遵循最左前缀原则。
8.没有使用InnoDB存储引擎。

三.读写分离
读写分离解决方案：应用层解决和中间件解决

应用层
优点：
多数据源切换方便，由程序自动完成。
不需要引入中间件。
理论上支持任何数据库。
缺点：
由程序员完成，运维参与不到。
不能做到动态增加数据源。

中间件
优点：
源程序不需要做任何改动就可以实现读写分离。
动态添加数据源不需要重启程序。
缺点：
程序依赖于中间件，会导致切换数据库变得困难。
由中间件做了中转代理，性能有所下降。

四.垂直切分
把数据按模块划分到不同数据库表中，如果一个模块的数据量太大就会存在性能瓶颈。

五.水平切分
同一个模块下，把数据按某种规则划分到不同的数据库表中。垂直切分可以使模块的划分更清晰，分成功能不同的表；水平切分可以解决大数据下大表性能的瓶颈问题。

安装MySQL
https://blog.csdn.net/m0_49605579/article/details/115381800
http://blog.itpub.net/70003733/viewspace-2865510/
https://blog.csdn.net/qq_34994855/article/details/123666825

查看MySQL版本
1.linux：mysql -V
2.mysql：select version();

连接MySQL
mysql -hlocalhost -P3306 -uroot -proot --skip-ssl

设置root账号可远程连接
update mysql.user set host='%' where user='root';

刷新权限
flush privileges;

查看数据库
show databases;

创建数据库
create database sb1 default character set utf8mb4 collate utf8mb4_0900_ai_ci;

连接数据库
use sb1;

查看所有表
show tables;

创建用户（MySQL8）
create user 'cs'@'%' identified by 'Cs123456';

授权
grant all privileges on . to 'cs'@'%';
grant all privileges on 数据库.* to '用户名'@'IP地址' with grant option;

修改加密规则
alter user 'cs'@'%' identified with mysql_native_password by 'Cs123456';

修改当前用户的密码（MySQL8）
set password = 'Cs123456';
flush privileges;

备份数据库
mysqlpump -uroot -proot sb1 > E:\MysqlBackup\jsbb.sql
mysqlpump -uroot -proot sb1 | gzip > E:\MysqlBackup\jsbb.sql.gz
mysqlpump -hlocalhost -P3306 -uroot -proot --single-transaction --default-parallelism=8 jsbb > E:\MysqlBackup\jsbb.sql

备份表
https://www.cnblogs.com/kevingrace/p/9760185.html
mysqlpump -uroot -proot --no-create-db --no-create-info jsbb sys_user sys_role > E:\MysqlBackup\jsbb.sql
mysqlpump -uroot -proot sb1 sys_user > D:\sb1.sys_user.sql

还原数据
mysql -uroot -proot --default-character-set=utf8mb4 jsbb < E:\MysqlBackup\jsbb.sql
source E:\MysqlBackup\jsbb.sql

-- 查看安装目录
show variables like 'basedir%';

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

-- 查看最大连接数
show variables like "max_connections";
set global max_connections = 10000;

-- 查看当前连接数
show status like 'threads_connected';

-- 查看是否开启二进制日志
show variables like 'log_bin%';

-- 查看最大数据包
show variables like 'max_allowed_packet';
set global max_allowed_packet = 10241024100;

-- 查询日志是否开启
show variables like 'general_log';

-- 查询日志日志保存位置
show variables like 'general_log_file';

-- 查询日志输出类型,table或file
show variables like 'log_output';

-- 开启查询日志
set global general_log = on;

-- 查看隔离级别
show variables like 'transaction_isolation';
set global transaction isolation level read uncommitted;
set global transaction isolation level repeatable read;

-- 复制表数据到其它表
insert into sys_user select * from sys_user2;
insert into sys_user(colum1,colum2) select colum1,colum2 from sys_user2;

添加字段
alter table sys_user add type varchar(36) not null comment '类型';

修改字段
alter table sys_user modify column type varchar(36) not null comment '类型';

重命名字段
alter table sys_user change login_name user_name varchar(20) not null comment '用户名';

root账号没有权限
service mysqld stop
mysqld --user=mysql --skip-grant-tables --skip-networking &
mysql -u root -p -hlocalhost
输入任意密码
use mysql;
update mysql.user set Grant_priv='Y', Super_priv='Y' where user='root';
flush privileges;
grant all on . to 'root'@'localhost';
flush privileges;
quit

启动MySQL
service mysqld start
systemctl start mysqld.service
service mysqld restart

select host,user,Grant_priv,Super_priv from mysql.user;

通过sys_user.ibd恢复数据
1.创建sys_user.ibd对应的表。（若不知道表结构可通过ibd2sdi --dump-file=sys_user.txt sys_user.ibd查看）
2.alter table sys_user discard tablespace;（若提示外键约束，可执行set foreign_key_checks=0;）
3.将sys_user.ibd复制到Data/db目录。
4.alter table sys_user import tablespace;

select concat('alter table ', table_name, ' discard tablespace;') from information_schema.tables where table_type='BASE TABLE' and table_schema='jsbb';
select concat('alter table ', table_name, ' import tablespace;') from information_schema.tables where table_type='BASE TABLE' and table_schema='jsbb';

通过binlog恢复数据
查看binlog文件前10条
show binlog events in 'binlog.000001' limit 0, 10;

binlog文件转SQL
mysqlbinlog --no-defaults -vv --base64-output=decode-rows --set-charset=utf8 binlog.000001 > binlog1.sql

binlog文件恢复数据
mysqlbinlog --no-defaults --start-datetime="2020-01-01 00:00:00" --stop-datetime="2022-11-30 00:00:00" binlog.000001 | mysql -uroot -proot
mysqlbinlog --no-defaults binlog.000001 | mysql -uroot -proot

定时备份

#!/bin/bash
mysql_host="localhost"
mysql_port="3306"
mysql_user="root"
mysql_password="root"

backup_dir=/data/mysqlbak
backup_time=`date +%Y%m%d%H%M`
expire_days=1

mysqlpump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B oa> $backup_dir/oa-$backup_time.sql
flag=`ls -l $backup_dir/oa-$backup_time.sql | awk '{print $5}'`
echo $flag
if [ $flag -gt 0 ]; then 
echo "success"
find $backup_dir/ -name "*.sql" -type f -mtime +$expire_days | xargs rm -rf
else 
echo "error"
fi

crontab -l
crontab -e
00 12,20 * * * /data/mysqlbak/backup-mysql.sh > /data/mysqlbak/backup.log 2>&1