修改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@'%';
修改字段属性
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';
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 错误的服务器端节点执行以下命令：
```shell iperf3 -s ```
在出现重试、EOF 错误的客户端节点执行以下命令：
iperf3 -c <server-IP>
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
VIM Linux Life