mysql 集群以及部署

本集群的主要结构为一个主mysql 服务器(Master)和多个从mysql服务器(Slave)建立复制(replication)连接，主服务器与从服务器实现一定程度上的数据同步,多个服务器存储相同的数据副本, 我们将写操作(update,insert)定向到主服务器,把大量的查询操作(select)定向到从服务器,实现集群的负载均衡功能.如果主服务器发生了故障,从服务器将转换角色成为主服务器，使应用系统为终端用户提供不间断的网络服务;主服务器恢复运行后，将其转换为从属服务器，存储数据库副本，继续对终端用户提供数据查询检索服务。

部署:

unix要打开系统的SSH服务功能，系统安装配置期间允许root用户远程登录，正常运行以后要关闭root用户的远程登录功能。在/etc/ssh /sshd_config配置文件中对PermitRootLogin设置为yes为允许root用户远程登录到系统，设置为no即为不允许。修改保存文件后执行kill –HUP `cat /var/run/sshd.pid`命令即可重启sshd守护进程，使设置生效。

一:安装mysql  

1.先配置mysql    cd 到mysql的目录 ./configure  -–prefix=/app/mysql5 -–with-charset=gb2312     2 安装mysql   cd到bin 目录下make/make install   3为系统添加mysql 用户   pw group add mysql   4. 再次更改/app/mysql5目录及其文件属性
 chmod –R root mysql5         chmod –R mysql mysql5/data
这样做是为了保证系统安全，防止通过MySQL系统漏洞的入侵行为。MySQL以mysql用户身份运行，对/data目录具有完全访问权限，对其它目录则只有系统管理员根据需要设置的访问权限   5 启动mysql服务器  /app/mysql5/bin/mysqld_safe –user=mysql   --user参数表示以mysql用户身份运行MySQL数据库服务守护进程，“&”符号表示以后台方式运行。输入 netstat –an|grep 3306命令,查看是否监听这个端口.

二 : mysql系统shuju系统数据库系统配置

(1) 配置主机名和IP地址

本方案中把mysql服务器Master 主机名设为FreebsdMater, IP地址配置为192.168.1.100.其它三台Slave依次设置为FreebsdSlave1，192.168.1.101；FreebsdSlave2，192.168.1.102;FreebsdSlave3，IP 地址192.168.1.103。

(2). 设置root用户密码
MySQL的root用户初始密码为空，为保证系统安全必须为root用户设置密码：
[root@FreebsdMaster /]# /app/mysql5/bin/mysql –uroot –p
Enter password:
mysql>SET PASSWORD=PASSWORD(‘secret');
同Unix系统一样，输入密码时屏幕没有任何回显，进入系统后执行SET PASSWORD SQL语句设置或修改MySQL root用户密码。为使应用系统服务器远程连接到MySQL数据库服务器，除本地root用户以外，还要设置一个能从用户内部网络其它终端登录的root 用户。具体操作如下：
　　mysql>USE mysql;
　　mysql>UPDATE user SET Host='%' WHERE Host='hostname.yourdomain' AND User='root';
　　mysql>flush privileges;

　完成上述操作后应用系统服务器就可以连接到MySQL数据库服务器进行各种操作了，这个远程root用户的初始密码为空，用户需要从远程终端登录，为其设置密码。

(3). 开启MySQL服务器的二进制更新日志功能
　　上述MySQL服务器安装完成后，安装程序没有生成my.cnf配置文件，为此用户需要手工添加/etc/my.cnf文件。MySQL服务是由 /app/myql5/libexec/mysqld提供的，其寻找my.cnf配置文件的默认顺序为/etc/my.cnf，~/.my.cnf， /usr/local/mysql/etc/my.cnf。启动ee编辑器，# ee /etc/my.cnf，在Master机器的配置文件写入如下内容：
　　[mysqld]
　　log-bin=mysql-master-bin
　　server-id=1
　　在其它三台Slave机器的配置文件/etc/my.cnf文件写入如下内容：
　　[mysqld]
　　server-id=int
　　其中，int为一大于1且互不相等的正整数，如2，3，4等。务必要正确设置每个机器上的/etc/my.cnf配置文件，尤其是server-id不能有重复，这是一对多数据库复制能否成功的关键[5]!
　　设定好上述/etc/my.cnf文件后启动Master上的MySQL数据库服务时就自动开启了二进制更新日志功能。在Slave上启动MySQL时要加上—log-bin参数，即可开启二进制更新日志功能。在Slave机器上启动MySQL服务的命令如下：
　　# /app/mysql5/bin/mysqld_safe --user=mysql --log-bin &
　　这样做的目的为了实现冗余容错功能。当主服务器Master出现故障停止服务时，在预转换角色成为Master的Slave上执行STOP SLAVE; RESET MASTER SQL语句，在其它两台Slave上执行CHANGE MASTER TO SQL语句，执行STOP SLAVE; RESET MASTER SQL语句的Slave服务器即转换成新的Master，其余两台机器则从新的Master复制数据库数据(复制的是更新UPDATE、INSERT等操作，并不是简单的copy)，从而实现冗余容错。

三 :系统的实现 

完成上述准备后可以开始实现MySQL数据库的一对多复制，对应用系统的数据库访问代码进行优化，使更新操作UPDATE、INSERT等SQL语句定向到Master服务器，查询检索SELECT语句定向到Slave服务器，从而实现负载均衡; 当主服务器Master出现故障停止服务时，通过服务器角色转换实现冗余容错;上述所有服务器通过高速核心交换机连接在一起，协同工作，提供集群 (Cluster)性能。下面，本文就此举一简例，具体说明实现(Implementation)步骤。
3.3.1 准备主服务器
　　(1). 在主服务器上创建示例数据库repl_db
　从本地或远程终端登录到Master服务器，执行CREATE DATABASE repl_db;语句，创建示例数据库repl_db。建立表repl_table：
　　mysql>CREATE TABLE repl_table (f1 INT, f2 VARCHAR(20)) ;
　　向表中添加数据：
　　mysql>INSERT INTO repl_table (f1, f2) VALUES(1, ‘first');
　　(2). 授予Slave复制(Replication)权限
　　授予从属服务器Slave进行复制的权限。假设从属服务器连接到主服务器复制的用户名为“repl”，密码为“g00r002b”：
　　mysql>GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY ‘g00r002b';
　　刷新系统权限表，锁定写入语句操作：
　　mysql>FLUSH TABLES WITH READ LOCK;
　　这时所有写入操作都被锁定，包括支持事务(Transaction)特性的InnoDB类型表的提交(COMMIT)操作也被锁定，为数据库初始复制(copy)做好准备。
　　(3). 简单备份repl_db
　　在此，通过tar命令把数据库初始数据备份到/tmp目录。
　　# tar –cvf /tmp/mysql-data.tar /app/mysql5/data/repl_db
　　(4). 记录主服务器状态
　　执行SHOW MASTER STATUS 语句：
　　mysql> SHOW MASTER STATUS;

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

　　| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

　　| mysql-master-bin.000001 | 1027 | | |

　　+------------------------------------+------------------+----------------------+---------------------------+
　　记下File和Position两个参数的值，从属服务器Slave为了复制(replication)连接到主服务器Master时要用到这两个参数，如果参数与此不符将导致复制(replication)失败!
　　(5). 释放主服务器表级写入锁定
　　mysql>UNLOCK TABLES;
　　至此，主服务器解除写入锁定，一对多复制准备工作已全部完成，准备接受Slave的复制(replication)连接。

3.3.2 准备从属服务器
　　(1). 停止从属服务器的MySQL数据库服务
　　# /app/mysql5/bin/mysqladmin –uroot –p shutdown
　　Enter password:
　　输入MySQL系统root用户密码，MySQL服务停止。
　　(2). 简单copy数据库初始数据
　　在从属服务器上执行ftp操作，访问主服务器，下载/tmp/mysql-data.tar文件，在/app/mysql5/data目录下执行如下操作，把初始数据copy到Slave服务器：
　　[root@FreebsdSlave1 /app/mysql5/data]# tar –xvf /tmp/msyql-data.tar
　　其它两台Slave服务器执行同样操作，实现数据库初始数据的简单copy。
　　(3). 重启从属服务器上的MySQL数据库服务
　　# /app/mysql5/bin/mysqld_safe –-user=mysql –-log-bin &
　　从属服务器已启动，同时启动二进制更新日志功能，为角色转换做好准备。
　　(4). 登录到从属服务器Slave的MySQL客户端
　　# /app/mysql/bin/msyql –uroot -p
　　(5). 启动从属服务器上的复制(replication)线程
　　mysql> CHANGE MASTER TO
　　-> MASTER_HOST='192.168.1.100',
　　-> MASTER_USER='repl',
　　-> MASTER_PASSWORD='g00r002b',
　　-> MASTER_LOG_FILE=' mysql-master-bin.000001',
　　-> MASTER_LOG_POS=1027;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> START SLAVE;
　　Query OK, 0 rows affected (0.00 sec)
在所有从属服务器上执行相同的操作，自此从属服务器已连接到主服务器，开始真正意义上的replication工作。测试从属服务器的复制工作是否正常：
　　mysql>show slave status\G

　　************************** 1. row ***************************

　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.1.100
　　Master_User: repl
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-master-bin.000001
　　Read_Master_Log_Pos: 1027
　　Relay_Log_File: FreebsdSlave1-relay-bin.000005
　　Relay_Log_Pos: 251
　　Relay_Master_Log_File: mysql-master-bin.000001
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　看到上述信息说明从属服务器已启动了与replication相关的线程I/O和SQL，一对多的replication已经开始工作。
　　(6). 功能测试
　　在主服务器上写入新的数据：
　　mysql>INSERT INTO repl_table (f1, f2) VALUES(2, ‘second');
　　mysql>SELECT * FROM repl_table;
　　+--------+---------+

　　| f1 | f2 |

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

　　| 1 | first |

　　| 2 | second |

　　+-----=--+----------+

　　2 rows in set (0.01 sec)
　　在slave上执行相同的查询操作：
　　mysql>SELECT * FROM repl_table;
　　+--------+---------+

　　| f1 | f2 |

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

　　| 1 | first |

　　| 2 | second |

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

2 rows in set (0.01 sec)
得到相同的查询结果，说明复制(replication)机制已成功开始工作!

3.3.3 配置网络DNS服务器
在BIND DNS服务器中，为上述三台从属服务器Slave配置同一个名字，客户端的查询检索操作将由DNS服务器定向到其中的一台Slave。因此，对于同一名字，不同的客户端会定向到不同的地址，访问不同的MySQL服务器，从而达到负载均衡的目的。假设用户为三台Slave分配的DNS名字为 mysqlslave.yourdomain，DNS服务器区域文件/var/named/yourdomain.zone中应包含如下数据项[6]：
　　sqlmaster.yourdomain. IN A 192.168.1.100

　　sqlslave1.yourdomain. IN A 192.168.1.101

　　sqlslave2.yourdomain. IN A 192.168.1.102

　　sqlslave3.yourdomain. IN A 192.168.1.103

　　sqlslave IN CNAME sqlslave1

　　sqlslave IN CNAME sqlslave2

　　sqlslave IN CNAME sqlslave3
当客户端进行查询操作时，提交给主机sqlslave.yourdomain的请求将由DNS服务器随机定向到三台Slave中的一台，由其执行查询作业，返回结果。从而在三台Slave之间实现查询级别的负载均衡。
3.3.4 应用系统程序代码优化
　　实际应用中，对数据库的写入操作相对查询操作少得多，因此，优化应用程序的数据库连接代码，把写入操作定向到Master服务器，查询操作定向到 Slave服务器，提供主服务器和从属服务器之间更新、查询的负载均衡功能。本文以PHP数据库连接代码为例，简要介绍代码优化方法[7]。
(1). 准备不同的数据库连接配置文件
准备两个数据库连接文件mysql_connect_master.php和mysql_connect_slave.php，其文件内容如下：

　　mysql_connect_master.php文件的内容：

　　

　　// Connect to the Database Server

　　$linkID = @mysql_connect("sqlmaster.yourdomain", "user", "password") or die("Sorry, could not connect to the database!");

　　// Select the Database

　　@mysql_select_db("repl_db") or die("Sorry, Could not select database!");

　　?>

　　mysql_connect_slave.php文件的内容：

　　

　　// Connect to the Database Server

　　$linkID = @mysql_connect("sqlslave.yourdomain", "user", "password") or die("Sorry, could not connect to the database!");

　　// Select the Database

　　@mysql_select_db("repl_db") or die("Sorry, Could not select database!");

　　?>

　　(2). 优化程序SQL语句代码

　　对应用程序中访问数据库的代码段做出如下优化：

　　...

　　// $sql is the query string to be committed to MySQL server.

　　if ( stripos( $sql, " SELECT" ) )

　　{

　　include_once("./mysql_connect_slave.php");

　　...

　　mysql_close();

　　}

　　else

　　{

　　include_once("./mysql_connect_master.php");

　　...

　　mysql_close();

　　}

　　...

　　?>
说明：每当向MySQL服务器提交数据库操作时，加入一个if-else判断语句对变量$sql进行判断定向，如果是SELECT查询操作，将其定向到从属服务器Slave中的一个;如果是其它更新语句，则将其定向到主服务器Master。此处代码段是应用系统实现负载均衡的关键，用户务必根据自己实际情况，写出准确无误的代码。每次对数据库操作完毕都要及时释放数据库连接，以免更新、查询操作分别定向失败，影响系统负载均衡功能。这样做虽然在一定程度上增加Web服务器或应用服务器的开销，但与MySQL服务器集群负载均衡功能带来的大幅性能提升及冗余容错特性相比，这个开销绝对是物超所值!

3.4 注意事项
正确部署实现具有负载均衡功能的MySQL服务器集群必须注意以下事项：

(1). MySQL数据库复制(replication)特性是核心

此处的复制不是简单的copy，从属服务器启动两个线程(thread)：I/O线程和SQL线程，I/O线程接收主服务器对参与复制数据库的更新操作事件(event)，并记入自己的中继二进制更新日志文件(hostname-relay-bin.00000n)，由SQL线程将更新操作写入自己的数据库表项。主从服务器之间复制的不是具体的数据内容，而是具体的以二进制格式记录的操作事件，因而在一定程度上实现主从服务器之间的数据同步。(这种复制类似于生物学意义上的按基因复制，在英语中replication的主要词义就是指该种复制。)
(2). 复制的复杂性
主从数据库服务器间的replication要求Master与Slave上的MySQL版本最好一致，主从服务器必须设置相同的字符集，否则很容易造成复制失败。主服务器上更新权限表内容的FLUSH语句不会被复制[8]。
(3). 按照范式化要求设计数据库
生产环境下基于MySQL服务器的应用系统要想稳定运行，按范式化设计系统数据库是基本要求，具体内容可参考相关书籍。
(4). 打开数据库服务器的远程用户连接功能
打开主从服务器的远程用户连接是实现更新、查询操作分别定向的必要条件，否则，来自应用服务器的连接请求失败，影响系统应用正常运行。
(5). 负载均衡功能的实现需要良好的团队合作
BIND DNS服务器实现了从属服务器Slave之间的负载均衡，Slave和Master之间的负载均衡则由应用系统开发人员在程序代码级实现。整个系统的性能提升和冗余容错需要网络管理和应用系统开发团队之间的良好合作，否则负载均衡功能的实现就会失败。
4. 常见问题
(1). 如何估算MySQL服务器集群的性能提升量？
针对本文采用的结构模式，可对应用系统整体性能提升做出大致估算。假设应用系统写操作占10%，读操作占90%，写操作耗时是读操作的2倍，系统的吞吐量(throughput)为T(用reads/s读操作次数/秒来衡量)。把写操作线性转换为读操作，则有：
T= 2Xwrites + 9Xwrites ==>writes=T/11① (不采用主从复制模式，读写操作集中到一个服务器上)
T= 2Xwrites + 9Xwrites/N ==> writes=T/(2+9/N)② (采用一对多的主从复制模式，读操作在从属服务器，写操作在主服务器)
其中，writes为系统单位时间内所能承受的最大写操作次数，N为从属服务器个数，N大于等于2。在不采用主从复制模式时，系统性能 writes=T/11;采用本文一对三的复制模式时，系统性能writes=T/5。采用负载均衡模式与不采用系统性能之比为11：5，即2.2：1，考虑到应用服务器的额外开销，系统整体性能提升了整整1倍!从②式可以看出，系统整体性能理论极限为T/2，当然在实际生产环境中不可能达到。具体部署时用户可以根据自己的实际情况估算出合理的从属服务器数量，主要影响因素是网络带宽和机器整体性能[9]。
(2). 如何应对主从服务器崩溃？
当某台从属服务器崩溃时，修复故障重启后重新连接到主服务器，根据其master.info文件更新其数据，保持与主服务器的数据同步。如果主服务器崩溃，在某一从属服务器上执行STOP SLAVES; GRANT REPLICATION SLAVE ON *.* repl_db TO ‘repl'@'%' IDENTIFIED BY ‘g00r002b';RESET MASTER;这三个SQL语句，由于从属服务器已启动了二进制更新日志功能，因此具备了角色转换的必要条件。更改其主机名、IP地址及server- id与Master一样，重启MySQL服务器，系统开始正常对外提供服务。其它两台从属服务器则不需执行任何操作，继续执行replication过程。BIND DNS服务器和应用程序也不需做任何调整，继续对系统用户提供不间断服务。主服务器排除故障恢复正常后，将其网络配置改为与现有Master转换角色之前一样的配置，重启MySQL服务，将其角色转换为从属服务器。也就是说，整个集群机器的角色可以相互循环转换，提高系统的冗余性和可靠性。在此需要注意的是，在应用系统调试运行正常之后，在Slave角色服务器的/etc/my.cnf文件[mysqld]段加入slave-skip- errors=all，保证集群之间复制(replication)的正常运行。
5. 结束语
部署与实现具有负载均衡功能的MySQL服务器集群是一项复杂的系统工程，需要多方面良好的协同合作才能做好。服务器的搭建配置、BIND DNS服务器的配置部署，以及应用系统程序的开发都要紧紧围绕实现MySQL服务器集群负载均衡功能这个目标。必须对主服务器的运行状态进行动态监控，如果发生故障，立即执行角色转换过程，确保为终端用户提供可靠、不间断的服务。可以针对具体系统环境写出监控脚本或程序，确保系统的可靠性与稳定性。