mysql 常见用法_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.youkuaiyun.com/qq_37871669/article/details/98647541