LAMP架构应用实战——MySQL服务

原创民工哥民工哥技术之路

LAMP架构应用实战——MySQL服务

MySQL是一种开源的关系型数据库产品，具有开放式的架构，由瑞典MySQL AB公司开发的，后被SUN公司收购，转至最后被Oracle公司收购

MySQL是传统的关系型数据库，它的开放式架构使得用户有更强的选择性，开源代码也是当下大多互联网公司的选择，MySQL在不断的发展中，功能也越来越强大，性能也越来越好，支持的平台也越来越多

一：MySQL版本

MySQL目前有很多的版本，有以下几种

1、Alpha版本：一般只在软件开发公司内部运行，不对外公开

2、Beta版本：完成功能的开发和所有的测试工作之后的产品，不会存在较大的功能或性能BUG

3、RC版本：属于生产环境发布前的一个版本，是测试版本而收集到的BUG或不足之处，然后进行修复和完善

4、GA版本：软件产品正式发布的版本，生产环境中使用的版本

二：MySQL架构图

三：MySQL常见的安装方式

1、常用的编译安装方式（适用于MySQL 5.5前的版本）

2、免安装方式的安装

3、RPM包的安装方式

4、Cmake方式安装

四：MySQL安装

首先下载安装需要用的软件

ftp://ftp.stu.edu.tw/pub/Unix/Database/Mysql/Downloads/

http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51.tar.gz

https://cmake.org/files/v2.8/cmake-2.8.8.tar.gz

选择相应的版本进行下载

这里介绍的是cmake方式安装 mysql-5.5.51

1、下载MySQL软件

[root@Centos tools]# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51.tar.gz

--2016-09-19 11:35:32-- http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51.tar.gz

Resolving mirrors.sohu.com... 221.236.12.140

Connecting to mirrors.sohu.com|221.236.12.140|:80... connected.

HTTP request sent, awaiting response... 302 Found

Location: http://113.215.21.17:80/1Q2W3E4R5T6Y7U8I9O0P1Z2X3C4V5B/mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51.tar.gz [following]

--2016-09-19 11:35:38-- http://113.215.21.17/1Q2W3E4R5T6Y7U8I9O0P1Z2X3C4V5B/mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.51.tar.gz

Connecting to 113.215.21.17:80... connected.

HTTP request sent, awaiting response... 200 OK

Length: 21031206 (20M) [application/octet-stream]

Saving to: “mysql-5.5.51.tar.gz” 100%[==========================================>] 21,031,206 1.68M/s in 11s

2016-09-19 11:35:49 (1.75 MB/s) - “mysql-5.5.51.tar.gz” saved [21031206/21031206]

[root@Centos tools]# ls

apr-1.5.2 apr-util-1.5.4 httpd-2.4.23 mysql-5.5.51.tar.gz pcre-8.38.tar.gz

apr-1.5.2.tar.gz apr-util-1.5.4.tar.gz httpd-2.4.23.tar.gz pcre-8.38

2、下载cmake软件

[root@Centos tools]# wget --no-check-certificate https://cmake.org/files/v2.8/cmake-2.8.8.tar.gz

--2016-09-19 11:39:35-- https://cmake.org/files/v2.8/cmake-2.8.8.tar.gz

Resolving cmake.org... 66.194.253.19

Connecting to cmake.org|66.194.253.19|:443... connected.

WARNING: certificate common name “*.kitware.com” doesn’t match requested host name “cmake.org”.

HTTP request sent, awaiting response... 200 OK

Length: 5691656 (5.4M) [application/x-gzip]

Saving to: “cmake-2.8.8.tar.gz” 100%[=========================================>] 5,691,656 20.6K/s in 3m 40s

2016-09-19 11:43:22 (25.3 KB/s) - “cmake-2.8.8.tar.gz” saved [5691656/5691656]

[root@Centos tools]# ls

apr-1.5.2.tar.gz apr-util-1.5.4.tar.gz httpd-2.4.23 mysql-5.5.51.tar.gz pcre-8.38.tar.gz apr-1.5.2 apr-util-1.5.4 cmake-2.8.8.tar.gz

httpd-2.4.23.tar.gz pcre-8.38

3、解压cmake软件并安装

[root@Centos tools]# tar zxf cmake-2.8.8.tar.gz

[root@Centos tools]# cd cmake-2.8.8

[root@Centos cmake-2.8.8]# ./configure

---------------------------------------------- 中间内容省略

IR QT_QTCORE_LIBRARY)

-- Configuring done

-- Generating done

-- Build files have been written to: /Downloads/tools/cmake-2.8.8

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

CMake has bootstrapped. Now run gmake.

[root@Centos cmake-2.8.8]# gmake

----------------------------------------------- 中间内容省略

[100%] Building CXX object Tests/FindPackageModeMakefileTest/CMakeFiles/foo.dir/foo.cpp.o

Linking CXX static library libfoo.a

[100%] Built target foo

[root@Centos cmake-2.8.8]# gmake install

-------------------------------------------- 中间内容省略

[root@Centos cmake-2.8.8]# echo $?

0 ----------------------------- 查看是否有错误

至此cmake软件安装完成

4、在安装MySQL之前先安装下面的组件

[root@Centos tools]# yum install ncurses-devel -y

Installed:

ncurses-devel.x86_64 0:5.7-4.20090207.el6 Dependency Updated:

ncurses-base.x86_64 0:5.7-4.20090207.el6

ncurses-libs.x86_64 0:5.7-4.20090207.el6

Complete!

5、安装配置MySQL

添加用户与用户组

[root@Centos tools]# groupadd mysql

[root@Centos tools]# useradd mysql -s /sbin/nologin -g mysql -M

解压软件

[root@Centos tools]# tar zxf mysql-5.5.51.tar.gz

[root@Centos tools]# cd mysql-5.5.51

以cmake方式编译安装mysql软件

[root@Centos mysql-5.5.51]#

cmake \

-DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.51 \

-DNYSQL_DATADIR=/application/mysql-5.5.51/data \

-DNYSQL_UNIX_ADDR=/application/mysql-5.5.51/tmp/mysql.sock \

-DDEFAULT_CHARSET=gbk \

-DDEFAULT_COLLATION=gbk_chinese_ci \

-DENABLED_LOCAL_INFILE=ON \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_FEDERATED_STORAGE_ENGINE=1

Manually-specified variables were not used by the project:

NYSQL_DATADIR

NYSQL_UNIX_ADDR

-- Build files have been written to: /Downloads/tools/mysql-5.5.51

[root@Centos mysql-5.5.51]# make

----------------------------------- 中间内容省略

Linking CXX executable my_safe_process

[100%] Built target my_safe_process

-- Installing: /application/mysql-5.5.51/man/man1/mysql_find_rows.1

-- Installing: /application/mysql-5.5.51/man/man1/resolveip.1

-- Installing: /application/mysql-5.5.51/man/man1/mysqlslap.1

-- Installing: /application/mysql-5.5.51/man/man8/mysqld.8

[root@Centos mysql-5.5.51]# echo $?

0 -------------------------- 查看是否有错误

至此MySQL编译安装完成

创建一个软链接

[root@Centos mysql-5.5.51]# ln -s /application/mysql-5.5.51/application/mysql

查看配置文件

[root@Centos mysql-5.5.51]# ll support-files/my*.cnf

-rw-r--r--. 1 root root 4709 Sep 19 13:39 support-files/my-huge.cnf

-rw-r--r--. 1 root root19759 Sep19 13:39 support-files/my-innodb-heavy-4G.cnf

-rw-r--r--. 1 root root 4683 Sep 19 13:39 support-files/my-large.cnf

-rw-r--r--. 1 root root 4694 Sep 19 13:39 support-files/my-medium.cnf

-rw-r--r--. 1 root root 2858 Sep 19 13:39 support-files/my-small.cnf

选择具体的配置文件（生产环境可使用support-files/my-innodb-heavy-4G.cnf）

[root@Centos mysql-5.5.51]# cp support-files/my-small.cnf /etc/my.cnf

cp: overwrite `/etc/my.cnf'? y

配置环境变量

[root@Centos mysql-5.5.51]# echo 'export PATH=/application/mysql/bin:$PATH'>>/etc/profile

[root@Centos mysql-5.5.51]# tail -1 /etc/profile

export PATH=/application/mysql/bin:$PATH

[root@Centos mysql-5.5.51]# source /etc/profile

[root@Centos mysql-5.5.51]# echo $PATH

/application/mysql/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sersync/bin:/root/bin:/usr/local/sersync/bin

初始化配置

[root@Centos mysql-5.5.51]# mkdir -p /application/mysql/data

建立数据目录

[root@Centos mysql-5.5.51]# chown -R mysql.mysql /application/mysql/*

给用户权限可以访问目录

[root@Centos mysql-5.5.51]# chmod -R 1777 /tmp

初始化

[root@Centos mysql-5.5.51]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data --user=mysql

160919 15:19:47 [Note] /application/mysql/bin/mysqld (mysqld 5.5.51) starting as process 26246 ...

Filling help tables...

160919 15:19:48 [Note] /application/mysql/bin/mysqld (mysqld 5.5.51) starting as process 26253 ...

/application/mysql/bin/mysqladmin -u root password 'new-password'

/application/mysql/bin/mysqladmin -u root -h Centos password 'new-password'

-------------------------------------- 中间内容省略

Please report any problems at http://bugs.mysql.com/

如果初始出现错误提示，待解决之后，需删除以下内容

[root@Centos mysql-5.5.51]# rm -rf /application/mysql/data/*

启动服务

[root@Centos mysql-5.5.51]# cp support-files/mysql.server /etc/init.d/mysqld

将启动命令放到/etc/init.d/mysqld

[root@Centos mysql-5.5.51]# chmod 700 /etc/init.d/mysqld 给定权限

[root@Centos mysql-5.5.51]# /etc/init.d/mysqld start 启动服务

Starting MySQL.... [ OK ]

[root@Centos mysql-5.5.51]# chkconfig mysqld on

加入开机自启

[root@Centos mysql-5.5.51]# chkconfig --list mysqld

查看启动情况

mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off

五：MySQL登陆

[root@Centos mysql-5.5.51]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.5.51 Source distribution

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>