Linux操作文档——MySQL数据库基础（5.6.36）

一、MySQL服务基础

1、MySQL的编译安装

1、安装依赖软件和cmake包

[root@localhost Packages]# rpm -ivh ncurses-devel-5.9-13.20130511.el7.x86_64.rpm 
[root@localhost media]# tar zxf cmake-2.8.6.tar.gz -C /usr/src/
[root@localhost media]# cd /usr/src/cmake-2.8.6/
[root@localhost cmake-2.8.6]# ./configure && gmake && gmake install
[root@localhost ~]# ln -s /usr/local/bin/cmake /usr/bin/

2、源码编译及安装

创建运行用户

[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -M -s /sbin/nologin mysql -g mysql

编译安装

[root@localhost media]# tar zxf mysql-5.6.36.tar.gz -C /usr/src/
[root@localhost media]# cd /usr/src/mysql-5.6.36/
[root@localhost 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、安装后的其他调整

对数据库目录进行权限设置

[root@localhost mysql-5.6.36]# chown -R mysql:mysql /usr/local/mysql/

建立配置文件

[root@localhost mysql-5.6.36]# rm -rf /etc/my.cnf
[root@localhost mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.conf

初始化数据库

[root@localhost mysql-5.6.36]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/

设置环境变量

[root@localhost mysql-5.6.36]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
[root@localhost mysql-5.6.36]# . /etc/profile

4、添加系统服务

[root@localhost mysql-5.6.36]# cp support-files/mysql.server /usr/local/mysql/bin/mysqld.sh
[root@localhost mysql-5.6.36]# chmod +x /usr/local/mysql/bin/mysqld.sh 
[root@localhost mysql-5.6.36]# 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服务

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@localhost ~]# 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.
[root@localhost ~]# netstat -anpt | grep mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      31346/mysqld

5、本地安装脚本

[root@localhost ~]# 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数据库

[root@localhost ~]# mysql -u root                 //"-u"选项用于指定认证用户

有密码

[root@localhost ~]# mysql -u root -p
Enter password:

修改密码

[root@localhost ~]# mysqladmin -u root password
New password: 
Confirm new password: 
[root@localhost ~]# 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"

[root@localhost ~]# 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表的数据记录

[root@localhost ~]# 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)

posted @ 2020-02-24 23:27 高中僧阅读(42) 评论(0) 收藏举报

刷新页面返回顶部

高中僧