让架构师职业生涯延长20年的2大MySQL实战技能
MySQL数据库丛集实战：
随着访问量的不断增加，单台MySQL数据库服务器压力不断增加，需要对MYSQL进行优化和架构改造，MYQSL优化如果不能明显改善压力情况，可以使用高可用、主从复制、读写分离来、拆分库、拆分表来进行优化。MYSQL主从复制丛集在中小企业、大型企业中被广泛使用，MYSQL主从复制的目的是实现数据库冗余备份，将Master数据库资料定时同步至Slave库中，一旦Master数据库宕机，可以将WEB应用数据库配置快速切换至Slave数据库，确保WEB应用较高的可用率，如图11-12所示，为MYSQL主从复制结构图：
图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库保持资料一致。
MySQL主从复制实战：
MYSQL主从复制环境构建至少需2台服务器，可以配置1主多从，多主多从，如下为1主1从，MYSQL主从复制架构实战步骤：系统环境准备Master：192.168.111.128
Slave：192.168.111.129
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授权使用者
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 statusG检视Mysql主从状态：
slave start；
show slave statusG
检视Slave端IO执行绪、SQL执行绪状态均为YES，代表Slave已正常连线Master实现同步：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
执行Show slave statusG，常见引数含义解析：
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资料已同步
免费的MYSQL全方面教学视讯、源代码了解一下？
私聊小编“MYSQL”即可免费领取
感谢大家的观看，小编还会持续更新更多精彩内容。来都来了，点个赞再走呗~
