MySQL手工編譯安裝（MySQL5.7含軟件包資源鏈接）實

一、前言

? 上篇文章中主要介紹了數據庫的相關的概念。我們知道，數據庫主要分為關系型數據庫和非關系型數據庫，上篇文章中也列出了一些主流的數據庫。那么，這次我們就來介紹一下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）。