Mysql常用命令

1.设置密码

MySQL 5.7.5及更早版本：
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewPass');   
MySQL 5.7.6及更高版本：
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass';

2.数据库操作

show databases;
create database thege;
#查看表
use thege;
show tables;
desc 表名;
truncate table table_name;
#批量清空表，删除tp_前缀表
select concat('truncate table ', table_name, ';') from information_schema.tables
where table_name like 'tp_%';
#删除以15开头的手机号
delete from customer_info where customer_phone like '15%';
#删除以com结尾的邮箱
delete from customer_info where customer_mail like '%com';
#查看数据库大小
select concat(round(sum(DATA_LENGTH)/1024/1024+sum(INDEX_LENGTH)/1024/1024),'M') from information_schema.tables where table_schema='thege';
select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size,concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA = 'thege' group by TABLE_NAME order by data_length desc;
#创建用户
create user 'test001'@'%' identified by 'test001';                           #创建test001用户    
grant all on thege.* to 'test001'@'%';                                       #数据库thege授权给test001用户
grant all on thege01.* to 'test001'@'%';                                     #数据库thege01授权给test001用户
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON thege.* TO 'test002'@'%';   #创建特定权限用户
grant all privileges on *.* to root@"%" identified by 'Mysql@123';           #直接创建远程授权用户
UPDATE user SET password=PASSWORD('newpassword') WHERE user='user';         
#查看授权
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;  
#查看所有用户
show grants for test001;                                                     #查看test001用户授权
REVOKE ALL ON thege.*  FROM 'test001'@'%';                                   #移除test001授权
DROP USER 'test001'@'%';                                                     #删除test001用户
#备份数据库
mysqldump -h 127.0.0.1 -u root -pPassword Thege > Thege.sql;
mysqldump --all-databases  -u root -p > mysql.sql
mysqldump --no-data  -uroot -pPassword --databases Thege   > Thege.sql   
#仅备份数据库结构
#备份除某张表以外的数据
mysqldump -u root -pmonitor monitor --ignore-table=monitor.cm_vehicle_track > monitor.sql
#还原数据库
mysql -uroot -pPassword Thege < Thege.sql
#修改连接数
show variables like '%connect%';
show status like '%connections%';
set global max_connections=3000;
#修改open_files_limit
#查看当前配置
show global variables like '%open%';
show global status like 'open%file%';
#修改配置
tee -a /lib/systemd/system/mysqld.service <<-'EOF' 
LimitNOFILE=10000
LimitMEMLOCK=10000 
EOF
#重启服务
systemctl daemon-reload;
systemctl restart  mysqld.service;
#open_files过大，可清除缓存
flush tables;
#忽略大小写配置
SHOW VARIABLES LIKE '%case%'
lower_case_table_names=1