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

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

Galera Cluster介绍

Galera Cluster在之前的文章中有介绍，可以去看看《MySQL/Galera集群-多主高可用性负载均衡》。

Galera Cluster是集群MySQL数据库服务器的同步复制解决方案。数据库写操作立即复制到其他Galera cluster节点上，且所有的服务器充当主节点。

负载均衡的Galera集群可以用在一个需要非常高的可用性的关键业务上。

Galera集群也支持MariaDB 和 Percona XtrDB Cluster。

首先，我们来创建一个测试表：

CREATE TABLE data (

id INTEGER NOT NULL AUTO_INCREMENT,

value CHAR(30),

count INTEGER,

PRIMARY KEY (value),

KEY (id)

);

1

2

3

4

5

6

7

CREATETABLEdata(

idINTEGERNOTNULLAUTO_INCREMENT,

valueCHAR(30),

countINTEGER,

PRIMARYKEY(value),

KEY(id)

);

Galera Cluster的搭建就不在此处累述了，大家可以看看之前的文档或看看官方文档。

Nginx Plus的TCP负载均衡配置

轮询连接三台数据库。

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

}

}

1

2

3

4

5

6

7

8

9

10

11

12

13

stream{

upstreamdb{

serverdb1:3306;

serverdb2:3306;

serverdb3:3306;

}

server{

listen3306;

proxy_passdb;

proxy_connect_timeout1s;# detect failure quickly

}

}

然后通过Nginx Plus连接到数据库，并检查连接到后台哪个实例上：

# mysql -u galera -p --protocol=tcp

Enter password: ********

mysql> SHOW VARIABLES WHERE Variable_name = 'hostname';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| hostname | db2 |

+---------------+-------+

1

2

3

4

5

6

7

8

9

# mysql -u galera -p --protocol=tcp

Enterpassword:********

mysql>SHOWVARIABLESWHEREVariable_name='hostname';

+---------------+-------+

|Variable_name|Value|

+---------------+-------+

|hostname|db2|

+---------------+-------+

当前连接到db2服务器上。如果我们重新连接，可能轮询到其他台服务器上的。

测试数据库集群

为了测试数据库集群，我们需要向刚才建立的表中插入数据并进行有意义的数据冲突碰撞。如果发生冲突，计数值加1。

mysql> INSERT INTO data (value, count) VALUES ( '$value', 1 )

ON DUPLICATE KEY UPDATE count=count+1;

1

2

mysql>INSERTINTOdata(value,count)VALUES('$value',1)

ONDUPLICATEKEYUPDATEcount=count+1;

下面的脚本query1.pl是插入100条数据，并打印出每条数据发送到哪台服务器上。

#!/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";

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

#!/usr/bin/perl -w

=pod

USEtest;

CREATETABLEdata(

idINTEGERNOTNULLAUTO_INCREMENT,

valueCHAR(30),

countINTEGER,

PRIMARYKEY(value),

KEY(id)

);

=cut

useDBI;

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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

$./query1.pl

db3

db1

db2

db3

......

mysql>SELECT *FROMdata;

+-----+-----------+-------+

|id|value|count|

+-----+-----------+-------+

|3|value-000|1|

|4|value-001|1|

.....

|101|value-098|1|

|102|value-099|1|

+-----+-----------+-------+

100rowsinset(0.04sec)

我们观察到，UPDATE操作轮询的访问数据库，并没有发生冲突。 当我们并行执行UPDATE操作时，计数值是这样的：

$ ./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)

1

2

3

4

5

6

7

8

9

10

11

12

13

14

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

......

mysql>SELECT *FROMdata;

+-----+-----------+-------+

|id|value|count|

+-----+-----------+-------+

|3|value-000|4|

|4|value-001|4|

.....

|101|value-098|4|

|102|value-099|4|

+-----+-----------+-------+

100rowsinset(0.04sec)

处理并行更新的问题

将表删除，重新创建。

mysql> DROP TABLE data;

mysql> CREATE TABLE data ( id INTEGER NOT NULL AUTO_INCREMENT, value CHAR(30),

count INTEGER, PRIMARY KEY (value), KEY (id) );

1

2

3

mysql>DROPTABLEdata;

mysql>CREATETABLEdata(idINTEGERNOTNULLAUTO_INCREMENT,valueCHAR(30),

countINTEGER,PRIMARYKEY(value),KEY(id));

并行执行20个更新操作：

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

1

$foriin{1..20};do(./query1.pl&);done

会收到下面的报错信息：

DBD::mysql::st execute failed: Deadlock found when trying to get lock; try

restarting transaction at ./query1.pl line 42.

1

2

DBD::mysql::stexecutefailed:Deadlockfoundwhentryingtogetlock;try

restartingtransactionat./query1.plline42.

检查表数据显示很少的条目计数器已经增加到20了：

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)

1

2

3

4

5

6

7

8

9

10

11

mysql>SELECT *FROMdata;

+------+-----------+-------+

|id|value|count|

+------+-----------+-------+

|1|value-000|14|

|31|value-001|15|

.....

|2566|value-098|18|

|2601|value-099|20|

+------+-----------+-------+

100rowsinset(0.03sec)

这是Galera复制过程的后果，当在并行的情况下更新相同的记录，可能会发生死锁，数据库会拒绝交易。

在某些情况下，这种行为是可以接受的。如果应用程序不太可能并行提交冲突的更新，应用程序代码可以优雅地处理这些非常罕见的拒绝交易(例如返回一个错误给用户)，那么这可能不是一个严重的问题。

如果这是不可接受的，最简单的解决方案是指定上游服务器组的一个成员为主要的数据库实例，通过标记其他成员为backup和down状态，如下所示：

upstream db {

server db1:3306;

server db2:3306 backup;

server db3:3306 down;

}

1

2

3

4

5

upstreamdb{

serverdb1:3306;

serverdb2:3306backup;

serverdb3:3306down;

}

采用这样配置，所有的交易都路由到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;

};

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

#!/usr/bin/perl -w

=pod

USEtest;

CREATETABLEdata(

idINTEGERNOTNULLAUTO_INCREMENT,

valueCHAR(30),

countINTEGER,

PRIMARYKEY(value),

KEY(id)

);

=cut

useDBI;

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})ordie"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)ordie"Can't prepare '$sql': ".$dbh->errstr;

$q->executeordie"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)ordie"Can't prepare '$sql': ".$dbh->errstr;

$q->executeordie"Can't execute '$sql': ".$q->errstr;

$q->finish;

# print "$upstream\n";

}ordo{

print"Failed: $@";

select(undef,undef,undef,$backoff);

$backoff *=1.5;

gotoTRY;

};

}

这一修改，我们可以回到原来的负载均衡方法(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)

1

2

3

4

5

6

7

8

9

10

11

mysql>SELECT *FROMdata;

+------+-----------+-------+

|id|value|count|

+------+-----------+-------+

|1|value-000|20|

|33|value-001|20|

.....

|2964|value-098|20|

|2993|value-099|20|

+------+-----------+-------+

100rowsinset(0.04sec)

抵御数据库失败

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

请注意，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.

1

2

3

Failed:Can't connect to DBI:mysql:test:dev: Lost connection to MySQL server at 'readinginitialcommunicationpacket', system error: 0 at ./query2.pl line 28.

Failed: Can'texecute'SHOW VARIABLES WHERE Variable_name = 'hostname';':LostconnectiontoMySQLserverduringqueryat./query2.plline33.

Failed:Can't execute 'INSERTINTOdata(value,count)VALUES('value-020',1)ONDUPLICATEKEYUPDATEcount=count+1':Unknowncommandat./query2.plline45.

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

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

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

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