第十七课时预习笔记

LAMP架构介绍

LAMP是 linux Apache MySQL PHP的简写，即把Apache MySQL PHP 安装在linux系统上，组成一个环境来运行PHP网站。这里的Apache是httpd服务。这些可以安装在一个机器上，也可以安装在多台机器上，但是httpd和PHP安装在一台机器上（php作为httpd的一个模块存在的。 他们两者必须要在一起，才能实现效果）。

httpd，MySQL，PHP三者如何工作 

静态，就是直接由webserver处理的请求，比如图片，js，css等，而动态的是需要和数据库打交道的请求，比如你现在访问ask.apelearn.com，登录用户，发帖子，看帖子，这些都是需要和数据库打交道的。这样的就是动态。

安装MySQL步骤：

安装时在那个目录下操作很重要，决定着你能否安装成功。

下载MySQL到指定目录下（/usr/local/src）.

[root@shuai-01 ~]# cd /usr/local/src/
[root@shuai-01 src]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

解压包：

[root@shuai-01 local]# tar zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

将解压的包移动并重命名为/usr/local/mysql

[root@shuai-01 local]# mv mysql-5.6.36-linux-glibc2.5-x86_64 /usr/local/mysql
[root@shuai-01 mysql]# ls
bin      data  include  man     mysql-test  scripts  sql-bench
COPYING  docs  lib      my.cnf  README      share    support-files

创建MySQL用户，创建/data/目录：

[root@shuai-01 local]# useradd mysql

[root@shuai-01 local]# mkdir /data/

安装MySQL，指定用户，指定目录：

[root@shuai-01 local]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

这时如果出现错误，如下图：

少了一个模块，模块名字dumper，少什么安装什么。

搜索模块包名：

[root@shuai-01 ~]yum list |grep perl |grep -i dumper
perl-Data-Dumper.x86_64                 2.145-3.el7                    @base    
perl-XML-Dumper.noarch                  0.81-17.el7                    @base    
perl-Data-Dumper-Concise.noarch         2.020-6.el7                    epel     
perl-Data-Dumper-Names.noarch           0.03-17.el7                    epel  

搜出来四个，安装完一个包后再安装MySQL，还出错，安装下一个包，四个都安装完肯定能安装MySQL。其实它是靠perl-Data-Dumper.x86_64这个包。

安装这个包：

[root@shuai-01 ~]# yum install -y perl-Data-Dumper

错误2：./scripts/mysql_install_db –user=mysql –datadir=/data/mysql 
Installing MySQL system tables…./bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

yum -y install libaio-devel

安装完成后就能安装MySQL了。

[root@shuai-01 ~]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql

安装完成后，检验是否安装成功，没有错误。 
安装MySQL后，立马执行echo $? 显示是0，就表示安装成功，没有问题。

[root@shuai-01 ~]# echo $?

其实，echo $? 是检测上一条命令是否执行成功

拷贝配置文件（/support-files/my-large.cnf）：

[root@shuai-01 mysql]# cp support-files/my-large.cnf /etc/my.cnf

mysql的配置文件就叫/etc/my.cnf 就在/etc下。

也可能你的/etc/my.cnf这个文件本来就有了，被其他的包装的时候带过来了。

查看是哪个包安装的/etc/my.cnf

[root@shuai-01 mysql]# rpm -qf /etc/my.cnf
mariadb-libs-5.5.52-1.el7.x86_64

这个也可以使用，不过要改一下文件。

[mysqld]
datadir=/data/mysql
socket=/tmp/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log
#pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
#!includedir /etc/my.cnf.d

复制启动脚本并修改属性：

[root@shuai-01 mysql]# support-files/mysql.server /etc/init.d/mysqld

•  

修改启动脚本： 
basedir=/usr/local/mysql 
datadir=/data/mysql

# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.

basedir=/usr/local/mysql
datadir=/data/mysql

# Default value, in seconds, afterwhich the script should timeout waiting
# for server start.

修改启动脚本文件权限：

[root@shuai-01 mysql]# chmod 755 /etc/init.d/mysqld

设置开机启动：

[root@shuai-01 ~]# chkconfig --add mysqld

也可以手动启动：

[root@shuai-01 ~]# service mysqld start

启动起来了：

[root@shuai-01 mysql]# ps aux |grep mysql
root       1535  0.0  0.1 115392  1688 ?        S    19:07   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/shuai-01.pid
mysql      1863  0.1 45.2 1300784 452380 ?      Sl   19:07   0:08 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/shuai-01.err --pid-file=/data/mysql/shuai-01.pid --socket=/tmp/mysql.sock
root       3473  0.0  0.0 112680   976 pts/0    S+   21:00   0:00 grep --color=auto mysql

监听端口（3306）：

[root@shuai-01 mysql]# netstat -lntp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1516/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      2168/master         
tcp6       0      0 :::3306                 :::*                    LISTEN      1863/mysqld         
tcp6       0      0 :::22                   :::*                    LISTEN      1516/sshd           
tcp6       0      0 ::1:25                  :::*                    LISTEN      2168/master  

如果没有启动脚本，可以用命令行启动：

命令行，指定配置文件路径，指定用户，指定/datadir 加&丢入后台。

[root@shuai-01 mysql]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/data/mysql &
[2] 3723
[root@shuai-01 mysql]# 171214 21:10:07 mysqld_safe Logging to '/data/mysql/shuai-01.err'.
171214 21:10:07 mysqld_safe Starting mysqld daemon with databases from /data/mysql

[root@shuai-01 mysql]# !ps
ps aux |grep mysql
root       3545  0.0  0.1 113264  1612 pts/0    T    21:08   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/data/mysql
mysql      3670 11.1  0.0      0     0 pts/0    Z    21:08   0:20 [mysqld] <defunct>
root       3723  0.0  0.1 113264  1612 pts/0    S    21:10   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --datadir=/data/mysql
mysql      3848  1.6 45.1 1300788 451364 pts/0  Sl   21:10   0:01 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/shuai-01.err --pid-file=/data/mysql/shuai-01.pid --socket=/tmpmysql.sock
root       3872  0.0  0.0 112676   976 pts/0    R+   21:11   0:00 grep --color=auto mysql

关闭时就只能killall：

[root@shuai-01 mysql]# killall mysqld

killall会先停止读写操作，把已有的写完后，就杀死MySQL服务。 
而kill -9 pid 会啥都不管，直接杀掉进程。这样做很可能导致数据的丢失甚至损坏表

MySQL有两个引擎：innodb (小)， myisam(大)

centos 7 mariadb安装

1、安装MariaDB

安装命令

yum -y install mariadb mariadb-server

安装完成MariaDB，首先启动MariaDB

systemctl start mariadb

设置开机启动

systemctl enable mariadb

接下来进行MariaDB的相关简单配置

mysql_secure_installation

首先是设置密码，会提示先输入密码

Enter current password for root (enter for none):<–初次运行直接回车

设置密码

Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码

其他配置

Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车

Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,

Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车

Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车

初始化MariaDB完成，接下来测试登录

mysql -uroot -ppassword

完成。

 

[root@localhost ~]# rpm -qa | grep mysql

[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
[root@localhost ~]# find / -name mariadb-libs-5.5.56-2.el7.x86_64
find: ‘/run/user/1000/gvfs’: 权限不够
[root@localhost ~]# find . -name mariadb-libs-5.5.56-2.el7.x86_64
[root@localhost ~]# 
[root@localhost ~]# yum -y install mariadb mariadb-server
已加载插件：fastestmirror, langpacks
Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=os&infra=stock error was
12: Timeout on http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=os&infra=stock: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds')
base | 3.6 kB 00:00:00 
Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=extras&infra=stock error was
12: Timeout on http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=extras&infra=stock: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds')
extras | 3.4 kB 00:00:00 
Could not retrieve mirrorlist http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=updates&infra=stock error was
12: Timeout on http://mirrorlist.centos.org/?release=7&arch=x86_64&repo=updates&infra=stock: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds')
updates | 3.4 kB 00:00:00 
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
正在解决依赖关系
--> 正在检查事务
---> 软件包 mariadb.x86_64.1.5.5.56-2.el7 将被 安装
---> 软件包 mariadb-server.x86_64.1.5.5.56-2.el7 将被 安装
--> 正在处理依赖关系 perl-DBI，它被软件包 1:mariadb-server-5.5.56-2.el7.x86_64 需要
--> 正在处理依赖关系 perl-DBD-MySQL，它被软件包 1:mariadb-server-5.5.56-2.el7.x86_64 需要
--> 正在处理依赖关系 perl(Data::Dumper)，它被软件包 1:mariadb-server-5.5.56-2.el7.x86_64 需要
--> 正在处理依赖关系 perl(DBI)，它被软件包 1:mariadb-server-5.5.56-2.el7.x86_64 需要
--> 正在检查事务
---> 软件包 perl-DBD-MySQL.x86_64.0.4.023-5.el7 将被 安装
---> 软件包 perl-DBI.x86_64.0.1.627-4.el7 将被 安装
--> 正在处理依赖关系 perl(RPC::PlServer) >= 0.2001，它被软件包 perl-DBI-1.627-4.el7.x86_64 需要
--> 正在处理依赖关系 perl(RPC::PlClient) >= 0.2000，它被软件包 perl-DBI-1.627-4.el7.x86_64 需要
---> 软件包 perl-Data-Dumper.x86_64.0.2.145-3.el7 将被 安装
--> 正在检查事务
---> 软件包 perl-PlRPC.noarch.0.0.2020-14.el7 将被 安装
--> 正在处理依赖关系 perl(Net::Daemon) >= 0.13，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在处理依赖关系 perl(Net::Daemon::Test)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在处理依赖关系 perl(Net::Daemon::Log)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在处理依赖关系 perl(Compress::Zlib)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在检查事务
---> 软件包 perl-IO-Compress.noarch.0.2.061-2.el7 将被 安装
--> 正在处理依赖关系 perl(Compress::Raw::Zlib) >= 2.061，它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要
--> 正在处理依赖关系 perl(Compress::Raw::Bzip2) >= 2.061，它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要
---> 软件包 perl-Net-Daemon.noarch.0.0.48-5.el7 将被 安装
--> 正在检查事务
---> 软件包 perl-Compress-Raw-Bzip2.x86_64.0.2.061-3.el7 将被 安装
---> 软件包 perl-Compress-Raw-Zlib.x86_64.1.2.061-4.el7 将被 安装
--> 解决依赖关系完成

依赖关系解决

========================================================================================
Package 架构 版本 源 大小
========================================================================================
正在安装:
mariadb x86_64 1:5.5.56-2.el7 base 8.7 M
mariadb-server x86_64 1:5.5.56-2.el7 base 11 M
为依赖而安装:
perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k
perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k
perl-DBD-MySQL x86_64 4.023-5.el7 base 140 k
perl-DBI x86_64 1.627-4.el7 base 802 k
perl-Data-Dumper x86_64 2.145-3.el7 base 47 k
perl-IO-Compress noarch 2.061-2.el7 base 260 k
perl-Net-Daemon noarch 0.48-5.el7 base 51 k
perl-PlRPC noarch 0.2020-14.el7 base 36 k

事务概要
========================================================================================
安装 2 软件包 (+8 依赖软件包)

总下载量：21 M
安装大小：110 M
Downloading packages:
警告：/var/cache/yum/x86_64/7/base/packages/mariadb-server-5.5.56-2.el7.x86_64.rpm: 头V3 RSA/SHA256 Signature, 密钥 ID f4a80eb5: NOKEY
mariadb-server-5.5.56-2.el7.x86_64.rpm 的公钥尚未安装
(1/10): mariadb-server-5.5.56-2.el7.x86_64.rpm | 11 MB 00:00:02 
(2/10): mariadb-5.5.56-2.el7.x86_64.rpm | 8.7 MB 00:00:02 
(3/10): perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64.rpm | 32 kB 00:00:00 
(4/10): perl-Compress-Raw-Zlib-2.061-4.el7.x86_64.rpm | 57 kB 00:00:00 
(5/10): perl-DBD-MySQL-4.023-5.el7.x86_64.rpm | 140 kB 00:00:00 
(6/10): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00:00 
(7/10): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00:00 
(8/10): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00:00 
(9/10): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00:00 
(10/10): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:00:00 
----------------------------------------------------------------------------------------
总计 8.1 MB/s | 21 MB 00:02 
从 file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 检索密钥
导入 GPG key 0xF4A80EB5:
用户ID : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
指纹 : 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
软件包 : centos-release-7-4.1708.el7.centos.x86_64 (@anaconda)
来自 : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : perl-Data-Dumper-2.145-3.el7.x86_64 1/10 
正在安装 : 1:mariadb-5.5.56-2.el7.x86_64 2/10 
正在安装 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 3/10 
正在安装 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 4/10 
正在安装 : perl-IO-Compress-2.061-2.el7.noarch 5/10 
正在安装 : perl-Net-Daemon-0.48-5.el7.noarch 6/10 
正在安装 : perl-PlRPC-0.2020-14.el7.noarch 7/10 
正在安装 : perl-DBI-1.627-4.el7.x86_64 8/10 
正在安装 : perl-DBD-MySQL-4.023-5.el7.x86_64 9/10 
正在安装 : 1:mariadb-server-5.5.56-2.el7.x86_64 10/10 
验证中 : perl-DBI-1.627-4.el7.x86_64 1/10 
验证中 : perl-Net-Daemon-0.48-5.el7.noarch 2/10 
验证中 : perl-Data-Dumper-2.145-3.el7.x86_64 3/10 
验证中 : perl-PlRPC-0.2020-14.el7.noarch 4/10 
验证中 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 5/10 
验证中 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 6/10 
验证中 : 1:mariadb-server-5.5.56-2.el7.x86_64 7/10 
验证中 : perl-IO-Compress-2.061-2.el7.noarch 8/10 
验证中 : perl-DBD-MySQL-4.023-5.el7.x86_64 9/10 
验证中 : 1:mariadb-5.5.56-2.el7.x86_64 10/10

已安装:
mariadb.x86_64 1:5.5.56-2.el7 mariadb-server.x86_64 1:5.5.56-2.el7

作为依赖被安装:
perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 
perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 
perl-DBD-MySQL.x86_64 0:4.023-5.el7 
perl-DBI.x86_64 0:1.627-4.el7 
perl-Data-Dumper.x86_64 0:2.145-3.el7 
perl-IO-Compress.noarch 0:2.061-2.el7 
perl-Net-Daemon.noarch 0:0.48-5.el7 
perl-PlRPC.noarch 0:0.2020-14.el7

完毕！
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl enable maiadb
Failed to execute operation: No such file or directory
[root@localhost ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@localhost ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] 
... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] 
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] 
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

2、配置MariaDB的字符集

文件/etc/my.cnf

vi /etc/my.cnf

在[mysqld]标签下添加

init_connect='SET collation_connection = utf8_unicode_ci' 
init_connect='SET NAMES utf8' 
character-set-server=utf8 
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake

文件/etc/my.cnf.d/client.cnf

vi /etc/my.cnf.d/client.cnf

在[client]中添加

default-character-set=utf8

文件/etc/my.cnf.d/mysql-clients.cnf

vi /etc/my.cnf.d/mysql-clients.cnf

在[mysql]中添加

default-character-set=utf8

 全部配置完成，重启mariadb

systemctl restart mariadb

之后进入MariaDB查看字符集

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like "%character%";show variables like "%collation%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)