数据库常用操作记录
1、mysqldump
-B 在备份数据库的时候会在备份的语句中写入create database的语句，导回来的时候就不需要指定库了,在-B的后面可以添加多个库的名字。
--singe-transaction这个参数在innodedb引擎中独有，作用是在导出的时候不锁表导出数据,保证数据的一致性。
mysqldump -B --singe-transaction 数据库1 数据库2
# 常用备份脚本
mysqldump -uroot -p123456 -P 3306 --routines --triggers --events --set-gtid-purged=OFF --single-transaction --flush-logs --master-data=2 --all-databases | gzip > /backup/backup-all-$(date +%F).sql.gz
find /backup -type f -mtime +7 -delete
2、导出表和导出表结构
-d 是只导出表结构，不导出表数据。
-t只导出表数据不导出表结构。
数据库的sql线程管理
通过show full processlist可以把正在执行的sql状态打印出来。第一行的id 就是线程的id,如果有锁表的查询语句可以直接通过kill id的方式来解决。有时候因为查询语句导致，占用cpu过高，可以查询到是那条语句造成的然后再进行结束线程处理。
导出触发器和存储过程
mysqldump -uxxx -pxxx -n -t -d -R ecology > tri.sql
--triggers # 导出触发器
-d # 结构(--no-data:不导出任何数据，只导出数据库表结构)
-t # 数据(--no-create-info:只导出数据，而不添加CREATE TABLE 语句)
-n # (--no-create-db:只导出数据，而不添加CREATE DATABASE 语句）
-R # (--routines:导出存储过程以及自定义函数)
3、从库设置只读
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.00 sec)
4、查表的触发器
mysql> SHOW TRIGGERs like 'wsforma%'\G;
like 是表名字
SHOW TRIGGERS FROM ecology LIKE 'cptcapital';
FROM 来自 哪个数据库
like 是哪个表名
5、mysql8.0修改root密码
1、关闭服务 systemctl stop mysqld
2、编辑my.conf内容并保存：
vi /etc/my.cnf
[mysqld]
skip-grant-tables # 跳过权限
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
3、mysql -u root -p 回车，不输入密码再回车。即可进入终端。
4、修改密码
use mysql;
update user set authentication_string='' where user='root';
FLUSH PRIVILEGES;
alter user 'root'@'localhost' identified by '你的密码';
FLUSH PRIVILEGES;
5、编辑my.cnf 注释掉2中修改的内容，重启mysql。
6、物理备份和恢复
xtrabackup下载地址
下载地址：https://www.percona.com/downloads
文档地址：https://docs.percona.com/percona-xtrabackup/8.0/index.html
官网说法版本大于等于数据库的版本就行。
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/tarball/percona-xtrabackup-2.4.27-Linux-x86_64.glibc2.12.tar.gz
# ==================物理备份============
xtrabackup --compress-threads=4 --datadir=/data/mysql/data/ --host=127.0.0.1 --user=admin --password=AWUye2A4JNN! --backup --parallel=4 --target-dir=/data/backups-jgj/`date +%F_%H`
--parallel=4 # 此选项指定xtrabackup子进程应用于同时备份文件的线程数。
--databases-exclude=name # 排除的库
-S # 指定socket
xtrabackup --defaults-file=/etc/my.cnf --compress-threads=6 --user='admin' --password='AWUye2A4JNN!' --throttle=600 --lock-ddl-per-table --check-privileges --no-version-check --backup --target-dir=/mnt/backup_2212228 &>/var/log/xtrabackup.log
--compress-threads=6 # 压缩备份的线程
--lock-ddl-per-table # 在2.4.8 之后的版本才有，备份的时候加上防止ddl语句造成的报错。
--throttle=600 # 根据磁盘IO调整大小，在执行的过程中，会涉及redolog拷贝的问题。
--compress # 备份的时候压缩
--no-lock # 备份不锁表
# =================恢复阶段==============
# 1、如果是qp文件需要先解压,不然会直接报错
wget https://repo.percona.com/yum/release/7/RPMS/x86_64/qpress-11-1.el7.x86_64.rpm
rpm -ivh qpress-11-1.el7.x86_64.rpm
# 2、解压
xtrabackup --decompress --parallel=4 --target-dir=/mysqldatabak/xtrabak/full_20220821
--parallel=4 # 用于并行数据文件传输的线程数。
# 3、恢复文件 执行一次CSR的过程
xtrabackup --prepare --apply-log-only --target-dir=/mysqldatabak/xtrabak/full_20220821
# 4、把数据拷贝到数据目录
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/data/nfs-data/backups-jgj/2022-12-29_09/
7、逻辑备份语句
mysqldump -S /var/lib/mysql/mysql.sock -pKnt@123 --routines --triggers --events --set-gtid-purged=OFF --single-transaction --flush-logs --master-data=2 --all-databases | gzip > all_databases.tar.gz
--singe-transaction 这个参数在`innodedb`引擎中独有，作用是在导出的时候不锁表导出数据,保证数据的一致性
--set-gtid-purged=OFF gtid模式下使用
--flush-logs 生成新的二进制日志文件
8、授权用户,和修改密码
CREATE USER 'admin2'@'%' IDENTIFIED BY 'L4bAWUye2A4JNN!';
GRANT ALL ON *.* TO 'admin2'@'%' WITH GRANT OPTION;
# WITH GRANT OPTION 授权特殊权限；不加就不授权；
docker 部署最简单的mysql
vi /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
collation_server=utf8_general_ci
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
symbolic-links=0
pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
# 最简单的docker mysql
docker run -d \
--name=mysql \
-p 3306:3306 \
-v /etc/timezone:/etc/timezone:ro \
-v /etc/localtime:/etc/localtime:ro \
-v /data/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD="root" \
mysql:5.7
误删除表恢复案例
案例1：
误删表数据：ecology.formtable_main_742
1)按日期提取binlog日志，只取DELETE部分：
mysqlbinlog --start-datetime="2023-02-03 00:00:00" --base64-output=decode-rows -v -v mysql-bin.000143 |sed -n '/### DELETE FROM `ecology`.`formtable_main_742`/,/COMMIT/p' >742.sql
--start-datetime= # 过滤执行的时间
--stop-datetime= # 过滤结束的时间
-d # 指定过滤的数据库
--no-defaults # 忽略默认配置，可以防止一些因为编码问题而报错。
2)转换为sql
cat 742.sql | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed -E 's/\@[0-9]+==//g' > 742.ok.sql
说明：(@4.*)在第4个值后面加;号，如果值有50行，需要修改为50
mysqlbinlog --start-datetime='2024-09-03 16:03:00' --stop-datetime='2024-09-03 16:05:00' -d ecology -vvv mysql-bin.000557 > /tmp/202409031604.log
3)恢复
mysql -p db1 < 742.ok.sql
查锁表
show OPEN TABLES where In_use > 0;
name_locked 0 表示被锁了
查询所有进程
SELECT * FROM information_schema.processlist;
主从延迟sql
查看导致主从延迟的sql语句
# 查看大于60s的事务
SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(timediff(now(),trx_started)) > 60\G;
SELECT trx_id, trx_started, trx_state, trx_query
FROM information_schema.innodb_trx
WHERE trx_id = 12345;
SELECT @@trx_id;
设置客户端字符集
mysql --default-character-set=utf8mb4
