mysql 命令行添加用户_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.00sec)

查看某个用户的权限：

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

FLUSHPRIVILEGES;

#and an analogous UPDATE-statement to change it back. Also you might need to make changes to the mysql.db table aswell: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");

Update 20201107: 在MySQL8下, 上面的命令已经不起作用, 需要使用ALTER USER, 在之前要检查用户的Authentication Plugin是什么

--

UPDATE user SET plugin='caching_sha2_password' WHERE User='root';

--

ALTER user 'root'@'localhost' identified by 'Newpass.2020';

撤销用户权限

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

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

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

注意: 假如你在给用户'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 FOR 'pig'@'%';

查看.

删除用户

DROP USER 'username'@'host'

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

创建用于localhost连接的用户并指定密码

mysql> create user 'pcom'@'localhost' identified by 'aaa7B2249';

Query OK,0 rows affected (0.00sec)

创建数据库

mysql>create database pcom default character set utf8 collate utf8_bin; # or utf8_general_ci

Query OK,1 row affected (0.00sec)

给本地用户授权, 这里不需要指定密码 (因为前面创建时, 对应localhost的密码已经创建了)

mysql> grant all on pcom.* to 'pcom'@'localhost';

Query OK,0 rows affected (0.00sec)

给其他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.00sec)

同样的

mysql> grant all on pcom.* to 'pcom'@'172.20.0.0/255.255.0.0' identified by 'aaa7B2249';

Query OK,0 rows affected (0.00sec)

Done!

导出数据并压缩

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

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

# 导出1个数据库结构及数据:

mysqldump somedatabase -u someuser -p > dump.sql

# 导出多个库结构以及数据

mysqldump -B dbname1 dbname2 -u root -p > dump.sql

# 导出一个表

mysqldump somedatabase tbl_1 -u someuser -p > dump_tbl_1.sql

# 导出一个数据库结构

# -d: 没有数据

mysqldump somedatabase -d -u someuser -p > dump_schema.sql

# 忽略某些表

--ignore-table=${DATABASE}.${TABLE}

如果有多个表, 上面的参数要使用多次(不能在同一个参数里加多个表名)

# 导出表结构及数据 + 存储过程和函数

# -R 不带这个参数不会导出存储过程和函数

mysqldump dbname -R -uroot -p > somedump.sql

# 只导出存储过程和函数, 不导出数据和结构, 需要添加下面三个参数, 可以合并为-ndt

# -d 结构(--no-data:不导出任何数据，只导出数据库表结构)

# -t 数据(--no-create-info:只导出数据，而不添加CREATE TABLE 语句)

# -n (--no-create-db:只导出数据，而不添加CREATE DATABASE 语句)

mysqldump dbname -R -ndt -uroot -p > somedump.sql

# 用bzip2压缩

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

在从库上进行以下操作

mysql> FLUSH TABLES WITH READLOCK;

--如果从库已经配置为 read_only则不需这步

mysql> SET GLOBAL read_only = ON;--此时从库停止同步--...--进行mysqldump或者其他的导出操作--...

mysql> SET GLOBAL read_only = OFF;--如果从库已经配置为 read_only则不需这步

mysql>UNLOCK TABLES;--此时从库会开始同步主库

为避免在时间任务中提示输入密码, 可以在执行脚本的用户目录下创建文件 .my.cnf, 放入以下内容

[client]

password=YouPassword

解压缩并导入数据

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

cd 至dump.sql文件所在目录

登入mysql

mysql-u someuser -p

导入dump

mysql> source dump.sql

查看MySQL服务器当前连接的详细资料

show processlist;

或者在命令行下

./mysqladmin -uadmin -p -h 10.140.1.1 processlist

MySQL5.7的密码策略设置

检查是否已安装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 90days: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;

#Defertothe 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;

.