mysql 杂记

MySQL 杂记

-- mysqld_exportor插件 监控用户权限
mysql -uroot -p
SHOW VARIABLES LIKE 'validate_password%';
set global validate_password_policy=LOW; 
set global validate_password_length=6;
-- 授权
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysql_exporter'@'localhost' IDENTIFIED BY '123456' WITH MAX_USER_CONNECTIONS 3;
flush privileges;

mysqldump -u root -p --master-data=2 --single-transaction --flush-logs --routines --triggers --events --all-databases >full_bak_202112151833.sql

mysqldump -u backup -p --master-data=2 --single-transaction --where "id>=4 and id <7" mydb3 employee > employ_4_6.sql

-- 在执行 mysqldump 时，你可以选择是否对表进行锁定。具体选项如下：
--lock-all-tables：锁定所有表，确保在备份期间不会被写操作影响。
--single-transaction：启用事务，使得备份时只锁定读取，而不会锁定写入操作。

0 2 * * 6 mysqldump --master-data=2 --single-transaction --databases buswash |gzip > /backup/mysql-backups/buswash_`date +\%F`.sql.gz
30 2 * * * mysqldump --master-data=2 --single-transaction  --databases keycloak |gzip > /backup/mysql-backups/keycloak_`date +\%F`.sql.gz
0 4 * * * find /backup/mysql-backups/ -type f -mtime +15  |xargs rm -f
0 3 * * 6 scp /backup/mysql-backups/buswash_`date +\%F`.sql.gz test@192.168.254.216:/home/test/mysql_backup   >/dev/null 2>&1
30 3 * * * scp /backup/mysql-backups/keycloak_`date +\%F`.sql.gz test@192.168.254.216:/home/test/mysql_backup   >/dev/null 2>&1

-- 全量备份恢复
mysqladmin -uroot -p flush-logs --  刷新log日志。
mysql -u root -p < full_bakv202112222136.sql

--  利用增量的binlog日志文件恢复增量数据
>mysqlbinlog data/binlog.000046 | mysql -uroot -p

--  查询 所有链接kill
SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist WHERE user='develop'
SELECT * FROM information_schema.PROCESSLIST WHERE ID = <Thread_ID>;

--  查看死锁信息
SHOW ENGINE INNODB STATUS;
ctrl+f  thread id \d+, 

1、查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX
2、查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3、查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
4、查询是否锁表
SHOW OPEN TABLES where In_use > 0;

show VARIABLES LIKE 'innodb_lock_wait_timeout';
--  查看当前的事务
select * from information_schema.innodb_trx;

SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

--  统计表容量
SELECT 
    table_name AS 'Table',    
        round(((data_length) / 1024 / 1024), 2) AS 'Data Size in MB',
        round(((index_length) / 1024 / 1024), 2) AS 'IndexData Size in MB',
    round(((data_length + index_length) / 1024 / 1024), 2) AS 'Totel Size in MB'
FROM 
    information_schema.TABLES 
WHERE 
    table_schema = 'bikeca'
ORDER BY 
    (data_length + index_length) DESC;

--  开启登录日志
SHOW VARIABLES LIKE 'general_log';
SET GLOBAL general_log = 'ON';
SET global log_output = 'table';
SELECT * FROM mysql.general_log WHERE command_type = 'Connect';

[mysqld]
general_log_file = "/var/log/mysql/mysql_general.log"
max_binlog_size = 100M
expire_logs_days = 7

--  查询表容量大小
SELECT ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'general_log';

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci utf8mb4_bin utf8mb4_general_ci

set persist max_connections=300;    mysql8.0 配置持久化至配置文件 mysqld-auto.cnf
select * from performance_schema.persisted_variables;

对读取的记录加S锁：
select * from student where id=5 LOCK IN SHARE MODE;
select * from student where id=5 FOR SHARE;  8.0新增语法
对读取的记录加X锁：
select * from student where id=5 FOR UPDATE; 
select * from student where id=5 FOR UPDATE NOWAIT;  8.0 会立即报错返回
select * from student where id=5 FOR UPDATE SKIP LOCKED;  8.0 返回结果不包括被锁定的行
LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。
LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。
UNLOCK TABLES; --  使用此命令解锁当前加锁的表
SHOW OPEN TABLES; --  主要关注In_use字段的值 或者  SHOW OPEN TABLES where In_use > 0;
SELECT * FROM performance_schema.data_lock_waits\G;   查看锁等待信息

Flush tables with read lock;    全局锁只读   unlock tables; 解锁

innodb_lock_wait_timeout=50s   锁等待超时时间设置
 show status like 'innodb_row_lock%';

replace函数直接替换mysql数据库中某字段中的特定字符串，不再需要自己写函数去替换，用起来非常的方便，mysql 替换函数replace()
Update `table_name` SET `field_name` = replace (`field_name`,’from_str’,'to_str’) Where `field_name` LIKE ‘%from_str%’

ALTER TABLE table_name DISABLE KEYS;   禁用索引
ALTER TABLE table_name ENABLE KEYS;    启用索引
SET foreign_key_checks=0;  禁用对外键的检查
SET foreign_key_checks=1;   恢复对外键的检查
set autocommit=0;
set autocommit=1;

SET GLOBAL MAX_EXECUTION_TIME=10000;    mysql 8.0限制执行时间

-- 8.0 隐藏索引优化器开关
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; -- 切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; -- 切换成非隐藏索引
 select @@optimizer_switch \G  查看优化器配置
 set session optimizer_switch="use_invisible_indexes=on";  配置优化器可使用隐藏索引

-- 5.7 query_cache_type有3个值 @代表关闭查询缓存OFF，1代表开启ON，2(DEMAND)  8.0已去除缓存
show variables like '%query_cache%';
my.cnf     query_cache_type=1    DEMAND:   select SQL_CACHE/SQL_NO_CACHE * from test where ID=5;

-- join_buffer_size=256k  决定驱动表数据能不能一次加载完
-- 增大max_length_for_sort_data和 sort_buffer_size 参数的设置优化排序

-- 查看sql执行过程
set profiling=1;
select * from table_name;
show profiles;
show profile for query 6;
show profile cpu,block io for query 7;

SELECT benchmark(1000, SELECT COUNT(*) FROM table_name);    sql执行一千次时间

mysql 8.0  激活所有角色  SET GLOBAL activate_all_roles_on_login=ON;

[client]
default-character-set=utf8

-- 修改已创建数据库的字符集
alter database dbtest1 character set 'utf8';
-- 修改已创建数据表的字符集
alter table t_emp convert to character set 'utf8';
-- 查看服务器的字符集和比较规则
SHOW VARIABLES LIKE  server';
-- 查看数据库的字符集和比较规则
SHOW VARIABLES LIKE  database';
-- 查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;
-- 修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
-- 查看服务器的字符集和比较规则
SHOW VARIABLES LIKE  server';
-- 查看数据库的字符集和比较规则
SHOW VARIABLES LIKE  database';
-- 查看具体数据库的字符集
SHOW CREATE DATABASE dbtest1;
-- 修改具体数据库的字符集
ALTER DATABASE dbtest1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
-- 查看表的字符集
SHOW CREATE TABLE emp1;
-- 查看表的比较规则
SHOW TABLE STATUS FROM atquigudb LIKE 'employees';
-- 修改表的字符集和比较规则
ALTER TABIE emp1 DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

mysqlbinlog delete恢复insert执行sql导出
mysqlbinlog --no-defaults -d bizpush mysql-bin.000640
mysqlbinlog --no-defaults  --start-datetime="2023-07-8 1:30:00" --stop-datetime="2023-07-8 1:30:12" mysql-bin.000640
mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime="2023-07-8 1:30:00" --stop-datetime="2023-07-8 1:30:12" -d bizpush mysql-bin.000640 > out.sql
mysqlbinlog --no-defaults --base64-output=decode-rows --database bizpush   -v   --start-position=262970015  --stop-position=264861184  mysql-bin.000640 > push.sql
cat push.sql | sed -n '/-- -- -- /p' | sed 's/-- -- --  //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/VALUES(/g;' |sed -r 's/(@11.*),/\1);/g'| sed 's/@1=//g'| sed 's/@[1-9]=/,/g' | sed 's/@[1-9][0-9]=/,/g' | sed '/INSERT/i );'  > push-7.8.sql
split -l 50000000 push-7.7.sql  -d -a 2 push-7.7

-- 事务自动提交: 
show variables like '%autocommit%';
set autocommit OFF;  默认ON   DML使用，DDL不受影响
delete from table ;
rollback;
commit;

-- 内存优化：
SELECT @@innodb_buffer_pool_size;
SELECT @@innodb_buffer_pool_instances;
SELECT @@innodb_buffer_pool_chunk_size;

-- 5.7修改默认字符集
show VARIABLES LIKE 'CHARACTER_%';
show VARIABLES LIKE 'COLLATION_%';
[mysql] 
default-character-set=utf8 -- 默认字符集
[mysqld] 
character-set-server=utf8
collation-server=utf8_general_ci

-- 字符集
ALTER DATABASE 数据库名 CHARACTER SET 字符集; -- 比如：gbk、utf8等
create database test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
ALTER database test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
ALTER TABLE  表名 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
 ALTER TABLE  表名 CHANGE  列名  列名  VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER DATABASE test CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`;
ALTER TABLE `TABLE_NAME` CONVERT TO CHARACTER SET `utf8mb4` COLLATE `utf8mb4_general_ci`; 

-- 释放被挂起的用户
set global connection_control_failed_connections_threshold=5

select user,host,plugin from mysql.user;
alter user '{用户名}'@'{主机地址}' identified with {身份验证插件类型} by '{新密码}';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
ALTER USER 'root'@'%' IDENTIFIED WITH caching_sha2_password BY '123456';
FLUSH PRIVILEGES;

ALTER USER 'root'@'%' IDENTIFIED BY '123456';
update mysql.user set password=password('xxxxxx') where user='root';     mysql 5.6

-- 修改sql_mode  cat /etc/my.cnf
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

-- 删除表
select concat("DROP TABLE IF EXISTS ", table_name, ";") from information_schema.tables where table_schema="Your_database_name";
-- 清空表
select concat("truncate TABLE ", table_name, ";") from information_schema.tables where table_schema="Your_database_name";

-- 创建库表及授权
CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;】
create database yourdb DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
create database sina default character set utf8mb4 collate utf8mb4_unicode_ci; 或者 create database confluence default character set utf8mb4 collate utf8mb4_bin; 修改用alter
create user jumpserver identified by 'n6vVsh0RlKJZTNV7C48agGno';
grant all privileges on jumpserver.* to 'jumpserver'@'localhost' identified by 'n6vVsh0RlKJZTNV7C48agGno' with grant option;
flush privileges;

-- 查询表字段被引用记录：
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME != ""

-- mysqldump备份用户授权
create user dumper@'%' identified by 'Dumper@123';
grant select on *.* to dumper@'%';
grant show view on *.* to dumper@'%';
grant lock tables on *.* to dumper@'%';
grant trigger on *.* to dumper@'%';
grant process on *.* to dumper@'%';
grant event on *.* to dumper@'%';
grant reload on *.* to dumper@'%';
grant replication client on *.* to dumper@'%';

revoke 'common' from 'xxxx';
revoke insert on testdb.* from xxxx;
RENAME USER 'test1'@'localhost'  TO 'testUser1'@'localhost';

-- 修改字段属性
alter table users modify column username varchar(30);
alter table users modify column registerTime datetime;

-- root用户丢失特权修复
skip-grant-tables
update mysql.user set Grant_priv='Y' ,Super_priv='Y' where User='root' and Host='localhost';
GRANT ALL ON *.* TO 'root'@'localhost'; 
flush privileges;

-- 查询变量
show variables like '%index%';
SHOW VARIABLES LIKE '%max_connections%';  
SHOW STATUS LIKE '%Connection%';  
show status like 'Threads%';
set global log_queries_not_using_indexes=OFF;
-- 查询进程
SHOW PROCESSLIST;  
SHOW FULL PROCESSLIST;  
flush hosts;

-- 导出表格
mysql -u vault -p -h 192.168.2.198 -P 4000 -e "SELECT * FROM app" vault > /root/cmd1.xlsx

-- 禁止MySQL对外部链接进行DNS解析
skip-name-resolve

-- pxc时区
default-time-zone='+08:00'
set global time_zone = '+8:00';

-- 修改sql文件
sed '1,40s/oldname/newname/g'  monitor.sql

-- pxc工作模式
SET GLOBAL pxc_strict_mode=PERMISSIVE;
SET GLOBAL pxc_strict_mode=ENFORCING;

-- 指定用户与目录启动数据库
mysqld --defaults-file=/root/mp/backup-my.cnf --user=root --skip-grant-tables --datadir=/root/mp &

-- 首先，修改validate_password_policy参数的值 
mysql> set global validate_password_policy=0;

validate_password_length(密码长度)参数默认为8，我们修改为1 
mysql> set global validate_password_length=1;

-- root用户设置
alter user 'root'@'localhost' identified by 'xxxxxxxxxxx';
use mysql;
update user set host = '%' where user = 'root';
set password for 'root'@'%' = '0101001';
SET PASSWORD = PASSWORD('XXXXXXX');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'XXXXXX' WITH GRANT OPTION;
flush privileges;

-- 备份
30 1 * * * root mysqldump -u root-pPASSWORD --all-databases |gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

-- 常用命令
create database laozi default character set utf8 collate utf8_general_ci;
create user 'laozi'@'%' identified by 'Laozi@123';
grant all privileges on cs_park.* to 'cs_park'@'%' with grant option;
flush privileges;
create database `double-prevention`;
CREATE USER 'double-prevention'@'%' IDENTIFIED BY 'Double-prevention@123';
GRANT ALL PRIVILEGES ON `double-prevention`.* TO 'double-prevention'@'%' WITH GRANT OPTION;
flush privileges;

-- 在出现重试、EOF 错误的服务器端节点执行以下命令：
iperf3 -s
-- 在出现重试、EOF 错误的客户端节点执行以下命令：
iperf3 -c <server-IP>

-- mysqldump备份
mysqldump -u用户名 -p密码 数据库名 表名 --where="筛选条件" > 导出文件路径
mysqldump -uroot -p123456 meteo sdata --where=" sensorid=11 and fieldid=0" > /home/xyx/Temp.sql
mysqldump -d -A --add-drop-table -uroot -p >xxx.sql
1.导出结构不导出数据  mysqldump -d 数据库名 -uroot -p > xxx.sql
2.导出数据不导出结构  mysqldump -t 数据库名 -uroot -p > xxx.sql
3.导出数据和表结构  mysqldump 数据库名 -uroot -p > xxx.sql
4.导出特定表的结构  mysqldump -uroot -p -B数据库名 --table 表名 > xxx.sql

-- Oracle 官方迁移工具，如 OGG、Gateway（透明网关）、CDC（Change Data Capture）

optimize table table_name 命令。该命令会对表进行碎片整理,去除空洞。show table status

helm inspect values pingcap/tidb-operator --version=${chart_version} > ${HOME}/tidb-operator/values-tidb-operator.yaml
helm install tidb-operator pingcap/tidb-operator --namespace=tidb-admin --version=${chart_version} -f ${HOME}/tidb-operator/values-tidb-operator.yaml && \
kubectl get po -n tidb-admin -l app.kubernetes.io/name=tidb-operator
helm upgrade tidb-operator pingcap/tidb-operator -f ${HOME}/tidb-operator/values-tidb-operator.yaml

-- 查询该字段中，内容最长的一条记录占多少个字节（注意：在mysql中，utf8编码格式下，一个汉字占一个字符，一个字符有三个字节长度）。
select length(max(title)) from item limit 0,1;

-- 查询该字段中，内容最短的一条记录占多少个字节
select length(min(title)) from item limit 0,1;

-- 查询title中从左边数第一个不重复的汉字（也就是一个字符）有多少行
select count(distinct left(title,1)) from item;

-- 计算title中，从左边数第10个不重复的汉字的行数与总行数的比
select count(distinct left(title,10))/count(*) from item;

-- mysql字段加密
UPDATE t_certify_user_test
SET ID_CARD_ = to_base64 ( AES_ENCRYPT( ID_CARD_, '86vRNVpxKX1zanzL' ) ),
REAL_NAME_ = to_base64 ( AES_ENCRYPT( REAL_NAME_, '86vRNVpxKX1zanzL' ) )
UPDATE t_certify_user_test
SET ID_CARD_ = AES_DECRYPT(FROM_BASE64(ID_CARD_),'86vRNVpxKX1zanzL'),
REAL_NAME_ = AES_DECRYPT(FROM_BASE64(REAL_NAME_),'86vRNVpxKX1zanzL')
where  CHAR_LENGTH(ID_CARD_) > 20
innodb_large_prefix参数修改为ON
UPDATE t_certify_user 
SET ID_CARD_ = replace(ID_CARD_,' ','')
where  CHAR_LENGTH(ID_CARD_) > 20

-- mysql 8.0 持久化变量
SET PERSIST max_connections = 1200;
SET PERSIST
-- 语句可以修改内存中变量的值，并且将修改后的值写⼊数据⽬录中的 mysqld-auto.cnf 中。
SET PERSIST_ONLY
-- 语句不会修改内存中变量的值，只是将修改后的值写⼊数据⽬录中的 mysqld-auto.cnf 中。
-- 清空变量设置
RESET PERSIST;
-- 需要的权限
GRANTSYSTEM_VARIABLES_ADMIN,PERSIST_RO_VARIABLES_ADMIN,SHUTDOWNon*.*to'admin'@'localhost';