MySQL日志及主从复制实现
MySQL日志及主从复制实现
一:MySQL是一个关系型数据库管理系统,最早由瑞典MySQL AB 公司发,后在2008年1月16号被Sun公司用10亿美金收购,但好景不长,2010年4月20日oracle用74亿美金收购了Sun,因此到目前MySQL属于 Oracle旗下产品。MySQL 最流行的关系型数据库管理系统,在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一,MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策,它分为社区版和商业版,由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,一般中小型网站的开发都选择 MySQL 作为网站数据库,由于其社区版的性能卓越,搭配 PHP 和 Apache 可组成良好的开发环境。
MySQL主从同步一共需要三个线程的操作,主MySQL有一个IO线程,从MySQL有一个IO线程和一个SQL线程, MySQL主从是实现MySQL高可用、数据备份、读写分离架构的一种最常见的解决方案,在绝大部分公司都有使用,要实现MySQL主从复制,必须要在Master打开binary log(bin-log)功能,因为整个MySQL的复制过程实际就是Slave从Master端获取响应的二进制日志,然后在Slave端顺序的执行日志中所记录的各种操作,二进制日志中几乎记录了出select以外的所有针对数据库的sql操作语句,具体的复制过程如下:
1.1.1:Slave端的IO线程连接上Master,并向Master请求指定日志文件的指定位置(新部署的Master和Slave从最开始的日志)之后的日志。
1.1.2:Master接收到来自Slave的IO线程请求,负责IO复制的IO线程根据Slave的请求信息读取相应的日志内容,然后将本地读取的bin-log的文件名、位置及指定位置之后的内容一起返回给Slave的IO线程处理。
1.1.3:Slave的IO线程将接收到的信息依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的bin-log的文件名和位置记录到Master-info文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从哪个bin-log的哪个位置开始往后的日志内容请发给我”。
1.1.4:Slave的sql线程检查到relay-log中新增了内容后,会马上将relay-log中的内容解析为在Master端真实执行时候的可执行命令,并顺序执行,从而保证对Slave的MySQL进行响应的增加或删除等操作,最终实现和Master数据保持一致。
1.2:减少主从同步延时的方案:
1.2.1:从库使用SSD硬盘,这样从库可以用最快的速度从主控同步数据
1.2.2:尽量在主库使用避免大量的写入,可以使用其他非关系型数据库的尽量使用非关系型数据库
1.2.3:主库和从库直接使用专用网络高速互联
1.2.4:对于数据一致性要求比较严格的场景,就不查询从库,因为从库可能有数据未同步到数据
1.2.5:读场景比较大的场景,可以使用多个从库,将读请求在各个slave服务器分解处理
1.3:数据库延迟复制,即设置slave故意从master延迟同步一小时或其他指定时间差,用于解决一下三个问题:
1.3.1:用于在数据库误操作之后快速的恢复数据,比如误删除表,那个这个延迟操作在延迟时间内从库的数据还没有发生变化,可以使用从库的数据进行恢复,然后再把一小时内的bin-log补写到Master即可完成数据的全部恢复。
1.3.2:用来做延迟测试,比如做好的数据库读写分离,把从库作为读库,即测试主从数据延迟五分钟或指定的时候,业务会发生什么样的问题,即可以模拟数据库延迟指定时间的业务错误。
1.3.3:用于老数据的查询,比如经常需要查看某天前的一个表或者字段的数值,你可能需要把备份恢复之后才能进行查看,但是如果有延迟从库,比如延迟一周,那么久可以解决这一的类似的业务需求场景。
1.3.4:设置延迟复制,通过设置SLAVE上的MASTER TO MASTER_DELAY参数来实现:
CHANGE MASTER TO MASTER_DELAY = N;
N为多少秒,该语句设置从库延时N秒后再与主数据库进行同步复制
1.3.5:具体MySQL延迟复制操作,登录到SLAVE之上进行操作:
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 600;
mysql> start slave;
mysql> SHOW SLAVE STATUS \G;
1.4:生产环境MySQL经验:
1.4.1:生产应用MySQL用户,不允许执行delete,可以只授权给update、select、insert,可以把delete更改为update,将要删除的数据加一个字段标记为已删除,因此线上的业务可以不需要delete
1.4.2:在有条件的情况下做一个延迟一小时等制定时间的从库
1.4.3:所有DML操作之前必须备份
1.4.4:规范开发在update数据库必须有两个脚本,如下:
1.4.4.1:数据库修改脚本,修改数据库的脚本
1.4.4.2:数据库回滚脚本,当数据更新失败的时候用于回滚
1.5:DML操作备份脚本
dml_backup.sh stable_name #输入表明即可备份表
1.5.1:开启相关日志:
[mysqld]
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
datadir=/data/mysql
innodb_file_per_table=1
relay-log = /data/mysql
server-id=20
log-error= /data/mysql_error.txt #记录错误日志
log-bin=/data/mysql/master-log #MySQL的bin-log即二进制日志,记录二进制形式SQL语句、主从复制和增量备份使用
general_log=ON #正常查询日志
general_log_file=/data/general_mysql.log
long_query_time=2 #慢查询日志配置
slow_query_log=1
slow_query_log_file= /data/slow_mysql.txt
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/tmp/mysql.sock
1.5.2:mysqlsla 工具使用:
使用语法如下:
# mysqlslap [options]
常用参数 [options] 详细说明:
–auto-generate-sql, -a 自动生成测试表和数据,表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
–auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括:read,key,write,update和mixed(默认)。
–auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列,从5.1.18版本开始支持。
–number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列,默认1
–number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列,默认1
–number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
–query=name,-q 使用自定义脚本执行测试,例如可以调用自定义的一个存储过程或者sql语句来执行测试。
–create-schema 代表自定义的测试库名称,测试的schema,MySQL中schema也就是database。
–commint=N 多少条DML后提交一次。
–compress, -C 如果服务器和客户端支持都压缩,则压缩信息传递。
–concurrency=N, -c N 表示并发量,也就是模拟多少个客户端同时执行select。可指定多个值,以逗号或者–delimiter参数指定的值做为分隔符。例如:–concurrency=100,200,500。
–engine=engine_name, -e engine_name 代表要测试的引擎,可以有多个,用分隔符隔开。例如:–engines=myisam,innodb。
–iterations=N, -i N 测试执行的迭代次数,代表要在不同并发环境下,各自运行测试多少次。
–only-print 只打印测试语句而不实际执行。
–detach=N 执行N条语句后断开重连。
–debug-info, -T 打印内存和CPU的相关信息。
说明:
测试的过程需要生成测试表,插入测试数据,这个mysqlslap可以自动生成,默认生成一个mysqlslap的schema,如果已经存在则先删除。可以用–only-print来打印实际的测试过程,整个测试完成后不会在数据库中留下痕迹。
各种测试参数实例(-p后面跟的是mysql的root密码):
单线程测试。测试做了什么。
# mysqlslap -a -uroot -p123456
多线程测试。使用–concurrency来模拟并发连接。
# mysqlslap -a -c 100 -uroot -p123456
迭代测试。用于需要多次执行测试得到平均值。
# mysqlslap -a -i 10 -uroot -p123456
# mysqlslap —auto-generate-sql-add-autoincrement -a -uroot -p123456
# mysqlslap -a –auto-generate-sql-load-type=read -uroot -p123456
# mysqlslap -a –auto-generate-secondary-indexes=3 -uroot -p123456
# mysqlslap -a –auto-generate-sql-write-number=1000 -uroot -p123456
# mysqlslap –create-schema world -q “select count(*) from City” -uroot -p123456
# mysqlslap -a -e innodb -uroot -p123456
# mysqlslap -a –number-of-queries=10 -uroot -p123456
测试同时不同的存储引擎的性能进行对比:
# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –engine=myisam,innodb –debug-info -uroot -p123456
执行一次测试,分别50和100个并发,执行1000次总查询:
# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –debug-info -uroot -p123456
50和100个并发分别得到一次测试结果(Benchmark),并发数越多,执行完所有查询的时间越长。为了准确起见,可以多迭代测试几次:
# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –debug-info -uroot -p123456
二:数据库主从同步实现:
2.1:两台服务器分别安装MySQL,提前准备好安装包和my.conf文件,使用以下脚本自动安装:
[root@sql-master mysql]# cat mysql-install.sh
#!/bin/bash
DIR=`pwd`
NAME="mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz"
FULL_NAME=${DIR}/${NAME}
DATA_DIR="/data/mysql"
yum install vim gcc gcc-c++ wget autoconf net-tools lrzsz iotop lsof iotop bash-completion -y
yum install curl policycoreutils openssh-server openssh-clients postfix -y
if [ -f ${FULL_NAME} ];then
echo "安装文件存在"
else
echo "安装文件不存在"
exit 3
fi
if [ -h /usr/local/mysql ];then
echo "Mysql 已经安装"
exit 3
else
tar xvf ${FULL_NAME} -C /usr/local/src
ln -sv /usr/local/src/mysql-5.6.34-linux-glibc2.5-x86_64 /usr/local/mysql
if id mysql;then
echo "mysql 用户已经存在,跳过创建用户过程"
fi
useradd mysql -s /sbin/nologin
if id mysql;then
chown -R mysql.mysql /usr/local/mysql/* -R
if [ ! -d /data/mysql ];then
mkdir -pv /data/mysql && chown -R mysql.mysql /data -R
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data/mysql --basedir=/usr/local/mysql/
cp /usr/local/src/mysql-5.6.34-linux-glibc2.5-x86_64/