MySQL 常用命令

总结一些MySQL的常用命令，已备以后忘记了。。。

免密码登录

方法一

[root@xiexianbin_cn ~]# cat .my.cnf
[client]
user=root
password=pass

方法二

mysql -uroot -S /var/lib/mysql/mysql.sock

用户

GRANT 语法

# 赋权
GRANT privileges (columns)
    ON what
    TO user IDENTIFIED BY "password"
    WITH GRANT OPTION

# 写入、刷新权限
FLUSH PRIVILEGES;

# 查询
show grants for <user>;

• 权限列表
  • ALL 所有权限，ALL PRIVILEGES同义词
  • ALTER 修改表和索引
  • CREATE 创建数据库和表
  • DELETE 删除表中已有的记录
  • DROP 抛弃(删除)数据库和表
  • INDEX 创建或抛弃索引
  • INSERT 向表中插入新行
  • REFERENCE 未用
  • SELECT 检索表中的记录
  • UPDATE 修改现存表记录
  • FILE 读或写服务器上的文件
  • PROCESS 查看服务器中执行的线程信息或杀死线程
  • RELOAD 重载授权表或清空日志、主机缓存或表缓存
  • SHUTDOWN 关闭服务器
  • USAGE 特殊的 无权限 权限

创建用户

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root' WITH GRANT OPTION;
FLUSH PRIVILEGES;

GRANT ALL ON test.* TO 'root'@'%' IDENTIFIED BY 'test';
GRANT ALL ON test.* TO 'root'@'localhost' IDENTIFIED BY 'test' WITH GRANT OPTION;
FLUSH PRIVILEGES;

GRANT SElECT ON test.* TO 'root'@'${ip}' IDENTIFIED BY "test";
FLUSH PRIVILEGES;

用户权限

show grants for itat@‘%';

数据库与表

查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data1              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

创建数据库

CREATE DATABASE IF NOT EXISTS data1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

mysql> create database date2;
Query OK, 1 row affected (0.01 sec)

mysql -uroot -proot -Dmysql -e"select host,user from user";

for name in a b c; do
  mysql -uroot -proot -Dmysql -e"CREATE DATABASE IF NOT EXISTS ${name}_t DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
  mysql -uroot -proot -Dmysql -e"CREATE DATABASE IF NOT EXISTS ${name}_o DEFAULT CHARSET utf8 COLLATE utf8_general_ci;";
done

删除数据库

drop database data1;

创建表

CREATE TABLE training(
  id INT(11) PRIMARY KEY,
  col1 VARCHAR(20) NOT NULL,
  START DATE);

查看表创建命令

mysql> show create table t_config \G
*************************** 1. row ***************************
       Table: t_config
Create Table: CREATE TABLE `t_config` (
  `id` int(11) NOT NULL,
  `col2` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

binlog

查看log类型

show variables like 'log_%';

查看binlog类型

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

查看binlog的相关参数

show variables like '%binlog%';

其他

查看mysql服务器数据文件

show variables like '%datadir%';

查看mysql版本

show variables like '%version%';

查看系统变量控制二进制日志自动删除的天数

show variables like 'expire_log%';
set global expire_logs_days=7;
select @@expire_logs_days;

查看MySQL支持的存储引擎

show engines;

查看innodb的状态

show engine innodb status;

数据库备份和还原

mysqldump -h $MYSQL_IP -u root -p$MYSQL_PASS <db-name> > /root/<db-name>-$(date +%s).sql
mysql -h$MYSQL_IP -p$MYSQL_PASS <db-name> < <db-name>-<time>.sql

表拆分

create table hpc_job_table_2021_1231 like hpc_job_table;
insert into hpc_job_table_2021_1231 select * from hpc_job_table where time_end < "2021-12-31";
delete from hpc_job_table where time_end < "2021-12-31";