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;
共有 0 条评论