用户配置
用户表
安装完成后，root登陆：
[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
刚安装好的root用户没有密码，可以修改密码：
[root@localhost /]# mysqladmin -u root password 'new password'
Warning: Using a password on the command line interface can be insecure.
查询数据库的用户信息，在Mysql中的mysql库中有user表：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.12 sec)
mysql> 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
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)
mysql> show columns from user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) | NO | | 0 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)
添加用户/赋权
在用户表中加入新的用户：
mysql> insert into mysql.user (host,user,password) values('localhost','testuser',PASSWORD('testuser'));
ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value
报错，查了一下原因，可能是高版本（5.1）之后不能这样添加用户了，正确的添加用户的方法是：grant 权限 on 数据库对象 to 用户，具体用法参考GRANT Syntax。
mysql> create database TestDB;
Query OK, 1 row affected (0.10 sec)
mysql> grant select on TestDB.* to 'testuser'@'localhost' IDENTIFIED BY 'testuser' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'testuser'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8' |
| GRANT SELECT ON `TestDB`.* TO 'testuser'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
这里添加了一个testuser用户，密码是testuser，给了查询所有表数据的权限，赋予全部权限是：
mysql> grant all on TestDB.* to 'testuser'@'localhost' IDENTIFIED BY 'testuser' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'testuser'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8' |
| GRANT ALL PRIVILEGES ON `TestDB`.* TO 'testuser'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
root用户退出来，用testuser登陆，成功：
[root@localhost /]# mysql -u testuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| TestDB |
| test |
+--------------------+
3 rows in set (0.00 sec)
服务设置
启动/关闭
官方给出的启动命令：sudo service mysql start/stop
[root@localhost /]# sudo service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[root@localhost /]# service start mysqld.service
The service command supports only basic LSB actions (start, stop, restart, try-restart, reload, force-reload, status). For other actions, please try to use systemctl.
实际使用时确没有成功，按照提示使用命令：/bin/systemctl start/stop mysql.service
[root@localhost /]# /bin/systemctl start mysql.service
[root@localhost /]# mysql -u testuser -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit;
Bye
[root@localhost /]# /bin/systemctl stop mysql.service
[root@localhost /]# mysql -u testuser -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
可以启动/关闭服务成功。实验之后，还有 systemctl start mysqld.service：
[root@localhost /]# systemctl start mysqld.service
[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> exit;
Bye
开机启动/不启动
开机启动的命令 systemctl enable mysqld.service，开机不启动的命令 systemctl disable mysqld.service。：
[root@localhost /]# systemctl enable mysqld
Created symlink from /etc/systemd/system/mysql.service to /usr/lib/systemd/system/mysqld.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@localhost /]# systemctl disable mysqld
Removed symlink /etc/systemd/system/multi-user.target.wants/mysqld.service.
Removed symlink /etc/systemd/system/mysql.service.
重启之后检查Mysql是否启动。查看是否在开机启动项中：
[root@localhost /]# systemctl list-unit-files | grep mysql
mysql.service enabled
mysqld.service enabled
访问控制
外部访问
命令：grant 权限 on 数据库对象 to 用户；
常用的权限有：CREATE、DROP、DELETE、INSERT、SELECT、UPDATE等，具体参考Mysql官网的Privileges Supported by MySQL。
数据库对象可以是所有数据，也可以指定具体的库或其中的某个表：*.*、TestDB.test等。
可以知道用户在知道的地址或域名访问的权限：%表示所有地址。
root用户登陆，允许testuser用户可以在任意外部访问数据库TestDB的所有表：
[root@localhost /]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.35 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT ALL PRIVILEGES ON TestDB.* to 'testuser'@'%' IDENTIFIED BY 'testuser';
Query OK, 0 rows affected (0.00 sec)
指定地址访问：
mysql> GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'192.168.1.1' IDENTIFIED BY 'testuser' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
查看当前用户的权限，命令：show grants，查看指定用户的权限：show grants for '用户名' @ '地址'。
mysql> show grants for 'testuser'@'localhost';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for testuser@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8' |
| GRANT ALL PRIVILEGES ON `TestDB`.* TO 'testuser'@'localhost' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
查看指定地址指定用户的权限：
mysql> show grants for 'testuser'@'192.168.1.1';
+----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for testuser@192.168.1.1 |
+----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'192.168.1.1' IDENTIFIED BY PASSWORD '*3A2EB9C80F7239A4DE3933AE266DB76A7846BCB8' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改端口
编辑 /etc/my.cnf 文件，修改端口为3308：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3308
无法启动mysql：
[root@localhost /]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
查看日志，显示错误：
2017-03-06 22:40:25 2950 [Note] Server hostname (bind-address): '*'; port: 3308
2017-03-06 22:40:25 2950 [Note] IPv6 is available.
2017-03-06 22:40:25 2950 [Note] - '::' resolves to '::';
2017-03-06 22:40:25 2950 [Note] Server socket created on IP: '::'.
2017-03-06 22:40:25 2950 [ERROR] Can't start server: Bind on TCP/IP port: Permission denied
2017-03-06 22:40:25 2950 [ERROR] Do you already have another mysqld server running on port: 3308 ?
2017-03-06 22:40:25 2950 [ERROR] Aborting
原因是SELinux默认开启，关于SELinux的相关介绍可以参考SELinux Project Wiki。查看SELinux的状态：
[root@localhost /]# sestatus
SELinux status: enabled
SELinuxfs mount: /sys/fs/selinux
SELinux root directory: /etc/selinux
Loaded policy name: targeted
Current mode: enforcing
Mode from config file: enforcing
Policy MLS status: enabled
Policy deny_unknown status: allowed
Max kernel policy version: 28
关闭SELinux，参考5.4.2. Disabling SELinux。
修改配置文件 /etc/selinux/config ，把 enforcing 改成 disabled ：
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
# SELINUX=enforcing
SELINUX=disabled
# SELINUXTYPE= can take one of three two values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
改完之后就可以启动了，但是如果需要远程访问，需要修改防火墙策略，允许端口访问。
总结
-
启动/停止服务：systemctl start/stop mysqld.service；
-
开机启动/不启动：systemctl enable/disable mysqld；
-
查看开机启动项：systemctl list-unit-files | grep mysql；
-
赋予权限：grant 权限 on 数据库对象 to 用户，
testuser用户、密码testuser，在任意地址任意访问TestDB的所有数据：GRANT ALL PRIVILEGES ON TestDB.* to 'testuser'@'%' IDENTIFIED BY 'testuser';
test用户、密码test，在任意地址查询TestDB的所有数据： GRANT SELECT ON TestDB.* to 'test'@'%' identified by 'test' with grant option;
-
查看权限：show grants for 'username'@'ip';
-
修改端口，关闭SELinux，修改防火墙策略。
网友评论