1. 数据库安全初始化
- [root@elasticsearch my.cnf.d]# mysql_secure_installation #安全初始化命令
- NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
- SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
- In order to log into MariaDB to secure it, we'll need the current
- password for the root user. If you've just installed MariaDB, and
- you haven't set the root password yet, the password will be blank,
- so you should just press enter here.
- Enter current password for root (enter for none): #输入 mysql 的 root 账户默认密码 (默认为空)
- OK, successfully used password, moving on...
- Setting the root password ensures that nobody can log into the MariaDB
- root user without the proper authorisation.
- Set root password? [Y/n] y #是否设置 root 密码
- New password: #为 root 用户输入一个新密码
- Re-enter new password: #再次输入密码
- Password updated successfully! #密码更新成功
- Reloading privilege tables..
- ... Success!
- By default, a MariaDB installation has an anonymous user, allowing anyone
- to log into MariaDB without having to have a user account created for
- them. This is intended only for testing, and to make the installation
- go a bit smoother. You should remove them before moving into a
- production environment.
- Remove anonymous users? [Y/n] y #是否删除匿名用户
- ... Success!
- Normally, root should only be allowed to connect from 'localhost'. This
- ensures that someone cannot guess at the root password from the network.
- Disallow root login remotely? [Y/n] y #是否允许 root 用户远程登录
- ... Success!
- By default, MariaDB comes with a database named 'test' that anyone can
- access. This is also intended only for testing, and should be removed
- before moving into a production environment.
- Remove test database and access to it? [Y/n] #是否删除 test 数据库
- - Dropping test database...
- ... Success!
- - Removing privileges on test database...
- ... Success!
- Reloading the privilege tables will ensure that all changes made so far
- will take effect immediately.
- Reload privilege tables now? [Y/n] #是否刷新以上操作, 使其立即生效
- ... Success!
- Cleaning up...
- All done! If you've completed all of the above steps, your MariaDB
- installation should now be secure.
- Thanks for using MariaDB!
以上操作完成后, 命令行直接输入 mysql 无法登录数据库, 使用本机外网地址也无法登录.
- [root@elasticsearch my.cnf.d]# mysql
- ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
- [root@elasticsearch my.cnf.d]# mysql -uroot -h192.168.0.194 -p
- Enter password:
- ERROR 1130 (HY000): Host 'node1' is not allowed to connect to this MariaDB server
- [root@elasticsearch my.cnf.d]# mysql -uroot -h127.0.0.1 -p
- Enter password:
- Welcome to the MariaDB monitor. Commands end with ; or \g.
- Your MariaDB connection id is 14
- Server version: 5.5.60-MariaDB MariaDB Server
- Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- [root@elasticsearch my.cnf.d]# mysql -uroot -hlocalhost -p
- Enter password:
- Welcome to the MariaDB monitor. Commands end with ; or \g.
- Your MariaDB connection id is 15
- Server version: 5.5.60-MariaDB MariaDB Server
- Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- MariaDB [(none)]> show databases;
查看当前授权的用户和地址可见 root 仅授权了 localhost 和 127.0.0.1 可登录
- MariaDB [(none)]> use mysql
- Reading table information for completion of table and column names
- You can turn off this feature to get a quicker startup with -A
- Database changed
- MariaDB [mysql]> select User,Host from user;
- +------+-----------+
- | User | Host |
- +------+-----------+
- | root | 127.0.0.1 |
- | root | ::1 |
- | root | localhost |
- +------+-----------+
- 3 rows in set (0.00 sec)
- MariaDB [mysql]>
2. 忘记管理员密码的解决办法:
启动 mysql 前, 编辑 / etc/my.cnf, 添加 skip-grant-tables 和 skip-networking;
- [mysqld]
- skip-grant-tables
- skip-networking
- datadir=/var/lib/mysql
通过 UPDATE 命令修改管理员密码;
- [root@elasticsearch ~]# systemctl start mariadb
- [root@elasticsearch ~]# mysql
- Welcome to the MariaDB monitor. Commands end with ; or \g.
- Your MariaDB connection id is 2
- Server version: 5.5.60-MariaDB MariaDB Server
- Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- MariaDB [(none)]> update mysql.user set authentication_string=password('centos') where user='root' and Host = 'localhost';
- Query OK, 1 row affected (0.00 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- MariaDB [(none)]> flush privileges;
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [(none)]> exit
- Bye
删除 / etc/my.cnf 中添加的内容, 以正常方式启动 mysqld 进程;
- [root@elasticsearch ~]# mysql -uroot -hlocalhost -pcentos
- Welcome to the MariaDB monitor. Commands end with ; or \g.
- Your MariaDB connection id is 3
- Server version: 5.5.60-MariaDB MariaDB Server
- Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- MariaDB [(none)]>
3. 增, 删, 改, 查用户
查看用户:
mysql 中用户表在 mysql.user 中
查看用户示例:
- MariaDB [(none)]> SELECT User,Host FROM mysql.user;
- +-------+-------------+
- | User | Host |
- +-------+-------------+
- | root | 127.0.0.1 |
- | root | ::1 |
- | root | localhost |
- +-------+-------------+
- 6 rows in set (0.01 sec)
添加用户
格式: 可一次创建多个用户
CREATE USER 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'] [,'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']...]
例:
- # 单条命令创建一个用户:
- MariaDB [(none)]> CREATE USER 'lxk'@'localhost' IDENTIFIED BY PASSWORD 'linux.centos.com';
- Query OK, 0 rows affected (0.00 sec)
- # 以逗号为分隔, 单条命令创建两个用户:
- MariaDB [(none)]> CREATE USER 'test0'@'192.168.1.%' IDENTIFIED BY 'maria.centos.com','test1'@'192.168.1.%' IDENTIFIED BY 'maria.centos.com';
- Query OK, 0 rows affected (0.00 sec)
重命名用户:
格式:
RENAME USER old_user TO new_user[, old_user TO new_user] ...
例:(创建用户时加了授权地址, 修改时也需加上授权地址)
- # 查看当前用户:
- MariaDB [(none)]> SELECT User,Host FROM mysql.user;
- +-------+-------------+
- | User | Host |
- +-------+-------------+
- | root | 127.0.0.1 |
- | test0 | 192.168.1.% |
- | test1 | 192.168.1.% |
- | root | ::1 |
- | lxk | localhost |
- | root | localhost |
- +-------+-------------+
- 6 rows in set (0.01 sec)
- MariaDB [(none)]> RENAME USER 'test1'@'192.168.1.%' TO 'test001'@'192.168.1.%';
- Query OK, 0 rows affected (0.01 sec)
- MariaDB [(none)]> SELECT User,Host FROM mysql.user;
- +---------+-------------+
- | User | Host |
- +---------+-------------+
- | root | 127.0.0.1 |
- | test0 | 192.168.1.% |
- | test001 | 192.168.1.% |
删除用户:
格式:
DROP USER 'user'@'host' [, 'user'@'host'] ...
例:
- MariaDB [(none)]> DROP USER 'test001'@'192.168.1.%';
- Query OK, 0 rows affected (0.01 sec)
- MariaDB [(none)]> SELECT User,Host FROM mysql.user;
- +-------+------------+
- | User | Host |
- +-------+------------+
- | root | 127.0.0.1 |
- | test0 | 192.168.1.%|
- | root | ::1 |
- | lxk | localhost |
- | root | localhost |
- +-------+------------+
- 5 rows in set (0.00 sec)
重新加载授权表:
作用: 有时操作并不会马上写到磁盘上, 执行此命令可把操作马上同步到磁盘上.
例:
- MariaDB [(none)]> FLUSH PRIVILEGES;
- Query OK, 0 rows affected (0.00 sec)
4. 用户授权相关:
查看用户授权:
格式:
- SHOW GRANTS [FOR 'user'@'host']
- MariaDB [(none)]> show grants; #不加用户, 默认查找的是 root 用户的授权信息.
- +----------------------------------------------------------------------------------------------------------------------------------------+
- | Grants for root@localhost |
- +----------------------------------------------------------------------------------------------------------------------------------------+
- | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*128977E278358FF80A246B5046F51043A2B1FCED' WITH GRANT OPTION |
- | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
- +----------------------------------------------------------------------------------------------------------------------------------------+
- 2 rows in set (0.00 sec)
- MariaDB [(none)]> show grants for 'test0'@'192.168.1.%'; #查看指定用户的授权信息.
- +---------------------------------------------------------------------------------------------------------------+
- | Grants for test0@192.168.1.% |
- +---------------------------------------------------------------------------------------------------------------+
- | GRANT USAGE ON *.* TO 'test0'@'192.168.1.%' IDENTIFIED BY PASSWORD '*5FC1DC57211AE5F87FC504DEEE4B7C65DEB2CBFA'|
- +---------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
给用户授权:
格式:
- GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
- ON [object_type] priv_level
- TO user_specification [, user_specification] ...
- [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
- [WITH with_option ...]
简化格式:
- GRANT priv_type ON [object_type] priv_level TO user_specification [, user_specification]
- object_type:
- TABLE
- | FUNCTION
- | PROCEDURE
- priv_level:
- *
- | *.* #所有库的所有表
- | db_name.* #某个库的所有表
- | db_name.tbl_name #某个库的某个表
- | tbl_name #某个表
- | db_name.routine_name #某个库的某个 routine
示例:
- MariaDB [(none)]> GRANT all ON *.* TO 'test0'@'192.168.1%';
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [(none)]> SHOW GRANTS FOR 'test0'@'192.168.1%';
- +------------------------------------------------------------------------------------------------------------------------+
- | Grants for test0@192.168.1.% |
- +------------------------------------------------------------------------------------------------------------------------+
- | GRANT ALL PRIVILEGES ON *.* TO 'test0'@'192.168.1.%' IDENTIFIED BY PASSWORD '*5FC1DC57211AE5F87FC504DEEE4B7C65DEB2CBFA'|
- +------------------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.01 sec)
- #
取消授权: REVOKE
格式
- REVOKE priv_type [(column_list)][, priv_type [(column_list)]] ...
- ON [object_type] priv_level
- FROM 'user'@'host' [, 'user'@'host'] ...
- REVOKE ALL PRIVILEGES, GRANT OPTION
- FROM user [, user] ...
示例:
- MariaDB [(none)]> REVOKE ALL PRIVILEGES FROM 'test0'@'192.168.1.%';
- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM'test0'@'192.168.1%'' at line 1
- MariaDB [(none)]> REVOKE ALL PRIVILEGES ON *.* FROM 'test0'@'192.168.1.%';
- Query OK, 0 rows affected (0.00 sec)
- MariaDB [(none)]> SHOW GRANTS FOR 'test0'@'192.168.1%';
- +---------------------------------------------------------------------------------------------------------------+
- | Grants for test0@192.168.1.% |
- +---------------------------------------------------------------------------------------------------------------+
- | GRANT USAGE ON *.* TO 'test0'@'192.168.1.%' IDENTIFIED BY PASSWORD '*5FC1DC57211AE5F87FC504DEEE4B7C65DEB2CBFA'|
- +---------------------------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
来源: http://www.bubuko.com/infodetail-2756195.html