MySQL常用操作

实用语句

查询指定ip的连接，并拼接出批量kill语句

SELECT GROUP_CONCAT('kill ', SEPARATOR id, ';') FROM information_schema.processlist WHERE HOST LIKE '%191.8.1.77%';

数据导出

# 仅导出表结构
mysqldump -d electricity -u root -p123456 > electricity_schema.sql

# 仅导出数据
mysqldump -t electricity -u root -p123456 > electricity_data.sql

# 导出electricity数据库中t_edata_day表，然后打包tar.gz，然后使用sz下载
mysqldump -u root -pxxxxxx electricity t_edata_day > t_edata_day.sql && tar -czf t_edata_day.tar.gz t_edata_day.sql && sz -bye t_edata_day.tar.gz

注意：

如果是 MySQL 5.8 导出后检查导出的语句中，是否有错的字符集 0900_ai，如果有就要将文件内的所有utf8mb4_0900_ai_ci 换成 utf8mb4_general_ci

命令中密码需要紧跟-p不要有空格

数据导入

先登录：

mysql -u root -p

再导入：

-- 建数据库
CREATE DATABASE `electricity` DEFAULT CHARACTER SET utf8mb4 collate utf8mb4_general_ci;

-- 导入表结构和数据
use electricity;
source /home/user/download/mysql/backup/electricity_schema.sql
source /home/user/download/mysql/backup/electricity_data.sql

添加索引

ALTER TABLE `t_room` ADD UNIQUE (`column`)

账号管理

-- 删除账号
drop user 'aaa'@'192.168.222.333';

-- 创建esystem账号，密码123456，限定只能从本机访问electricity数据库

CREATE USER 'esystem'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT ALL PRIVILEGES ON electricity.* TO 'esystem'@'localhost';

-- 创建esystem账号，密码123456，限定只能从ip为192.168.222.333的主机上访问electricity数据库

CREATE USER 'esystem'@'192.168.222.333' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT ALL PRIVILEGES ON electricity.* TO 'esystem2'@'192.168.222.333';

-- 创建wulian账号，密码123456，限定能从任何ip的主机上访问当前MySQL的任何数据库

CRAETE USER 'wulian'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'wulian'@'%' identified by '123456';

-- 创建用户后需要刷新权限
flush privileges;

注意：
IDENTIFIED WITH mysql_native_password是 MySQL5.8 的写法，而5.7可以用IDENTIFIED BY的写法