Mysql主从架构部署

MySQL复制概述

Mysql内建的复制功能是构建大型，高性能应用程序的基础。将Mysql的数据分布到多个系统上去，这种分布的机制，是通过将Mysql的某一台主机的数据复制到其它主机（slaves）上，并重新执行一遍来实现的。复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

请注意当你进行复制时，所有对复制中的表的更新必须在主服务器上进行。否则，你必须要小心，以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

1、MYSQL支持的复制类型：

1）基于语句的复制：  在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。  一旦发现没法精确复制时，   会自动选着基于行的复制

2）基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持

3）混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

2、复制解决的问题

MYSQL复制技术有以下一些特点：

1）数据分布 (Data distribution )

2）负载平衡(load balancing)

3）备份(Backups)

4）高可用性和容错行 High availability and failover

3、复制如何工作

整体上来说，复制有3个步骤：

1）master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

2）slave将master的binary log events拷贝到它的中继日志(relay log)；

3）slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了复制的过程：

MySQL主从复制原理

Mysql主从同步其实是一个异步复制的过程，要实现复制首先需要在master上开启bin-log日志功能，整个过程需要开启3个线程，分别是Master开启IO线程，slave开启IO线程和SQL线程。

1）在从服务器执行slave start，从服务器上IO线程会通过授权的用户连接上master，并请求master从指定的文件和位置之后发送bin-log日志内容。

2）Master服务器接收到来自slave服务器的IO线程的请求后，master服务器上的IO线程根据slave服务器发送的指定bin-log日志之后的内容，然后返回给slave端的IO线程。（返回的信息中除了bin-log日志内容外，还有本次返回日志内容后在master服务器端的新的binlog文件名以及在binlog中的下一个指定更新位置）

3）Slave的IO线程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master.info文件中，以便在下一次读取的时候能够清楚的告诉Master"我需要从某个bin-log的哪 个位置开始往后的日志内容，请发给我"；

4）Slave的Sql线程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

环境准备：

两台CentOS 6.9系统服务器

mysql-master：192.168.8.7

mysql-slave：192.168.8.8

mysql-master端，执行以下安装命令

[root@localhost ~]# yum -y install mysql mysql-devel mysql-server

mysql-slave端，执行以下安装命令

[root@localhost ~]# yum -y install mysql mysql-devel mysql-server

master端

修改vim /etc/my.cnf配置文件

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=1
log-bin=mysql-bin
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#保存退出

创建数据存储目录

[root@localhost ~]# mkdir -p /data/mysql

[root@localhost ~]# chown -R mysql.mysql /data/mysql

启动mysql

[root@localhost ~]# service mysqld start

slave端

修改vim /etc/my.cnf配置文件

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=2
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#保存退出

创建数据存储目录

[root@localhost ~]# mkdir -p /data/mysql

[root@localhost ~]# chown -R mysql.mysql /data/mysql

启动mysql

[root@localhost ~]# service mysqld start

在master数据库服务器上设置权限，执行以下操作命令

mysql> grant replication slave on *.* to tongbu@'%' identified by 'tongbu';

mysql> flush privileges;

mysql> show master status;

在slave数据库服务器上执行以下操作命令

mysql> change master to master_host='192.168.8.7′,master_user='tongbu',master_password='tongbu',master_log_file='mysql-bin.000003′,master_log_pos=540;

mysql> start slave;

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes两个状态为YES，代表slave已经启动两个线程，一个为IO线程，一个为SQL线程。

测试主从是否同步，在mater创建测试数据库，执行以下命令

mysql> create database xiaoqi;

mysql> use xiaoqi;

mysql> create table test (id varchar(20),name varchar(20));

mysql> show tables;

在slave端查看是否有xiaoqi数据库跟test表，如果存在，则代表主从同步成功。

mysql> show databases;

mysql> use xiaoqi;

mysql> show tables;

通过以上步骤，mysql主从负责搭建完成~~~

MySQL主从注意事项

mysql主从同步的原理：

1、在master上开启bin-log日志功能，记录更新、插入、删除的语句。

2、必须开启三个线程，主上开启io线程，从上开启io线程和sql线程。

3、从上io线程去连接master，master通过io线程检查有slave过来的请求，请求日志、postsion位置。

4、master将这些相应的日志返回给slave，slave自己去下载到本地的realy_log里面，写入一个master-info

日志记录同步的点。

5、slave的sql线程检查到realy-log日志有更新，然后在本地去exec执行。

6、主从同步是属于异步方式。

20133

insert into table values ('testweb','testserver');

20134

insert into t1 values ('Linux系统安装方法'，'dsfjsdklsdfjsdlk');

20135

change master to master_host='192.168.8.7′,master_user='tongbu',master_password='tongbu′,master_log_file='mysql-bin.000001′,master_log_pos=272;

MySQL主从同步故障解决方案

方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况

解决：

Master上执行：flush tables with read lock;

Slave上执行：

stop slave;

#表示跳过一步错误，后面的数字可变

set  global sql_slave_skip_counter =1;

start slave;

之后再用mysql> show slave status\G  查看：

Slve_IO_Running: Yes

Slave_SQL_Running: Yes

ok，现在主从同步状态正常了。。。

方式二：重新做主从，完全同步

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况

解决步骤如下：

1）先进入主库，进行锁表，防止数据写入

使用命令：

mysql> flush tables with read lock;

注意：该处是锁定为只读状态，语句不区分大小写

2）进行数据备份

#把数据备份到mysql.bak.sql文件

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.sql

这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失

3）查看master状态

mysql> show master status;

+——————-+———-+————–+——————————-+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |

+——————-+———-+————–+——————————-+

| mysqld-bin.000001 |     272 |              |

+——————-+———-+————–+——————————-+

1 row in set (0.00 sec)

4）把mysql备份文件传到从库机器，进行数据恢复

#使用scp命令

[root@server01mysql]# scp mysql.sql root@192.168.8.8:/tmp/

5）停止从库的状态

mysql> stop slave;

6）然后到从库执行mysql命令，导入数据备份

mysql> source /tmp/mysql.sql

7）设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项

change master to master_host = '192.168.8.7', master_user = 'tongbu',master_password='tongbu′, master_log_file = 'mysqld-bin.000001', master_log_pos=272;

8）重新开启从同步

mysql> start slave;

9）查看同步状态

mysql> show slave status\G  查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

在master上解锁：

unlock tables;