一、MySQL服务基础
1、MySQL的编译安装
1、安装依赖软件和cmake包
# rpm -ivh ncurses-devel-5.9-13.20130511.el7.x86_64.rpm
# tar zxf cmake-2.8.6.tar.gz -C /usr/src/
# cd /usr/src/cmake-2.8.6/
# ./configure && gmake && gmake install
# ln -s /usr/local/bin/cmake /usr/bin/
2、源码编译及安装
创建运行用户
# groupadd mysql
# useradd -M -s /sbin/nologin mysql -g mysql
编译安装
# tar zxf mysql-5.6.36.tar.gz -C /usr/src/
# cd /usr/src/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 && make && make install
参数 | 说明 |
---|---|
-DCMAKEJNSTALL_PREFIX | 指定将MySQL数据库程序安装到某目录下 |
-DSYSCONFDIR | 指定初始化参数文件目录 |
-DDEFAULT_CHARSET | 指定默认使用的字符集编码,如utf-8 |
-DDEFAULT_COLLATION | 指定默认使用的字符集校对规则,utf8_general_ci是适用于utf-8 字符集的通用规则 |
-DWITH_EXTRA_CHARSETS | 指定额外支持的其他字符集编码 |
3、安装后的其他调整
对数据库目录进行权限设置
# chown -R mysql:mysql /usr/local/mysql/
建立配置文件
# rm -rf /etc/my.cnf
# cp support-files/my-default.cnf /etc/my.conf
初始化数据库
# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
设置环境变量
# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
# . /etc/profile
4、添加系统服务
# cp support-files/mysql.server /usr/local/mysql/bin/mysqld.sh
# chmod +x /usr/local/mysql/bin/mysqld.sh
# vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Service
After=network.target
[Service]
User=mysql //指定程序运行的用户账号
Group=mysql //指定程序运行的组账号
Type=forking
PIDFile=/usr/local/mysql/data/localhost.localdomain.pid //指定PID文件位置,默认为主机名.pid
ExecStart=/usr/local/mysql/bin/mysqld.sh start
ExecStop=/usr/local/mysql/bin/mysqld.sh stop
[Install]
WantedBy=multi-user.target
启动mysqld服务
# systemctl start mysqld
# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
# systemctl status mysqld.service
● mysqld.service - MySQL Service
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 五 2020-06-05 10:48:34 CST; 13s ago
Main PID: 31346 (mysqld)
CGroup: /system.slice/mysqld.service
├─31246 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/loc...
└─31346 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --da...
6月 05 10:48:33 localhost.localdomain systemd[1]: Starting MySQL Service...
6月 05 10:48:34 localhost.localdomain systemd[1]: mysqld.service: Supervising...
6月 05 10:48:34 localhost.localdomain systemd[1]: Started MySQL Service.
6月 05 10:48:42 localhost.localdomain systemd[1]: mysqld.service: Supervising...
Hint: Some lines were ellipsized, use -l to show in full.
# netstat -anpt | grep mysqld
tcp6 0 0 :::3306 :::* LISTEN 31346/mysqld
5、本地安装脚本
# vim mysql.sh
#!/bin/bash
#安装mysql前提软件
yum -y install make gcc gcc-c++ kernel-devel m4 ncurses-devel openssl-devel perl-Data-Dumper
#解压cmake工具,并编译安装
cd /media
tar zxf cmake-2.8.6.tar.gz -C /usr/src
cd /usr/src/cmake-2.8.6
./configure && gmake && gmake install
#创建mysql程序用户
useradd -M -s /sbin/nologin mysql
#解压编译安装mysql
cd /media
tar zxf mysql-5.6.36.tar.gz -C /usr/src
cd /usr/src/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 && make && make install
#为mysql安装路径设置权限
chown -R mysql:mysql /usr/local/mysql
#删除默认的mysql配置文件,复制源码包的配置文件模板
rm -rf /etc/my.cnf
cp /usr/src/mysql-5.6.36/support-files/my-default.cnf /etc/my.cnf
#初始化数据库
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
#把mysql命令添加到环境变量,并刷新变量
echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile
#复制启动脚本模板到指定目录并赋权
cp /usr/src/mysql-5.6.36/support-files/mysql.server /usr/local/mysql/bin/mysqld.sh
chmod +x /usr/local/mysql/bin/mysqld.sh
#添加mysql到系统服务
hostname=`cat /etc/hostname`
cat >> /usr/lib/systemd/system/mysqld.service << END
[Unit]
Description=MySQL Server
After=network.target
[Service]
User=mysql
Group=mysql
Type=forking
PIDFile=/usr/local/mysql/data/$hostname.pid
ExecStart=/usr/local/mysql/bin/mysqld.sh start
ExecStop=/usr/local/mysql/bin/mysqld.sh stop
[Install]
WantedBy=multi-user.target
END
#启动mysql服务,并设置开机自启动
systemctl start mysqld
systemctl enable mysqld
2、访问MySQL数据库
1、登录到MySQL服务器
未设置密码的root用户登录本机的MySQL数据库
# mysql -u root //"-u"选项用于指定认证用户
有密码
# mysql -u root -p
Enter password:
修改密码
# mysqladmin -u root password
New password:
Confirm new password:
# mysql -u root -p
Enter password:
2、执行MySQL操作语句
mysql> status //查看当前数据库服务的基本信息
--------------
mysql Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using EditLine wrapper
Connection id: 4
Current database:
Current user: root@localhost
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: 5 min 41 sec
Threads: 1 Questions: 14 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.041
--------------
3、退出“mysql>”操作环境
mysql> exit
Bye
二、使用MySQL数据库
1、查看数据库结构
1、查看当前服务器中有哪些库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
2、查看当前使用的库中有哪些表
mysql> use mysql; //切换到所使用的库
Database changed
mysql> show tables;
3、查看表的结构
mysql> use mysql;
Database changed
mysql> describe user;
或者指定”库名.表名”
mysql> describe mysql.user;
2、创建及删除库和表
1、创建新的库
mysql> create database auth;
Query OK, 1 row affected (0.00 sec)
2、创建新的表
基本格式:CREATE TABLE 表名(字段1名称 类型,字段2名称 类型,…,PRIMARY KEY (主键名))
mysql> use auth;
Database changed
mysql> create table users (user_name char(16) not null,user_passwd char(48) default '',primary key (user_name));
Query OK, 0 rows affected (0.00 sec)
3、删除一个数据表
mysql> drop table auth.users;
Query OK, 0 rows affected (0.01 sec)
4、删除一个数据库
mysql> drop database auth;
Query OK, 0 rows affected (0.01 sec)
3、管理表中的数据记录
1、插入数据记录
语句格式:INSERT INTO 表名(字段1,字段2,…) VALUES(字段1的值,字段2的值,…)
向auth库中的users表插入一条记录:用户名为"zhangsan",对应的密码为 "123456"
mysql> use auth;
Database changed
mysql> insert into users(user_name,user_passwd) values('zhangsan',password('123456'));
Query OK, 1 row affected (0.00 sec)
当插入新的数据完整包括表中所有字段的值
mysql> insert into users values('lisi',password('654321'));
Query OK, 1 row affected (0.00 sec)
2、查询数据记录
语句格式:SELECT 字段名1,字段名2,…FROM 表名 WHERE 条件表达式
显示所有的数据记录
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |
| zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
2 rows in set (0.01 sec)
查找users表中用户名为"zhangsan"的记录,显示其中用户名、密码字段的信息
mysql> select user_name,user_passwd from auth.users where user_name='zhangsan';
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
3、修改数据记录
语句格式:UPDATE 表名 SET 字段名1=字段值1 [,字段名2=字段值2] WHERE 条件表达式
users表中用户名为“lisi"的记录,将密码字串设为空值
mysql> update auth.users set user_passwd=password('') where user_name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from auth.users; +-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| lisi | |
| zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
2 rows in set (0.00 sec)
将数据库root用户的密码设为"123456"
mysql> update mysql.user set password=password('123456') where user='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql> flush privileges; //刷新用户授权信息
Query OK, 0 rows affected (0.00 sec)
在Linux命令行环境中时,将数据库用户root的密码设置为"12345678"
# mysqladmin -u root -p'123456' password '123456'
4、删除数据记录
语句格式:DELETE FROM 表名 WHERE 条件表达式
删除users表中用户名为"lisi”的数据记录
mysql> delete from auth.users where user_name='lisi';
Query OK, 1 row affected (0.00 sec)
mysql> select * from auth.users;
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
显示user字段为空的用户记录,并用DELETE语句进行该用户记录的删除
mysql> select user,host,password from mysql.user where user='';
+------+-----------------------+----------+
| user | host | password |
+------+-----------------------+----------+
| | localhost | |
| | localhost.localdomain | |
+------+-----------------------+----------+
2 rows in set (0.00 sec)
mysql> delete from mysql.user where user='';
Query OK, 2 rows affected (0.00 sec)
三、数据库用户授权
1、授予权限
语句格式:
GRANT 权限列表 ON 库名.表名 TO 用户名@来源地址 [ IDENTIFIED BY '密码' ]
参数 | 说明 |
---|---|
权限列表 | 用于列出授权使用的各种数据库操作,以逗号进行分隔, 使用"all”表示所有权限,可授权执行任何操作 |
库名.表名 | 用于指定授权操作的库和表的名称,其中可以使用通配符“※” ,使用 "auth.*"表示授权操作的对象为auth库中的所有表 |
用户名@来源地址 | 用于指定用户名称和允许访问的客户机地址,即谁能连接、能从哪里连接。来源地址可以是域名、IP地址,还可以使用"%"通配符,表示某个区域或网段内的所有地址 |
IDENTIFIED BY | 用于设置用户连接数据库时所使用的密码字符串。在新建用户时,若省略IDENTIFIED BY"部分,则用户的密码将为空 |
添加一个名为"xsqi"的数据库用户,并允许其从本机访问,对auth库中的所有表具有查询权限,验证密码为"123456"
mysql> grant select on auth.* to 'xiaoqi'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
切换到其他Shell终端,以用户xiaoqi的身份连接数据库查询auth库中users表的数据记录
# mysql -uxiaoqi -p123456
mysql> select * from auth.users; //验证授权的访问操作
+-----------+-------------------------------------------+
| user_name | user_passwd |
+-----------+-------------------------------------------+
| zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+-------------------------------------------+
1 row in set (0.00 sec)
查询其他库中的表的记录被拒绝
mysql> select * from mysql.user; //验证非授权的访问操作
ERROR 1142 (42000): SELECT command denied to user 'xiaoqi'@'localhost' for table 'user'
新建school库,并授权从IP地址为192.168.4.19的主机连接,用户名为"teacher”,密码为"pwd@123", 允许在school库中执行所有操作。
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on school.* to 'teacher'@'192.168.4.19' identified by 'pwd123';
Query OK, 0 rows affected (0.00 sec)
2、查看权限
语句格式:SHOW GRANTS FOR 用户名@来源地址
查看用户teacher从主机192.168.4.19访问数据库时的授权信息
mysql> show grants for 'teacher'@'192.168.4.19';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for teacher@192.168.4.19 |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'teacher'@'192.168.4.19' IDENTIFIED BY PASSWORD '*353C33BC20A4B4B2281F3DAAE901DBD0A5224E24' |
| GRANT ALL PRIVILEGES ON `school`.* TO 'teacher'@'192.168.4.19' |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
3、撤销权限
语句格式:REVOKE 权限列表 ON 数据库名.表名 FROM 用户名@来源地址
撤销用户xiaoqi从本机访问数据库auth的所有权限
mysql> show grants for 'xiaoqi'@'localhost'; //确认已撤销对auth库的权限
+---------------------------------------------------------------------------------------------------------------+
| Grants for xiaoqi@localhost |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xiaoqi'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
评论区