mysql创建只读用户

创建只读用户

1
2
3
4
# 格式
GRANT SELECT ON dbName.tableName TO 'username'@'host' IDENTIFIED BY "password";
# 刷新权限
FLUSH PRIVILEGES;

例子，用户readonly_user可以在当前mysql的服务器，只读方式访问所有数据库

1
2
GRANT SELECT ON *.* TO 'readonly_user'@'localhost' IDENTIFIED BY "12345678";
FLUSH PRIVILEGES;

分步创建

1
2
3
CREATE USER 'readonly_user'@'%' IDENTIFIED BY '12345678';
GRANT SELECT ON *.* TO 'readonly_user'@'%'; 
FLUSH PRIVILEGES;

centos7安装mysql

  • 分类 软件 ·
  • 发表于 2022-01-07 16:52:51

安装

下载地址https://downloads.mysql.com/archives/community/

找到想要的版本

1
2
3
4
5
6
7
8
9
yum install libaio
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-server-5.7.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.34-1.el7.x86_64.rpm

# 提示
error: Failed dependencies:
        mysql-community-client(x86-64) >= 5.7.9 is needed by mysql-community-server-5.7.34-1.el7.x86_64
        mysql-community-common(x86-64) = 5.7.34-1.el7 is needed by mysql-community-server-5.7.34-1.el7.x86_64

提示依赖mysql-community-client和mysql-community-common

1
2
3
4
5
6
7
8
9
10
11
12
13
14
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-common-5.7.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-common-5.7.34-1.el7.x86_64.rpm
# 提示和mariadb-libs冲突
file /usr/share/mysql/czech/errmsg.sys from install of mysql-community-common-5.7.34-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.68-1.el7.x86_64
# 查看出来删除掉
rpm -qa|grep mariadb-libs
rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
# 再重新安装mysql-community-common

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-client-5.7.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.34-1.el7.x86_64.rpm
error: Failed dependencies:
        mysql-community-libs(x86-64) >= 5.7.9 is needed by mysql-community-client-5.7.34-1.el7.x86_64

提示依赖mysql-community-libs

1
2
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-community-libs-5.7.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.34-1.el7.x86_64.rpm

一共需要下面4个文件

  • mysql-community-client-5.7.34-1.el7.x86_64.rpm
  • mysql-community-common-5.7.34-1.el7.x86_64.rpm
  • mysql-community-libs-5.7.34-1.el7.x86_64.rpm
  • mysql-community-server-5.7.34-1.el7.x86_64.rpm

启动

1
2
3
systemctl start mysqld
# 查看状态
systemctl status mysqld

设置密码

如果没有设置密码

1
/usr/bin/mysqladmin -u root password 'xxx'

如果设置了密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 找到密码
sudo grep 'temporary password' /var/log/mysqld.log
# 登录
mysql -uroot -p
# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'

# 出错
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# 降低密码强度
set global validate_password_policy=0;
set global validate_password_length=1;

# mysql8，没这两个变量
SHOW VARIABLES LIKE 'validate_password%';
# 改为
set global validate_password.policy=0;
set global validate_password.length=1;
systemctl restart mysqld