mysql数据库只要本机访问就可以了,这样的话,默认安装就OK,但是如果需要外网访问mysql数据库,就需要另外授权
0)登录到mysql数据库中
#mysql -u root -p
如果忘记mysql的root密码,可以修改文件
#vim /etc/my.cnf.d/mariadb-server.cnf
找到[mysqld],增加一条配置skip-grant-tables,重启mysql即可不用密码就能登录,进去后重新修改root密码
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#增加一条跳过授权
#skip-grant-tables
[mysql]> update mysql.user set password=PASSWORD('123456') where user='root';
[mysql]> flush privileges;
[mysql]> exit
再把/etc/my.cnf.d/mariadb-server.cnf中的skip-grant-tables注释掉,然后重启mysql
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| keystone |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
2)使用mysql管理数据库
mysql> use mysql;
Database changed
3)查看当前数据库表
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| column_stats |
| columns_priv |
| db |
| event |
| func |
| general_log |
| gtid_slave_pos |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| index_stats |
| innodb_index_stats |
| innodb_table_stats |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| roles_mapping |
| servers |
| slow_log |
| table_stats |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
4)查看USER表的字段信息
MariaDB [mysql]> show columns from user ;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(80) | 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 | |
5)查看user表,列出现有用户,密码及允许连接的主机
MariaDB [mysql]> select host,user,password,select_priv,insert_priv,update_priv,delete_priv from user;
+-----------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+
| host | user | password | select_priv | insert_priv | update_priv | delete_priv |
+-----------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| mysql1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| localhost | keystone | *442DFE587A8B6BE1E9538855E8187C1EFB863A73 | N | N | N | N |
| % | keystone | *442DFE587A8B6BE1E9538855E8187C1EFB863A73 | N | N | N | N |
+-----------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+
由上表中,可以看到root用户仅设置了本地访问,keystone用户可以本地访问和远程访问,在host字段中用%表示,允许任何主机访问,但是它没有insert,update等操用权限。
6)使用grant语句,创建一个admin用户,授权它可以本地和远程访问数据库,并且有操作权限
grant all privileges on *.* to admin@localhost identified by '123456' with grant option;
grant all privileges on *.* to admin@"%" identified by '123456' with grant option;
MariaDB [mysql]> select host,user,password,select_priv,insert_priv,update_priv,delete_priv from user;
+-----------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+
| host | user | password | select_priv | insert_priv | update_priv | delete_priv |
+-----------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| mysql1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| localhost | keystone | *442DFE587A8B6BE1E9538855E8187C1EFB863A73 | N | N | N | N |
| % | keystone | *442DFE587A8B6BE1E9538855E8187C1EFB863A73 | N | N | N | N |
| localhost | admin | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | Y | Y | Y | Y |
| % | admin | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
+-----------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+
(7)给keystone用户增加操作权限
update user set select_priv='y',insert_priv='y',update_priv='y',delete_priv='y' where user ='keystone';
MariaDB [mysql]> select host,user,password,select_priv,insert_priv,update_priv,delete_priv from user;
+-----------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+
| host | user | password | select_priv | insert_priv | update_priv | delete_priv |
+-----------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+
| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| mysql1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| ::1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
| localhost | keystone | *442DFE587A8B6BE1E9538855E8187C1EFB863A73 | Y | Y | Y | Y |
| % | keystone | *442DFE587A8B6BE1E9538855E8187C1EFB863A73 | Y | Y | Y | Y |
| localhost | admin | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | Y | Y | Y | Y |
| % | admin | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | Y | Y | Y |
+-----------+----------+-------------------------------------------+-------------+-------------+-------------+-------------+