[TOC]
mysql 知识点补充
一、mysql 备份
1.1 mysqldump 工具备份
1.2 使用 xtrabackup工具备份
1.3 LVM 或者文件系统快照备份
使用 LVM 或者文件系统快照备份：LVM 或者文件系统快照是一种快速备份 MySQL 数据库的方法，可以在不停止 MySQL 服务的情况下备份数据。该方法需要使用磁盘分区来创建快照，然后将快照复制到备份目录中。需要注意的是，该方法需要一定的技术水平和经验，以确保备份的正确性和一致性。1.4 逻辑卷备份数据库
# 列出所有 LVM 卷
[root@mysql ~]# sudo lvdisplay
# 创建 LVM 快照，其中 /dev/vg_db/mysql 是要备份的 LVM 卷，backup-snapshot 是快照卷名
[root@mysql ~]# sudo lvcreate -L 10G -s -n backup-snapshot /dev/vg_db/mysql
# 创建挂载点目录
[root@mysql ~]# sudo mkdir /mnt/backup-snapshot
# 将 LVM 快照挂载到挂载点目录
[root@mysql ~]# sudo mount /dev/vg_db/backup-snapshot /mnt/backup-snapshot
# 备份数据到 /backup/mysql 目录下，可根据需要修改目录路径
[root@mysql ~]# sudo tar czvf /backup/mysql/mysql_backup.tar.gz /mnt/backup-snapshot/mysql/
# 卸载挂载点
[root@mysql ~]# sudo umount /mnt/backup-snapshot
# 删除快照卷
[root@mysql ~]# sudo lvremove /dev/vg_db/backup-snapshot二、binlog日志介绍
2.1 binlog 日志格式
在 MySQL 中，可以通过设置 binlog_format 参数来修改 binlog 日志的格式。
STATEMENT -- 语句
ROW --行
MIXED --混合模式。
# 修改 binlog_format 的方法如下：
mysql> mysql -uroot -pQianFeng@123
# 执行 show variables like 'binlog_format'; 命令查看当前的 binlog 日志格式。
mysql> show variables like 'binlog_format';
# 临时修改
mysql> set binlog_format = <format>;
# 永久修改
[root@mysql ~]# vim /etc/my.cnf
binlog_format="<format>"
# 重启数据库
[root@mysql ~]# systemctl restart mysqld
2.2 binlog 保留时长
# 在 MySQL 中，可以通过设置 expire_logs_days 参数来控制 binlog 日志文件的保留时间。该参数表示 binlog 日志文件的过期时间，即在过期时间内生成的 binlog 日志文件不会被自动删除。
# 修改 expire_logs_days 的方法如下：
mysql> mysql -uroot -pQianFeng@123
# 执行 show variables like 'expire_logs_days'; 命令查看当前的过期时间。
mysql> show variables like 'expire_logs_days';
# 临时修改
mysql> set global expire_logs_days = <days>;
# 永久修改
[root@mysql ~]# vim /etc/my.cnf
expire_logs_days=<days>;
# 重启数据库
[root@mysql ~]# systemctl restart mysqld
2.3 binlog 解析
2.3.1 语句格式
# 查看日志格式
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
# 查看当前正在输入的日志
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mysql.000006 | 431 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 写入测试数据
mysql> insert into t1 values(1),(2),(3);
# 查看日志内容
[root@mysql binlog]# mysqlbinlog -v --base64-output=decode-rows mysql.000006 
at 行记录了该事件在 binlog 文件中的位置
server id 表示服务器的 ID
exec_time 表示执行该 SQL 语句所消耗的时间
error_code 表示错误码
SET TIMESTAMP 表示该事件的时间戳
INSERT 表示该事件是一个插入操作
t1 表示插入的目标表名，后面的部分则是具体的插入语句
Xid 表示该事务的 ID
COMMIT 则表示该事务已经提交2.3.2 行格式
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=/data/binlog/mysql
binlog_format=ROW
[root@mysql ~]# systemctl restart mysqld
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
# 每次重启都会重新启动一个新的日志文件
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mysql.000007 | 154 | | | |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> insert into t1 values(4),(5),(6);
# 查看日志内容
[root@mysql binlog]# mysqlbinlog -v --base64-output=decode-rows mysql.000007
Table_map：表示该事件要操作的表的信息，包括表名、表 ID 等。
Write_rows / Update_rows / Delete_rows：表示该事件的具体操作，包括插入、更新、删除等。
Xid：表示该事件所属的事务 ID。2.4 配置文件实例
这个参数表示启用 binlog 功能，并指定 binlog 的存储目录
log-bin=/data/binlog/mysql-bin
# 设置一个 binlog 文件的最大字节
# 设置最大 100MB
max_binlog_size=104857600
# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 7
# binlog 日志只记录指定库的更新（配置主从复制的时候会用到）
#binlog-do-db=db1
# binlog 日志不记录指定库的更新（配置主从复制的时候会用到）
#binlog-ignore-db=db1
# 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
sync_binlog=0
# 为当前服务取一个唯一的 id（MySQL5.7 之后需要配置）
server-id=1三、MySQL主从复制
3.1 异步复制（Asynchronous replication）
MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题：主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。3.2 全同步复制（Fully synchronous replication）
指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。3.3 半同步复制（Semisynchronous replication）
介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。3.4 半同步复制配置方法
主数据库:
#加载mysql半同步复制的插件
plugin-load=rpl_semi_sync_master=semisync_master.so
#或者设置为"1"，即开启半同步复制功能
rpl_semi_sync_master_enabled=ON
#超时时间为1000ms，即1s
rpl-semi-sync-master-timeout=1000
从数据库:
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=ON3.5 检查方法
主数据库:
show status like 'Rpl_semi_sync_master_status';
show variables like 'rpl_semi_sync_master_timeout';
从数据库:
#从数据库执行（此时可能还是OFF状态，需要在下一步重启IO线程后，从库半同步状态才会为ON）
show status like 'Rpl_semi_sync_slave_status';
#重启从数据库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
#在主库查询半同步状态
show status like '%Rpl_semi%'; 3.6 参数解释
Rpl_semi_sync_master_clients #半同步复制客户端的个数
Rpl_semi_sync_master_net_avg_wait_time #平均等待时间（默认毫秒）
Rpl_semi_sync_master_net_wait_time #总共等待时间
Rpl_semi_sync_master_net_waits #等待次数
Rpl_semi_sync_master_no_times #关闭半同步复制的次数
Rpl_semi_sync_master_no_tx #表示没有成功接收slave提交的次数
Rpl_semi_sync_master_status #表示当前是异步模式还是半同步模式，on为半同步
Rpl_semi_sync_master_timefunc_failures #调用时间函数失败的次数
Rpl_semi_sync_master_tx_avg_wait_time #事物的平均传输时间
Rpl_semi_sync_master_tx_wait_time #事物的总共传输时间
Rpl_semi_sync_master_tx_waits #事物等待次数
Rpl_semi_sync_master_wait_pos_backtraverse #可以理解为"后来的先到了，而先来的还没有到的次数"
Rpl_semi_sync_master_wait_sessions #当前有多少个session因为slave的回复而造成等待
Rpl_semi_sync_master_yes_tx #成功接受到slave事物回复的次数
3.7 主从延迟同步
CHANGE MASTER TO MASTER_DELAY = 1800；3.8 同步指定的数据库
在 MySQL 主从复制过程中，可以使用 replicate-do-db 参数来指定需要复制的数据库，避免对所有数据库进行全库复制。
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
# 如果指定多个数据库，使用逗号隔开
replicate-do-db=database_name
[root@mysql ~]# systemctl restart mysqld
四、mysql 优化
4.1 硬件要求
内存：MySQL在执行查询和数据操作时需要使用内存，因此需要足够的内存来支持MySQL的运行。具体内存需求量取决于数据库大小、负载和操作类型等因素，一般建议至少为8GB或以上。
CPU：MySQL的性能也与CPU性能密切相关。当数据库的负载比较高时，需要有足够的CPU资源来处理请求。一般建议使用多核CPU。
存储：MySQL需要存储数据文件和日志文件。因此需要足够的存储空间来存储这些文件，同时建议使用固态硬盘（SSD）或RAID等技术来提高I/O性能。
网络：MySQL作为一个客户端/服务器模型的应用程序，需要通过网络来传输数据和执行查询。因此需要足够的带宽和网络性能来支持MySQL的运行。4.2 配置优化
配置MySQL缓存参数：可以通过设置query_cache_size、key_buffer_size、innodb_buffer_pool_size等参数来适当调整MySQL的缓存大小，提高查询性能。
开启慢查询日志：通过开启慢查询日志可以记录执行时间超过一定时间的查询语句，便于排查性能问题。
配置字符集：如果数据库中的数据涉及多种字符集，可以通过设置character_set_server、collation_server等参数来适当调整字符集的配置。
配置索引：对于查询频繁的列，可以适当添加索引来提高查询性能。
配置InnoDB引擎：如果使用InnoDB引擎，可以通过设置innodb_file_per_table、innodb_flush_log_at_trx_commit等参数来优化性能。
配置并发连接数：可以通过设置max_connections、max_user_connections等参数来限制MySQL的并发连接数，以避免因过多连接导致系统负载过高。4.3 mysql 慢查询
MySQL慢查询日志是MySQL自带的性能分析工具，可以记录执行时间超过一定阈值的SQL语句。下面是在MySQL中配置慢查询日志的步骤：
1. 打开MySQL配置文件my.cnf或my.ini，添加以下配置：
slow_query_log = 1 # 启用慢查询日志
slow_query_log_file = /var/log/mysql/slow.log # 慢查询日志文件路径
long_query_time = 1 # 查询时间超过1秒的语句将被记录
2. 重启MySQL服务，使配置生效。
$ sudo tail -f /var/log/mysql/slow.log
以上配置会将慢查询日志记录到/var/log/mysql/slow.log文件中，查询时间超过1秒的语句将被记录。可以根据实际情况调整日志文件路径和查询时间阈值。
需要注意的是，启用慢查询日志会对MySQL的性能产生一定的影响，因此建议在生产环境中谨慎使用，并定期清理日志文件以避免占用过多的磁盘空间。五、mysql 的锁
# 读锁
USE db_name;
LOCK TABLES table_name READ;
# 写锁
LOCK TABLES table_name WRITE;
# 全局读锁
Flush tables with read lock;
# 解锁
UNLOCK TABLES;
Comments | NOTHING