开发者社区> 莫-邪> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL基本用户授权步骤

简介:
+关注继续查看

1.MySQL用户授权步骤:

select distinct concat('user: ''',user,'''@''',host,''';') as query from mysql.user;
create user 'liuliang'@'10.161.74.190' identified by '!@bbdx1230.0.';

set password for 'yuelu_channels'@'10.46.209.207'=password('@#$%^yuelu0922&');

GRANT ALL PRIVILEGES ON *.* TO 'yuelu_channels'@'10.46.209.207';

flush privileges;

1.Centos7.2与MySQL5.7新建用户和数据库并授权:

#CREATE USER liuliang@139.129.18.65 IDENTIFIED BY 'zlxtqbd062350';

#GRANT ALL PRIVILEGES ON *.* TO 'liuliang'@'139.129.18.65';
(1).mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'pwd';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'

(2).mysql> create user 'root'@'%' identified by 'pwd';
Query OK, 0 rows affected (0.00 sec)

(3).mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

(4).mysql> show grants for current_user();
+---------------------------------------------------------------------+
| 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)

如果使用 ALTER USER 'root'@'%' IDENTIFIED WITH sha256_password BY 'pwd';
指定加密方式则可能在客户端连接时有问题:
注意2:ERROR 2059 (HY000): Authentication plugin 'sha256_password' cannot be loaded:
No such file or directory
注意事项:
(1).update user set plugin='mysql_native_password' where user='root' and host='localhost';
(2).update mysql.user set password=PASSWORD("pwd") where User='root';
注意3:ERROR 1054 (42S22): Unknown column 'password' in 'field list'
update mysql.user set authentication_string=password("pwd") where user='root';
注意4:'PASSWORD' is deprecated and will be removed in a future release.
password 即将被废弃,官方不建议用继续使用了,建议使用第1点中的 ALTER USER 语法去管理用户属性。

Access denied for user 'root'@'IP地址' ,是因为相应的主机没有对应的访问权限

--开放权限如下
use mysql;
update user u set u.host = '%' where u.user = 'root' limit 1;
flush privileges;

--查看用户权限
show grants for current_user();

--mysql不推荐通过修改表的方式修改用户密码
INSERT or UPDATE statements for the mysql.user table that refer to literal passwords are logged as is,so you should avoid such statements
--通过客户端sql修改
MariaDB [mysql]> UPDATE user SET Password = password('123456') WHERE User = 'root' ;
--此时可在binglog中可以看到明文的密码
[root@rudy_01 3306]# mysqlbinlog binlog.000006 --start-position=4224 >/tmp/test.sql
[root@rudy_01 3306]# cat /tmp/test.sql
SET @@session.collation_database=DEFAULT/*!*/;
UPDATE user SET Password = password('123456') WHERE User = 'root'

--在 mysql 5.7 中 password 字段已经不存在了
mysql> UPDATE user SET Password = password('123456') WHERE User = 'root' ;
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |

--注意出于安全考虑,alter user 时提示更新的是 0 条数据,但实际 password 已更新
mysql> select host,user,authentication_string,password_last_changed from user where user='root' and host='%';
+------+------+-------------------------------------------+-----------------------+
| host | user | authentication_string | password_last_changed |
+------+------+-------------------------------------------+-----------------------+
| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 2016-01-08 15:38:13 |
+------+------+-------------------------------------------+-----------------------+
1 row in set (0.04 sec)

--提示更新0条,使用此方法不需要再 flush privileges
If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE,SET PASSWORD, or RENAME USER,
the server notices these changes and loads the grant tables into memory again immediately.
mysql> alter user 'root'@'%' identified by '12345678';
Query OK, 0 rows affected (0.00 sec)
--实际已更新
mysql> select host,user,authentication_string,password_last_changed from user where user='root' and host='%';
+------+------+-------------------------------------------+-----------------------+
| host | user | authentication_string | password_last_changed |
+------+------+-------------------------------------------+-----------------------+
| % | root | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | 2016-01-08 15:53:09 |
+------+------+-------------------------------------------+-----------------------+
1 row in set (0.00 sec)

--在binlog中查出的sql如下
[root@rudy mysql]# cat /tmp/test.sql
SET @@session.collation_database=DEFAULT/*!*/;
ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'


--mysql对于密码有3种检验策略,默认validate_password_policy为MEDIUM
? LOW policy tests password length only. Passwords must be at least 8 characters long.
? MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.
? STRONG policy adds the condition that password substrings of length 4 or longer must not match words

--注意validate_password默认是没有安装的
If the validate_password plugin is not installed, the validate_password_xxx system variables are not available,
passwords in statements are not checked, and VALIDATE_PASSWORD_STRENGTH() always returns 0.


--检验密码复杂度
mysql> select VALIDATE_PASSWORD_STRENGTH('abc1235jeme');
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc1235jeme') |
+-------------------------------------------+
| 0 |
+-------------------------------------------+
1 row in set (0.00 sec)
--查找安装的插件,发现找不到validate_password
mysql> show plugins;
--手动安装
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+----------------------+---------+
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.04 sec)
--再次检验密码复杂度
mysql> select VALIDATE_PASSWORD_STRENGTH('abc1235jeme');
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc1235jeme') |
+-------------------------------------------+
| 50 |
+-------------------------------------------+

--安装validate_password插件后,就必需符合validate_password_policy的要求,否则语句执行出错
mysql> alter user 'root'@'%' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
2、主从复制遇到的权限及异常问题

1、主从均需重启mysql服务
/etc/init.d/mysqld restart
或者:
sudo service mysqld restart

2、主配置:增加从机复制账户并授权,以便从机远程登录过来复制 binlog
create user 'replicationUsername'@'%' identified by 'Passwd';
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'name'@'%' WITH GRANT OPTION;
show master status;

3、从配置:
stop slave;
CHANGE MASTER TO
MASTER_HOST='110.126.103.126',
MASTER_USER='replicationUsername',
MASTER_PASSWORD='Passwd',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
start slave;
show slave status\G

4、Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后,事务回滚造成的.
3.也可能遇到各种SQL错误导致 SQL 线程中断退出。
解决方法:
stop slave;
set global sql_slave_skip_counter = 1 ;
start slave;
之后Slave会和Master去同步 主要看:
从机:show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否为0,0就是已经同步了
主机:show processlist\G
如果出现Command: Binlog Dump,则说明配置成功.

5、测试:
create database db_test_slave;
use db_test_slave;
create table tb_test(id int(3), name varchar(50));
insert into tb_test values(1,'hello slave');
show databases;

6、slave 从零开始同步 master 所有数据库:
(1)master操作:
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

candidates=$(echo "show databases" | mysql -uroot -pPASSWD| grep -Ev "^(Database|sys|mysql|performance_schema|information_schema)$")
mysqldump -uroot -pPASSWD --databases ${candidates} --single-transaction > mysqldump.sql
UNLOCK TABLES;

(2)slave操作:
nc -l 12345 < <(cat mysqldump.sql) ##主
nc -n 10.48.186.32 12345 > mysqldump.sql

STOP SLAVE;
mysql -uroot -pPASSWD < mysqldump.sql

show master status; --主

CHANGE MASTER TO
MASTER_HOST='10.48.186.32',
MASTER_USER='birepl',
MASTER_PASSWORD='PASSWD',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=398062,
MASTER_CONNECT_RETRY=10;
RESET SLAVE;
start slave;
SHOW SLAVE STATUS\G
Refer:

[1] mysql 权限与安全

https://yq.aliyun.com/articles/2719

[2] mysql-5.7主从同步安装配置

http://www.apelearn.com/bbs/thread-13435-1-1.html

[3] CentOS 7 下MySQL 5.7.12主从复制架构配置记录(亲自验证可行)

http://www.voidcn.com/blog/juan0728juan/article/p-6050119.html

[4] MySQL 5.7的多源复制

http://www.cnblogs.com/xuanzhi201111/p/5151666.html

[5] Slave_SQL_Running: No mysql同步故障解决方法

http://kerry.blog.51cto.com/172631/277414

[6] Slave_SQL_Running: No mysql同步故障解决方法

http://blog.csdn.net/xiaoxinla/article/details/7679578

[7] 有没有办法让从msyql主动从零开始在主mysql那里同步数据

https://www.v2ex.com/t/78848

[8] How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?

http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o

[9] Any option for mysqldump to ignore databases for backup?

http://dba.stackexchange.com/questions/35081/any-option-for-mysqldump-to-ignore-databases-for-backup

[10] mysql的binlog详解

http://blog.csdn.net/wyzxg/article/details/7412777

[11] 在什么时候可以调用reset master?

http://bbs.chinaunix.net/thread-1199047-1-1.html

[12] mysql只读模式的设置方法与实验

http://blog.csdn.net/yumushui/article/details/41645469

[13] MySql 创建只读账号

http://blog.csdn.net/norsd/article/details/9081833

© 著作权归作者所有
分类:mysql 字数:1699
点赞 (2) 收藏 (6) 分享
xrzs xrzs 关注此人
粉丝: 1101 博客数: 547 共码了 335494 字
评论(0)
尚无网友评论

登录后评论

2.Centos6与MySQL5.3新建用户和数据库并授权:

一、新建用户
//登录MYSQL
@>mysql -u root -p
@>密码
//创建用户
mysql> insert into mysql.user(Host,User,Password)
values(“localhost”,”cplusplus”,password(“cplusplus.me”));
//刷新系统权限表
mysql>flush privileges;
这样就创建了一个名为:cplusplus 密码为:cplusplus.me 的用户。
二、登录测试
mysql>exit;
@>mysql -u cplusplus -p
@>输入密码
mysql>登录成功
三、用户授权
//登录MYSQL
@>mysql -u root -p
@>密码
//首先为用户创建一个数据库(cplusplusDB)
mysql>create database cplusplusDB;
//授权cplusplus用户拥有cplusplusDB数据库的所有权限。
>grant all privileges on cplusplusDB.* to cplusplus@localhost identified
by ‘cplusplus.me';
//刷新系统权限表
mysql>flush privileges;
mysql>其它操作
四、部分授权
mysql>grant select,update on cplusplusDB.* to cplusplus@localhost
identified by ‘cplusplus.me';
//刷新系统权限表。
mysql>flush privileges;
五、删除用户
@>mysql -u root -p
@>密码
mysql>DELETE FROM user WHERE User=”cplusplus” and Host=”localhost”;
mysql>flush privileges;
六、删除数据库
mysql>drop database cplusplusDB;
七、修改密码
@>mysql -u root -p
@>密码
mysql>update mysql.user set password=password(‘新密码’) where
User=”cplusplus” and Host=”localhost”;
mysql>flush privileges;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
+关注
莫-邪
每个人的生活都是一条通向自身的道路。每个人的真正职责只有一个:找到自我。然后在心中坚守一生,全心全意!
6
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载