mysql数据库
数据库安装 https://www.cnblogs.com/itbsl/p/10076303.html
MySQL系列
官方网址：
https://www.mysql.com/
http://mariadb.org/
https://www.percona.com
官方文档
https://dev.mysql.com/doc/
https://mariadb.com/kb/en/
https://www.percona.com/software/mysql-database/percona-server
版本演变：
MySQL：5.1 --> 5.5 --> 5.6 --> 5.7 -->8.0
MariaDB：5.5 -->10.0--> 10.1 --> 10.2 --> 10.3
MYSQL的特性
插件式存储引擎：也称为“表类型”，存储管理器有多种实现版本，功能和特性可能均略有差别；用户可根据需要灵活选择,Mysql5.5.5开始innoDB引擎是MYSQL默认引擎
MyISAM ==> Aria
InnoDB ==> XtraDB
单进程，多线程
诸多扩展和新特性
提供了较多测试组件
开源
安装MYSQL
Mariadb安装方式：
1、源代码：编译安装
2、二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
3、程序包管理器管理的程序包
CentOS 安装光盘
项目官方：https://downloads.mariadb.org/mariadb/repositories/
国内镜像：https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/
https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/
RPM包安装MySQL
RPM包安装
CentOS 7：安装光盘直接提供
mariadb-server 服务器包
mariadb 客户端工具包
CentOS 6
提高安全性
mysql_secure_installation
设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库
MariaDB程序
客户端程序：
mysql: 交互式的CLI工具
mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成insert等写操作语句保存文本文件中
mysqladmin：基于mysql协议管理mysqld
mysqlimport：数据导入工具
MyISAM存储引擎的管理工具：
myisamchk：检查MyISAM库
myisampack：打包MyISAM表，只读
服务器端程序
mysqld_safe
mysqld
mysqld_multi 多实例 ，示例：mysqld_multi --example
用户账号
mysql用户账号由两部分组成：
'USERNAME'@'HOST‘
说明：
HOST限制此用户可通过哪些远程主机连接mysql服务器
支持使用通配符：
% 匹配任意长度的任意字符
172.16.0.0/255.255.0.0 或 172.16.%.%
_ 匹配任意单个字符
Mysql 客户端
mysql使用模式：
交互式模式：
可运行命令有两类：
客户端命令：
\h, help
\u，use
\s，status
\!，system
服务器端命令：
SQL语句， 需要语句结束符；
脚本模式：
mysql –uUSERNAME -pPASSWORD < /path/somefile.sql
mysql> source /path/from/somefile.sql
mysql客户端可用选项：
-A, --no-auto-rehash 禁止补全
-u, --user= 用户名,默认为root
-h, --host= 服务器主机,默认为localhost
-p, --passowrd= 用户密码,建议使用-p,默认为空密码
-P, --port= 服务器端口
-S, --socket= 指定连接socket文件路径
-D, --database= 指定默认数据库
-C, --compress 启用压缩
-e “SQL“ 执行SQL命令
-V, --version 显示版本
-v --verbose 显示详细信息
--print-defaults 获取程序默认使用的配置
socket地址
服务器监听的两种socket地址：
ip socket: 监听在tcp的3306端口，支持远程通信
unix sock: 监听在sock文件上，仅支持本机通信
如：/var/lib/mysql/mysql.sock)
说明：host为localhost,127.0.0.1时自动使用unix sock
执行命令
运行mysql命令：默认空密码登录
mysql>use mysql
mysql>select user();查看当前用户
mysql>SELECT User,Host,Password FROM user;
登录系统：mysql –uroot –p
客户端命令：本地执行
mysql> help
每个命令都完整形式和简写格式
mysql> status 或 \s
服务端命令：通过mysql协议发往服务器执行并取回结果
每个命令末尾都必须使用命令结束符号，默认为分号
示例：SELECT VERSION();
yum安装mariadb数据库
二进制安装mariadb数据库
源码安装mariadb数据库
二进制安装mysql5.7.26数据库
数据库多实例
数据库多实例启动数据库脚本
#!/bin/bash
port=3306
mysql_user="root"
mysql_pwd="centos"
cmd_path="/usr/bin"
mysql_basedir="/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"
function_start_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
function_stop_mysql()
{
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}
function_restart_mysql()
{
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac
MariaDB数据库创建用户和设置用户密码
MariaDB数据库权限管理
mysqladmin
mysqladmin –help
查看mysql服务是否正常，如果正常提示mysqld is alive
mysqladmin -uroot -pcentos ping
创建数据库testdb
mysqladmin -uroot –pcentos create testdb
删除数据库testdb
mysqladmin -uroot -pcentos drop testdb
日志滚动,生成新文件/var/lib/mysql/ mariadb-bin.00000N
mysqladmin -uroot -pcentos flush-logs
修改root密码
mysqladmin –uroot –pcentos password ‘magedu’
关闭mysql服务，但mysqladmin命令无法开启
mysqladmin –uroot –pcentos shutdown
服务器端配置
服务器端(mysqld)：工作特性有多种配置方式
1、命令行选项：
2、配置文件：类ini格式
集中式的配置，能够为mysql的各应用程序提供配置信息
[mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqldump]
[server]
[client]
格式：parameter = value
说明：_和- 相同
1，ON，TRUE意义相同， 0，OFF，FALSE意义相同
配置文件
配置文件：
后面覆盖前面的配置文件，顺序如下：
/etc/my.cnf Global选项
/etc/mysql/my.cnf Global选项
SYSCONFDIR/my.cnf Global选项
$MYSQL_HOME/my.cnf Server-specific 选项
--defaults-extra-file=path
~/.my.cnf User-specific 选项
索引
索引：是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现
优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O
缺点：
占用额外空间，影响插入速度
索引类型：
B+ TREE、HASH、R TREE
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高
二叉树
红黑树
B-TREE索引
B+TREE索引
B+Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
可以使用B+Tree索引的查询类型：
全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
匹配最左前缀：即只使用索引的第一列，如：姓wang
匹配列前缀：只匹配一列值开头部分，如：姓以w开头的
匹配范围值：如：姓ma和姓wang之间
精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的
只访问索引的查询
索引创建、查询
索引使用情况
B+Tree索引
B+Tree索引的限制：
如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列
特别提示：
索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求
Hash索引
Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好
Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
适用场景：只支持等值比较查询，包括=, <=>, IN()
不适合使用hash索引的场景
不适用于顺序查询：索引存储顺序的不是值的顺序
不支持模糊匹配
不支持范围查询
不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效
索引
空间数据索引R-Tree（ Geospatial indexing ）
MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多
InnoDB从MySQL5.7之后也开始支持
全文索引(FULLTEXT)
在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
InnoDB从MySQL 5.6之后也开始支持
聚簇和非聚簇索引
聚簇和非聚簇索引，主键和二级索引
索引
冗余和重复索引：
冗余索引：（A），（A，B）
重复索引：已经有索引，再次建立索引
索引优化策略：
独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估
索引选择性：不重复的索引值和数据表的记录总数的比值
多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
索引优化建议
只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
尽量使用短索引，如果可以，应该制定一个前缀长度
对于经常在where子句使用的列，最好设置索引
对于有多个列where或者order by子句，应该建立复合索引
对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
尽量不要在列上进行运算（函数操作和表达式操作）
尽量不要使用not in和<>操作
SQL语句性能优化
查询时，能不要*就不用*，尽量写全字段名
大部分情况连接效率远大于子查询
多表连接时，尽量小表驱动大表，即小表 join 大表
在有大量记录的表分页时使用limit
对于经常使用的查询，可以开启缓存
多使用explain和profile分析查询语句
查看慢查询日志，找出执行时间长的sql语句优化
管理索引
创建索引：
CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
help CREATE INDEX;
删除索引：
DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);
查看索引：
SHOW INDEXES FROM [db_name.]tbl_name;
优化表空间：
OPTIMIZE TABLE tb_name;
查看索引的使用
SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;
事务
事务Transactions：一组原子性的SQL语句，或一个独立工作单元
事务日志：记录事务信息，实现undo,redo等故障恢复功能
ACID特性：
A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中
事务
启动事务：
BEGIN
BEGIN WORK
START TRANSACTION
结束事务：
COMMIT：提交
ROLLBACK: 回滚
注意：只有事务型存储引擎中的DML语句方能支持此类操作
自动提交：set autocommit={1|0} 默认为1，为0时设为非自动提交
建议：显式请求和提交事务，而不要使用“自动提交”功能
事务支持保存点：savepoint
SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier
事务隔离级别
事务隔离级别：从上至下更加严格
READ UNCOMMITTED 可读取到未提交数据，产生脏读
READ COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
REPEATABLE READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差
MVCC: 多版本并发控制，和事务级别相关
事务隔离级别
事务
指定事务隔离级别：
服务器变量tx_isolation指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置
SET tx_isolation=''
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE
服务器选项中指定
vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE
并发控制
死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
事务日志：
事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging
事务日志文件： ib_logfile0， ib_logfile1
日志
日志
事务日志 transaction log
错误日志 error log
通用日志 general log
慢查询日志 slow query log
二进制日志 binary log
中继日志 reley log
事务日志
事务日志：transaction log
事务型存储引擎自行管理和使用，建议和数据文件分开存放
redo log
undo log
Innodb事务日志相关配置：
show variables like '%innodb_log%';
innodb_log_file_size 5242880 每个日志文件大小
innodb_log_files_in_group 2 日志组成员个数
innodb_log_group_home_dir ./ 事务文件路径
innodb_flush_log_at_trx_commit 默认为1
事务日志
innodb_flush_log_at_trx_commit
说明：设置为1，同时sync_binlog = 1表示最高级别的容错 innodb_use_global_flush_log_at_trx_commit的值确定是否可以使用SET语句重置此变量
1默认情况下，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。 这是完全遵守ACID特性
0提交时没有任何操作; 而是每秒执行一次日志缓冲区写入和刷新。 这样可以提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2每次提交后都会写入日志缓冲区，但每秒都会进行一次刷新。 性能比0略好一些，但操作系统或停电可能导致最后一秒的交易丢失
3模拟MariaDB 5.5组提交（每组提交3个同步），此项MariaDB 10.0支持
事务日志优化
错误日志
错误日志
mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息
错误日志相关配置
SHOW GLOBAL VARIABLES LIKE 'log_error'
错误文件路径
log_error=/PATH/TO/LOG_ERROR_FILE
是否记录警告信息至错误日志文件
log_warnings=1|0 默认值1
通用日志
通用日志：记录对数据库的通用操作，包括错误的SQL语句
文件：file，默认值
表：table
通用日志相关设置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
慢查询日志
慢查询日志：记录执行查询时长超出指定时长的操作
slow_query_log=ON|OFF 开启或关闭慢查询
long_query_time=N 慢查询的阀值，单位秒
slow_query_log_file=HOSTNAME-slow.log 慢查询日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
上述查询类型且查询时长超过long_query_time，则记录日志
log_queries_not_using_indexes=ON 不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录
log_slow_rate_limit = 1 多少次查询才记录，mariadb特有
log_slow_verbosity= Query_plan,explain 记录内容
log_slow_queries = OFF 同slow_query_log 新版已废弃
二进制日志
二进制日志
记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
功能：通过“重放”日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放
中继日志：relay log
主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
二进制日志记录格式
二进制日志记录三种格式
基于“语句”记录：statement，记录语句，默认模式
基于“行”记录：row，记录数据，日志量较大
混合模式：mixed, 让系统自行判定该基于哪种方式进行
格式配置
show variables like ‘binlog_format';
二进制日志文件的构成
有两类文件
日志文件：mysql|mariadb-bin.文件名后缀，二进制格式
如： mariadb-bin.000001
索引文件：mysql|mariadb-bin.index，文本格式
二进制日志相关的服务器变量：
sql_log_bin=ON|OFF：是否记录二进制日志，默认ON
log_bin=/PATH/BIN_LOG_FILE：指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可
binlog_format=STATEMENT|ROW|MIXED：二进制日志记录的格式，默认STATEMENT
max_binlog_size=1073741824：单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
说明：文件达到上限时的大小未必为指定的精确值
sync_binlog=1|0：设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：二进制日志可以自动删除的天数。 默认为0，即不自动删除
二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表，及大小
SHOW {BINARY | MASTER} LOGS
查看使用中的二进制日志文件
SHOW MASTER STATUS
查看二进制文件中的指定内容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
show binlog events in ‘mysql-bin.000001' from 6516 limit 2,3
日志
mysqlbinlog：二进制日志的客户端命令工具
命令格式：
mysqlbinlog [OPTIONS] log_file…
--start-position=# 指定开始位置
--stop-position=#
--start-datetime=
--stop-datetime=
时间格式：YYYY-MM-DD hh:mm:ss
--base64-output[=name]
-v -vvv
示例：mysqlbinlog --start-position=6787 --stop-position=7527 /var/lib/mysql/mariadb-bin.000003 -v
mysqlbinlog --start-datetime="2018-01-30 20:30:10" --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv
二进制日志事件的格式：
# at 328
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID
清除指定二进制日志：
PURGE { BINARY | MASTER } LOGS
{ TO 'log_name' | BEFORE datetime_expr }
示例：
PURGE BINARY LOGS TO ‘mariadb-bin.000003’;删除3之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';
删除所有二进制日志，index文件重新记数
RESET MASTER [TO #]; 删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB10.1.6开始支持TO #
切换日志文件：
FLUSH LOGS;
事务的基本要素（ACID）
1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。
3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。
事物的4中隔离级别
数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。而且，在事务的并发操作中可能会出现脏读，不可重复读，幻读。下面通过事例一一阐述它们的概念与联系。
Read uncommitted(读未提交)
读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。
事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。
分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。
那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。
Read committed（读提交）
读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。
事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…
分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。
那怎么解决可能的不可重复读问题？Repeatable read ！
Repeatable read（重复读）
重复读，就是在开始读取数据（事务开启）时，不再允许修改操作
事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。
分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。
什么时候会出现幻读？
事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。
那怎么解决幻读问题？Serializable！
Serializable 序列化
Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
值得一提的是：大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。
备份和恢复
为什么要备份
灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
备份注意要点
能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据
还原要点
做还原测试，用于测试备份的可用性
还原演练
备份类型：
完全备份，部分备份
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
注意：二进制日志文件不应该与数据文件放在同一磁盘
数据库冷备份(相当于拷贝磁盘文件，数据库完全备份)
冷、温、热备份
冷备：读写操作均不可进行
温备：读操作可执行；但写操作不可执行
热备：读写操作均可执行
MyISAM：温备，不支持热备
InnoDB：都支持
物理和逻辑备份
物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度
备份时需要考虑的因素
温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长
备份什么
数据
二进制日志、InnoDB的事务日志
程序代码（存储过程、函数、触发器、事件调度器）
服务器的配置文件
mysqldump工具备份数据库
备份工具
cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对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来快速备份数据库
基于LVM的备份
(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
(3) 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4) 释放锁
mysql> UNLOCK TABLES;
(5) 挂载快照卷，执行数据备份
(6) 备份完成后，删除快照卷
(7) 制定好策略，通过原卷备份二进制日志
备份和恢复
逻辑备份工具：mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump工具：客户端命令，通过mysql协议连接至mysql服务器进行备份
mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]
mysqldump参考： https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
mysqldump常见选项：
-A， --all-databases 备份所有数据库，含create database
-B , --databases db_name… 指定备份的数据库，包括create database语句
-E, --events：备份相关的所有event scheduler
-R, --routines：备份所有存储过程和自定义函数
--triggers：备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 指定字符集
--master-data[=#]： 此选项须启用二进制日志
1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
2：记录为注释的CHANGE MASTER TO语句
此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
mysqldump常见选项
-F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次日志
--compact 去掉注释，适合调试，生产不使用
-d, --no-data 只备份表结构
-t, --no-create-info 只备份数据,不备份create table
-n,--no-create-db 不备份create database，可被-A或-B覆盖
--flush-privileges 备份mysql或相关时需要使用
-f, --force 忽略SQL错误，继续执行
--hex-blob 使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick 不缓存查询，直接输出，加快备份速度
MyISAM备份选项：
支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
锁定方法如下：
-x,--lock-all-tables：加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能
注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用
InnoDB备份选项： 支持热备，可用温备但不建议用
--single-transaction
此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
备份大型表时，建议将--single-transaction选项和--quick结合一起使用
基于yum安装5.5.60-MariaDB主从同步
生产环境实战备份策略
InnoDB建议备份策略
mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql
MyISAM建议备份策略
mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql
xtrabackup
xtrabackup数据库备份工具
Percona
官网：www.percona.com
percona-server
InnoDB --> XtraDB
Xtrabackup
percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具
手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
特点：
备份还原过程快速、可靠
备份过程不会打断正在执行的事务
能够基于压缩等功能节约磁盘空间和流量
自动实现备份检验
开源，免费
Xtrabackup2.2版之前包括4个可执行文件：
innobackupex: Perl 脚本
xtrabackup: C/C++ 编译的二进制
xbcrypt: 加解密
xbstream: 支持并发写的流文件格式
xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互
innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和 MySQL Server 发送命令进行交互，如加全局读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。即innobackupex是在 xtrabackup 之上做了一层封装实现的
