二进制日志的介绍与配置（二）

7.记录二进制日志的三种模式

MySQL使用不同的模式记录二进制日志信息，常见的有三种模式。
（1）语句模式
语句（statement-based）模式是MySQL5.6版本默认的模式，简单地说，就是每一条被修改的数据的SQL语句都会记录到master的binlog中。在复制slave库的时候，SQL进程会解析成与原来master端执行过的相同的SQL来再次执行。
该模式的优点是不需要记录细到每一行数据的更改变化，因此，可减少binlog日志量，实际上是减少了很多，节约了磁盘I/O，提高了系统性能。
但该模式同样有一些缺点，由于语句模式记录的是执行的SQL语句，所以，对于某些具有特殊功能的SQL语句来说，就可能会导致无法在从库上正确执行，从而导致主从库数据不一致的问题。
例如，当特殊的函数被执行时，当触发器、存储过程等特殊功能被执行时，而row level模式是基于每一行来记录变化的，所以不会出现类似的问题（更多详情请参考混合模式）。
（2）行级模式
简单地说，行级（row-based）模式就是将数据被修改的每一行的情况记录为一条语句。
优点：在行级模式下，binlog中可以不记录执行的SQL语句的上下文相关信息，仅仅记录哪一条记录被修改了，修改成什么样了即可，所以row level的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题。
缺点：行级模式下，所有的执行语句都将根据修改的行来记录，而这就可能会产生大量的日志内容，例如一条语句修改了100万行，语句模式就用一条语句即可搞定，而行级模式执行之后，日志中记录的就是100万行的修改记录，binlog日志的量可能会大得惊人。
（3）混合模式
混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。
·当函数中包含UUID()时。
·当表中有自增列（AUTO_INCREMENT）被更新时。
·当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。
·当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。

8.企业中如何选择二进制日志模式

在互联网公司中，使用MySQL的特殊功能比较少（存储过程、触发器、函数），此时可以选择默认的语句模式。
如果公司较多用到MySQL的特殊功能，如存储过程、触发器、函数等，并且需要做主从复制请首选行级模式，次选mixed模式。

9.二进制日志的模式配置调整

临时调整命令如下：
mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
mysql> SET GLOBAL binlog_format = ‘ROW’;

@1=5

@2=‘test’

UPDATE oldboy.student

WHERE

@1=6

@2=‘oldgirl1’

SET

@1=6

@2=‘test’

at 500

12、MySQL数据库优化

MySQL数据库优化是一项非常重要的工作,而且是一项长期的工作, MySQL优化三分靠配置文件及硬件资源的优化,七分靠SQL语句的优化。
MySQL数据库具体优化包括:配置文件的优化、SQL语句的优化、表结构的优化、索引的优化,而配置的优化包括:系统内核、硬件资源、内存、CPU、 MySQL本身配置文件的优化。
硬件上的优化有两种方式:一种是增加内存和提高磁盘读写速度,进而提高 MySQL数据库的查询、更新的速度;另一种提高 MySQL性能的方式是使用多块磁盘来存数可以从多块磁盘上并行读取数据,进而提高读取数据的速度MySQL参数的优化,内存中会为 MySQL保留部分的缓冲区,这些缓冲区可以提高MySQL.的速度,缓冲区的大小可以在 MySQL的配置文件中进行设置
以下为企业级 MySQL百万量级真实环境配置文件my.cnf的内容,用户可以根据实情况修改,代码如下：
[client]
port=3306
socket =/tmp/mysql. sock
[ mysqld]
mysql
server id= 10
port=3306
socket=/tmp/mysql. sock
datadir =/data/mysql/
old_passwords=1
lower_case_table_names=1
character-set- server=utf8
default- storage-engine=MYISAM
log-bin=bin. log
log-error=error log
pid-file= mysql. pid
long_ query_time=2
slow_query_log=ON
slow_query_log_file=slow.log
binlog_cache_size= 4MB
binlog_format=mixed
max_binlog_cache_size=16MB
max_binlog_size= 1GB
expire_logs_days= 30
ft_min_word_len =4
back_log=512
max_allowed_packet= 64MB
max_connections=4096
max_connect_errors= 100
join_buffer_size= 2MB
read_buffer_size= 2MB
read_rnd_buffer_size=2MB
sort_buffer_size= 2MB
query_cache_size= 64MB
table_open_cache= 10000
thread_cache_size=256
max_heap_table_size= 64MB
tmp_table_size=64MB
thread_stack=192KB
thread_concurrency=24
local-infile=0
skip-show-database
skip-name- resolve
skip-external-locking
connect_timeout=600
interactive_timeout=600
wait_timeout=600
#***MyISAM
key_buffer_size= 512MB
bulk_insert_buffer_size=64MB
myisam_sort_ buffer_size=64MB
myisam_max_sort_file_size=1GB
myisam_repair_threads=1
concurrent_insert= 2
myisam_recover

*** INNODB

innodb_buffer_pool_size= 64GB
innodb_additional_mem_pool_size= 32MB
innodb_data_file_path=ibdata1:1G; ibdata2: 1G:autoextend
innodb_read_io_threads=8
innodb_write_io_threads =8
innodb_file_per_table= 1
innodb_flush_log_at_trx_commit =2
innodb_lock_wait_timeout=120
innodb_log_buffer_size= 8MB
innodb_log_file_size=256MB
innodb_log_files_in_group =3
innodb_max_dirty_pages_ pct= 90
innodb_thread_concurrency =16
innodb_open_files =10000

*** innodb_force_recovery =4

*** Replication Slave

read-only
#skip- slave-start
relay-log= relay. log
log-slave-updates

13、MySQL数据库集群实战

随着访问量的不断增加,单台 MySQL数据库服务器压力不断地增加,需要对 MySQL进行优化和架构改造,如果 MyQSL优化不能明显改善压力,可以使用高可用、主从复制、读MySQL.主从复制集群在中小企业、大型企业中被广泛应用, MYSQL主从复制的的写分离来、拆分库、拆分表等方法来进行优化。
是实现数据库冗余备份,将 master数据库数据定时同步至 slave库中,一且 master数
宕机,可以将Web应用
MySQL主从复制集群至少需要2台数据库服务器,其中一台为 master库,另外一台为slave库, MySQL主从数据同步是一个异步复制的过程,要实现复制首先需要在 master上开启 bin-log日志功能, bin-log日志用于记录在 master库中执行的增、删、修改、更新操作的SQL语句,整个过程需要开启3个线程,分别是 master开启1/0线程, slave开启I/O线程和SQL线程,具体主从同步原理详解如下
1、slave上执行 slave start, slave I/O线程会通过在 master创建的授权用户连接上至master,
并请求 master从指定的文件和位置之后发送 bin-log日志内容
2、master接收到来自 slave I/O线程的请求后, master I/O线程根据slave发送的指定bin-log日志 position点之后的内容,然后返回给slave的1/O线程
3、返回的信息中除了 bin-log日志内容外,还有 master最新的 bin-log文件名以及在bin-log中的下一个指定更新 position点;
4、slave I/O线程接收到信息后,将接收到的日志内容依次添加到slave端的 relay-log
文件的最末端,并将读取到的 master端的 bin-log的文件名和 posion点记录master.info文件中,以便在下一次读取的时候能告知 master从相应的bin-log文件名及最后一个 position点开始发起请求
5、slave SQL线程检测到 relay-log中内容有更新,会立刻解析relay-log日志中的内容,
将解析后的SQL语句在slave里执行,执行成功后slave库与master数保持一致

14、mysql主从复制

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

(1)系统环境准备

master:192.168.0.134
slave:192.168.0.135

(2) 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= 256MB
max_allowed_packet= 1MB
table_open_cache=256MB
sort_buffer_size=1MB
read_buffer_size=1MB
read_rnd_buffer_size=4MB
myisam_sort_buffer_size=64MB
thread_cache_size=8
query_cache_size=16MB
thread_concurrency=8
log-bin=mysql-bin
binlog_format=mixed
server- id= 1
[mysqldump]
quick
max_allowed_packet=16MB
[ mysql]
no- auto- rehash
[myisamchk]
key_buffer_size= 128MB
sort_buffer_size= 128MB
read_buffer= 2MB
write_buffer= 2MB
[mysqlhotcopy]
interactive-timeout
在 master数据库服务器命令行中创建 tongbu用户及密码并设置权限,执行如下查看 bin-log文件及 position点，如图所示。
grant replication slave on . to ‘tongbu’@’%’ identified by ‘song123’;
show master status;

(3) 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
skip-external-locking
key_buffer_size=256MB
max_allowed_packet=1MB
table_open_cache=256
sort_buffer_size=1MB
read_buffer_size=1MB
read_rnd_buffer_size=4MB
myisam_sort_buffer_size=64MB
thread_cache_size=8
query_cache_size= 16MB
thread_concurrency=8
server-id=2
[mysqldump]
quick
max_allowed_packet=16MB
no-auto-rehash
[myisamchk]
key_buffer_size=128MB
sort_buffer_size= 128MB
read_buffer=2MB
write_buffer=2MB
[mysqlhotcopy]
interactive- timeout
为slave指定 master IP、用户名、密码、 bin-log文件名( mysql-bin.00028)及position(257),
代码如下:
change master to
master_host=’192 168.0. 134’, master_use= ‘tongbu’, master_password=’song123’, master_log_
file='mysql - bin.000046’, master_log_pos=774;
如图：

在 slave中启动 slave start,并执行 show slave status\G查看 MySQL.主从状态,代码如下:
slave start;
show slave status\G
如图

查看 slave端I/O线程、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_a: master端监听端口
Connect_Retry:主从连接失败,重试时间间隔。
Master_Log_File:I/O线程读取的 master二进制日志文件的名称。
Read_Master_Log_Pos:IO线程已读取的 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线程和1/O线程之间的时间差距,单位秒,常被用于主从延迟检查方法之一。
(4)进行测试：
在master端创建tongbu数据库和stu表,命令如下,详情如图所示
mysql> create database tongbu character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use tongbu;
Database changed
mysql> create table stu(
-> sid int primary key auto_increment,
-> snam varchar(20),
-> sage int
-> )character set utf8;
Query OK, 0 rows affected (0.00 sec)
然后在slave库上进行查看，发现进行了随时进行了备份

MySQL.主从架构至此配置成功。
在master服务器stu表中插入两条数据，命令如下，在slave查看是否同步，master上执行详情如图所示。
Slave端执行查询命令,如图所示,表示master端插人的SQL数据已经同步到slave端