mysql（源码安装和配置）

mysql

数据库概述

• 什么是数据库？
  数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。
• 什么是数据库管理系统？
  数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。

常见的数据库管理系统：

数据库名称	应用
MYSQL	开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL6.x版本也开始收费
Oracle	收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL
DB2	IBM公司的数据库产品,收费的。常应用在银行系统中
SQLServer	MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用
SyBase	已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner
SQLite	嵌入式的小型数据库，应用在手机端

• 常用数据库：MYSQL，Oracle．
• 这里使用MySQL数据库。MySQL中可以有多个数据库，数据库是真正存储数据的地方。

1.下载mysql的二进制包：

• 我们这边已经将myqll安装包提前下载到/usr/src里面

[root@wuliyong src]# ls
debug  kernels  mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz

在安装mysql所依赖的包

[root@wuliyong src]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel
已加载插件：product-id, subscription-manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
软件包 ncurses-devel-5.9-13.20130511.el7.x86_64 已安装并且是最新版本
软件包 1:openssl-devel-1.0.1e-34.el7.x86_64 已安装并且是最新版本
软件包 1:openssl-1.0.1e-34.el7.x86_64 已安装并且是最新版本
软件包 cmake-2.8.11-4.el7.x86_64 已安装并且是最新版本
软件包 1:mariadb-devel-5.5.35-3.el7.x86_64 已安装并且是最新版本
无须任何处理

• 创建用户和组

[root@wuliyong src]# groupadd -r mysql
[root@wuliyong src]# useradd -M -s /sbin/nologin -g mysql mysql

2.解压软件至/usr/local:

[root@wuliyong ~]# ls
anaconda-ks.cfg  mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
[root@wuliyong ~]# tar -xf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /usr/local
[root@wuliyong local]# ls
bin  games    lib    libexec  mysql-5.7.23-linux-glibc2.12-x86_64  share
etc  include  lib64  mysql    sbin                                 src

3.建立一个软连接，并将mysql的路径写入到环境变量中：

[root@wuliyong src]# cd /usr/local/
[root@wuliyong local]#  ln -sv mysql-5.7.23-linux-glibc2.12-x86_64/ mysql
"mysql" -> "mysql-5.7.23-linux-glibc2.12-x86_64/"
[root@wuliyong local]# ll
总用量 4
drwxr-xr-x. 2 root root    6 3月  13 2014 bin
drwxr-xr-x. 2 root root    6 3月  13 2014 etc
drwxr-xr-x. 2 root root    6 3月  13 2014 games
drwxr-xr-x. 2 root root    6 3月  13 2014 include
drwxr-xr-x. 2 root root    6 3月  13 2014 lib
drwxr-xr-x. 2 root root    6 3月  13 2014 lib64
drwxr-xr-x. 2 root root    6 3月  13 2014 libexec
lrwxrwxrwx. 1 root root   36 2月  21 22:01 mysql -> mysql-5.7.23-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 4096 2月  21 20:11 mysql-5.7.23-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root    6 3月  13 2014 sbin
drwxr-xr-x. 5 root root   46 1月  15 00:15 share
drwxr-xr-x. 2 root root    6 3月  13 2014 src

• 修改目录/usr/local/mysql的属主属组

[root@wuliyong local]#  chown -R mysql.mysql /usr/local/mysql

4.创建mysql存放数据的目录，并修改属组和属主为mysql:

[root@wuliyong usr]#  mkdir /opt/data
[root@wuliyong usr]# chown -R mysql.mysql /opt/data/
[root@wuliyong usr]# ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 2月  21 22:03 data

5.添加环境变量：

[root@wuliyong local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@wuliyong local]# . /etc/profile.d/mysql.sh
[root@wuliyong local]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

6.初始化数据库，并记录下随机密码，最后一排结尾即是第一次登陆的随机密码:

• 这边会显示一个问题关于下面的报错：

[root@wuliyong usr]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

• 解决办法安装libaio libaio-devel 这两个安装包：

[root@wuliyong usr]# yum install -y libaio libaio-devel
[root@wuliyong usr]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2019-02-21T14:05:45.670935Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-02-21T14:05:46.553999Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-02-21T14:05:46.647011Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-02-21T14:05:46.714655Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: c8f8640e-35e1-11e9-8d4c-000c2916bd48.
2019-02-21T14:05:46.716217Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-02-21T14:05:46.727607Z 1 [Note] A temporary password is generated for root@localhost: 9PJwTz5w7x#J

• 我们将这边生成的临时密码写入到root下面的tex里面：

[root@wuliyong usr]# echo '9PJwTz5w7x#J' > /root/tex

7.修改mysql的配置文件：

[root@wuliyong ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
"/usr/local/include/mysql" -> "/usr/local/mysql/include/"
[root@wuliyong ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@wuliyong ~]# ldconfig -v
过程略

• 生成配置文件

[root@wuliyong ~]# cat > /etc/my.cnf <<EOF
> [mysqld]
> basedir = /usr/local/mysql
> datadir = /opt/data
> socket = /tmp/mysql.sock
> port = 3306
> pid-file = /opt/data/mysql.pid
> user = mysql
> skip-name-resolve
> EOF

8.配置服务启动脚本：

[root@wuliyong ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@wuliyong ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@wuliyong ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld

9.启动服务修改密码：

[root@wuliyong ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/wuliyong.err'.
 SUCCESS! 
[root@wuliyong ~]#  ps -ef|grep mysql
root       2761      1  0 22:08 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql      2939   2761  2 22:08 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=wuliyong.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root       2969   2614  0 22:08 pts/0    00:00:00 grep --color=auto mysql
[root@wuliyong ~]#  ss -antl
State      Recv-Q Send-Q             Local Address:Port               Peer Address:Port 
LISTEN     0      100                    127.0.0.1:25                            *:*     
LISTEN     0      128                            *:22                            *:*     
LISTEN     0      100                          ::1:25                           :::*     
LISTEN     0      80                            :::3306                         :::*     
LISTEN     0      128                           :::22                           :::*     

10.数据库的备份

#输入临时密码进入数据库，创建数据库wzy,在库里面建haha表

[root@wuliyong ~]# 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.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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> 

mysql> create database wzy;          #创建数据库wzy;
Query OK, 1 row affected (0.00 sec)

mysql> use wzy;
Database changed
mysql> create table haha (id int not null,name char not null,age int not null）
Query OK, 0 rows affected (0.01 sec)           #在wzy数据库中创建haha表

mysql> desc haha;                                         #查看表结构
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
| name  | char(1) | NO   |     | NULL    |       |
| age   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
mysql> select * from haha;                       #查看haha表的内容
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | p    |   3 |
|  1 | 8    |   3 |
|  1 | -    |   3 |
|  1 | ?    |   3 |
+----+------+-----+
4 rows in set (0.00 sec)
1234567891011121314151617181920212223242526

11.备份数据库

[root@wuliyong ~]# mysqldump -uroot -p -h192.168.26.16 --all-databases > all-2018.09.28.sql                   #在备份端上将数据库备份到all-2018.09.28.sql文件中
Enter password: 
[root@wuliyong ~]# mysql -uroot -p -h192.168.26.16 -e 'show databases;'
Enter password:                        #查看备份过来的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| wzy               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
12345678910111213

12.删除wzy数据库，进行数据恢复

mysql> drop database wzy;                        #模拟误删wzy数据库
Query OK, 1 row affected (0.09 sec)

[root@wuliyong ~]# mysql -uroot -p123456 -h192.168.26.16 <all-2018.09.28.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@wuliyong ~]# mysql -uroot -p123456 -h192.168.118.128 -e 'show databases';
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+                              #备份数据库
| Database           |
+--------------------+
| information_schema |
| dxk                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

1234567891011121314151617
服务端查询数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dxk                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from dxk;
+---------------+
| Tables_in_dxk |
+---------------+
| haha          |
+---------------+
1 row in set (0.00 sec)

---------------------

搭建好mysql服务后，我们来完成以下数据库操作要求：

• 1.创建一个以你名字为名的数据库，并创建一张表的学生，该表包含三个字段（ID，姓名，年龄），表结构如下：

mysql> desc student;
±------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±------+
| id | int(11) | NO | | NULL | |
| name | varchar(100) | NO | | NULL | |
| age | tinyint(4) | YES | | NULL | |
±------±-------------±-----±----±--------±------+

• 操作过程：

[root@bogon]# mysql -uroot -wuzhiyong123
mysql> create databasewuzhiyong;
mysql> use wuzhiyong
mysql> create table student(id int(11) NOT NULL,name CHAR(100) NOT NULL,age tinyint NULL);
mysql> DESC qinyong.student;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | int(11)    | NO   |     | NULL    |       |
| name  | char(100)  | NO   |     | NULL    |       |
| age   | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

• 2.查看下该新建的表有无内容（用select语句）

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

• 3 往新建的student表中插入数据（用insert语句），结果应如下所示：

+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+

• 操作过程如下：

mysql> insert into student (id,name,age)values (1,'tom',20),(2,'jerry',23),(3,'wangqing',25),(4,'sean',28),(5,'zhangshan',26),(6,'zhangshan',20),(7,'lisi',NULL),(8,'chenshuo',10),(9,'wangwu',3),(10,'qiuyi',15),(11,'qiuxiaotian',20);
mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        | NULL |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
--------------------- 

• 4修改lisi的年龄为50

mysql> update student set age = 50 where name = 'lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
--------------------- 

• 5.以下字段降序排序

mysql> select * from student order by age desc;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  7 | lisi        |   50 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  3 | wangqing    |   25 |
|  2 | jerry       |   23 |
|  1 | tom         |   20 |
|  6 | zhangshan   |   20 |
| 11 | qiuxiaotian |   20 |
| 10 | qiuyi       |   15 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |    3 |
+----+-------------+------+c;
--------------------- 

• 6查询学生表中年龄最小的3位同学

mysql> select * from student order by age limit 3;
+----+----------+------+
| id | name     | age  |
+----+----------+------+
|  9 | wangwu   |    3 |
|  8 | chenshuo |   10 |
| 10 | qiuyi    |   15 |
+----+----------+------+
--------------------- 

• 7 查询学生表中年龄最大的4位同学

mysql> select * from student order by age desc limit 4;
+----+-----------+------+
| id | name      | age  |
+----+-----------+------+
|  7 | lisi      |   50 |
|  4 | sean      |   28 |
|  5 | zhangshan |   26 |
|  3 | wangqing  |   25 |
+----+-----------+------+
--------------------- 

• 8 查询学生表中名字叫zhangshan的记录

mysql> select name from student where name='zhangshan';
+-----------+
| name      |
+-----------+
| zhangshan |
| zhangshan |
+-----------+

• 9 查询学生表中名字叫zhangshan且年龄大于20岁的记录

mysql> select name from student where age>20 and name='zhangshan';
+-----------+
| name      |
+-----------+
| zhangshan |

• 10 查询学生表中年龄在23到30之间的记录

mysql> select name from student where age between 23 and 30 ;
+-----------+
| name      |
+-----------+
| jerry     |
| wangqing  |
| sean      |
| zhangshan |
+-----------+
--------------------- 

• 11.修改wangwu的年龄为100

mysql> update student set age = 100 where name = 'wangwu';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  6 | zhangshan   |   20 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
--------------------- 

• 12 删除学生中名字叫zhangshan且年龄小于等于20的记录

mysql> delete from student where name='zhangshan' and  age <= 20;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+-------------+------+
| id | name        | age  |
+----+-------------+------+
|  1 | tom         |   20 |
|  2 | jerry       |   23 |
|  3 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
+----+-------------+------+
---------------------