第10章 MYSQL企业服务器实战

1.1 MYSQL服务器企业实战

MySQL是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。

本章向读者介绍关系型数据库特点、MySQL数据库引擎特点、数据库安装配置、SQL案例操作、数据库索引、慢查询、MySQL数据库集群实战等。

1.2 MYSQL数据库入门简介

MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言。

MySQL数据库主要用于存储各类信息数据,例如:员工姓名、身份证ID、商城订单及金额、销售业绩及报告,学生考试成绩、网站帖子、论坛用户信息、系统报表等等。

MySQL 软件采用了双授权政策,它分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库。由于其社区版的性能卓越,搭配 PHP 和 Apache 可组成良好的开发环境。

关系数据库管理系统(Relational Database Management System,RDBMS),是将数据组织为相关的行和列的系统,而管理关系数据库的计算机软件就是关系数据库管理系统,常用的关系型数据库软件有MYSQL、Mariadb、Oracle、SQL Server、PostgreSQL、DB2等。)

RDBMS数据库的特点如下:

  • 数据以表格的形式出现;
  • 每行记录数据的真实内容;
  • 每列记录数据真实内容的数据域;
  • 无数的行和列组成一张表;
  • 若干的表组成一个数据库;

目前主流架构LAMP(Linux+Apache+MySQL+PHP),MySQL更是得到各位IT运维、DBA的青睐,虽然MySQL数据库已被Orcacle公司收购,不过好消息是原来MySQL创始人已独立出来自己重新开发了MariaDB数据库,开源免费,目前越来越多的人开始尝试使用。MariaDB数据库兼容MySQL数据库所有的功能和相关参数。

MySQL数据库运行在服务器前,需要选择启动的引擎,好比一辆轿车,性能好的发动机会提升轿车的性能,从而启动、运行更加的高效。同样MYSQL也有类似发动机引擎,这里称之为MYSQL引擎。

MYSQL引擎包括:ISAM、MyISAM、InnoDB 、MEMORY、CSV、BLACKHOLE、ARCHIVE、PERFORMANCE_SCHEMA、Berkeley、Merge、Federated、Cluster/NDB等,其中MyISAM、InnoDB使用最为广泛,如下为Myisam BDB Memory InnoDB Archive引擎功能的对比:

引擎特性

Myisam

BDB

Memory

InnoDB

Archive

批量插入的速度

非常高

集群索引

不支持

不支持

不支持

支持

不支持

数据缓存

不支持

不支持

支持

支持

不支持

索引缓存

支持

不支持

支持

支持

不支持

数据可压缩

支持

不支持

不支持

不支持

支持

硬盘空间使用

NULL

非常低

内存使用

中等

外键支持

不支持

不支持

不支持

支持

不支持

存储限制

没有

没有

64TB

没有

事务安全

不支持

支持

不支持

支持

不支持

锁机制

表锁

页锁

表锁

行锁

行锁

B树索引

支持

支持

支持

支持

不支持

哈希索引

不支持

不支持

支持

支持

不支持

全文索引

支持

不支持

不支持

不支持

不支持

性能总结:MyISAM MySQL 5.0 之前的默认数据库引擎,最为常用。拥有较高的插入,查询速度,但不支持事务;

InnoDB事务型数据库的首选引擎,支持ACID事务,ACID包括:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability),一个支持事务(Transaction)的数据库,必需要具有这四种特性,否则在执行事务过程无法保证数据的正确性。

MYSQL5.5之后默认引擎为InnoDB,Innodb支持行级锁定, 支持事物、外键等功能。

BDB源自 Berkeley DB,事务型数据库的另一种选择,支持Commit 和Rollback 等其他事务特性;

Memory所有数据置于内存的存储引擎,拥有极高的插入,更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在 MySQL 重新启动时丢失;

Mysql常用的两大引擎有MyISAM和innoDB,那他们有什么明显的区别呢,什么场合使用什么引擎呢?

MyISAM类型的数据库表强调的是性能,其执行数度比InnoDB类型更快,但不提供事务支持,不支持外键,如果执行大量的SELECT(查询)操作,MyISAM是更好的选择,支持表锁。

InnoDB提供事务支持事务、外部键、行级锁等高级数据库功能,执行大量的INSERT或UPDATE,出于性能方面的考虑,可以考虑使用InnoDB引擎。

1.3 Mysql数据库安装方式

MySQL数据库安装方法有两种,一种是yum/rpm通过YUM源在线安装,另外一种是通过源码软件编译安装。

  1. YUM方式安装MYSQL方法,执行命令:

yum install mysql-server mysql-devel mysql-libs -y CentOS6.x YUM安装

yum install mariadb-server mariadb mariadb-libs -y CentOS7.x YUM安装

  1. 源码安装MYSQL5.5.20方法,通过cmake、make、make install三个步骤实现。

wget http://down1.chinaunix.net/distfiles/mysql-5.5.20.tar.gz

yum -y install gcc-c++ ncurses-devel cmake make perl gcc autoconf automake zlib libxml2 libxml2-devel libgcrypt libtool bison

tar -xzf mysql-5.5.20.tar.gz

cd mysql-5.5.20

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql56/ \

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \

-DMYSQL_DATADIR=/data/mysql/ \

-DSYSCONFDIR=/etc \

-DMYSQL_USER=mysql \

-DMYSQL_TCP_PORT=3306 \

-DWITH_XTRADB_STORAGE_ENGINE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DWITH_READLINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_EXTRA_CHARSETS=1 \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=all \

-DWITH_BIG_TABLES=1 \

-DWITH_DEBUG=0

make

make install

  1. 源码安装MYSQL5.7.20方法,通过cmake、make、make install三个步骤实现。

wget http://nchc.dl.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz

tar zxvf boost_1_59_0.tar.gz

mv boost_1_59_0 /usr/local/boost

yum -y install gcc-c++ ncurses-devel cmake make perl gcc autoconf automake zlib libxml2 libxml2-devel libgcrypt libtool bison

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5/ \

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \

-DMYSQL_DATADIR=/data/mysql/ \

-DSYSCONFDIR=/etc \

-DMYSQL_USER=mysql \

-DMYSQL_TCP_PORT=3306 \

-DWITH_XTRADB_STORAGE_ENGINE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DWITH_READLINE=1 \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_EXTRA_CHARSETS=1 \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=all \

-DWITH_BIG_TABLES=1 \

-DWITH_DEBUG=0 \

-DDOWNLOAD_BOOST=1 \

-DWITH_BOOST=/usr/local/boost

make

make install

/usr/local/mysql5/bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql5 –datadir=/data/mysql

  1. MYSQL源码安装参数详解

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql55 Cmake预编译;-DMYSQL_UNIX_ADDR=/tmp/mysql.sock MYSQL Socket通信文件位置;

-DMYSQL_DATADIR=/data/mysql MYSQL数据存放路径;

-DSYSCONFDIR=/etc 配置文件路径;

-DMYSQL_USER=mysql MYSQL运行用户;

-DMYSQL_TCP_PORT=3306 MYSQL监听端口;

-DWITH_XTRADB_STORAGE_ENGINE=1 开启xtradb引擎支持;

-DWITH_INNOBASE_STORAGE_ENGINE=1 开启innodb引擎支持;

-DWITH_PARTITION_STORAGE_ENGINE=1 开启partition引擎支持;

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 开启blackhole引擎支持;

-DWITH_MYISAM_STORAGE_ENGINE=1 开启MyISAM引擎支持;

-DWITH_READLINE=1 启用快捷键功能;

-DENABLED_LOCAL_INFILE=1 允许从本地导入数据;

-DWITH_EXTRA_CHARSETS=1 支持额外的字符集;

-DDEFAULT_CHARSET=utf8 默认字符集UTF-8;

-DDEFAULT_COLLATION=utf8_general_ci 检验字符;

-DEXTRA_CHARSETS=all 安装所有扩展字符集;

-DWITH_BIG_TABLES=1 将临时表存储在磁盘上;

-DWITH_DEBUG=0 禁止调试模式支持;

make 编译;

make install 安装。

  1. 将源码安装的Mysql数据库服务设置为系统服务,可以使用chkconfig管理,并启动MYSQL数据库,如图11-1所示:

cd /usr/local/mysql55/

\cp support-files/my-large.cnf /etc/my.cnf

\cp support-files/mysql.server /etc/init.d/mysqld

chkconfig –add mysqld 
chkconfig –level 35 mysqld on

mkdir -p /data/mysql

useradd mysql

/usr/local/mysql55/scripts/mysql_install_db –user=mysql –datadir=/data/mysql/ –basedir=/usr/local/mysql55/

ln -s /usr/local/mysql55/bin/* /usr/bin/

service mysqld restart

图11-1 查看MYSQL启动进程

  1. 不设置为系统服务,也可以用源码启动方式。

cd /usr/local/mysql55

mkdir –p /data/mysql

useradd mysql

/usr/local/mysql55/scripts/mysql_install_db –user=mysql –datadir=/data/mysql/ –basedir=/usr/local/mysql55/

ln -s /usr/local/mysql55/bin/* /usr/bin/

/usr/local/mysql55/bin/mysqld_safe –user=mysql &

1.4 Mysql数据库必备命令操作

MySQL数据库安装完毕之后,对MYSQL数据库中各种指令的操作变得尤为重要,熟练掌握MYSQL必备命令是SA、DBA必备工作之一,如下为MYSQL数据库中操作必备命令,所有操作指令均在MYSQL命令行中操作,不能在Linux Shell解释器上直接运行:

直接在Shell终端执行命令:mysql或者/usr/local/mysql55/bin/mysql,按Enter键,进入MYSQL命令行界面如图11-2所示:

图11-2 MYSQL命令行界面

MYSQL命令行常用命令如下,操作结果如图11-3(a)、11-3(b)、11-3(c)所示:

show databases; 查看所有的数据库;

create database jfedu; 创建名为jfedu数据库;

use jfedu; 进入jfedu数据库;

show tables; 查看数据库里有多少张表;

create table t1 (id varchar(20),name varchar(20)); 创建名为t1表,并创建两个字段,id、name,varchar表示设置数据长度,用字符来定义长度单位,其中1汉字=2字符=2Bytes;

insert into t1 values (“1″,”jfedu”); 向表中插入数据;

select * from t1; 查看t1表数据内容;

Select * from t1 where id=1 and age =’jfedu’; id、age多个条件查询;

desc t1; 查看t1表字段内容;

alter table t1 modify column name varchar(20); 修改name字段的长度;

update t1 set name=’jfedu.net’ where id=1; 修改name字段的内容;

flush privileges; 刷新权限;

delete from t1 ; 清空表内容;

drop table t1 ; 删除表;

drop database jfedu; 删除jfedu数据库;

show variables like ‘%char%’; 查看数据库字符集;

show engines; 查看MySQL存储引擎;

show variables like ‘%storage_engine%’; 查看MySQL默认的存储引擎;
alter table t1 engine=innodb;   修改MySQL t1表存储引擎;

图11-3(a) MYSQL命令操作

图11-3(b) MYSQL命令操作

图11-3(c) MYSQL命令操作

1.5 Mysql数据库字符集设置

计算机中储存的信息都是用二进制数方式来表示的,读者每天看到屏幕显示的英文、汉字等字符是二进制数转换之后的结果。通俗的说,将汉字按照某种字符集编码存储在计算机中,称为”编码”。将存储在计算机中的二进制数解析显示出来,称为”解码”,在解码过程中,如果使用了错误的解码规则,会导致显示乱码。

MYSQL数据库在存储数据时,默认编码latin1,存储中文字符时,在显示或者WEB调用时会显示为乱码,为解决该乱码问题,需修改Mysql默认字符集为UTF-8,有两种方法:

  1. 编辑vim /etc/my.cnf配置文件,在相应段中加入相应的参数字符集修改完毕,重启MySQL服务即可。

[client]字段里加入: default-character-set=utf8

[mysqld]字段里加入: character-set-server=utf8

[mysql]字段里加入 : default-character-set=utf8

  1. MYSQL命令行中运行如下指令,如图11-4所示:

show variables like ‘%char%’;

SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

图11-4 设置MYSQL数据库字符集

1.6 Mysql数据库密码管理

MYSQL数据库在使用过程中为了加强安全防范,需要设置密码访问,如何设置密码、及密码忘记如何破解呢,如下为设置密码授权、密码修改及密码破解的方法:

  1. MYSQL创建用户及授权

grant all on jfedu.* to test@localhost identified by ‘pas’;

grant select,insert,update,delete on *.* to test@”%” identified by ‘pas’;

grant all on jfedu.* to test@’192.168.111.118’ identified by ‘pas’;

如上三条grant语句授权参数详解如下:

授权localhost主机通过test用户和pas密码访问本地的jfedu库的所有权限;

授权所有主机通过test用户和pas密码访问本地的jfedu库的查询、插入、更新、删除权限;

授权192.168.111.118主机通过test用户和pas密码访问本地的jfedu库的所有权限;

  1. MYSQL密码破解方法

在使用MYSQL数据库中,偶尔会出现密码忘记,或者被其他人员修改掉数据库权限的,如果需要紧急修改密码,如何破解MYSQL密码呢,首先停止MYSQL数据库服务,以跳过权限方式启动,命令如下:

/etc/init.d/mysqld stop

/usr/bin/mysqld_safe –user=mysql –skip-grant-tables &

MYSQL跳过权限方式启动后,在Shell终端执行mysql命令并按Enter键,进入mysql命令行,如图11-5所示:

图11-5 跳过权限启动并登陆MYSQL

由于MYSQL用户及密码认证信息存放在mysql库中的user表,需进入mysql库,更新相应的密码字段即可,例如将MYSQL中root用户的密码均改为123456,如图11-6所示:

use mysql

update user set password=password(‘123456’) where user=’root’;

图11-6 MYSQL密码破解方法

MYSQL root密码修改完,需停止以Mysql跳过权限表的启动进程,再以正常方式启动MYSQL,再次以新的密码登陆即可进入Mysql数据库,如图11-7所示:

图11-7 MYSQL正常方式启动

1.7 MySQL数据库集群实战

随着访问量的不断增加,单台MySQL数据库服务器压力不断增加,需要对MYSQL进行优化和架构改造,MYQSL优化如果不能明显改善压力情况,可以使用高可用、主从复制、读写分离来、拆分库、拆分表来进行优化。

MYSQL主从复制集群在中小企业、大型企业中被广泛使用,MYSQL主从复制的目的是实现数据库冗余备份,将Master数据库数据定时同步至Slave库中,一旦Master数据库宕机,可以将WEB应用数据库配置快速切换至Slave数据库,确保WEB应用较高的可用率,如图11-12所示,为MYSQL主从复制结构图:

1_conew1

图11-12 MYSQL主从原理架构图

Mysql主从复制集群至少需要2台数据库服务器,其中一台为Master库,另外一台为Slave库,MYSQL主从数据同步是一个异步复制的过程,要实现复制首先需要在master上开启bin-log日志功能,bin-log日志用于记录在Master库中执行的增、删、修改、更新操作的sql语句,整个过程需要开启3个线程,分别是Master开启IO线程,Slave开启IO线程和SQL线程,具体主从同步原理详解如下:

  • Slave上执行slave start,Slave IO线程会通过在Master创建的授权用户连接上至Master,并请求master从指定的文件和位置之后发送bin-log日志内容;
  • Master接收到来自slave IO线程的请求后,master IO线程根据slave发送的指定bin-log日志position点之后的内容,然后返回给slave的IO线程。
  • 返回的信息中除了bin-log日志内容外,还有master最新的binlog文件名以及在binlog中的下一个指定更新position点;
  • Slave IO线程接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的 bin-log的文件名和position点记录到master.info文件中,以便在下一次读取的时候能告知master从响应的bin-log文件名及最后一个position点开始发起请求;
  • Slave Sql线程检测到relay-log中内容有更新,会立刻解析relay-log的内容成在Master真实执行时候的那些可执行的SQL语句,将解析的SQL语句并在Slave里执行,执行成功后,Master库与Slave库保持数据一致。

1.8 MySQL主从复制实战

MYSQL主从复制环境构建至少需2台服务器,可以配置1主多从,多主多从,如下为1主1从,MYSQL主从复制架构实战步骤:

  1. 系统环境准备

Master:192.168.111.128

Slave: 192.168.111.129

  1. Master安装及配置

Master端使用源码安装MySQL-5-5版本软件后,在/etc/my.cnf 配置文件[mysqld]段中加入如下代码,然后重启MYSQL服务即可。如果在安装时cp my-large.cnf /etc/my.cnf,则无需添加如下代码:

server-id = 1

log-bin = mysql-bin

Master端/etc/my.cnf完整配置代码如下:

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 8

log-bin=mysql-bin

binlog_format=mixed

server-id = 1

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

Master数据库服务器命令行中 创建tongbu用户及密码并设置权限,执行如下命令,查看bin-log文件及position点,如图11-13所示:

grant replication slave on *.* to ‘tongbu’@’%’ identified by ‘123456’;

show master status;

图11-13 MYSQL Master授权用户

  1. Slave安装及配置

Slave端使用源码安装MySQL-5-5版本软件后,在/etc/my.cnf 配置文件[mysqld]段中加入如下代码,然后重启MYSQL服务即可。如果在安装时cp my-large.cnf /etc/my.cnf,则需修改server-id,MASTER与Slave端server-id不能一样,Slave端也无需开启bin-log功能:

server-id = 2

Slave端/etc/my.cnf完整配置代码如下:

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 8

server-id = 2

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

Slave指定Master IP、用户名、密码、bin-log文件名(mysql-bin.000028)及position(257),代码如下:

change master to

master_host=’192.168.1.115′,master_user=’tongbu’,master_password=’123456′,master_log_file=’mysql-bin.000001′,master_log_pos=297;

在slave启动slave start,并执行show slave status\G查看Mysql主从状态:

slave start;

show slave status\G

查看Slave端IO线程、SQL线程状态均为YES,代表Slave已正常连接Master实现同步:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

执行Show slave status\G,常见参数含义解析:

Slave_IO_State I/O线程连接Master状态;

Master_User 用于连接Master的用户;

Master_Port Master端监听端口;

Connect_Retry 主从连接失败,重试时间间隔;

Master_Log_File I/O线程读取的Master二进制日志文件的名称。

Read_Master_Log_Pos I/O线程已读取的Master二进制日志文件的位置;

Relay_Log_File SQL线程读取和执行的中继日志文件的名称。

Relay_Log_Pos SQL线程已读取和执行的中继日志文件的位置;

Relay_Master_Log_File SQL线程执行的Master二进制日志文件的名称;

Slave_IO_Running I/O线程是否被启动并成功地连接到主服务器上;

Slave_SQL_Running SQL线程是否被启动;

Replicate_Do_DB 指定的同步的数据库列表;

Skip_Counter SQL_SLAVE_SKIP_COUNTER设置的值;

Seconds_Behind_Master Slave端SQL线程和I/O线程之间的时间差距,单位为秒,常被用于主从延迟检查方法之一。

在Master端创建mysql_db_test数据库和t0表,如图11-14所示,命令如下:

create database mysql_ab_test charset=utf8;

show databases;

use mysql_ab_test;

create table t0 (id varchar(20),name varchar(20));

show tables;

图11-14 MYSQL master创建数据库和表

Slave服务器查看是否有mysql_ab_test数据库和t0的表,如果存在则代表Slave从Master复制数据成功,证明MYSQL主从配置至此已经配置成功,如图11-15所示:

图11-15 MYSQL Slave自动同步数据

在Master服务器的t0表插入两条数据,在slave查看是否已同步,master上执行如下图11-16所示:

insert into t0 values (“001”,”wugk1”);

insert into t0 values (“002”,”wugk2”);

select * from t0;

图11-16 MYSQL Master insert数据

Slave端执行查询命令,如图11-17所示,表示在Master插入的数据已经同步到Slave端:

图11-17 MYSQL Slave数据已同步