MySQL百分之百高可用性架构 使用nginx plus和galera cluster实现

默北
MySQL216,9342字数 5993阅读19分58秒阅读模式

在本文中，我们将使用MySQL Galera Cluster和nginx plus r5的TCP负载均衡来创建和测试数据库高可用集群。同时将展示适当的nginx的负载平衡配置，处理相互冲突的写操作和访问数据库集群失败的技术。

Galera Cluster文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/

Galera Cluster介绍

Galera Cluster在之前的文章中有介绍，可以去看看《MySQL/Galera集群-多主高可用性负载均衡》。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/

Galera Cluster是集群MySQL数据库服务器的同步复制解决方案。数据库写操作立即复制到其他Galera cluster节点上，且所有的服务器充当主节点。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/

负载均衡的Galera集群可以用在一个需要非常高的可用性的关键业务上。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/

Galera集群也支持MariaDB 和 Percona XtrDB Cluster。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/

首先，我们来创建一个测试表：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

CREATE TABLE data ( 
    id INTEGER NOT NULL AUTO_INCREMENT, 
    value CHAR(30), 
    count INTEGER, 
    PRIMARY KEY (value), 
    KEY (id)
);

Galera Cluster的搭建就不在此处累述了，大家可以看看之前的文档或看看官方文档。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/

Nginx Plus的TCP负载均衡配置

轮询连接三台数据库。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

stream {
    upstream db {
        server db1:3306;
        server db2:3306;
        server db3:3306;
    }

    server {
        listen 3306;
        proxy_pass db;
        proxy_connect_timeout 1s; # detect failure quickly
    }
}

然后通过Nginx Plus连接到数据库，并检查连接到后台哪个实例上：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

# mysql -u galera -p --protocol=tcp
Enter password: ********

mysql> SHOW VARIABLES WHERE Variable_name = 'hostname';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | db2   |
+---------------+-------+

当前连接到db2服务器上。如果我们重新连接，可能轮询到其他台服务器上的。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/

测试数据库集群

为了测试数据库集群，我们需要向刚才建立的表中插入数据并进行有意义的数据冲突碰撞。如果发生冲突，计数值加1。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

mysql> INSERT INTO data (value, count) VALUES ( '$value', 1 ) 
           ON DUPLICATE KEY UPDATE count=count+1;

下面的脚本query1.pl是插入100条数据，并打印出每条数据发送到哪台服务器上。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

#!/usr/bin/perl -w
=pod

USE test;
CREATE TABLE data ( 
	id INTEGER NOT NULL AUTO_INCREMENT, 
	value CHAR(30), 
	count INTEGER, 
	PRIMARY KEY (value), 
	KEY (id)
);

=cut

use DBI;
 
my $host   = "dev";
my $dbname = "test";
my $table  = "data";
my $user   = "galera";
my $pass   = "password";

foreach $i ( 0..99 ) { 

	my $dbh = DBI->connect("DBI:mysql:$dbname:$host", $user, $pass );

	# Which database have we connected to?
	my $sql = "SHOW VARIABLES WHERE Variable_name = 'hostname';";
	my $q = $dbh->prepare($sql);
	$q->execute;

	# Only expect one row, with key 'hostname', value thehostname
	@row = $q->fetchrow_array();
	$upstream = $row[1];
	$q->finish;

	# Add a row: value = "value-$i", count = 1; increment count on duplicate adds
	$value = sprintf "value-%03d", $i;
	$sql = "INSERT INTO $table (value, count) VALUES ( '$value', 1 ) ON DUPLICATE KEY UPDATE count=count+1";

	$q = $dbh->prepare($sql);
	$q->execute;
 	$q->finish;

 	print "$upstream\n";
}
$ ./query1.pl
db3
db1
db2
db3
......

mysql> SELECT * FROM data;
+-----+-----------+-------+
| id  | value     | count |
+-----+-----------+-------+
|   3 | value-000 |     1 |
|   4 | value-001 |     1 |
.....
| 101 | value-098 |     1 |
| 102 | value-099 |     1 |
+-----+-----------+-------+
100 rows in set (0.04 sec)

我们观察到，UPDATE操作轮询的访问数据库，并没有发生冲突。 当我们并行执行UPDATE操作时，计数值是这样的：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

$ ./query1.pl ; ./query1.pl ; ./query1.pl
......

mysql> SELECT * FROM data;
+-----+-----------+-------+
| id  | value     | count |
+-----+-----------+-------+
|   3 | value-000 |     4 |
|   4 | value-001 |     4 |
.....
| 101 | value-098 |     4 |
| 102 | value-099 |     4 |
+-----+-----------+-------+
100 rows in set (0.04 sec)

处理并行更新的问题

将表删除，重新创建。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

mysql> DROP TABLE data;
mysql> CREATE TABLE data ( id INTEGER NOT NULL AUTO_INCREMENT, value CHAR(30), 
           count INTEGER, PRIMARY KEY (value), KEY (id) );

并行执行20个更新操作：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

$ for i in {1..20} ; do ( ./query1.pl& ) ; done

会收到下面的报错信息：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

DBD::mysql::st execute failed: Deadlock found when trying to get lock; try 
restarting transaction at ./query1.pl line 42.

检查表数据显示很少的条目计数器已经增加到20了：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

mysql> SELECT * FROM data;
+------+-----------+-------+
| id   | value     | count |
+------+-----------+-------+
|    1 | value-000 |    14 |
|   31 | value-001 |    15 |
.....
| 2566 | value-098 |    18 |
| 2601 | value-099 |    20 |
+------+-----------+-------+
100 rows in set (0.03 sec)

这是Galera复制过程的后果，当在并行的情况下更新相同的记录，可能会发生死锁，数据库会拒绝交易。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/

在某些情况下，这种行为是可以接受的。如果应用程序不太可能并行提交冲突的更新，应用程序代码可以优雅地处理这些非常罕见的拒绝交易（例如返回一个错误给用户），那么这可能不是一个严重的问题。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/

如果这是不可接受的，最简单的解决方案是指定上游服务器组的一个成员为主要的数据库实例，通过标记其他成员为backup和down状态，如下所示：文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/ 

upstream db {
    server db1:3306;
    server db2:3306 backup;
    server db3:3306 down;
}

采用这样配置，所有的交易都路由到db1上。如果db1失败，当前建立的连接将丢弃，Nginx plus实现故障转移，将新连接到db2。请注意，DB3作为集群中的沉默的伙伴，仅接收来自db1和DB2的更新。

如果测试这个配置，你会发现在每个数据库实例，数据都会存在，并且有正确的计数值20。然而，如果在测试过程中db1发生失败，少数交易将会丢失。

更好的MySQL高可用性解决方案

交易可能因各种原因失败。如果需要一个非常高的程度的保护，需要确保我们的应用程序可以检测和重试失败的交易。

对于测试脚本，需要进行错误捕获，并短暂休息后重新提交交易。query2.pl脚本如下所示： 

#!/usr/bin/perl -w
=pod

USE test;
CREATE TABLE data ( 
	id INTEGER NOT NULL AUTO_INCREMENT, 
	value CHAR(30), 
	count INTEGER, 
	PRIMARY KEY (value), 
	KEY (id)
);

=cut

use DBI;
 
my $host   = "dev";
my $dbname = "test";
my $table  = "data";
my $user   = "galera";
my $pass   = "password";

foreach $i ( 0..99 ) { 

	my $backoff = 0.1; # exponential backoff, in seconds
TRY:
	eval {
		my $dbh = DBI->connect("DBI:mysql:$dbname:$host", $user, $pass, {PrintError => 0}) or die "Can't connect to DBI:mysql:$dbname:$host: ".$DBI::errstr;
	
		# Which database have we connected to?
		my $sql = "SHOW VARIABLES WHERE Variable_name = 'hostname';";
		my $q = $dbh->prepare($sql) or die "Can't prepare '$sql': ".$dbh->errstr;
		$q->execute or die "Can't execute '$sql': ".$q->errstr;

		# Only expect one row, with key 'hostname', value thehostname
		@row = $q->fetchrow_array();
		$upstream = $row[1];
		$q->finish;

		# Add a row: value = "value-$i", count = 1; increment count on duplicate adds
		$value = sprintf "value-%03d", $i;
		$sql = "INSERT INTO $table (value, count) VALUES ( '$value', 1 ) ON DUPLICATE KEY UPDATE count=count+1";

		$q = $dbh->prepare($sql) or die "Can't prepare '$sql': ".$dbh->errstr;
		$q->execute or die "Can't execute '$sql': ".$q->errstr;
	 	$q->finish;

#	 	print "$upstream\n";
 	} or do {
 		print "Failed: $@";
 		select( undef, undef, undef, $backoff );
 		$backoff *= 1.5;
 		goto TRY;
 	};

}

这一修改，我们可以回到原来的负载均衡方法（3活动数据库）和重新测试并行更新。常见的死锁错误检测和相应的事务重试。计数器正确的递增和该系统被证明是可靠的具有多个活动的主。 

mysql> SELECT * FROM data;
+------+-----------+-------+
| id   | value     | count |
+------+-----------+-------+
|    1 | value-000 |    20 |
|   33 | value-001 |    20 |
.....
| 2964 | value-098 |    20 |
| 2993 | value-099 |    20 |
+------+-----------+-------+
100 rows in set (0.04 sec)

抵御数据库失败

我们可以模拟故障，关闭一个或多个数据库服务器在向集群并行提交多个更新。

请注意，proxy_connect_timeout值降低到1秒，Nginx可以快速检测连接失败。

正如预期的那样，客户端代码得到的误差范围为数据库被关闭和重新启动中的交易，信息如下所示： 

Failed: Can't connect to DBI:mysql:test:dev: Lost connection to MySQL server at 'reading initial communication packet', system error: 0 at ./query2.pl line 28. 
Failed: Can't execute 'SHOW VARIABLES WHERE Variable_name = 'hostname';': Lost connection to MySQL server during query at ./query2.pl line 33.
Failed: Can't execute 'INSERT INTO data (value, count) VALUES ( 'value-020', 1 ) ON DUPLICATE KEY UPDATE count=count+1': Unknown command at ./query2.pl line 45.

尽管有这些错误，在广泛的测试中，没有单一的交易被丢失，也没有执行多次，而且三台数据库的数据依然保持一致性的。

与适当的应用逻辑相结合，Nginx plus负载均衡和Galera Cluster相结合，可以提供一个可靠的，高性能的，100%可靠的MySQL数据库集群。

是不是如上所说的100%可靠的，大家在应用中拭目以待吧。

参考资料：http://nginx.com/blog/mysql-high-availability-with-nginx-plus-and-galera-cluster/

weinxin
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
默北
  • 本文由 发表于 07/12/2014 01:00:20
  • 转载请务必保留本文链接：https://www.ttlsa.com/mysql/mysql-high-availability-with-nginx-and-galera/
  • Galera Cluster
  • MariaDB
  • Percona XtrDB Cluster
评论  2  访客  2
    • 运维生存时间
      运维生存时间 7
      2F

      是哟

      • 十字路口
        十字路口 9
        1F

        这个不错啊，可以考虑试试

      评论已关闭！