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
的写法