MySQL 常用命令

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

免密码登录

方法一

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

方法二

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

用户

创建用户

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%';

字符集

# 更改数据库字符集
ALTER DATABASE DATABASE_NAME DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# 更改表字符集
ALTER TABLE <table-name> DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

# 更改表和表中所有字段字符集
ALTER TABLE <table-name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

• 配置字符集

其他

查看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";