三期总目录链接
目录
案例1、授权一个用户名是boss,允许从本机访问,对TSZABC这个库拥有查询权限,验证密码是'123456'
案例2、授权一个用户名是boss,允许从本机访问,对TSZABC这个库拥有所有权限,验证密码是'123456'
案例3、授权一个用户名是aaa,允许从192.168.1.4访问,对aaa这个库拥有所有权限,验证密码是'123456'
一、Mysql 数据库的概述及安装
Mysql 定义: 是一个真正的多线程、多用户的SQL数据库服务 (高性能、高可靠性、易于使用的特性)
编译安装Mysql 数据库的优点: 确保功能的完整性、可定制性
安装Mysql-5.6.36 版本
步骤:
(一)、准备工作
1、为了避免端口冲突、程序冲突等现象,先确认本机没有使用rpm 方式安装mysql-server 和mysql 软件包
[root@C7--02 ~]# rpm -q mysql-server mysql
未安装软件包 mysql-server
未安装软件包 mysql
2、安装光盘自带的ncurses-devel 插件包
安装:rpm -ivh ncurses-devel-5.9-13.20130511.el7.x86_64.rpm (二选一)
[root@C7--02 ~]# mount /dev/cdrom /media/cdrom #挂载光盘
mount: /dev/sr0 写保护,将以只读方式挂载
[root@C7--02 ~]# cd /media/cdrom/Packages/ #进入目录
[root@C7--02 Packages]# rpm -ivh ncurses-devel-5.9-13.20130511.el7.x86_64.rpm
警告:ncurses-devel-5.9-13.20130511.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID f4a80eb5: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:ncurses-devel-5.9-13.20130511.el7################################# [100%]
或者 yum -y install ncurses-devel (避免出现依赖性关系,自动解决并安装依赖包)
[root@C7--02 ~]# yum -y install ncurses-devl
已加载插件:fastestmirror
Loading mirror speeds from cached hostfile
没有可用软件包 ncurses-devl。
错误:无须任何处理
3、安装cmake 软件包(Mysql 5.X 版本都需要cmake 进行编译)
[root@C7--02 ~]# ls
anaconda-ks.cfg cmake-2.8.6.tar.gz #上传cmake 软件包
[root@C7--02 ~]# tar zxvf cmake-2.8.6.tar.gz #解压
cmake-2.8.6/.gitattributes
cmake-2.8.6/bootstrap
.......
....省略
[root@C7--02 ~]# cd cmake-2.8.6 #切换目录
[root@C7--02 cmake-2.8.6]# ./configure #配置
.....
..时间较长
[root@C7--02 cmake-2.8.6]# gmake && gmake install
[ 0%] Building C object Source/kwsys/CMakeFiles/cmsys.dir/ProcessUNIX.c.o
[ 0%] Building C object Source/kwsys/CMakeFiles/cmsys.dir/Base64.c.o
[ 1%] Building C object Source/kwsys/CMakeFiles/cmsys.dir/MD5.c.o
[ 1%] Building C object Source/kwsys/CMakeFiles/cmsys.dir/Terminal.c.o
[ 1%] Building C object Source/kwsys/CMakeFiles/cmsys.dir/System.c.o
.....
..
完成
所有准备工作完成
(二)、源码编译及安装
1、创建运行用户和组 (加强数据库服务的权限控制)
[root@C7--02 ~]# groupadd mysql #创建组
[root@C7--02 ~]# useradd -M -s /sbin/nologin mysql -g mysql #创建mysql 用户并加入mysql 组中,该用户不能登录系统
2、上传软件包,并解包
[root@C7--02 ~]# ls
anaconda-ks.cfg cmake-2.8.6 cmake-2.8.6.tar.gz mysql-5.6.36.tar.gz #上传软件包mysql
[root@C7--02 ~]# tar zxvf mysql-5.6.36.tar.gz -C /usr/src #解压到/usr/src目录下
[root@C7--02 ~]# cd /usr/src/mysql-5.6.36/ #进入目录
[root@C7--02 mysql-5.6.36]# ls #查看内容
BUILD configure.cmake include man README storage vio
client COPYING INSTALL mysql-test regex strings win
cmake dbug libevent mysys scripts support-files zlib
CMakeLists.txt Docs libmysql mysys_ssl sql tests
cmd-line-utils Doxyfile-perfschema libmysqld packaging sql-bench unittest
config.h.cmake extra libservices plugin sql-common VERSION
3、配置 (配置mysql 的功能,添加支持)
[root@C7--02 mysql-5.6.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all
........
...内容太多
-DCMAKE_INSTALL_PREFIX :指定安装位置
-DSYSCONFDIR: 初始化参数文件的位置
-DDEFAULT_CHARSET: 默认的字符集编码
-DDEFAULT_COLLATION: 默认使用的字符集校对规则
-DWITH_EXTRA_CHARSETS:额外安装的其他字符集
4、编译并安装
[root@C7--02 mysql-5.6.36]# make && make install
.........
.....内容太多
5、安装后的其他调整
①、对数据库目录权限的调整
[root@C7--02 mysql-5.6.36]# chown -R mysql:mysql /usr/local/mysql/ #设置/usr/local/mysql/ 属主和属组 -R 递归到所有的子目录和文件
②、建立主配置文件
[root@C7--02 mysql-5.6.36] rm -rf /etc/my.cnf #删除默认配置文件
[root@C7--02 mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.cnf #将样本文件拷贝到/etc 并重命名为my.cnf
③、初始化数据库系统(以运行用户mysql 身份执行初始化脚本)
[root@C7--02 mysql-5.6.36]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
Installing MySQL system tables...2021-08-13 21:35:55 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
........
....
④、设置环境变量 (方便在任何目录下使用mysql 命令)
[root@C7--02 mysql-5.6.36]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile #把PATH=$PATH:/usr/local/mysql/bin 追加到/etc/profile的文件里面
[root@C7--02 mysql-5.6.36]# . /etc/profile #启动脚本立即生效
6、添加系统服务 (方便通过systemctl 进行管理)
[root@C7--02 mysql-5.6.36]# cp support-files/mysql.server /usr/local/mysql/bin/mysqld.sh #复制服务脚本并重命名为mysqld.sh
[root@C7--02 mysql-5.6.36]# chmod +x /usr/local/mysql/bin/mysqld.sh #添加可执行权限
[root@C7--02 mysql-5.6.36]# /usr/local/mysql/bin/mysqld.sh start #启动Mysql
Starting MySQL.Logging to '/usr/local/mysql/data/C7--02.err'.
.. SUCCESS!
[root@C7--02 mysql-5.6.36]# /usr/local/mysql/bin/mysqld.sh stop #停止MysqL(直接管理)
Shutting down MySQL.. SUCCESS!
[root@C7--02 mysql-5.6.36]# vim /usr/lib/systemd/system/mysqld.service #创建Mysql 系统服务的配置文件
[Unit] #添加内容
Description=Mysql Server
After=network.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFILE=/usr/local/mysql/data/www.pid
ExecStart=/usr/local/mysql/bin/mysqld.sh start
ExecStop=/usr/local/mysql/bin/mysqld.sh stop
[Install]
WantedBy=multi-user.target
保存退出
这时实现可以使用systemctl 进行管理mysqld 服务
[root@C7--02 mysql-5.6.36]# systemctl start mysqld #启动服务
[root@C7--02 mysql-5.6.36]# systemctl enable mysqld #设置为服务开机自启
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@C7--02 mysql-5.6.36]# systemctl status mysqld #查看服务状态
● mysqld.service - Mysql Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 五 2021-08-13 21:53:34 CST; 45s ago
Main PID: 39158 (mysqld_safe)
CGroup: /system.slice/mysqld.service
├─39158 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=...
└─39269 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/da...
8月 13 21:53:33 C7--02 systemd[1]: Starting Mysql Server...
8月 13 21:53:34 C7--02 systemd[1]: Started Mysql Server.
8月 13 21:53:48 C7--02 systemd[1]: [/usr/lib/systemd/system/mysqld.service:13] Unknown lvalue 'PID...ice'
Hint: Some lines were ellipsized, use -l to show in full.
[root@C7--02 mysql-5.6.36]# netstat -anpt | grep mysqld #查看服务端口监听状态
tcp6 0 0 :::3306 :::* LISTEN 39269/mysqld
安装完成,Mysql 服务可以正常登陆
二、登录并使用Mysql 服务器
1、登录
[root@C7--02 ~]# mysql -u root # 默认管理员是root ,密码为空
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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>
2、设置密码:
[root@C7--02 ~]# mysqladmin -u root -p password '123.com' #第一次设置密码(在mysql外面设置密码)
Enter password:
Warning: Using a password on the command line interface can be insecure.
[root@C7--02 ~]# mysql -u root #没有给密码出现错误
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@C7--02 ~]# mysql -u root -p123.com #加上密码成功进入mysql数据库
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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>
[root@C7--02 ~]# mysqladmin -u root -p password #非第一次修改密码
Enter password: #旧密码
New password: #新密码输入两次
Confirm new password:
[root@C7--02 ~]# mysql -u root -p123123 #修改后的密码
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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>
3、登录后 (注意: 所有的mysql 操作语句需要以 “;” 结束;输入时不区分大小写)
mysql> status; #查看信息状态
--------------
mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper #版本信息
Connection id: 6
Current database:
Current user: root@localhost #当前用户root本地登录
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.36 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 18 min 18 sec #运行时间
Threads: 1 Questions: 19 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.017
--------------
mysql> exit; 或者 mysql> quit; #退出mysql数据库
Bye
[root@C7--02 ~]#
(一)、查看数据库的结构
库——表——数据
1、查看当前服务器中存在哪些库
mysql> show databases; (默认建立了4 个库)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
2、切换到库
mysql> use mysql; #切换到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
3、查看当前库中存在哪些表
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
28 rows in set (0.00 sec)
4、查看表的结构
mysql> describe user; (查看表的数据结构) = mysql> describe mysql.user; (查看mysql 库中的user 表的数据结构)
mysql> describe mysql.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 | |
........
...
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
43 rows in set (0.00 sec)
(二)、创建及删除库和表
1、创建新的库:
mysql> create database aaa; #创建了一个aaa库 (在/usr/local/mysql/data/ 下会自动生成一个与库名相同的目录)
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
2、创建表 (注意:创建表前需要明确表的结构、各字段的名称和类型等信息)
语法结构: create table 表名 (字段1 名称 类型,字段2名称 类型,...,primary key (主键名));
案例:
mysql> create table a1 (姓名 char(16) not null,年龄 char(12) default '',班级 char(10), primary key (姓名));
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| a1 |
| a3 |
+---------------+
2 rows in set (0.00 sec)
(含义: 创建了一个名字是 a1 的表,该表中包含5个字段(姓名 、年龄、班级),每个字段的类型都是char(固定字符串类型);主键是xm字段;姓名 字段不允许为空(not null);年龄 字段默认值是空)
3、删除表
mysql> drop table 库名.表名;
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| a1 |
| a3 |
+---------------+
2 rows in set (0.00 sec)
mysql> drop table aaa.a3;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| a1 |
+---------------+
1 row in set (0.00 sec)
mysql>
4、删除库
mysql> drop database 库名;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| a2 |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database a2;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| aaa |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql>
(三)、管理表中的数据记录
1、往表中插入数据
语法结构: INSERT INTO 表名(字段1,字段2,...) VALUES(字段1 的值,字段2 的值,...);
简化语法: insert into 表名 values (字段1值,字段2值......);
案例:
insert into a1 (姓名,年龄,班级) values('露西亚','19','02');
(往aaa 成绩表中插入数据,该表中有3 个字段,插入的值依次是'露西亚','19','02');
2、查询数据记录
标准的语法结构: SELECT 字段1名,字段2名,... from 表名 where 条件;
1)、查询表中的所有数据 (通配符*)
案例: select * from aaa.a1; (查询aaa这个库中a1中的所有数据)
mysql> select * from aaa.a1;
+-----------+--------+--------+
| 姓名 | 年龄 | 班级 |
+-----------+--------+--------+
| 露西亚 | 19 | 02 |
+-----------+--------+--------+
1 row in set (0.00 sec)
2)、根据条件查询:
案例:查询aaa这个库中a1中年龄等于19 的数据
mysql> select * from aaa.a1;
+--------------+--------+--------+
| 姓名 | 年龄 | 班级 |
+--------------+--------+--------+
| 哈佛大学 | 1000 | 0 |
| 露西亚 | 19 | 02 |
+--------------+--------+--------+
2 rows in set (0.00 sec)
mysql> select 姓名,年龄,班级 from aaa.a1 where 年龄='19';
+-----------+--------+--------+
| 姓名 | 年龄 | 班级 |
+-----------+--------+--------+
| 露西亚 | 19 | 02 |
+-----------+--------+--------+
1 row in set (0.00 sec)
mysql>
3、修改表中的数据 (UPDATE)
标准语法结构: update 表名 set 字段1名=新值,字段2名=新值... where 条件;
案例:
mysql> update aaa.a1 set 班级='03' where 姓名='哈佛大学';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from aaa.a1;
+--------------+--------+--------+
| 姓名 | 年龄 | 班级 |
+--------------+--------+--------+
| 哈佛大学 | 1000 | 03 |
| 露西亚 | 19 | 02 |
+--------------+--------+--------+
2 rows in set (0.00 sec)
(把aaa库中的a1中姓名是哈佛大学的记录的“班级”字段值改为03 )
4、在数据表中删除指定的数据 (DELETE)
标准语法结构: DELETE from 表名 where 条件
案例: delete from TSZ355成绩表 where 姓名='魏帅';
mysql> delete from a1 where 姓名='哈佛大学';
Query OK, 1 row affected (0.00 sec)
mysql> select * from aaa.a1
-> ;
+-----------+--------+--------+
| 姓名 | 年龄 | 班级 |
+-----------+--------+--------+
| 露西亚 | 19 | 02 |
+-----------+--------+--------+
1 row in set (0.00 sec)
mysql>
(把 a1中姓名是哈佛大学的记录删除)
三、数据库用户授权
原因:Mysql 数据库中的root 账户拥有对所有库、表的全部权限 (权限太高,产生安全风险)
解决方案:创建一些低权限用户,让该账户只负责一部分库、表的管理和维护
1、授予权限 (GRANT语句)
基本语法结构: GRANT 权限列表 ON 库名.表名 to 用户名@来源地址 [identified by '密码']
权限列表:select(查询)、insert(插入)、update(修改)、delete(删除)、all(所有) 等
库名.表名: 针对指定的库或表 (如果用 * 表示所有)
用户名@来源地址 : 用于指定用户名称和允许访问的客户端地址
identified by: 设置用户连接数据库时所用的密码
案例1、授权一个用户名是boss,允许从本机访问,对aaa这个库拥有查询权限,验证密码是'123456'
grant select on aaa.* to 'boss'@'localhost' identified by '123456';
mysql> grant select on aaa.* to 'boss'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> exit;
Bye
[root@C7--02 ~]# mysql -u boss -p'123456'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, 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> select * from aaa.a1;
+-----------+--------+--------+
| 姓名 | 年龄 | 班级 |
+-----------+--------+--------+
| 露西亚 | 19 | 02 |
+-----------+--------+--------+
1 row in set (0.00 sec)
mysql> drop table aaa.a1;
ERROR 1142 (42000): DROP command denied to user 'boss'@'localhost' for table 'a1'
mysql>
我们设置了只能在本地登录只有查看权限其他没有其他权限
案例2、授权一个用户名是boss,允许从本机访问,对table这个库拥有所有权限,验证密码是'123456'
注意------只有root有最高权限需要退出换成root登录
grant all on table.* to 'boss'@'localhost' identified by '123456';
[root@C7--02 ~]# mysql -u boss -p'123456'
......
.........
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create table aaa.a3 (姓名 char(16) not null,年龄 char(12) default '',班级 char(10), primary key (姓名));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| a1 |
| a3 |
+---------------+
2 rows in set (0.00 sec)
成功创建一个a3的表格证明应用aaa数据库的所有权限
案例3、授权一个用户名是aaa,允许从192.168.1.4访问,对aaa这个库拥有所有权限,验证密码是'123456'
mysql> grant all on aaa.* to 'aaa'@'192.168.1.4' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
在192.168.100.102 上登录数据库的语法:
yum -y install mysql (装mysql 客户端)
mysql -u aaa -p123456 -h 192.168.100.101 (登录语法)
[root@C7--04 ~]# mysql -u aaa -p123456 -h 192.168.1.2
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.2' (113)
我们发现出现错误(113) 发现防火墙没有关闭
我们mysql服务器的防火墙没有关闭关闭后在访问
[root@C7--04 ~]# mysql -u aaa -p123456 -h 192.168.1.2
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
登录成功
排错思路
1、确定远程机器的防火墙关闭,或在防火墙允许3306端口号
2、确定数据库允许远程访问,通过语句grant all privileges on *.* to 'root'@'%' identified by '123456'即可。
mysql> select user,host,password from mysql.user;
+------+-------------+-------------------------------------------+
| user | host | password |
+------+-------------+-------------------------------------------+
| root | localhost | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
| root | c7--02 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | c7--02 | |
| aaa | 192.168.1.4 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| aaa | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| root | % | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+------+-------------+-------------------------------------------+
10 rows in set (0.00 sec)
[root@C7--02 ~]# mysql -u root -p'123.com'
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@C7--04 ~]# mysql -u root -p123.com -h 192.168.1.2
ERROR 1045 (28000): Access denied for user 'root'@'192.168.1.4' (using password: YES)
[root@C7--04 ~]# mysql -u root -p123456 -h 192.168.1.2
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.36 Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
root 远程登录成功
3、如果上面方法还是没有解决。直接kill进程,重启。
[root@C7--02 ~]# netstat -npl|grep 3306
tcp6 0 0 :::3306 :::* LISTEN 1163/mysqld
[root@C7--02 ~]# kill -9 1163
[root@C7--02 ~]# systemctl restart mysqld
[root@C7--02 ~]# netstat -npl|grep 3306
tcp6 0 0 :::3306 :::* LISTEN 16079/mysqld
案例4、查看权限
语法: show grants for 用户名@来源地址 注意 :在mysql服务器上查看
mysql> show grants for aaa@192.168.1.4;
+--------------------------------------------------------------------------------------------------------------+
| Grants for aaa@192.168.1.4 |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'aaa'@'192.168.1.4' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `aaa`.* TO 'aaa'@'192.168.1.4' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
2、取消权限 (REVOKE 语句)
语法结构: REVOKE 权限列表 on 库名.表名 from 用户名@来源地址
mysql> revoke all on aaa.* from aaa@192.168.1.4
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for aaa@192.168.1.4;
+--------------------------------------------------------------------------------------------------------------+
| Grants for aaa@192.168.1.4 |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'aaa'@'192.168.1.4' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
练习题
1. MySQL 5.X系列版本源码编译安装时通过什么进行编译?
需要cmake 进行编译
2. 在编译安装MySQL 5.5数据库系统时,如何添加对多种字符集的支持?
-DWITH_EXTRA_CHARSETS
3. MySQL 数据库中mysql_install_db脚本的作用是什么?
MySQL数据库的初始化
4. MySQL数据库默认使用TCP 什么端口提供服务?
3306
5. 用2种方法去修改MySQL用户root的密码
mysqladmin -u root -p password '密码值'
update mysql.user set password=password('123456')
6. 写出MySQL数据库中创建表的基本语法格式
create table 表名 (字段1 名称 类型,字段2名称 类型,...,primary key (主键名));
7. 在MySQL数据库中执行”DROP TABLE auth.users;”命令的作用是什么?
删除表users
8. 写出MSQL数据库中插入数据的基本语法格式
INSERT INTO 表名(字段1,字段2,) VALUES(字段1的值,字段2 的值,...);
9. ”DELETE FROM auth.users WHERE user_name='lisi';” 命令的作用是什么?
把users表中user_name是lisi的记录删除
10.”GRANT select ON auth.* TO 'xiaoqi'@'localhost' IDENTIFIED BY '123456';”命令的作用是什么?
授权一个用户名是xiaoqi,允许从本机访问,对这个auth库拥有查询权限,验证密码是'123456'