MyISAM 存储引擎	InnoDB 引擎
表级锁定	行级锁
不支持事务	支持事务，适合处理大量短期事务
读写相互阻塞，写入不能读，读时不能写	读写阻塞与事务隔离级别相关
只缓存索引	可缓存数据和索引
不支持外键约束	支持外键
不支持聚簇索引	支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发	支持MVCC高并发
崩溃恢复性较差	崩溃恢复性更好
MySQL5.5.5 前默认的数据库引擎	从MySQL5.5.5开始为默认的数据库引擎
存储量理论可达256T	存储量理论可达64T
三个文件：frm（元属性文件）、myd（数据文件）、myi（索引文件）	两个文件：frm（元属性文件）、ibd（数据索引）

INDEX 索引（分为）

B+ TREE、HASH、R TREE、FULL TEXT
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引: 是否是多个字段的索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

锁机制

读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务），多个读互不阻塞
写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写
S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外
事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，
但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突

锁粒度：表级锁：MyISAM 行级锁：InnoDB

READ #读锁
WRITE #写锁

列： lock tables students read; 读锁解锁 UNLOCK TABLES

关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁

FLUSH TABLES WITH READ LOCK;

范例: 同时在两个终端对同一行记录修改

#同时对同一行记录执行update
#在第一终端提示1行成功
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0
#在第二终端提示0行修改
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0

事务

CID特性：
A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态,类似于能量守恒定律(N50周启皓语录)
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离
级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

管理事务

显式启动事务（开启之后和正常一样，如果写错可以返回），切具有优化提速功能

BEGIN （开启事务命令都一样），事务具有安全性开启期间读写别人看不见
BEGIN WORK
START TRANSACTION

结束事务：

#提交,相当于vi中的wq保存退出
COMMIT
#回滚,相当于vi中的q!不保存退出
ROLLBACK

只有事务型存储引擎中的DML语句方能支持此类操作

自动提交： set autocommit={1|0} 有的不自动提交，可以自己更改
默认为1，为0时设为非自动提交
建议：显式请求和提交事务，而不要使用"自动提交"功能

查看事务：

#查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
#以下两张表在MySQL8.0中已取消
#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

死锁：

两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
范例：找到未完成的导致阻塞的事务(支持Mariadb)

#在第一会话中执行事务中
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> update students set classid=10;
#在第二个会话中执行访问没法用
MariaDB [hellodb]> update students set classid=20;

#在第三个会话中执行
MariaDB [hellodb]> show engine innodb status;
...省略...
---TRANSACTION 120, ACTIVE 673 sec
2 lock struct(s), heap size 1136, 28 row lock(s), undo log entries 27
MySQL thread id 13, OS thread handle 139719808595712, query id 206 localhost
root
...省略...
#查看正在进行的事务
MariaDB [hellodb]> SELECT * FROM information_schema.INNODB_TRX\G
trx_mysql_thread_id: 13 #线程ID
trx_mysql_thread_id: 15 #线程ID
#杀掉未完成的事务
MariaDB [hellodb]> kill 13;
Query OK, 0 rows affected (0.000 sec
#查看事务锁的超时时长，默认50s
MariaDB [hellodb]> show global variables like 'innodb_lock_wait_timeout';

事务隔离级别

MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格

隔离级别	脏读	不可重复读	幻读	加读锁
读未提交	可以出现	可以出现	可以出现	否
读提交	不允许出现	可以出现	可以出现	否
可重复读	不允许出现	不允许出现	可以出现	否
序列化	不允许出现	不允许出现	不允许出现	是

?**READ UNCOMMITTED**
可读取到未提交数据，产生脏读
?**READ COMMITTED**
可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
?**REPEATABLE READ**
可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
?**SERIALIZABLE**
可串行化，未提交的读事务阻塞写事务（加读锁，但不阻塞读事务），或者未提交的写事务阻塞读和写事务（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

MVCC和事务的隔离级别：

MVCC（多版本并发控制机制）只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其
他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前
事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

指定事务隔离级别：

select@@transaction_isolation; #MySQL8.0
SET tx_isolation #MySQL8.0之前版本

服务器选项中指定

vim /etc/my.cnf rock 的#MySQL8.0
[mysqld]
transaction-isolation= 'READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-
READ|SERIALIZABLE' 四选一#MySQL8.0
vim /etc/mysql.cof.d/mysqld.cof ubuntu的
#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-
READ|SERIALIZABLE'

日志管理

MySQL 支持丰富的日志类型，如下：有两个文件，1写完写2,2写完写1

事务日志：transaction log
事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead logging
事务日志文件： ib_logfile0， ib_logfile1
错误日志 error log
通用日志 general log
慢查询日志
二进制日志 binary log
中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

事务日志：transaction log

redo log：记录某数据块被修改后的值，数据更新前先记录redo log( WALWrite Ahead Log ),可以
用来恢复未写入data file的已成功事务更新的数据
undo log：保存与执行的操作相反的操作,即记录某数据被修改前的值，可以用来在事务失败时进
行rollback（撤销）

Innodb事务日志相关配置：

show variables like '%innodb_log%';
innodb_log_file_size 50331648 #每个日志文件大小
innodb_log_files_in_group 2 #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径

[root@rocky8 ~]#vim /etc/my.cnf 修改链接大小在这里面列：

[myslq]
innodb_log_group_home_dir=dara/mysql

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

高并发业务行业最佳实践，是使用第三种折衷配置（=2）：

1.配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快
2.配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃，也绝对不会丢数据

说明：
设置为1，同时sync_binlog = 1表示最高级别的容错
innodb_use_global_flush_log_at_trx_commit=0 时，将不能用SET语句重置此变量（ MariaDB
10.2.6 后废弃）

错误日志

mysqld启动和关闭过程中输出的事件信息
mysqld运行中产生的错误信息
event scheduler运行一个event时产生的日志信息
在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误文件路径

SHOW GLOBAL VARIABLES LIKE 'log_error' ;

通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句
通用日志可以保存在：file（默认值）或 table（mysql.general_log表）
通用日志相关设置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE 能win放在表里默认没有开启 set global log_output=TABLE

范例: 启用通用日志并记录至文件中

默认没有启用通用日志
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
#启用
mysql> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec
#默认通用日志存放在文件中
mysql> SHOW GLOBAL VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set (0.00 sec)
#通用日志存放的文件路径
mysql> select @@general_log_file;
+----------------------------+
| @@general_log_file |
+----------------------------+
| /var/lib/mysql/centos8.log |
+----------------------------+
1 row in set (0.00 sec)

慢查询日志

慢查询日志：记录执行查询时长超出指定时长的操作

select @@slow_query_log; 查看是否开启慢查询

select @@slow_query_log_file 查看指定存放文档

setglobal slow_query_log=ON; global级格式
slow_query_log=ON|OFF #开启或关闭慢查询，支持全局和会话，只有全局设置才会生成慢查询文件
set long_query_time=N #慢查询的阀值，单位秒,默认为10s
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，MariaDB 10.0/MySQL 5.6.1 版后已删除

二进制日志(备份，贼重要)

记录导致数据改变或潜在导致数据改变的SQL语句
记录已提交的日志
不依赖于存储引擎类型
事务日志和二进制日志区别
事务日志在线,二进制离线
事务日志记录事务执行的过程,包括提交和未提交,二进制日志记录只记提交的过程
事务日志只支持innodb,二进制都支持MyiSAM和innoDB
功能：通过"重放"日志文件中的事件来生成数据副本
注意：建议二进制日志和数据文件分开存放

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

二进制日志相关的服务器变量

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，即不自动删除

注：log_bin=/data/mysql/logbin/mysql-bin () 改完之后别忘了把文件夹创建出来，要有所有者，所出组 mkdir /datamysql/logbin -p chown mysql.mysql /data/mysql/ -R

systemctl restatr mariadb (重新开启下) 最好独立出来

二进制日志相关配置
查看mariadb自行管理使用中的二进制日志文件列表，及大小

SHOW {BINARY | MASTER} LOGS

查看使用中的二进制日志文件

SHOW MASTER STATUS

#MySQL 8.0 查看那种格式、

mysql> show variables like 'binlog_format'

可视化查看二进制文件

mysqlbinlog /mysql/logbin/mysql.000001

mysqlbinlog /mysql/logbin/mysql.000001 --start-position=123 --stop-position=321; 把选中的文件导出> 456.sql mysql hellodb < 456.sql 可以到入选中的文件就可以恢复数据

在线查看二进制文件中的指定内容

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
列：show binlog events in 'mysql-bin.000001' from 6516 limit 2,3

查看所有二进制文件

show master logs; 或者 show binary logs;

+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       537 | No        |
| binlog.000002 |  30507815 | No        |
+---------------+-----------+-----------+

show master status; 目前正在用的二进制文件

同步远程主机的二进制日志

#从10.0.0.8远程主机实时同步从指定的二进制日志binlog.000002开始向后进行同步到当前录
#先在10.0.0.8的MySQL创建用户并授权
[root@centos8 ~]#mysql -uroot -p123456
mysql> create user test@'10.0.0.%' identified by '123456'; 授权
mysql> grant REPLICATION SLAVE on *.* to test@'10.0.0.%' ; 授权具有远程复制的能力
#安装和MySQL服务器相同版本的客户端（大版本）
[root@centos8 ~]#yum -y install mysql
#前台执行,自动同步二进制日志
[root@centos8 data]#mysqlbinlog -R --host=10.0.0.8 --user=test --password=123456
--raw --stop-never binlog.000002

清除指定二进制日志

PURGE BINARY LOGS TO 'mariadb-bin.000003'; #删除mariadb-bin.000003之前的日志
PURGE BINARY LOGS BEFORE '2017-01-23';
PURGE BINARY LOGS BEFORE '2017-03-22 09:25:30';

删除所有二进制日志，index文件重新记数

RESET MASTER [TO #]; #删除所有二进制日志文件，并重新生成日志文件，文件名从#开始记数，默认从1开始，一般是master主机第一次启动时执行，MariaDB 10.1.6开始支持TO #

MySQL 备份和恢复

一个100G 回复大概半小时左右

mysqldump：备份的主流工具重点掌握它

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 < hellodb_innodb.sql 多执行几次

create table testlog (id int auto_increment primary key,name char(10),salary int default 20);

delimiter $$

create procedure  sp_testlog() 
begin  
declare i int;
set i = 1; 
while i <= 100000 
do  insert into testlog(name,salary) values (concat('wang',FLOOR(RAND() * 100000)),FLOOR(RAND() * 1000000)); 
set i = i +1; 
end while; 
end$$

delimiter ;

tar zcf mysql-backup.tar.gz /var/lib/mysql 压缩另储存文件
scp mysql-backup.tar.gz 10.0.0.100: 倒入另一台备份（备份建议最少两份）
tar xf mysql-backup.tar.gz -C /opt/ 找一个干净目录将其解开
systemctl stop mysql.service 先停止服务再将其写入，，
mv /opt/var/lib/mysql/* /var/lib/mysql 将其还原到源数据当中（若是去他机器必须干净）
systemctl start mysqld 还原完之后将其重启

可以写个脚本执行自动向另一个机器备份

最好在加md5sum做哈希算比对确认是否完全网络抖动的是不安全的

DATAPATH=/var/lib/mysql
TIME=`date +%F_%H-%M-%S`
BACKUP_SERVER=10.0.0.100
LOCK_FILE=/tmp/backup_mysql.lock
lock() {                                         锁，判断是否存在，有退出，不存在创建
    if [ -e ${LOCK_FILE} ] ;then
        echo "另一个备份正在执行，退出"
        exit
    else
        touch ${LOCK_FILE}
    fi
}

unlock (){
    if [ -e ${LOCK_FILE} ];then
        rm -f ${LOCK_FILE}             解锁 文件存在将其删除
    fi
}

backup_mysql(){ 
    lock                                         备份之前枷锁
    systemctl stop mysqld &>/dev/null                            停止
    tar zcf mysql-backup-${TIME}.tar.gz ${DATAPATH} &>/dev/null   备份
    scp mysql-backup-${TIME}.tar.gz  ${BACKUP_SERVER}:/backup/  &>/dev/null      拷贝
    systemctl start mysqld &>/dev/null     开启
    unlock                                  备份结束 解锁
}

backup_mysql

crontab -e 定时执行分时日月周，立即执行

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

* * * * * /root/backup_mysql.sh

mysqldump 备份工具

命令格式:

1 mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份，但数据库本身定义不备份 不推荐
2 mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份 推脱简单好用
mysqldump [OPTIONS] -A [OPTIONS] #备份所有数据库，包含数据库本身定义也会备份

mysqldump -uroot -p123456 hellodb 【库中表，不跟整个库】他不是备份，就是输出出来 mysqldump -uroot -p123456 hellodb | gzip > data.gz 压缩导入，解压还原后，需要先创建数据库，还原之后有风险，可能字符集之类的不一样了 只保存表不保存字符集不建议用

mysqldump -uroot -p123456 -B hellodb > data -B .sql 备份
mysqldump -uroot < data -B .sql 它不需要指定数据库，备份文件里有，备份的字符集都一样的

3 mysqldump -uroot -p123456 -A > /data.sql 全备份（除了系统自带的，mysql存放的是系统账号是需要备份的）也就是mysql 和数据库需要备份
mysqldump -A -F -B hellodb > /data/hell.sql -F刷新 -B指定数据库
systemctl stop mysqld.service 暂停
mysqld --initialize-insecure --user=mysql --datadir=/var/lib/mysql 生成系统文件，数据库文件文什么都没有的话
mysql < /data/hell.sql 回复
systemctl start mysqld.service 启动

for 循环排除自带数据库备份

for db in `mysql -e 'show databases' |grep -Ev '^(Database|information_schema|performance_schema|sys)$'`;do mysqldump -B $db > /backup/$db-`date +%F`.sql;done

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

mysqldump 常见通用选项：

-u, --user=name User for login if not current user
-p, --password[=name] Password to use when connecting to server
-A, --all-databases #备份所有数据库，含create database
-B, --databases db_name... #指定备份的数据库，包括create database语句
-E, --events：#备份相关的所有event schedule
-R, --routines：#备份所有存储过程和自定义函数
--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 #指定字符集
--master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-data
#此选项须启用二进制日志
#1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用
#2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
#此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，
配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-
transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact   #去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data #只备份表结构,不备份数据,即只备份create table
-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 #不缓存查询，直接输出，加快备份速度

mysqldump的MyISAM存储引擎相关的备份选项：

MyISAM不支持事务，只能温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

-x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或
--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认on,--
skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
#注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

mysqldump的InnoDB存储引擎相关的备份选项：

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可实现温备但不建用

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

完全备份

大概思路

完全备份需要先在定时全局备份的前提上加上日志备份，后续先回溯全局备份完然后在跳过备份日志错误的部分备份其余的

完全备份的 --source-data 完全备份需要加上这

生产环境实战备份策略

InnoDB建议备份策略

mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --master-data=2 --flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql
#新版8.0.26以上
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --source-
data=2 --flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

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

mysql -e status 查看数据库信息 flush logs; 手动触发二进制日志生成

实验流程：数据库损坏客户在读写，反馈后修复还原全部还原，二级制日志还原

备份

mysqldump -uroot -A -F --triggers --single-transaction --master-data=2 aster-data=2 --fl--flush-privileges --default-character-set=utf8mb4 --hex-blob > /backup/data.sql
备份全部，刷新，事件函数等备份下来，事务方式2单机版，刷新权限，指定字符集，非字符数据转换，备份相应目录

mysql> set sql_log_bin=off; 停止日志记录，为了后续日志回复
show master logs; 查看二进制日志
mysql> source /backup/data.sql; 还原完全备份
less /backup/data.sql 查看日志记录
mysqlbinlog /var/lib/mysql/binlog.000003 > /backup/inc.sql 将其导到一个文件
vim /backup/inc.sql 打开文件夹 /drop 查看误操作的命令
sed -i '/^DROP TABLE/d' /backup/data.sql 用sed删除（大文件最好用sed）
source /backup/inc.sql; 回复日志
select @@sql_log_bin; 查看是否开启日志
set sql_log_bin=1; 开启（退出也会自动开启）

（如果恢复日志文件在03其后面还有别的文件，那就需要都导出来然后追加到一个目录里）

主从配置mysql

多实力（一台机器上多个mysql）
二进制基本要求必须开启二进制
反向代理三个线程主节点Bin log 线程 io线程 SQL线程依赖于与二进制实现的

总结简称 2 实力以上 2 开启二进制 3线程

配置

主节点配置：启用二进制日志，设置ip，设置远程用户链接账号，指定从哪里来复制

主从复制，主从如果不一样，主服务版本要低，从服务版本要高，这样才可以从主版本里拉文件

启用二进制日志

[mysqld]
log_bin=/data/logbin/mysql-bin

为当前节点设置一个全局惟一的ID号一样的文件有可能分不请是谁的，加长ip一方错乱

[mysqld]
server-id=#
log-basename=master #可选项，设置datadir中日志名称，确保不依赖主机名

说明：

server-id的取值范围
1 to 4294967295 (>= MariaDB 10.2.2)，默认值为1，MySQL8.0默认值为1
0 to 4294967295 (<= MariaDB 10.2.1)，默认值为0，如果从节点为0，所有master都将拒绝此
slave的连接

查看从二进制日志的文件和位置开始进行复制

SHOW MASTER STATUS;

创建有复制权限的用户账号

实验过程

systemctl stop mysqld.service 停
rm -rf /var/lib/mysql/* 删
vim /etc/my.cnf 写ip号
[mysqld]
server-id=8
systemctl start mysqld.service 开
show master logs; binlog.000001 | 156 看
mysql> create user repluser@'10.0.0.%' identified by '123456'; 创建远程链接账号
mysql> grant replication slave on *.* to repluser@'10.0.0.%'; 给其权限
show master logs; binlog.000001 | 681 如果以后让从当主建议复制过去

从

systemctl stop mysqld 关
vim /etc/my.cnf 写ip
[mysqld]
server-id=18
systemctl start mysqld.service 开
CHANGE MASTER TO 直接贴过去确定
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=156,
MASTER_CONNECT_RETRY=10;

mysql> show slave status \G; 从节点相关信息，及状态

mysql> start slave; 开启线程

Slave_IO_Running: No 双线程
Slave_SQL_Running: No
Seconds_Behind_Master: NULL 越大约不一致

主 mysql> show processlist; 在主服务器上可以看到谁在链接

在创建个2从

mysqldump -A -F --single-transaction --master-data=1 > /backup/all.sql 在主上备份
scp /backup/all.sql 10.0.0.12: 传到二从上
systemctl enable --now mysqld 从服务器因为刚安装过所以从来没起来过；
不管主从都强烈建议开启二进制
vim all.sqlxia 下
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000001',MASTER_LOG_POS=156;
> set sql_log_bin=0; 关闭二进制
mysql> source all.sql 导入
mysql> show slave status/G; 查看
mysql> show processlist; 查看链接情况

可以看到主上有两个Binlog Dump 线程，主节点写操作是没有人帮他分担的，读操作是可以帮其分担的，这样只能直线读操作的负载浚航和高可用，写操作仍然有·单点失败的风险，因为写只能发生在主节点上；

级联复制（双主）

需要在中间的从服务器启用以下配置，实现中间slave节点能将master的二进制日志在本机进行数据库更新，并且也同时更新本机的二进制，从而实现级联复制

[mysqld]
server-id=18
log_bin
log_slave_updates #级联复制中间节点的必选项,MySQL8.0此为默认值,可以不要人为添加,其它
版本默认不开启
read-only

select @@log_slave_updates; 查看是否开启级联

双主

从 mysql> show master logs; 把 binlog.000001 | 12126 把数值复制下来
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=12126; 把相关参数改一下

主

CHANGE MASTER TO 在主里执行下从的
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000001',
MASTER_LOG_POS=12126;
show slave status\G; 查看是否成功
mysql> start slave; 开启

如果主从·一起写入一样的会起冲突，解决方法条过去
stop slave；双方停止复制线程
set globat sql_slave_skip_counter=1 双方跳过1个错误有几个挑几个
start slave; 双发启动
show slave status\G; 可以查看是否有问题
可能会有数据前后错误，少则手动改，多则数据恢复
set sql_log_bin=0 停止服务在其操作改完开启

#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL

原文链接：https://blog.csdn.net/cp_dvd/article/details/126556108

myisam和innodb的区别

锁机制

事务