MySQL 常用命令行

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明:username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';

用户授权

GRANT privileges ON databasename.tablename TO 'username'@'host'

说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用 * 表示, 如 . . 例子:

GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';

MYSQL可能的设置的权限

• 表权限: 'Select', 'Insert', 'Update', 'Delete', 'Create', 'Drop', 'Grant', 'References', 'Index', 'Alter'
• 列权限: 'Select', 'Insert', 'Update', 'References'
• 过程权限: 'Execute', 'Alter Routine', 'Grant'

注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

查看现有用户授权

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看某个用户的权限

mysql> show grants for 'jack'@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for jack@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jack'@'%' IDENTIFIED BY PASSWORD '*9BCDC990E611B8D852EFAF1E3919AB6AC8C8A9F0' |
+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

对账户重命名

mysql> rename user 'jack'@'%' to 'jim'@'%';
Query OK, 0 rows affected (0.00 sec)

修改账户的host

UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;
-- and an analogous UPDATE-statement to change it back. Also you might need to make changes to the mysql.db table as well:
UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='username';

设置与更改用户密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用

SET PASSWORD = PASSWORD("newpassword");

例子:

SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");

撤销用户权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

说明: privilege, databasename, tablename - 同授权部分. 例子:

-- 删除所有权限
revoke all on some_db.* from 'peppa'@'%';
-- 仅删除select权限
REVOKE SELECT ON *.* FROM 'pig'@'%';

注意: REVOKE和GRANT必须严格对应. 假如你在给用户'pig'@'%'授权的时候是这样的(或类似的)

GRANT SELECT ON test.user TO 'pig'@'%'

则在使用

REVOKE SELECT ON *.* FROM 'pig'@'%'

命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是

GRANT SELECT ON *.* TO 'pig'@'%'

则

REVOKE SELECT ON test.user FROM 'pig'@'%'

命令也不能撤销该用户对test数据库中user表的Select 权限.
具体信息可以用SHOW GRANTS命令查看.

SHOW GRANTS FOR 'pig'@'%';

删除用户

DROP USER 'username'@'host'

一个典型的数据库建表建用户过程

-- 创建用于localhost连接的用户并指定密码
mysql> create user 'pcom'@'localhost' identified by 'aaa7B2249';
Query OK, 0 rows affected (0.00 sec)

-- 创建数据库
mysql> create database pcom default character set utf8 collate utf8_bin; # or utf8_general_ci
Query OK, 1 row affected (0.00 sec)

-- 给本地用户授权, 这里不需要指定密码 (因为前面创建时, 对应localhost的密码已经创建了)
mysql> grant all on pcom.* to 'pcom'@'localhost';           
Query OK, 0 rows affected (0.00 sec)

-- 给其他IP地址下的用户授权, 注意: 因为前面未创建此IP下的用户, 这里必须指定密码, 否则就可以无密码访问
mysql> grant all on pcom.* to 'pcom'@'192.168.0.0/255.255.0.0' identified by 'aaa7B2249';
Query OK, 0 rows affected (0.00 sec)

-- 同样的
mysql> grant all on pcom.* to 'pcom'@'172.20.0.0/255.255.0.0' identified by 'aaa7B2249';       
Query OK, 0 rows affected (0.00 sec)

导出数据并压缩

执行mysqldump的用户需要的权限:

GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON dbname.* TO 'dump-user'@'localhost';

导出数据时, 为了避免输入用户名密码交互, 可以在user home下创建 .mysql.cnf, 写入以下内容

[client]
user=mysqluser
password=mysqlpass

MySQL 5.7以前

导出整个数据库:

mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u someuser -p somedatabase > dump.sql

导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u someuser -p somedatabase tbl_1 > dump_tbl_1.sql

默认一个insert带多个值, 如果要每个insert一个值, 可以用--extended-insert=FALSE参数

mysqldump -u aaa -p --extended-insert=FALSE some_db some_table > output.sql

如果要限制一个insert带的值的数量, 没有直接的参数, 但是可以通过控制 net_buffer_length 减小值得数量

mysqldump --net_buffer_length=4096 -u some_user -p some_db some_table > output.sql

导出一个数据库结构

mysqldump -u someuser -p -d --add-drop-table somedatabase > dump_schema.sql
-d: 没有数据 
--add-drop-table: 在每个create语句之前增加一个drop table 

忽略某些表 --ignore-table=${DATABASE}.${TABLE}
如果有多个表, 上面的参数要使用多次(不能在同一个参数里加多个表名)

用bzip2压缩

tar -cv somefile | bzip2 > somefile.tar.bz2

如果需要严格时间点上的备份, 需要配置MySQL主从, 并在从库上进行备份, 可以参考官方文章 https://dev.mysql.com/doc/refman/5.5/en/replication-solutions-backups-read-only.html

在从库上进行以下操作

mysql> FLUSH TABLES WITH READ LOCK;
-- 如果从库已经配置为 read_only则不需这步
mysql> SET GLOBAL read_only = ON;
-- 此时从库停止同步
-- ...
-- 进行mysqldump或者其他的导出操作
-- ...
mysql> SET GLOBAL read_only = OFF;
-- 如果从库已经配置为 read_only则不需这步
mysql> UNLOCK TABLES;
-- 此时从库会开始同步主库

MySQL8 开启ReplicaSet

导出整个服务器

mysqldump -uroot -p --all-databases --triggers --routines --events > dump.sql
Enter password: 

导出单个数据库

mysqldump -uroot -p --set-gtid-purged=OFF somedb > somedb.sql
Enter password: 

对于带中文的内容，dump和restore时都需要加参数--default-character-set=utf8

使用mysqlpump

mysqlpump --single-transaction --host=192.168.12.12 --port=3306 --databases mydatabase --result-file=dumptest.sql -uroot -p 

解压缩并导入数据

# 对于较新的linux版本
tar -xf somefile.tar.bz2 

cd 至dump.sql文件所在目录
# 登入mysql
mysql -u someuser -p
# 选择db
mysql> use db_name;
# 导入dump, 设置 autocommit=0 可以提高速度
mysql> SET autocommit=0 ; source dump.sql ; COMMIT ;

或者直接在shell中通过mysql导入

mysql -u username -p database_name < file.sql

导入速度优化

调整配置, 在my.cnf中 [mysqld]下添加以下配置项, 值可以根据自己的服务器实际配置调整

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0

重启mysql, 用这些命令检查

select @@innodb_buffer_pool_size;
select @@innodb_log_buffer_size;
select @@innodb_doublewrite;

在导入开始前执行

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

如果要检查这几项的值, 用show variables like 'autocommit';这样的命令检查

show variables like 'autocommit';
show variables like 'unique_checks';
show variables like 'foreign_key_checks';

导入结束后恢复原my.cnf, 然后重启mysql服务, autocommit这几项在重启后会恢复回默认启用的状态.

如果不重启, 执行下面的命令恢复, 第一个commit 不能忘

COMMIT;
SET unique_checks=1;
SET foreign_key_checks=1;

启用这些优化后, 普通机械硬盘的MySQL服务器上, 6G的sql导入时间大约在25分钟

其他命令

查看MySQL服务器当前连接的详细资料
show processlist;
或者在命令行下

./mysqladmin -uadmin -p -h 10.140.1.1 processlist
MySQL5.7的密码策略设置

参考 https://dev.mysql.com/doc/refman/5.7/en/validate-password-installation.html

检查是否已安装validate_password

SHOW VARIABLES LIKE 'validate_password%';
安装

# 方法1, 
vi /etc/my.cnf
[mysqld]
plugin-load-add=validate_password.so

# 方法2, 运行时 (注意: 在主库上运行时, 需要在从库上再运行一次, 不会自动同步)
INSTALL PLUGIN validate_password SONAME 'validate_password.so';
参数

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+

主要的设置是 policy, 取值为0 - 2, 分别采取的限制为

• 0 or LOW: Length
• 1 or MEDIUM: Length; numeric, lowercase/uppercase, and special characters
• 2 or STRONG: Length; numeric, lowercase/uppercase, and special characters; dictionary file

设置密码有效期

-- Require the password to be changed every 90 days:
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- Disable password expiration:
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;

-- Defer to the global expiration policy:
CREATE USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;

配置

# To establish a global policy that passwords have a lifetime of approximately six months, start the server with these lines in a server my.cnf file:
[mysqld]
default_password_lifetime=180

#To establish a global policy such that passwords never expire, set default_password_lifetime to 0:
[mysqld]
default_password_lifetime=0

# 运行时的设置 default_password_lifetime can also be changed at runtime:
SET GLOBAL default_password_lifetime = 180;
SET GLOBAL default_password_lifetime = 0;

查看MySQL的数据表大小, 并按大小倒序排列

SELECT 
	table_name, 
    table_rows, 
    data_length, 
    index_length, 
    round(((data_length + index_length) / 1024 / 1024),2) "Size in MB"
FROM
	information_schema.tables 
WHERE
	table_schema = "db_name"
ORDER BY 
	(data_length + index_length) DESC;