MySQL手工编译安装（MySQL5.7含软件包资源链接）实

发布时间：2020-04-10 00:56:23 作者：wx5d8a17c45cb5b
来源：网络阅读：247

一、前言

上篇文章中主要介绍了数据库的相关的概念。我们知道，数据库主要分为关系型数据库和非关系型数据库，上篇文章中也列出了一些主流的数据库。那么，这次我们就来介绍一下MySQL数据库的相关知识。数据库简介：https://blog.51cto.com/14557673/2463928

二、MySQL介绍

2.1MySQL概述

MySQL数据库是关系型数据库的一种，它是一款深受欢迎的开源关系型数据库，是Oracle公司旗下的产品，遵守GPL协议，可以免费使用与修改。

2.2MySQL数据库的特点

性能卓越、服务稳定；
开源、无版权限制、成本低；
支持多线程、多用户；
基于客户端/服务器（C/S）架构；
安全可靠

2.3MySQL的版本

MySQL分为商业版和社区版，其中社区版是可以免费使用的，而商业版则是由MySQL AB公司负责开发与维护，需要付费使用。

三、MySQL手工编译安装流程（实例）

MySQL的手工编译安装在之前搭建LAMP（MySQL5.6）和LNMP（MySQL5.7+boost）架构的时候有安装过，这次我们手工编译安装一下完整的5.7版本其实和在LNMP搭建时的操作差不多。
LNMP架构搭建

1、安装环境包与编译器等所需工具
资源链接：https://pan.baidu.com/s/1mkqaEcxrXkTKi7Klq--JXw
提取码：i4ak

安装的是5.7版本

[root@localhost mysql]# ls
boost_1_59_0.tar.gz  mysql-5.7.17.tar.gz
[root@localhost mysql]# yum install -y gcc gcc-c++ ncurses ncurses-devel bison cmake

2、创建mysql用户

[root@localhost mysql]# useradd -s /sbin/nologin mysql

3、解压缩

[root@localhost mysql]# tar zxf mysql-5.7.17.tar.gz -C /opt/
[root@localhost mysql]# tar zxf boost_1_59_0.tar.gz -C /usr/local
[root@localhost mysql]# cd /usr/local
[root@localhost local]# mv boost_1_59_0/ boost/
[root@localhost local]# cd /opt/mysql-5.7.17

4、配置相关参数

[root@localhost mysql-5.7.17]# cmake \
-DCMKAE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=/usr/local/boost \
-DWITH_SYSTEMD=1
...#省略配置过程

5、编译与安装

[root@localhost mysql-5.7.17]# make 
...     #省略编译过程
[root@localhost mysql-5.7.17]# make install
...     #省略安装过程

6、设置属主和属组

[root@localhost mysql-5.7.17]# cd /usr/local/
[root@localhost local]# chown -R mysql.mysql mysql/

7、修改配置文件

[root@localhost local]# vim /etc/my.cnf
[root@localhost local]# cat /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

8、设置环境变量

[root@localhost local]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@localhost local]# echo 'export PATH' >> /etc/profile
[root@localhost local]# source /etc/profile

9、初始化数据库

[root@localhost mysql]# ls
bin      docs     man         mysql.sock.lock  share
COPYING  include  mysqld.pid  mysql-test       support-files
data     lib      mysql.sock  README           usr
[root@localhost mysql]# bin/mysqld \
> --initialize-insecure \
> --user=mysql \
> --basedir=/usr/local/mysql \
> --datadir=/usr/local/mysql/data
...#省略部分内容

10、服务文件设置，便于systemctl管理

[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /lib/systemd/system/

11、开启服务、可以设置开机自启动

[root@localhost mysql]# systemctl start mysqld.service 
[root@localhost mysql]# netstat -antp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      13105/mysqld        
[root@localhost mysql]# systemctl enable mysqld.service 
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

12、设置初始化密码，进入mysql开始使用

[root@localhost mysql]# mysqladmin -uroot -p password
Enter password: 
New password: 
Confirm new password: 
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 Source distribution

Copyright (c) 2000, 2016, 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> exit
Bye
[root@localhost mysql]#

以上就是mysql5.7版本的手工编译安装的所有操作了，具体的含义与命令可以参考方才提及的文章

四、MySQL的常用操作命令

MySQL数据库系统也是一个典型的C/S架构的应用，要访问MySQL数据库需要使用专门的客户端软件，而在Linux系统中，最简单、易用的MySQL客户端软件就是其自带的mysql工具。

4.1访问数据库

MySQL操作语句以分号表示结束，可以不区分大小写；

根据上面的演示步骤中已经介绍了如何登录数据库了，那么我们如何修改登录密码呢？

4.1.1设置登录密码和修改密码

mysql> set password=password('123123');  #设置新的密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@localhost ~]# mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)         #输入原来的密码失效
[root@localhost ~]# mysql -u root -p  #重新登录
Enter password:         #新密码成功
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
...#省略部分内容

4.1.2查看数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

...#默认自带的4个数据库

4.1.3退出数据库

mysql> exit
Bye
[root@localhost ~]#

4.2使用MySQL数据库

这里介绍查看数据库、表结构的相关操作语句。

4.2.1查看和使用服务器包含的数据库

mysql> show databases;   #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use 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

4.2.2查看数据库中的表

命令格式：show tables；

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
...#省略部分内容      
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

4.2.3查看表的结构

命令格式：describe 数据表名；

mysql> describe db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| 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       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

4.3SQL语言介绍

SQL语言是数据库目前标准的指令的集合。SQL，及结构化查询语言。由一下部分组成：

4.3.1 DDL

DDL——Data Definition Language，数据定义语言：用来建立数据库、数据库对象和定义其列，如create、alter、drop。

4.3.2DML

DML——Data Manipulation Language，数据操纵语言：用来查询、插入、删除和修改数据库中的数据，如select、insert、update、delete。

4.3.3DCL

DCL——Date Control Language，数据控制语言：用来控制数据库组件的存取许可、存取权限等，如commit、rollback、grant、revoke。

4.4创建及删除库和表

4.4.1创建新的库

命令格式：create database 库名；

实例：

mysql> create database book;
Query OK, 1 row affected (0.00 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| book               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use book;
Database changed
mysql> show tables;
Empty set (0.00 sec)

4.4.2创建新的表

命令格式：create table 数据表名 (字段1名称类型，字段2名称类型，... primary key （主键名）)；

实例：

mysql> create table tech_book (id int,price decimal(4,2),bookname varchar(10),primary key(id));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_book |
+----------------+
| tech_book      |
+----------------+
1 row in set (0.00 sec)

mysql>

4.4.3删除一个表

命令格式：drop table 数据库名.表名；或者drop table 表名；

实例：

mysql> drop table tech_book;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

4.4.4删除一个数据库

命令格式：drop database 数据库名；

实例;

mysql> drop database book;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

4.5管理表中的数据记录

首先我们新建数据库和一个表格用来介绍管理表数据记录的命令操作

mysql> create database fruit;
Query OK, 1 row affected (0.00 sec)

mysql> use fruit;
Database changed

mysql> create table fruit_info (id int(4) not null,price decimal(3,2) not null,type char(5) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-----------------+
| Tables_in_fruit |
+-----------------+
| fruit_info      |
+-----------------+
1 row in set (0.00 sec)

mysql> desc fruit_info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(4)       | NO   |     | NULL    |       |
| price | decimal(3,2) | NO   |     | NULL    |       |
| type  | char(5)      | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

4.5.1插入数据记录

语句结构：insert into 表名（字段1，字段2,...） values（字段1值，字段2值，...）;

实例：

mysql> insert into fruit_info (id,price,type) values (1,5.5,'apple');
Query OK, 1 row affected (0.00 sec)

mysql> insert into fruit_info values (2,3.5,'pear');
Query OK, 1 row affected (0.00 sec)

mysql> insert into fruit_info values (3,5.5,'grape'),(4,8,'peach');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

4.5.2查询数据记录

语句格式：select 字段1，字段2，...from 表名 where 条件表达式；

实例：

mysql> select * from fruit_info; #直接查看表的信息
+----+-------+-------+
| id | price | type  |
+----+-------+-------+
|  1 |  5.50 | apple |
|  2 |  3.50 | pear  |
|  3 |  5.50 | grape |
|  4 |  8.00 | peach |
+----+-------+-------+
4 rows in set (0.00 sec)

mysql> select id,type from fruit_info where price=5.5;
+----+-------+
| id | type  |
+----+-------+
|  1 | apple |
|  3 | grape |
+----+-------+
2 rows in set (0.00 sec)

mysql> select id,type from fruit.fruit_info where price=5.5;
+----+-------+
| id | type  |
+----+-------+
|  1 | apple |
|  3 | grape |
+----+-------+
2 rows in set (0.00 sec)

4.5.3修改数据记录

语句格式：update 表名 set 字段1 = 字段值 2[，字段2 = 字段值2，...] where 条件表达式；

实例：

mysql> update fruit_info set price = 4.5 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from fruit_info;
+----+-------+-------+
| id | price | type  |
+----+-------+-------+
|  1 |  5.50 | apple |
|  2 |  3.50 | pear  |
|  3 |  4.50 | grape |
|  4 |  8.00 | peach |
+----+-------+-------+
4 rows in set (0.00 sec)

mysql> update fruit_info set price = 4.5 where type='apple' or id =2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from fruit_info;
+----+-------+-------+
| id | price | type  |
+----+-------+-------+
|  1 |  4.50 | apple |
|  2 |  4.50 | pear  |
|  3 |  4.50 | grape |
|  4 |  8.00 | peach |
+----+-------+-------+
4 rows in set (0.00 sec)

4.5.4删除数据记录

语句格式：delete from 表名 where 条件表达式；

实例：

mysql> delete from fruit_info where type='apple';
Query OK, 1 row affected (0.01 sec)

mysql> select * from fruit_info;
+----+-------+-------+
| id | price | type  |
+----+-------+-------+
|  2 |  4.50 | pear  |
|  3 |  4.50 | grape |
|  4 |  8.00 | peach |
+----+-------+-------+
3 rows in set (0.00 sec)

mysql> delete from fruit_info where price=4.5 and type='pear';
Query OK, 1 row affected (0.01 sec)

mysql> select * from fruit_info;
+----+-------+-------+
| id | price | type  |
+----+-------+-------+
|  3 |  4.50 | grape |
|  4 |  8.00 | peach |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> delete from fruit_info where price=4.5 or type='peach';
Query OK, 2 rows affected (0.00 sec)

mysql> select * from fruit_info;
Empty set (0.00 sec)

五、本文总结

本文主要演示了mysql5.7版本的手工编译安装全流程，并且介绍了mysql数据库相关的常用命令和SQL语言的使用格式以及相关实例。其基本管理主要包括查看数据库结构、创建及删除数据库和表、管理数据表的记录（增删改查操作——insert、delete、update、select）。