总结一些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";