第二十九节 MySQL数据库企业级应用实践
一，概述
1.1 MySQL介绍
MySQL属于传统关系型数据库产品，它开放式的架构使得用户选择性很强，同时社区开发与维护人数众多。其功能稳定，性能卓越，且在遵守GPL协议的前提下，可以免费使用与修改，也为MySQL的推广与使用带来了更多的利好。在MySQL成长与发展过程中，支持的功能逐渐增多，性能也不断提高，对平台的支持也越来越多。
MySQL是一种关系型数据库管理系统，关系型数据库的特点是将数据保存在不同的表中，再将这些表放入不同的数据库中，而不是将所有数据统一放在一个大仓库里，这样的设计增加了MySQL的读取速度，而且灵活性和可管理性也得到了很大提高。访问及管理MySQL数据库的最常用标准化语言为SQL结构化查询语言。
1.2 MariaDB 数据库的诞生背景介绍
自甲骨文公司收购MySQL后，其在商业数据库与开源数据库领域市场的占有份额都跃居第一，这样的格局引起了业内很多的人士的担忧，因为商业数据库的老大有可能将MySQL闭源。为了避免Oracle将MySQL闭源，而无开源的类MySQL数据库可用，MySQL社区采用分支的方式来避开这个风险。MariaDB数据库就这样诞生了，MariaDB是一个向后兼容，可能在以后替代MySQL的数据库产品，其官方地址为：https://mariadb.org/ 。不过，这里还是建议大家选择更稳定，使用更广泛的MySQL数据库，可以先测试MariaDB数据库，等使用的人员更多一些，社区更活跃后再考虑使用为好。
二，MySQL多实例介绍
在之前LNMP的讲解中，已经针对MySQL数据库进行了介绍，并说明了为什么要选择MySQL数据库，以及MySQL数据库在Linux系统下的多种安装方式，同时讲解了MySQL的二进制方式单实例安装，基础优化等内容，本节将为同学们讲解更为实用的MySQL多实例安装，主从复制集群等重要应用实践。
2.1 什么是MySQL多实例
- 简单的说，MySQL多实例就是在一台服务器上同时开启多个不同的服务器端口（如：3306，3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务器端口来提供服务。
- 这些MySQL多实例共用一套MySQL安装程序，使用不同的my.cnf（也可以相同）配置文件，启动程序（也可以相同）和数据文件。在提供服务时，多实例MySQL在逻辑上看起来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。
- 打个比方吧，MySQL多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（CPU，Mem，Disk），软件资源（Centos操作系统）可以看作房子的卫生间，厨房，客厅，是房子的公用资源。
- 其实很多网络服务都是可以配置多实例的，例如Nginx，Apache，Haproxy，Redis，Memcache等。这在门户网站使用得很广泛。
2.2 MySQL多实例的作用与问题
MySQL多实例的作用如下：
（1）有效利用服务器资源
当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。
（2）节约服务器资源
当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术时，多实例就再好不过了。
MySQL多实例有它的好处，但也有其弊端，比如，会存在资源互相抢占的问题。
当某个数据库实例并发很高或有SQL慢查询时，整个实例会消耗大量的系统CPU，磁盘I/O等资源，导致服务器上的其他数据库实例提供服务的质量一起下降。这就相当于大家住在一个房子的不同卧室一样，早晨起来上班，都要刷牙，洗脸等，这样卫生间就会长期占用，其他人要等待一样。不同实例获取的资源是相对独立的，无法像虚拟化一样完全隔离。
三， MySQL多实例的生产应用场景
3.1 资金紧张型公司的选择
若公司资金紧张，公司业务访问量不太大，但又希望不同业务的数据库服务各自尽量独立地提供服务而互相不受影响，同时，还需要主从复制等技术提供备份或读写分离服务，那么多实例就再好不过了。例如：可以通过3台服务器部署9～15个实例，交叉做主从复制，数据备份及读写分离，这样就可达到9～15台服务器每个只装一个数据库才有的效果。这里要强调的是，所谓的尽量独立是相对的。
3.2 并发访问不是特别大的业务
当公司业务访问量不太大的时候，服务器的资源基本上都浪费了，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MySQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源，搭配好服务，也不会有太大问题。
3.3 门户网站应用MySQL多实例场景
门户网站通常都会使用多实例，因为配置硬件好的服务器，可节省IDC机柜空间，同时，跑多实例也会减少硬件资源跑不满的浪费。比如，百度公司的很多数据库都是多实例，不过，一般是从库多实例，例如某部门中使用的IBM服务器为48核CPU，内存96GB，一台服务器跑3～4个实例；此外，新浪网使用的也是多实例，内存48GB左右。
说明：
据调查，新浪网的数据库单机1～4个数据库实例的居多，其中又数1～2个的最多，因为大业务占用的机器比较多。服务器是DELL R510的居多，CPU是E5210，48GB内存，磁盘12×300G SAS，做RAID10，此为门户网站的服务器配置参考，希望能给同学们的面试带来一些启迪。
另外，新浪网站安装数据库时，一般采用编译安装的方式，并且会在优化之后做成rpm包，以便统一使用。
四， MySQL多实例常见的配置方案
4.1 单一配置文件，单一启动程序的多实例部署方案
下面是MySQL官方文档提到的单一配置文件，单一启动程序多实例部署方案，但不推荐此方案，这里仅作为知识点提及，后文不再涉及此方案的说明。my.cnf配置文件示例（MySQL手册里提到的方法）如下：
[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = mysql
[mysqld1]
socket = /var/lib/mysql/mysql.sock
port = 3306
pid-file = /var/lib/mysql/mysql.pid
datadir = /var/lib/mysql/
user = mysql
[mysqld2]
socket = /mnt/data/db1/mysql.sock
port = 3302
pid-file = /mnt/data/db1/mysql.pid
datadir = /mnt/data/db1/
user = mysql
skip-name-resolv
server-id=10
default-storage-engine=innodb
innodb_buffer_pool_size=512M
innodb_additional_mem_pool=10M
default_character_set=utf8
character_set_server=utf8
#read-only
relay-log-space-limit=3G
expire_logs_day=20
启动程序的命令如下：
mysqld_multi --config-file=/data/mysql/my_multi.cnf start 1,2
该方案的缺点是耦合度太高，一个配置文件，不好管理。工作开发和运维的统一原则为降低耦合度。
4.2 多配置文件，多启动程序的部署方案
多配置文件，多启动程序部署方案，是本文主要讲解的方案，也是非常常用并极力推荐的多实例方案。下面来看配置示例。
[root@localhost /]# tree /data
/data
├── 3306
│ ├── data #3306实例的数据目录
│ ├── my.cnf #3306实例的配置文件
│ └── mysql #3306实例的启动文件
└── 3307
├── data #3307实例的数据目录
├── my.cnf #3307实例的配置文件
└── mysql #3307实例的启动文件
4 directories, 4 files
提示：
这里的配置文件my.cnf,启动程序mysql都是独立的文件，数据文件data目录也是独立的。
多实例MySQL数据库的安装和之前讲解的单实例没有任何区别，因此，同学们如果有前文单实例的安装环境，那么可以直接略过5.1节的内容。
五，安装并配置多实例MySQL数据库
5.1 安装MySQL多实例
1，安装MySQL需要的依赖包和编译软件
（1）安装MySQL需要的依赖包
安装MySQL之前，最好先安装MySQL需要的依赖包，不然后面会出现很多报错信息，到那时还得再回来安装MySQL的依赖包。安装命令如下：
[root@localhost ~]# yum -y install ncurses-devel libaio-devel
[root@localhost ~]# rpm -qa ncurses-devel libaio-devel
ncurses-devel-5.7-4.20090207.el6.x86_64
libaio-devel-0.3.107-10.el6.x86_64
（2）安装编译MySQL需要的软件
首先通过网络获得cmake软件，然后进行如下操作：
[root@localhost ~]# ls -lh cmake-2.8.6.tar.gz
-rw-r--r-- 1 root root 5.4M 7月 19 20:43 cmake-2.8.6.tar.gz #此软件需提前准备
[root@localhost ~]# tar xf cmake-2.8.6.tar.gz -C /usr/src/
[root@localhost ~]# cd /usr/src/cmake-2.8.6/
[root@localhost cmake-2.8.6]# ./configure
[root@localhost cmake-2.8.6]# gmake && gmake install
[root@localhost cmake-2.8.6]# which cmake
2，开始安装MySQL
为了让同学们学习更多的MySQL技术，接下来会以相对复杂的源代码安装来讲解MySQL多实例的安装。大型公司一般都会将MySQL软件定制成rpm包，然后放到yum仓库里，使用yum安装，中小企业里的二进制和编译安装的区别不大。
（1）建立MySQL用户账号
首先以root身份登录到Linux系统中，然后执行如下命令创建mysql用户账号：
[root@localhost ~]# useradd -s /sbin/nologin -M mysql
[root@localhost ~]# id mysql
uid=500(mysql) gid=500(mysql) 组=500(mysql)
（2）获取MySQL软件包
MySQL软件包的下载地址为：https://dev.mysql.com/downloads/mysql/
提示：
本例以MySQL编译的方式来讲解，之前已经演示过二进制方式安装了。在生产场景中，二进制和源码包两种安装方法都是可以用的，其应用场景一般没什么差别。不同之处在于，二进制的安装包较大，名字和源码包也有些区别，二进制安装过程比源码更快。
MySQL源码包和二进制安装包的名称见下图
（3）采用编译方式安装MySQL
配置及编译安装的步骤如下：
[root@localhost ~]# tar xf mysql-5.5.22.tar.gz -C /usr/src/
[root@localhost ~]# cd /usr/src/mysql-5.5.22/
[root@localhost mysql-5.5.22]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5.22 > -DMYSQL_DATADIR=/usr/local/mysql-5.5.22/data \ #数据存放目录
> -DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5.22/tmp/mysql.sock \ #MySQL进程间通信的套接字位置
> -DDEFAULT_CHARSET=utf8 \ #默认字符集为utf8
> -DDEFAULT_COLLATION=utf8_general_ci \ #默认字符集排序规则
> -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \ #额外的字符集支持
> -DENABLED_LOCAL_INFILE=ON \ #是否启用加载本地数据
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \ #静态编译innodb存储引擎到数据库
> -DWITH_FEDERATED_STORAGE_ENGINE=1 \ #静态编译FEDERATED存储引擎到数据库
> -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #静态编译blackhole存储引擎到数据库
> -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \ #不编译EXAMPLE存储引擎到数据库
> -DWITHOUT_PARTITION_STORAGE_ENGINE=1 \ #不支持数据库分区
> -DWITH_FAST_MUTEXES=1 > -DWITH_ZLIB=bundled \ #zlib压缩模式
> -DENABLED_LOCAL_INFILE=1 \ #是否启用本地的LOCAL_INFILE
> -DWITH_READLINE=1 \ #使用捆绑的readline
> -DWITH_EMBEDDED_SERVER=1 \ #是否要建立嵌入式服务器
> -DWITH_DEBUG=0 #禁用DEBUG（开启影响性能）
# 提示：编译时可配置的选项很多，具体可参考官方文档
[root@localhost mysql-5.5.22]# make && make install
下面设置不带版本号的软链接/usr/local/mysql,操作步骤如下：
[root@localhost mysql-5.5.22]# ln -s /usr/local/mysql-5.5.22 /usr/local/mysql
[root@localhost mysql-5.5.22]# ls /usr/local/mysql
bin data include lib mysql-test scripts sql-bench
COPYING docs INSTALL-BINARY man README share support-files
如果上述操作未出现错误，查看/usr/local/mysql目录下有内容，则MySQL5.5.22源代码包采用cmake方式的安装就算成功了。
5.2 创建MySQL多实例的数据文件目录
在企业中，通常以/data目录作为MySQL多实例总的根目录，然后规划不同的数字（即MySQL实例端口号）作为/data下面的二级目录，不同的二级目录对应的数字就作为MySQL实例的端口号，以区别不同的实例，数字对应的二级目录下包含MySQL的数据文件，配置文件及启动文件等。
下面以配置3306,3307两个实例为例进行讲解。创建MySQL多实例的目录如下：
[root@localhost ~]# mkdir -p /data/{3306,3307}/data
[root@localhost ~]# tree /data/
/data/
├── 3306 #3306实例目录
│ └── data #3306实例的数据文件目录
├── 3307 #3307实例目录
└── data #3307实例的数据文件目录
4 directories, 0 files
提示：
（1）mkdir -p /data/{3306,3307}/data相当于mkdir -p /data/3306/data;mkdir -p /data/3307/data两条命令
（2）如果是创建多个目录，可以增加如3308,3309这样的目录名，在生产环境中，一般为3~4个实例为佳。
5.3 创建MySQL多实例的配置文件
MySQL数据库默认为用户提供了多个配置文件模板，用户可以根据服务器硬件配置的大小来选择。
[root@localhost mysql]# ls -l support-files/my*.cnf
-rw-r--r-- 1 root root 4751 7月 19 21:33 support-files/my-huge.cnf
-rw-r--r-- 1 root root 19805 7月 19 21:33 support-files/my-innodb-heavy-4G.cnf
-rw-r--r-- 1 root root 4725 7月 19 21:33 support-files/my-large.cnf
-rw-r--r-- 1 root root 4736 7月 19 21:33 support-files/my-medium.cnf
-rw-r--r-- 1 root root 2900 7月 19 21:33 support-files/my-small.cnf
注意：
这些配置文件里的注释非常详细，不过是英文的。。。
上面是单实例的默认配置文件模板，如果配置多实例，和单实例会有不同。为了让MySQL多实例之间彼此独立，要为每一个实例建立一个my.cnf配置文件和一个启动文件MySQL，让他们分别对应自己的数据文件目录data。
首先，通过vim命令添加配置文件内容，命令如下：
vim /data/3306/my.cnf
vim /data/3307/my.cnf
不同的实例需要添加的my.cnf内容会有区别,其中的配置由官方的配置模板修改而来。当然，在实际工作中，我们是拿早已配置好的模板来进行修改的，可以通过rz等方式上传配置文件模板my.cnf文件到相关目录下。
MySQL3306,3307实例配置文件如下
##实例3306配置文件my.cnf
[root@localhost ~]# cat /data/3306/my.cnf
[client]
port = 3306
socket = /data/3306/mysql.sock
[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit = 1024
back_log = 600
max_connections = 800
max_connect_errors = 3000
table_open_cache = 614
external-locking = FALSE
max_allowed_packet = 8M
#binlog_cache_size = 1M
#max_heap_table_size = 64M
#read_buffer_size = 2M
#read_rnd_buffer_size = 16M
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
#ft_min_word_len = 4
#default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 2M
max_heap_table_size = 2M
#log-bin=mysql-bin
#binlog_format=mixed
#slow_query_log
long_query_time = 1
pid-file = /data/3306/mysql.pid
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info
binlog_cache_size = 1M
max_binlog_cache_size = 1M
max_binlog_size = 2M
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
lower_case_table_names = 1
skip-name-resolve
slave-skip-errors = 1032,1062
replicate-ignore-db = mysql
server-id = 1
#key_buffer_size = 32M
#bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
#myisam_recover
innodb_additional_mem_pool_size = 4M
innodb_buffer_pool_size = 32M
innodb_data_file_path = ibdata1:128M:autoextend
innodb_file_io_threads = 4
#innodb_write_io_threads = 8
#innodb_read_io_threads = 8
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 4M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 0
[mysqldump]
quick
max_allowed_packet = 2M
[mysql]
no-auto-rehash
#[myisamchk]
#key_buffer_size = 512M
#sort_buffer_size = 512M
#read_buffer = 8M
#write_buffer = 8M
#[mysqlhotcopy]
#interactive-timeout
[mysqld_safe]
log-error = /data/3306/mysql_yunjisuan3306.err
pid-file = /data/3306/mysqld.pid
提示：实例3307的配置文件只需要将3306配置文件里的所有3306数字替换成3307(server-id换个数字)即可。
最终完成后的多实例根/data目录结果如下：
[root@localhost ~]# tree /data
/data
├── 3306
│ ├── data
│ └── my.cnf #这个就是3306实例的配置文件
└── 3307
├── data
└── my.cnf #这个就是3307实例的配置文件
4 directories, 2 files
5.4 创建MySQL多实例的启动文件
MySQL多实例启动文件的创建和配置文件的创建几乎一样，也可以通过vim命令来添加，如下：
vim /data/3306/mysql
vim /data/3307/mysql
需要添加的MySQL启动文件内容如下。（当然，在实际工作中我们是拿早已配置好的模板来进行修改的，可以通过rz等方式上传配置文件模板MySQL文件到相关目录下）
[root@localhost ~]# cat /data/3306/mysql
#!/bin/bash
###############################################
#this scripts is created by Mr.chen at 2016-06-25
port=3306
mysql_user="root"
mysql_pwd="" #这里需要修改为用户的实际密码
CmdPath="/usr/local/mysql/bin"
mysql_sock="/data/${port}/mysql.sock"
#startup function
function_start_mysql(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL....\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf 2>&1 >/dev/null &
else
printf "MySQL is running...\n"
exit
fi
}
#stop function
function_stop_mysql(){
if [ ! -e "$mysql_sock" ];then
printf "MySQL is stopped...\n"
exit
else
printf "Stoping MySQL...\n"
${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown
fi
}
#restart function
function_restart_mysql(){
printf "Restarting MySQL...\n"
function_stop_mysql
sleep 2
function_start_mysql
}
case $1 in
start)
function_start_mysql
;;
stop)
function_stop_mysql
;;
restart)
function_restart_mysql
;;
*)
printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
esac
3307实例的启动文件只需修改3306启动文件的端口即可
最终完成后的多实例根/data目录结果如下：
[root@localhost ~]# tree /data
/data
├── 3306
│ ├── data
│ ├── my.cnf #3306实例的配置文件
│ └── mysql #3306实例的启动文件
└── 3307
├── data
├── my.cnf #3307实例的配置文件
└── mysql #3307实例的启动文件
4 directories, 4 files
需要特别说明一下，在多实例启动文件中，启动MySQL不同实例服务，所执行的命令实质是有区别的，例如，启动3306实例的命令如下：
mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
启动3307实例的命令如下：mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &
下面看看在多实例启动文件中，停止MySQL不同实例服务的实质命令。
停止3306实例的命令如下：mysqladmin -uroot -pyunjisuan123 -S /data/3306/mysql.sock shutdown
停止3307实例的命令如下：mysqladmin -u root -pyunjisuan123 -S /data/3307/mysql.sock shutdown
5.5 配置MySQL多实例的文件权限
1）通过下面的命令，授权mysql用户和组管理整个多实例的根目录/data
[root@localhost ~]# chown -R mysql.mysql /data
[root@localhost ~]# find /data -name "mysql" | xargs ls -l
-rw-r--r--. 1 mysql mysql 1039 Jul 20 19:33 /data/3306/mysql
-rw-r--r--. 1 mysql mysql 1039 Jul 20 19:34 /data/3307/mysql
2）通过下面的命令，授权MySQL多实例所有启动文件的mysql可执行，设置700权限最佳，注意不要用755权限，因为启动文件里有数据库管理员密码，会被读取到。
[root@localhost ~]# find /data -name "mysql" | xargs chmod 700
[root@localhost ~]# find /data -name "mysql" | xargs ls -l
-rwx------. 1 mysql mysql 1039 Jul 20 19:33 /data/3306/mysql
-rwx------. 1 mysql mysql 1039 Jul 20 19:34 /data/3307/mysql
5.6 MySQL相关命令加入全局路径的配置
（1）配置全局路径的意义
如果不为MySQL的命令配置全局路径，就无法直接在命令行输入mysql这样的命令，只能用全路径命令（/usr/local/mysql/bin/mysql）,这种带着路径输入命令的方式很麻烦。
（2）配置MySQL全局路径的方法
1）确认mysql命令所在路径，命令如下：
[root@localhost ~]# ls /usr/local/mysql/bin/mysql
/usr/local/mysql/bin/mysql
2）在PATH变量前面增加/usr/local/mysql/bin路径，并追加到/etc/profile文件中，命令如下：
[root@localhost ~]# echo ‘export PATH=/usr/local/mysql/bin:$PATH‘ >> /etc/profile
#注意，echo后边是单引号，双引号的话变量内容会被解析掉。
[root@localhost ~]# tail -1 /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
[root@localhost ~]# source /etc/profile
#执行source使上一行添加到/etc/profile中，内容直接生效
#以上命令的用途为定义mysql全局路径，实现在任意路径执行mysql命令
提示：
更简单的设置方法为用下面命令做软链接：ln -s /usr/local/mysql/bin/* /usr/local/sbin/,把mysql命令说在路径链接到全局路径/usr/local/sbin/的下面。
5.7 初始化MySQL多实例的数据库文件
上述步骤全都配置完毕后，就可以初始化数据库文件了，这个步骤其实也可以在编译安装MySQL之后就操作，只不过放到这里更合理一些。
（1）初始化MySQL数据库
初始化命令如下：
[root@localhost scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql
[root@localhost scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql
提示：
--basedir=/usr/local/mysql为MySQL的安装路径，--datadir为不同的实例数据目录
（2）初始化数据库的原理及结果说明
初始化数据库的实质就是创建基础的数据库系统的库文件，例如：生成MySQL库表等。
初始化数据库后查看对应实例的数据目录，可以看到多了如下文件：
[root@localhost scripts]# tree /data
#以下省略若干...
5.8 启动MySQL多实例的命令
[root@localhost scripts]# /data/3306/mysql start
Starting MySQL....
[root@localhost scripts]# /data/3307/mysql start
Starting MySQL....
root@localhost scripts]# netstat -antup | grep 330
tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 24743/mysqld
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 24020/mysqld
从输出中可以看到，3306和3307实例均已正常启动。
六，配置及管理MySQL多实例数据库
6.1 配置MySQL多实例数据库开机自启动
服务的开机自启动很关键，MySQL多实例的启动也不例外，把MySQL多实例的启动命令加入/etc/rc.local,实现开机自启动，命令如下：
[root@localhost ~]# echo "#mysql multi instances" >> /etc/rc.local
[root@localhost ~]# echo "/data/3306/mysql start" >> /etc/rc.local
[root@localhost ~]# echo "/data/3307/mysql start" >> /etc/rc.local
[root@localhost ~]# tail -3 /etc/rc.local
#mysql multi instances
/data/3306/mysql start
/data/3307/mysql start
#这里一定要确保MySQL脚本可执行~
6.2 登陆MySQL测试
[root@localhost ~]# mysql -S /data/3306/mysql.sock #直接敲就进来了，而且身份还是root。但是多了-S /data/3306/mysql.sock,用于区别登陆不同的实例
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22 Source distribution
Copyright (c) 2000, 2011, 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> show databases; #查看当前所有的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> select user(); #查看当前的登陆用户
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
6.3 MySQL多实例数据库的管理方法
-
MySQL安装完成后，默认情况下，MySQl管理员的账号root是无密码的。登陆不同的实例需要指定不同实例的mysql.sock文件路径，这个mysql.sock是在my.cnf配置文件里指定的。
-
下面是无密码情况下登陆数据库的方法，关键点是-S参数及后面指定的/data/33306/mysql.sock,注意，不同实例的sock虽然名字相同，但是路径是不同的，因此是不同的文件。
mysql -S /data/3306/mysql.sock
mysql -S /sata/3307/mysql.sock
下面是重启对应实例数据库的命令
/data/3306/mysql stop
/data/3307/mysql start
6.4 MySQL安全配置
MySQL管理员的账号root密码默认为空，极不安全，可以通过mysqladmin命令为MySQL不同实例的数据库设置独立的密码，命令如下：
[root@localhost ~]# mysqladmin -u root -S /data/3306/mysql.sock password ‘123123‘ #为mysql设置密码
[root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock #无法直接登陆了
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.22 Source distribution
Copyright (c) 2000, 2011, 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>
#提示：3307实例设置方法和3306实例相同，只是连接时的mysql，sock路径不同，这已经提醒多次，大家要注意
带密码登陆不同实例数据库的方法：
#登陆3306实例的命令如下：
mysql -uroot -p123123 -S /data/3306/mysql.sock
#登陆3307实例的命令如下：
mysql -uroot -p123123 -S /data/3307/mysql.sock
提示：
基础弱的同学，在测试时尽量保证多实例的密码相同，可以减少麻烦，后面还原数据库时会覆盖密码！
若要重启多实例数据库，也需要进行相应的如下配置。再重启数据库前，需要调整不同实例启动文件里对应的数据库密码。
[root@localhost ~]# vim /data/3306/mysql
[root@localhost ~]# sed -n ‘7p‘ /data/3306/mysql #这是之前mysql多实例启动脚本里mysql的登陆密码变量
mysql_pwd=""
[root@localhost ~]# sed -i ‘7 s#""#"123123"#‘ /data/3306/mysql
[root@localhost ~]# sed -n ‘7p‘ /data/3306/mysql
mysql_pwd="123123" #修改成实际的登录密码
[root@localhost ~]# sed -n ‘7p‘ /data/3307/mysql
mysql_pwd=""
[root@localhost ~]# sed -i ‘7 s#""#"123123"#‘ /data/3307/mysql
[root@localhost ~]# sed -n ‘7p‘ /data/3307/mysql
mysql_pwd="123123"
多实例下正常停止数据库的命令如下：
/data/3306/mysql stop
由于选择了mysqladmin shutdown的停止方式，所以停止数据库时需要在启动文件里配置数据库的密码
/data/3306/mysql start
重点提示：
禁止使用pkill，kill -9，killall -9等命令强制杀死数据库，这会引起数据库无法启动等故障的发生。
6.5 如何再增加一个MySQL的实例
若再3306和3307实例的基础上，再增加一个MySQL实例，该怎么办？下面给出增加一个MySQL3308端口实例的命令集合：
mkdir -p /data/3308/data
\cp /data/3306/my.cnf /data/3308/
\cp /data/3306/mysql /data/3308/
sed -i ‘s#3306#3308#g‘ /data/3308/my.cnf
sed -i ‘s#server-id = 1#server-id = 8#g‘ /data/3308/my.cnf
sed -i ‘s#3306#3308#g‘ /data/3308/mysql
chown -R mysql:mysql /data/3308
chmod 700 /data/3308/mysql
cd /usr/local/mysql/scripts
./mysql_install_db --datadir=/data/3308/data --basedir=/usr/local/mysql --user=mysql
chown -R mysql:mysql /data/3308
egrep "server-id|log-bin" /data/3308/my.cnf
/data/3308/mysql start
netstat -antup | grep 3308
#提示：最好把server-id按照IP地址最后一个小数点的数字设置
#成功标志：多了一个启动的端口3308
如果配置以后，服务启动后却没有运行起来，别忘了一定要看MySQL错误日志，在/data/3308/my.cnf最下面有错误日志路径地址。
6.6 多实例MySQL登陆问题分析
（1）多实例本地登录MySQL
多实例本地登录一般通过socket文件来指定具体登陆到哪个实例，此文件的具体位置是在MySQL编译过程或my.cnf文件中指定的。在本地登陆数据库时，登陆程序会通过socket文件来判断登陆的是哪个数据库实例。
例如：通过mysql -uroot -p ‘123123‘ -S /data/3307/mysql.sock可知，登陆的是3307这个实例。
mysql.sock文件是MySQL服务器端与本地MySQL客户端进行通信的UNIX套接字文件。
（2）远程连接登陆MySQL多实例
远程登陆MySQL多实例中的一个实例时，通过TCP端口（port）来指定说要登陆的MySQL实例，此端口的配置是在MySQL配置文件my.cnf中指定的。
例如：在mysql -uyunjisuan -p ‘123123‘ -h 192.168.200.101 -P 3307中，-P为端口参数，后面接具体的实例端口，端口是一种“逻辑连接位置”，是客户端程序被分派到计算机上特殊服务程序的一种方式，强调提前在192.168.200.101上对yunjisuan用户做了授权。
七， MySQL主从复制介绍
MySQL数据库的主从复制方案，与使用scp/rsync等命令进行的文件级别复制类似，都是数据的远程传输，只不过MySQL的主从复制是其自带的功能，无需借助第三方工具，而且，MySQL的主从复制并不是数据库磁盘上的文件直接拷贝，而是通过逻辑的binlog日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的SQL语句，重新应用到MySQL数据库中。
7.1 概述
- MySQL数据库支持单向，双向，链式级联，环状等不同业务场景的复制。在复制过程中，一台服务器充当主服务器（Master），接收来自用户的内容更新，而一个或多个其他的服务器充当从服务器（Slave），接收来自主服务器binlog文件的日志内容，解析出SQL，重新更新到从服务器，使得主从服务器数据达到一致。
- 如果设置了链式级联复制，那么，从服务器（Slave）本身除了充当从服务器外，也会同时充当其下面从服务器的主服务器。链式级联复制类似A-->B-->C的复制形式。
下图为单向主从复制架构逻辑图，此架构只能在Master端进行数据写入
下图为双向主主复制逻辑架构图，此架构可以在Master1端或Master2端进行数据写入，或者两端同时写入数据（需要特殊设置）
下图为线性级联单向双主复制逻辑架构图，此架构只能在Master1端进行数据写入，工作场景中，Master1和master2作为主主互备，Slave1作为从库，中间的Master2需要做特殊的设置。
下图为环状级联单向多主同步逻辑架构图，任意一个点都可以写入数据，此架构比较复杂，属于极端环境下的“成品”，一般场景慎用
在当前的生产工作中，MySQL主从复制都是异步的复制方式，既不是严格实时的数据同步，但是正常情况下给用户的体验是真实的。
7.2 MySQL主从复制的企业应用场景
MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写成为可能，同时有效地保护了物理服务器宕机场景的数据备份。
应用场景1：从服务器作为主服务器的实时数据备份
- 主从服务器架构的设置可以大大加强MySQL数据库架构的健壮性。例如：当主服务器出现问题时，我们可以人工或设置自动切换到从服务器继续提供服务，此时从服务器的数据与宕机时的主数据库几乎是一致的。
- 这类似NFS存储数据通过inotify + rsync同步到备份的NFS服务器，只不过MySQL的复制方案是其自带的工具。
- 利用MySQL的复制功能进行数据备份时，在硬件故障，软件故障的场景下，该数据备份是有效的，但对于人为地执行drop，delete等语句删除数据的情况，从库的备份功能就没用了，因为从服务器也会执行删除的语句。
应用场景2：主从服务器实现读写分离，从服务器实现负载均衡
- 主从服务器架构可通过程序（PHP，java等）或代理软件（mysql-proxy，Amoeba）实现对用户（客户端）的请求读写分离，即让从服务器仅仅处理用户的select查询请求，降低用户查询响应时间，以及同时读写在主服务器上带来的访问压力。对于更新的数据（例如：update，insert，delete语句），则仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。
- 百度，淘宝，新浪等绝大多数的网站都是用户浏览页面多于用户发布内容，因此通过在从服务器上接收只读请求，就可以很好地减轻主库的读压力，且从服务器可以很容易地扩展为多台，使用LVS做负载均衡效果就非常棒了，这就是传说中的数据库读写分离架构。逻辑架构图如下所示：
应用场景3：把多个从服务器根据业务重要性进行拆分访问
可以把几个不同的从服务器，根据公司的业务进行拆分。例如：有为外部用户提供查询服务的从服务器，有内部DBA用来数据备份的从服务器，还有为公司内部人员提供访问的后台，脚本，日志分析及供开发人员查询使用的从服务器。这样的拆分除了减轻主服务器的压力外，还可以使数据库对外部用户浏览，内部用户业务处理及DBA人员的备份等互不影响。
7.3 实现MySQL主从读写分离的方案
（1）通过程序实现读写分离（性能和效率最佳，推荐）
PHP和Java程序都可以通过设置多个连接文件轻松地实现对数据库的读写分离，即当语句关键字为select时，就去连接读库的连接文件，若为update，insert，delete时，则连接写库的连接文件。
通过程序实现读写分离的缺点就是需要开发人员对程序进行改造，使其对下层不透明，但这种方式更容易开发和实现，适合互联网业务场景。
根据业务重要性拆分从库方案
（2）通过开源的软件实现读写分离
MySQL-proxy，Amoeba等代理软件也可以实现读写分离功能，这些软件的稳定性和功能一般，不建议生产使用。绝大多数公司常用的还是在应用端发程序实现读写分离。
（3）大型门户独立开发DAL层综合软件
百度，阿里等大型门户都有开发牛人，会花大力气开发适合自己业务的读写分离，负载均衡，监控报警，自动扩容，自动收缩等一系列功能的DAL层软件。
7.4 MySQL主从复制原理介绍
- MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Slave），在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和I/O线程）在Slave端，另外一个线程（I/O线程）在Master端。
- 要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后再在Slave上以相同顺序执行获取的binlog日志中所记录的各种SQL操作。
- 要打开MySQL的binlog记录功能，可通过在MySQL的配置文件my.cnf中的mysqld模块（[mysqld]标识后的参数部分）增加“log-bin”参数选项来实现，具体信息如下。
[mysqld]
log-bin=/data/3306/mysql-bin
提示：
有些同学把log-bin放在了配置文件结尾，而不是[mysqld]标识后，从而导致配置复制不成功。
7.5 MySQL主从复制原理过程详细描述
下面简单描述MySQL Replication的复制原理过程
1）在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制
2）此时，Slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求连接Master服务器，并请求从指定binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change
master命令指定的）之后开始发送binlog日志内容。
3）Master服务器接收到来自Slave服务器的I/O线程的请求后，其上负责复制的I/O线程会根据Slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的I/O线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。
4）当Slave服务器的I/O线程获取到Master服务器上I/O线程发送的日志内容，日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay
Log（即中继日志）文件（MySQL-relay-bin.xxxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新binlog日志时能够告诉Master服务器从新binlog日志的指定文件及位置开始请求新的binlog日志内容。
5）Slave服务器端的SQL线程会实时检测本地Relay Log中I/O线程新增加的日志内容，然后及时地把Relay
Log文件中的内容解析成SQL语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这些SQL语句，并在relay-log.info中记录当前应用中继日志的文件名及位置点。
经过了上面的过程，就可以确保在Master端和Slave端执行了同样的SQL语句。当复制状态正常时，Master端和Slave端的数据是完全一样的。当然，MySQL的复制机制也有一些特殊情况，具体请参考官方的说明，大多数情况下，同学们不用担心。
MySQL Replication的复制原理逻辑图
特别说明：
当企业面试MySQL主从复制原理时，不管是面试还是笔试，都要尽量画图表达，而不是口头讲或文字描述，面试时可以找黑板或拿出纸来给面试官详细讲解。
下面针对MySQL主从复制原理的重点进行小结：
- 主从复制是异步的逻辑的SQL语句级的复制
- 复制时，主库有一个I/O线程，从库有两个线程，即I/O和SQL线程
- 实现主从复制的必要条件是主库要开启记录binlog功能
- 作为复制的所有MySQL节点的server-id都不能相同。
- binlog文件只记录对数据库有更改的SQL语句（来自主数据库内容的变更），不记录任何查询（如select，show）语句。
八，MySQL主从复制实践
8.1 主从复制实践准备
8.1.1 主从复制数据库实战环境准备
MySQL主从复制实践对环境的要求比较简单，可以是单机单数据库多实例的环境，也可以是两台服务器，每个机器一个独立数据库的环境。本文以单机数据库多实例的环境为例讲解。实例端口信息查看如下：
[root@localhost ~]# ss -antup | grep 330
tcp LISTEN 0 128 *:3307 *:* users:(("mysqld",3910,11))
tcp LISTEN 0 128 *:3306 *:* users:(("mysqld",2450,11))
提示：
这里把3306实例作为主库，3307实例作为从库，如果根据前面的内容配置了MySQL多实例环境，直接开启多实例环境使用即可。
8.1.2 定义主从复制需要的服务器角色
这里的主从复制技术是针对前面的内容以单机数据库多实例环境来讲的。一般情况下，小企业在做常规的主从复制时，主从服务器多数在不同的机器上，并且监听的端口均为默认的3306.虽然不在同一个机器上，但是步骤和过程却是一样的。
同学们在掌握了但数据库多实例的同步方法后，可以自己适当扩展，完成异机相同端口之间的主从复制。
8.1.3 数据库中英文名称约定
8.2 在主库Master上执行操作配置
8.2.1 设置server-id值并开启binlog功能参数
根据之前介绍的MySQL主从复制原理我们知道，要实现主从复制，关键是要开启binlog日志功能，所以，首先来打开主库的binlog日志参数。
1）修改主库的配置文件。执行vi /data/3306/my.cnf,编辑多实例3006的my.cnf配置文件，按如下内容修改两个参数：
[mysqld]
server-id = 1 #用于同步的每台机器或实例server-id都不能相同
log-bin = /data/3306/mysql-bin #binlog日志的位置
提示：
上面的两个参数要放在my.cnf中的[mysqld]模块下，否则会出错。
不同实例间server-id的值不可以重复
要先在my.cnf配置文件中查找相关参数，并按要求修改。若发现不存在，再添加参数，切记，参数不能重复。
修改my.cnf配置后，需要重启动数据库，命令为：/data/3306/mysql restart ,注意要确认真正重启了。
2）检查配置参数后的结果，如下：
root@localhost ~]# egrep "server-id|log-bin" /data/3306/my.cnf
server-id = 1
log-bin=/data/3306/mysql-bin #log-bin后面也可以不带等号内容，MySQL会使用默认日志
3）重启主库MySQL服务，命令如下：
[root@localhost ~]# /data/3306/mysql restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL....
4）登陆数据库，检查参数的更改情况，如下：
[root@localhost ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock #登陆3306实例
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22-log Source distribution
Copyright (c) 2000, 2011, 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> show variables like ‘server_id‘; #查看MySQL的系统变量（like类似于grep过滤）
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 | #配置的server_id为1
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like ‘log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON | #binlog功能已开启
+---------------+-------+
1 row in set (0.00 sec)
mysql>
#这样，binlog功能就开启了。
8.2.2 在主库上建立用于主从复制的账号
根据主从复制的原理，从库要想和主库同步，必须有一个可以连接主库的账号，并且这个账号是主库上创建的，权限是允许主库的从库连接并同步数据。
1）登陆MySQL3306实例主数据库，命令如下：
[root@localhost ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock
2)建立用于从库复制的账号yunjisuan，命令如下：
mysql> grant replication slave on *.* to ‘yunjisuan‘@‘192.168.0.%‘ identified by ‘yunjisuan123‘;
Query OK, 0 rows affected (0.00 sec)
#语句说明：
1）replication slave为mysql同步的必须权限，此处不要授权all权限
2）*.* 表示所有库所有表，也可以指定具体的库和表进行复制。例如yunjisuan.test中，yunjisuan为库名，test为表名
3）‘yunjisuan‘@‘192.168.0.%‘ yunjisuan为同步账号。192.168.0.%为授权主机网段，使用了%表示允许整个192.168.0.0网段可以用yunjisuan这个用户访问数据库
4）identified by ‘yunjisuan123‘; yunjisuan123为密码，实际环境下设置的复杂些为好
创建完账号并授权后，需要刷新权限，使授权的权限生效
mysql> flush privileges; #刷新权限
Query OK, 0 rows affected (0.00 sec)
3）检查主库创建的yunjisuan复制账号命令及结果如下：
mysql> select user,host from mysql.user;
+-----------+-------------+
| user | host |
+-----------+-------------+
| root | 127.0.0.1 |
| yunjisuan | 192.168.0.% | #出现这行表示复制账号已经配置好了
| root | ::1 |
| | localhost |
| root | localhost |
+-----------+-------------+
5 rows in set (0.00 sec)
#说明：
MySQL里的授权用户是以数据表格的形式存储在mysql这个库的user表里。
mysql> select user,host from mysql.user where user=‘yunjisuan‘; #where是SQL查询语句的条件
+-----------+-------------+
| user | host |
+-----------+-------------+
| yunjisuan | 192.168.0.% |
+-----------+-------------+
1 row in set (0.00 sec)
mysql> show grants for yunjisuan@‘192.168.0.%‘; #查看账号的授权情况
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for yunjisuan@192.168.0.% |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO ‘yunjisuan‘@‘192.168.0.%‘ IDENTIFIED BY PASSWORD ‘*A2CC7FA422EF5A7CB098FEA7732C1F78CDC32F67‘ | #结果显示授权正确
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
8.2.3 实现对主数据库锁表只读
1）对主数据库锁表只读（当前窗口不要关掉）的命令如下：
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
提示：
在引擎不同的情况下，这个锁表命令的时间会受下面参数的控制。锁表时，如果超过设置时间不操作会自动解锁。
默认情况下自动解锁的时长参数值如下：
mysql> show variables like ‘%timeout%‘;
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 120 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 | #自动解锁时间受本参数影响
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 | #自动解锁时间受本参数影响
+----------------------------+----------+
10 rows in set (0.00 sec)
#提示：有关这两个参数，请同学们自行测试
2）锁表后查看主库状态。可通过当前binlog日志文件名和二进制binlog日志偏移量来查看，结果如下：
注意，show master status;命令显示的信息要记录在案，后面的从库导入全备后，继续和主库复制时就是要从这个位置开始。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 345 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
或者新开一个命令行窗口，用如下命令查看锁表后的主库binlog位置点信息：
[root@localhost ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 533 | | |
+------------------+----------+--------------+------------------+
3）锁表后，一定要单开一个新的SSH窗口，导出数据库的所有数据，如果数据量很大（50GB以上），并且允许停机，可以停库直接打包数据文件进行迁移，那样更快。
[root@localhost ~]# mkdir -p /server/backup
[root@localhost ~]# mysqldump -uroot -p123123 -S /data/3306/mysql.sock --events -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
#注意：-A表示备份所有库；-B表示增加use DB和 drop 等（导库时会直接覆盖原有的）
[root@localhost ~]# ll /server/backup/mysql_bak.2017-07-21.sql.gz
-rw-r--r--. 1 root root 137344 Jul 21 10:17 /server/backup/mysql_bak.2017-07-21.sql.gz
#为了确保导出数据期间，数据库没有数据插入，导库完毕可以再次检查主库状态信息，结果如下：
[root@localhost ~]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "show master status"
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 533 | | |
+------------------+----------+--------------+------------------+
提示：若无特殊情况，binlog文件及位置点和锁表后导出数据前是一致的，即没有变化。
#导出数据完毕后，解锁主库，恢复可写，命令如下.因为主库还要对外提供服务，不能一直锁定不让用户访问。
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
#可能会有同学因为锁表后的binlog位置问题犯迷糊，实际上做从库前，无论主库更新了多少数据，最后从库都可以从上面show master status的位置很快赶上主库的进度。
8.2.4 把主库导出的MySQL数据迁移到从库
下面主要讲解单数据库多实例的主从配置，也就是说，mysqldump备份的3306实例的数据和要恢复的3307实例在一台机器上，因此无需异地复制拷贝。想查看主库导出的数据，如下：
[root@localhost ~]# ll /server/backup/mysql_bak.2017-07-21.sql.gz
-rw-r--r--. 1 root root 137344 Jul 21 10:17 /server/backup/mysql_bak.2017-07-21.sql.gz
8.3 在MySQL从库上执行的操作过程
8.3.1 设置server-id值并关闭binlog功能参数
- 数据库的server-id一般在一套主从复制体系内是唯一的，这里从库的server-id要和主库及其他从库的不同，并且要注释掉从库的binlog参数配置，如果从库不做级联复制，并且不作为备份用，就不要开启binlog，开启了反而会增加从库磁盘I/O等的压力。
- 但是，有以下两种情况需要打开从库的binlog记录功能，记录数据库更新的SQL语句：
- 级联同步A-->B-->C中间的B时，就要开启binlog记录功能。
- 在从库做数据库备份，数据库备份必须要有全备和binlog日志，才是完整的备份。
（1）修改配置文件，配置从库1的相关参数
执行vi /data/3307/my.cnf,编辑my.cnf配置文件，按如下内容修改两个参数：
[mysqld]
server-id = 3 #调整等号后的数值，和任何一个数据库实例都不同
提示：
上面两参数要放在my.cnf中的[mysqld]模块下，否则会出错。
server-id的值不能和任何MySQL实例重复。
要先在文件中查找相关参数按要求修改。若发现不存在，再添加参数，切记，参数不能重复。
修改my.cnf配置后需要重启数据库，命令为：/data/3307/mysql restart,注意要确认真正重启了。
（2）检查配置参数后的结果
命令如下：
[root@localhost ~]# egrep "server-id|log-bin" /data/3307/my.cnf
server-id = 3
（3）重启3307的从数据库
命令如下：
[root@localhost ~]# /data/3307/mysql restart
Restarting MySQL...
Stoping MySQL...
Starting MySQL....
[root@localhost ~]# ss -antup | grep 3307
tcp LISTEN 0 128 *:3307 *:* users:(("mysqld",5659,11))
（4）登陆数据库检查参数的改变情况
命令如下：
[root@localhost ~]# mysql -uroot -p123123 -S /data/3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22 Source distribution
Copyright (c) 2000, 2011, 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> show variables like ‘log_bin‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like ‘server_id‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
8.3.2 把从主库mysqldump导出的数据恢复到从库
操作命令如下：
[root@localhost ~]# cd /server/backup/
[root@localhost backup]# ls -l
total 136
-rw-r--r--. 1 root root 137344 Jul 21 10:17 mysql_bak.2017-07-21.sql.gz
[root@localhost backup]# gzip -d mysql_bak.2017-07-21.sql.gz
[root@localhost backup]# ll
total 496
-rw-r--r--. 1 root root 506730 Jul 21 10:17 mysql_bak.2017-07-21.sql
[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock <mysql_bak.2017-07-21.sql #这是把数据还原到3307实例的命令
#提示：
如果备份时使用了-A参数，则在还原数据到3307实例时，登陆3307实例的密码也会和3306主库的一致，因为3307实例的授权表MySQL也被覆盖了。
8.3.3 登陆3307从库，配置复制参数
（1）MySQL从库连接主库的配置信息如下：
CHANGE MASTER TO
MASTER_HOST=‘192.168.0.200‘, #这里是主库的IP
MASTER_PORT=3306, #这里是主库的端口，从库端口可以和主库不同
MASTER_USER=‘yunjisuan‘, #这里是主库上建立的用于复制的用户yunjisuan
MASTER_PASSWORD=‘yunjisuan123‘, #这里是yunjisuan用户的密码
MASTER_LOG_FILE=‘mysql-bin.000001‘, #这里是show master status时查看到的二进制日志文件名称，注意不能多空格
MASTER_LOG_POS=533; #这里是show master status时查看到的二进制日志偏移量，注意不能多空格
#提示：字符串用单引号括起来，数值不用引号，注意内容前后不能有空格。
（2）登陆数据库后，去掉上述语句中的注释，执行如下：
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.0.200‘,MASTER_PORT=3306,MASTER_USER=‘yunjisuan‘,MASTER_PASSWORD=‘yunjisuan123‘,MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=533;
#提示：这个步骤的参数一定不能错，否则，数据库复制配置会失败
上述操作的原理实际上是把用户密码等信息写入从库新的master.info文件中
[root@localhost backup]# ll /data/3307/data/master.info
-rw-rw----. 1 mysql mysql 90 Jul 21 11:31 /data/3307/data/master.info
[root@localhost backup]# cat /data/3307/data/master.info
18
mysql-bin.000001 #这里是show master status时查看的二进制日志文件名称
533 #这里是show master status时查看的二进制日志偏移量
192.168.0.200 #这里是主库的IP
yunjisuan #这里是主库上建立的用于复制的用户yunjisuan
yunjisuan123 #这里是yunjisuan用户的密码
3306 #这里是主库的端口
60
0...以下省略若干...
8.4 启动从库同步开关，测试主从复制配置情况
（1）启动从库主从复制开关，并查看复制状态
相关语句如下：
[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "start slave"
[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.200
Master_User: yunjisuan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 533
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 533
Relay_Log_Space: 403
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
主从同步是否成功，最关键的为下面的3项状态参数：
[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show slave status\G" | egrep "IO_Running|SQL_Running|Seconds_Behind_Master"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
- [x] :Slave_IO_Running: Yes,这个时I/O线程状态，I/O线程负责从从库到主库读取binlog日志，并写入从库的中继日志，状态为Yes表示I/O线程工作正常。
- [x] :Slave_SQL_Running: Yes,这个是SQL线程状态，SQL线程负责读取中继日志（relay-log）中的数据并转换为SQL语句应用到从数据库中，状态为Yes表示I/O线程工作正常。
- [x] :Seconds_Behind_Master:0,这个是复制过程中从库比主库延迟的秒数，这个参数极度重要，但企业里更准确地判断主从延迟的方法为：在主库写时间戳，然后从库读取时间戳，和当前数据库时间进行比较，从而认定是否延迟。
（2）测试主从复制结果
在主库上写入数据，然后观察从库的数据状况。
[root@localhost backup]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "create database benet"
[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show databases"
+--------------------+
| Database |
+--------------------+
| information_schema |
| benet |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@localhost backup]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "create database yunjisuan"
[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show databases"
+--------------------+
| Database |
+--------------------+
| information_schema |
| benet |
| mysql |
| performance_schema |
| test |
| yunjisuan |
+--------------------+
[root@localhost backup]# mysql -uroot -p123123 -S /data/3306/mysql.sock -e "drop database yunjisuan"
[root@localhost backup]# mysql -uroot -p123123 -S /data/3307/mysql.sock -e "show databases"
+--------------------+
| Database |
+--------------------+
| information_schema |
| benet |
| mysql |
| performance_schema |
| test |
+--------------------+
#根据测试可以判断，主从库是同步的。
8.5 MySQL主从复制配置步骤小结
MySQL主从复制配置完整步骤如下：
- 准备两台数据库环境或单台多实例环境，确定能正常启动和登陆
- 配置my.cnf文件：主库配置log-bin和server-id参数；从库配置server-id，该值不能和主库及其他从库一样，一般不开启从库log-bin功能。注意，配置参数后要重启才能生效。
- 登陆主库，增加从库连接主库同步的账户，例如：yunjisuan，并授权replication slave同步的权限。
- 登陆主库，整库锁表flush table with read lock（窗口关闭后即失效，超时参数设置的时间到了，锁表也失效），然后show master status查看binlog的位置状态。
- 新开窗口，在Linux命令行备份导出原有的数据库数据，并拷贝到从库所在的服务器目录。如果数据库数据量很大，并且允许停机，可以停机打包，而不用mysqldump。
- 导出主库数据后，执行unlock tables解锁主库。
- 把主库导出的数据恢复到从库
- 根据主库的show master status查看到的binlog的位置状态，在从库执行change master to....语句。
- 从库开启复制开关，即执行start slave；。
- 从库show slave status\G,检查同步状态，并在主库进行更新测试。
8.6 MySQL主从复制线程状态说明及用途
8.6.1 MySQL主从复制主库I/O线程状态说明
（1）登陆主数据库查看MySQL线程的同步状态
mysql> show processlist\G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 5
User: yunjisuan
Host: 192.168.0.200:42008
db: NULL
Command: Binlog Dump
Time: 267
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
2 rows in set (0.00 sec)
#提示：上述状态的意思是线程已经从binlog日志读取所有更新，并已经发送到了从数据库服务器。线程目前为空闲状态，等待由主服务器上二进制日志中的新事件更新。
下图中列出了主服务器binlog Dump线程中State列的最常见状态。如果你没有在主服务器上看见任何binlog Dump线程，则说明复制没有运行，二进制binlog日志由各种事件组成，事件通常会为更新添加信息。
（2）登陆从数据库查看MySQL线程工作状态
从库有两个线程，即I/O和SQL线程。从库I/O线程的状态如下：
mysql> show processlist\G
*************************** 1. row ***************************
Id: 3
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 8
User: system user
Host:
db: NULL
Command: Connect
Time: 659
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 9
User: system user
Host:
db: NULL
Command: Connect
Time: 511
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3 rows in set (0.00 sec)
下图列出了从服务器的I/O线程的State列的最常见的状态。该状态也出现在Slave_IO_State列，由SHOW SLAVE STATUS显示。
下图列出了从服务器的SQL线程的State列的最常见状态
8.6.2 查看MySQL线程同步状态的用途
- 通过MySQL线程同步状态可以看到同步是否正常进行，故障的位置是什么，另外还可查看数据库同步是否完成，可用于主库宕机切换数据库或人工数据库主从切换迁移等。
- 例如：主库宕机，要选择最快的从库将其提升为主库，就需要查看主从库的线程状态，如果主从复制在正常情况下进行角色切换，也需要查看主从库的线程状态，根据复制状态确定更新是否完成。
8.7 MySQL主从复制更多应用技巧实践
8.7.1 工作中MySQL从库停止复制故障案例
模拟重现故障的能力是运维人员最重要的能力。下面就来次模拟操作。先在从库创建一个库，然后去主库创建同名的库来模拟数据冲突，命令如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.200
Master_User: yunjisuan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 544
Relay_Log_File: relay-bin.000010
Relay_Log_Pos: 336
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1007
Last_Error: Error ‘Can‘t create database ‘yunjisuan‘; database exists‘ on query. Default database: ‘yunjisuan‘. Query: ‘create database yunjisuan‘
Skip_Counter: 0
Exec_Master_Log_Pos: 451
Relay_Log_Space: 810
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1007
Last_SQL_Error: Error ‘Can‘t create database ‘yunjisuan‘; database exists‘ on query. Default database: ‘yunjisuan‘. Query: ‘create database yunjisuan‘
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
对于该冲突，解决办法如下
办法一：关闭从同步，调动sql_slave指针
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> set global sql_slave_skip_counter=1; #将sql线程同步指针向下移动一个，如果多次不同步，可以重复操作
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
对于普通的互联网业务，上述的移动指针的操作带来的问题不是很大。当然，要在确认不影响公司业务的前提下。
若是在企业场景下，对当前业务来说，解决主从同步比主从不一致更重要，如果主从数据一致也是很重要的，那就再找个时间恢复这个从库。
是主从数据不一致更重要，还是保持主从同步持续状态更重要，要根据业务选择。这样Slave就会与Master同步了，主要关键点如下：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0 #0表示已经同步状态
#提示：set global sql_slave_skip_counter=n; #n取值>0,忽略执行N个更新。
办法二：根据可以忽略的错误号事先在配置文件中配置，跳过指定的不影响业务数据的错误，例如：
[root@localhost ~]# grep slave-skip /data/3306/my.cnf
slave-skip-errors = 1032,1062
#提示：类似由于入库重复导致的失败可以忽略，其他情况是不是可以忽略需要根据不同公司的具体业务来评估。
其他可能引起复制故障的原因：
- MySQL自身的原因及人为重复插入数据。
- 不同的数据库版本会引起不同步，低版本到高版本可以，但是高版本不能往低版本同步。
- MySQL的运行错误或程序bug
- binlog记录模式，例如：row level模式就比默认的语句模式要好。
8.7.2 让MySQL从库记录binlog日志的方法
从库需要记录binlog的应用场景：当前的从库还要作为其他从库的主库，例如级联复制或双主互为主从场景的情况下。下面介绍从库记录binlog日志的方法。
在从库的my.cnf中加入如下参数，然后重启服务生效即可。
log-slave-updates #必须要有这个参数
log-bin = /data/3307/mysql-bin
expire_logs_days = 7 #相当于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 | xargs rm -f
8.7.3 MySQL主从复制集群架构的数据备份策略
- 有主从复制了，还需要做定时全量加增量备份么？答案是肯定的！
因为，如果主库有语句级误操作（例如：drop database yunjisuan；），从库也会执行drop database yunjisuan；，这样MySQL主从库就都删除了该数据。 - 把从库作为数据库备份服务器时，备份策略如下：
高并发业务场景备份时，可以选择在一台从库上备份（Slave5），把从库作为数据库备份服务器时需要在从库开启binlog功能，其逻辑图如下所示：
步骤如下：
1）选择一个不对外提供服务的从库，这样可以确保和主库更新最接近，专门用于做数据备份。
2）开启从库的binlog功能
备份时可以选择只停止SQL线程，停止应用SQL语句到数据库，I/O线程保留工作状态，执行命令为stop slave sql_thread;,备份方式可以采取mysqldump逻辑备份或直接物理备份，例如：使用cp，tar（针对/data目录）工具或xtrabackup（第三方的物理备份软件）进行备份，则逻辑备份和物理备份的选择，一般是根据总的备份数据量的多少进行选择的，数据量低于30G，建议选择mysqldump逻辑备份方法，安全稳定，最后把全备和binlog数据发送到备份服务器上留存。
8.7.4 MySQL主从复制延迟问题的原因及解决方案
问题一：主库的从库太多，导致复制延迟
从库数量以3~5个为宜，要复制的从节点数量过多，会导致复制延迟。
问题二：从库硬件比主库差，导致复制延迟。
查看Master和Slave的系统配置，可能会因为机器配置不当，包括磁盘I/O，CPU，内存等各方面因素造成复制的延迟。这一般发生在高并发大数据量写入场景中。
问题三：慢SQL语句太多
假如一条SQL语句执行时间是20秒，那么从执行完毕到从库上能查到数据至少需要20秒，这样就延迟20秒了。
一般要把SQL语句的优化作为常规工作，不断的进行监控和优化，如果单个SQL的写入时间长，可以修改后分多次写入。通过查看慢查询日志或show full processlist命令，找出执行时间长的查询语句或大的事务。
问题四：主从复制的设计问题
例如，主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。
更高版本的MySQL可以支持多线程复制，门户网站则会自己开发多线程同步功能。
问题五：主从库之间的网络延迟
主从库的网卡，网线，连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外，跨公网主从复制很容易导致主从复制延迟。
问题六：主库读写压力大，导致复制延迟。
主库硬件要搞好一点，架构的前端要加buffer及缓存层。
8.7.5 通过read-only参数让从库只读访问
read-only参数选项可以让从服务器只允许来自从服务器线程或具有SUPER权限的数据库用户进行更新，确保从服务器不接受来自用户端的非法用户更新。
read-only参数允许数据库更新的条件为：
- 具有SUPER权限的用户可以更新，不受read-only参数影响，例如：管理员root。
- 来自从服务器线程可以更新，不受read-only参数影响，例如：前文的yunjisuan用户。
- 再生产环境中，可以在从库Slave中使用read-only参数，确保从库数据不被非法更新。
read-only参数的配置方法如下：
方法一：直接带 --read-only参数启动或重启数据库，
使用killall mysqld
或mysqladmin -uroot -p123123 -S /data/3307/mysql.sock shutdownmysqld_safe --defaults-file=/data/3307/my.cnf --read-only &
方法二：在my.cnf里[mysqld]模块下加read-only参数重启数据库，配置如下：
[mysqld]
read-only
九，本节重点回顾
- MySQL多实例的实现原理及实战部署
- MySQL主从复制的原理（面试常问）
- MySQL主从复制的实践
- MySQL主从复制故障解决思路
- MySQL主从复制延迟原因及解决思路
- MySQL主从复制集群，从库备份的思想和思路