构建高性能的--MySQL--数据库系统---主从复制剖析

原创

蜗牛的嘲讽 2019-07-02 16:52:12 博主文章分类：Linux 数据库 ©著作权

文章标签 mysql高可用keepalived 文章分类 运维

©著作权归作者所有：来自51CTO博客作者蜗牛的嘲讽的原创作品，请联系作者获取转载授权，否则将追究法律责任

主从复制解决方案

主从复制解决方案是mysql自身提供的一种高可用解决方案，数据同步方法采用的是 MySQL 复制技术。

MySQL 复制技术：主从架构中，从服务器到主服务器拉取二进制日志文件，然后再将日志文件解析成相应的sql 在从服务器上重新执行一遍主服务器的操作，通过这种方式保持数据的一致性。

注意：MySQL 复制技术仅仅提供了日志的同步执行功能，而从服务器只提供读操作，并且主服务器故障时，必须通过手动来处理故障转移，通常的做法是将一台从服务器更改为主服务器。

为了达到更高的可用性，在实际的应用环境中，一般都是采用MySQL复制技术配合高可用集群软件来实现自动故障转移。例如 keepalived

下面举个栗子！（部署过程）

实验架构图：

修改DB1配置文件增加以下几项： [mysqld] log-bin=mysql-bin relay-log=mysql-relay-bin server-id=1 replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.%

修改DB2配置文件增加以下几项： [mysqld] log-bin=mysql-bin relay-log=mysql-relay-bin server-id=2 replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schema.%

推荐在主从库上使用replicate-wild-ignore-table和replicate_wild_do_table两个选项来解决复制过滤的问题。

保证数据同步如果DB1上已经有MySQL数据，那么在执行主主互备之前，需要将DB1和DB2上的MySQL保持数据同步。

首先在DB1上执行锁表然后将数据导出，在DB2上应用。锁表命令：FLUSH TABLES WITH READ LOCK;

创建复制用户并授权首先在DB1创建复制用户

然后在DB2的MySQL库中将DB1设为自己的主服务器

mysql> change master to -> master_host='192.168.0.254', -> master_user='repl_user', -> master_password='123456', -> master_log_file='mysql-bin.000002', -> master_log_pos=106;

注意：master_log_file和master_log_pos这两个选项的值要对应在DB1上通过SQL语句"show master status" 查询到的值。

在DB2上启动Slave服务

mysql> start slave;

查看DB2上slave的运行状态

这样DB1到DB2的主从复制已经完成，接下来配置从DB2到DB1的主从复制，这个过程和前面完全一样。首先在DB2的MySQL库中创建复制用户

然后在DB1的MySQL库中将DB2设为自己的主服务器 mysql> change master to -> master_host='192.168.0.251', -> master_user='repl_user', -> master_password='123456', -> master_log_file='mysql-bin.000002', -> master_log_pos=106;

启动复制线程 mysql> start slave;

查看DB1上slave的运行状态

从状态看出复制服务运行正常，MySQL双主模式的主从复制配置完成。

配置Keepalived实现MySQL双主高可用

需要在两台服务器上安装keepalived，这里拿DB1安装过程举例，DB2的安装过程相同这里不重复。 tar xf keepalived-1.2.12.tar.gz

cd keepalived-1.2.12 ./configure --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-504.el6.x86_64/ make make install

DB1服务器配置文件 /etc/keepalived/keepalived.conf

global_defs { notification_email { root@localhost } notification_email_from keepalived.example.com router_id MySQL_HA }

vrrp_script check_mysqld { script "/etc/keepalived/check_slave.pl 127.0.0.1" interval 2 weight 21 }

vrrp_instance HA_1 { state BACKUP //DB1和DB2上均配置为BACKUP interface eth0 virtual_router_id 80 priority 100 advert_int 2 nopreempt //不抢占模式，只在优先级高的机器上设置即可，优先级低的机器可以不设置。

authentication {
    auth_type PASS
    auth_pass 23b14455cd
}

track_script { check_mysqld } virtual_ipaddress { 192.168.0.150
} }

其中，/etc/keepalived/check_slave.pl 脚本内容为：

#!/usr/bin/perl -w use DBI; use DBD::mysql;

CONFIG VARIABLES

$SBM = 120; $db = "wpdb"; $host = $ARGV[0]; $port = 3306; $user = "root"; $pw = "123456";

SQL query

$query = "show slave status";

$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError => 0,PrintError => 0 });

if (!defined($dbh)) { exit 1; }

$sqlQuery = $dbh->prepare($query); $sqlQuery->execute;

$Slave_IO_Running = ""; $Slave_SQL_Running = ""; $Seconds_Behind_Master = "";

while (my $ref = $sqlQuery->fetchrow_hashref()) { $Slave_IO_Running = $ref->{'Slave_IO_Running'}; $Slave_SQL_Running = $ref->{'Slave_SQL_Running'}; $Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'}; }

$sqlQuery->finish; $dbh->disconnect();

if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) { exit 1; } else { if ( $Seconds_Behind_Master > $SBM ) { exit 1; } else { exit 0; } }

这是用Perl写的检测MySQL复制状态的脚本，修改文件中的MySQL数据库端口，用户名，密码即可直接使用。

如果执行这个脚本报如下错误时：

使用以下命令可以解决：