mysql常见用法

1、常见命令

1.1、查看所有账户信息

select host,user from mysql.user;

select distinct concat('User: ''',user,'''@''',host,''';') as query from mysql.user;

 

1.2、修改账户密码

use mysql;

UPDATE user SET authentication_string=PASSWORD('Root123!') WHERE user='root';

 

1.3、创建账户

create user 'deployusr'@'localhost' identified by 'Deploy123!';
create user 'deployusr'@'%' identified by 'Deploy123!';

 

1.4、给库赋予权限

use deploy；
grant all privileges on deploy to 'deployusr'@'localhost';

grant select,insert,update,delete,create,drop on deploy.* to 'deployusr'@'localhost';

grant all privileges on deploy.* to 'deployusr'@'%';

 

1.5、对表赋予权限

grant select,insert,update,delete,create,drop on deploy.table1 to 'deployusr'@'%';

 

1.6、普通用户想要导出数据库中的表需要赋予file权限
首先赋予其他权限
grant select,insert,update,delete,create,drop on deploy.* to 'deployusr'@'%';

然后赋予file权限
grant file on *.* to deployusr@'%';

flush privileges;

默认只能导出到/var/lib/mysql-files目录，导入其他目录会报错

 

1.7、查看用户权限

show grants for deployusr;
show grants for 'deployusr'@'%';

 

1.8、撤销权限

revoke all on deploy from 'deployusr'@'localhost';

flush privileges;

 

1.9、删除用户和库

delete from user where user='deployusr' and host='localhost';

或者

drop user 'deployusr'@'localhost';

 

flush privileges;

drop database deploy;

 

1.10、备份数据库

mysqldump -u root -h host -p deploy > backup_deploy.sql

 

1.11、备份表

mysqldump -u root -h host -p deploy table1, table2 > backup_deploy_table.sql

 

1.12、批量执行插入等操作

把命令写入.sql的文件中
登录数据库
进入相应表
source .sql

 

1.13、查看表空间

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 = 'missing_cust' 
group by TABLE_NAME 
order by data_length desc;

 

2、跳过密码登录

vi /etc/my.cnf
skip-grant-tables

 

3、最大超时时间设置

参考：
https://www.cnblogs.com/ivictor/p/5979731.html

 

4、锁表问题

参考：
https://www.iteye.com/blog/johnny-lee-2434634

 

5、truncate

参考：
https://blog.csdn.net/qq_37871669/article/details/9864754