笺注:这是在 LNMP一键安装包(lnmp_CentOS6.9) 的基础上进行的。
MySQL的版本信息:
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.5.48, for Linux (x86_64) using readline 5.1
[root@localhost ~]#
[root@localhost ~]# mysql -V |awk '{print $5}'
5.5.48,
[root@localhost ~]#
[root@localhost ~]# mysql -V |awk '{print $5}' |awk -F, '{print $1}'
5.5.48
[root@localhost ~]#
检测MySQL的运行状态:
[root@localhost ~]# service mysql stop
Shutting down MySQL. SUCCESS!
[root@localhost ~]#
[root@localhost ~]# ps -ef |grep mysql |grep -v grep
[root@localhost ~]#
[root@localhost ~]# echo $?
1
[root@localhost ~]# ps aux |grep mysql |grep -v grep
[root@localhost ~]#
[root@localhost ~]# echo $?
1
[root@localhost ~]# netstat -anp | grep mysql
[root@localhost ~]#
[root@localhost ~]# echo $?
1
[root@localhost ~]# netstat -anp | grep 3306
[root@localhost ~]#
[root@localhost ~]# echo $?
1
[root@localhost ~]# service mysql start
Starting MySQL.. SUCCESS!
[root@localhost ~]#
[root@localhost ~]# ps -ef |grep mysql |grep -v grep
root 2711 1 0 02:39 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/localhost.localdomain.pid
mysql 3195 2711 0 02:39 pts/1 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/var/localhost.localdomain.err --open-files-limit=65535 --pid-file=/usr/local/mysql/var/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306
[root@localhost ~]#
[root@localhost ~]# echo $?
0
[root@localhost ~]# ps aux |grep mysql |grep -v grep
root 2711 0.0 0.0 11308 1516 pts/1 S 02:39 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/var --pid-file=/usr/local/mysql/var/localhost.localdomain.pid
mysql 3195 0.2 2.5 465436 48700 pts/1 Sl 02:39 0:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/var --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/var/localhost.localdomain.err --open-files-limit=65535 --pid-file=/usr/local/mysql/var/localhost.localdomain.pid --socket=/tmp/mysql.sock --port=3306
[root@localhost ~]#
[root@localhost ~]# echo $?
0
[root@localhost ~]# netstat -anp | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3195/mysqld
unix 2 [ ACC ] STREAM LISTENING 12705 3195/mysqld /tmp/mysql.sock
[root@localhost ~]#
[root@localhost ~]# echo $?
0
[root@localhost ~]# netstat -anp | grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3195/mysqld
[root@localhost ~]#
[root@localhost ~]# echo $?
0
登录MySQL后,修改数据库管理员root@localhost的密码:
use mysql;
UPDATE user SET Password = password ( '1688' ) WHERE User = 'root';
flush privileges;
服务器本地登录MySQL数据库:(使用的是数据库管理员root@localhost,密码1688)
mysql -u"root" -p"1688"
查看数据库里有哪些库:(以下3个库是默认就有的)
show databases;
切换到库mysql,并列出这个库里所有的表:
mysql> use mysql;
Database changed
mysql>
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>
查看指定表的表结构:(截图有省略)
describe mysql.user;
笺注:库mysql中的表user里面包含了所有数据库用户的信息。
查看所有数据库用户及其主机信息:(以下是初始状态)
select user,host from mysql.user;
查看数据库管理员root@localhost的权限:
mysql> show grants for root@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*398D8BD83BF246533D81FF0E614402C6505D0EED' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
笺注:如果站点的数据库配置文件中直接使用了数据库管理员户root@localhost,并且站点文件和数据库在同一台服务器上,就无需另外创建数据库用户、授权了。
######
######
创建一个数据库用户并授权:
授权数据库用户zhuohua@'%'(密码886),可以从任意IP进行访问,对所有的库有完全控制的权限:
mysql> grant all on *.* to zhuohua@'%' identified by '886';
Query OK, 0 rows affected (0.01 sec)
查看数据库用户zhuohua@'%'的权限:
mysql> show grants for zhuohua@'%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*F961C54AFEB4D281CE53D7CB8E7822890D86FFFC' |
+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
再次查看所有数据库用户及其主机信息:
######
编辑防火墙规则:(打开TCP 3306端口)
[root@localhost ~]# vi /etc/sysconfig/iptables
修改:
-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT
重启iptable服务:
[root@localhost ~]# service iptables restart
iptables:将链设置为政策 ACCEPT:filter [确定]
iptables:清除防火墙规则:[确定]
iptables:正在卸载模块:[确定]
iptables:应用防火墙规则:[确定]
查看防火墙的filter表的规则:(截图有省略)
iptables -nL -t filter --line
############
############
SQLyog是一个图形化管理MySQL/MariaDB数据库的工具。
下载SQLyog:https://pan.baidu.com/s/16Hb9sUgSt5Q8QPaquYNw8w
Windows客户端使用SQLyog,可以远程访问MySQL/MariaDB数据库:
运行 SQLyogEnt.exe ,新建一个连接:
MySQL服务器的IP地址 192.168.168.130
数据库用户名 zhuohua
密码 886
端口 3306
SQLyog的主界面:
使用SQLyog创建库:(快捷键 Ctrl+D )
自定义的库data1创建成功:
在MySQL服务器本地查看库data1的字符集:
[root@localhost ~]# mysql -u"root" -p"1688" -e "show create database data1;"
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| data1 | CREATE DATABASE `data1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
[root@localhost ~]#
注释:库data1的字符集为 utf8_general_ci
使用SQLyog创建表:
设置好字段后,点击左下角的“创建表”:
输入自定义表名:
使用SQLyog查看库data1中的表table1的表结构:
创建表后,还可以查看表的字段信息、索引信息、DDL信息:
DDL信息:
CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`age` tinyint(4) DEFAULT NULL,
`math` float DEFAULT NULL,
`english` decimal(50,2) DEFAULT NULL,
`adress1` text,
`adress2` mediumtext,
`adress3` longtext,
`shijian` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
可以使用代码创建表:
在MySQL服务器本地查看库data1中的表table1的表结构:
[root@localhost ~]# mysql -u"root" -p"1688" -e "desc data1.table1;"
使用SQLyog插入记录:(选择表名后,选择粘贴SQL语句)
代码:(字段id为主键、标识符列,会自动增长,不用写)
INSERT INTO data1.table1
(
NAME,
age,
math,
english,
adress1,
adress2,
adress3,
shijian
)
VALUES
(
'zhuohua',
18,
60,
80.5,
'中国',
'广东省',
'佛山市',
'2019-12-2 15:33:5'
);
在MySQL服务器本地查看库data1中的表table1的表数据:
[root@localhost ~]# mysql -u"root" -p"1688" -e "use data1;select * from table1;"
######
######
在MySQL服务器本地修改数据库用户zhuohua@'%'的密码为1234:
[root@localhost ~]# mysql -u"root" -p"1688" -e "update mysql.user set password=password('1234') where user='zhuohua' and host='%';flush privileges;"
[root@localhost ~]#
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
+-----------------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+-----------------------------------------------------------------------------------------------------------------+
[root@localhost ~]#
注释:密码是加密了的。
######
######
在MySQL服务器本地撤销数据库用户zhuohua@'%'对所有的库有完全控制的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "revoke all on *.* from zhuohua@'%';flush privileges;"
[root@localhost ~]#
再次查看数据库用户zhuohua@'%' 的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
+--------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+--------------------------------------------------------------------------------------------------------+
[root@localhost ~]#
此时数据库用户 zhuohua@'%' 可以登录SQLyog,但实际上已经没有任何权限了:
######
重新授权数据库用户zhuohua@'%'(密码1234),可以从任意IP进行访问,只对库data1有完全控制的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "grant all on data1.* to zhuohua@'%' identified by '1234';"
[root@localhost ~]#
重新查看数据库用户zhuohua@'%'的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
+--------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
| GRANT ALL PRIVILEGES ON `data1`.* TO 'zhuohua'@'%' |
+--------------------------------------------------------------------------------------------------------+
[root@localhost ~]#
此时数据库用户 zhuohua@'%' 可以登录SQLyog,只对库data1有完全控制的权限:
######
撤销数据库用户zhuohua@'%'对库data1的完全控制权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "revoke all on data1.* from zhuohua@'%';flush privileges;"
[root@localhost ~]#
重新查看数据库用户zhuohua@'%'的权限:
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
+--------------------------------------------------------------------------------------------------------+
| Grants for zhuohua@% |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhuohua'@'%' IDENTIFIED BY PASSWORD '*A4B6157319038724E3560894F7F932C8886EBFCF' |
+--------------------------------------------------------------------------------------------------------+
[root@localhost ~]#
笺注:撤销数据库用户权限的方式,要匹配授权时的方式。
######
删除数据库用户zhuohua@'%':
[root@localhost ~]# mysql -u"root" -p"1688" -e "drop user zhuohua@'%';flush privileges;"
[root@localhost ~]#
数据库用户被删除后,再查询其权限,会有如下显示:
[root@localhost ~]# mysql -u"root" -p"1688" -e "show grants for zhuohua@'%';"
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'zhuohua' on host '%'
[root@localhost ~]#
重新查看所有数据库用户及其主机信息:
[root@localhost ~]# mysql -u"root" -p"1688" -e "select user,host from mysql.user;"
相关文章:
使用Navicat远程管理MySQL
Windows2012R2_安装MySQL5.5
Windows下MySQL的备份和还原
Linux下MySQL的备份和还原
MySQL的SQL语句
MySQL的最大连接数/最大可打开表数/最大可打开文件数
重置MySQL5.5/5.6/5.7的用户密码
Zabbix自定义监控Linux下的MySQL
Zabbix5.0.12_使用模板监控MySQL主从同步状态
CentOS8_在Docker中安装MySQL
CentOS8_在Docker中安装Nagios
CentOS8_Mycat实现MySQL的读写分离
CentOS6_iptables |