数据库常用操作记录

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

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