mysql常用命令

MySQL内操作

查看左右数据库容量

SELECT 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)',
sum(truncate(DATA_FREE/1024/1024, 2)) as '碎片占用(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

操作权限

查看用户

select User,Host,authentication_string from mysql.user;

为用户授权

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname';

刷新

flush privileges;

关闭innodb的严格模式

vi /etc/my.cnf

innodb_strict_mode=0

打开genlog日志

set global general_log=1;
show variables like 'general%';

设置超时时间

SET global wait_timeout = 3600;
SET global connect_timeout = 3600;
SET global interactive_timeout = 3600;

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
SHOW GLOBAL VARIABLES LIKE 'connect_timeout';
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';

查看二进制日志开启状态

show variables like '%log_bin%';
show binary logs;

删除binlog日志

purge binary logs to 'binlog.000022';

删除binlog.000022之前的日志，保留该日志

查看数据库当前连接

SHOW PROCESSLIST;

mysql添加用户

mysql -uroot -p
输入刚刚配置安全时设置的密码
 
#创建数据库用户testmy,并授予远程连接权限。
create user 'testmy'@'%' identified by 'testmy123'; 
 
#为testmy用户授权数据库所有权限。
grant all privileges on *.* to 'testmy'@'%'; 
 
#创建新用户会默认使用新的 caching_sha2_password,客户端不支持新的加密方式,修改
ALTER USER 'testmy'@'%' IDENTIFIED WITH mysql_native_password BY 'testmy123';
 
#刷新权限。
flush privileges;

MySQL外操作

备份所有库所有函数并gzip压缩

mysqldump -u [用户名] -p [数据库名] | gzip > [备份文件路径].sql.gz
mysqldump -u root -p --all-databases --routines --events | gzip > /backup/alldb_20250326.sql.gz

刷新general_log日志

/usr/local/mysql/mysql-8.0/bin/mysqladmin -uroot -p flush-logs general

备份日志

/usr/local/mysql/mysql-8.0/bin/mysqldump -uroot -p$passwd  --all-databases --set-gtid-purged=OFF  > $backup_dir$current_time-all.sql

重启MySQL服务命令端

如果想要通过MySQL命令端来重启服务，在命令行中输入以下命令：

mysqladmin -u root -p shutdown
执行该命令之后，系统会提示输入管理员密码，输入密码后即可重启MySQL服务。

如果想要启动MySQL服务，可以使用以下命令：

mysqld_safe --user=mysql &
如果想要停止MySQL服务，可以使用以下命令：

mysqladmin -u root -p shutdown

查询用户

select User,Host,authentication_string from mysql.user;

 授权

grant select privileges on aaa.bbb to "abc"@"%" identified by "123456"; #创建一个只有对aaa库下的bbb表查询权限、可以在任何ip登陆的abc用户
grant all privileges on *.* to "user"@"%" identified by "123456";  #同理，创建所有权限（除grant）的用户

 

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
更新user为root，host为% 的密码为123456

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
更新user为root，host为localhost 的密码为123456

 

flush privileges;