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;

3.查看表

use thege;
show tables;
desc 表名;

4.查看数据库大小

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;

5.创建用户

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';           #修改密码

6.查看授权

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用户

7.备份数据库

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   #仅备份数据库结构

8.备份除某张表以外的数据

mysqldump -u root -pmonitor monitor --ignore-table=monitor.cm_vehicle_track > monitor.sql

9.还原数据库

mysql -uroot -pPassword Thege < Thege.sql

10.修改连接数

show variables like '%connect%';
show status like '%connections%';
set global max_connections=3000;

11.修改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;