基于Galera架构的MySQL数据库集群部署

Galera 是一套在 MySQL InnoDB 存储引擎上面实现Multi-master及数据实时同步的系統架构。其有点如下所示：1、开源软件、免费；2、 业务层面无需做读写分离工作；3、数据库的读写压力都能按照既定的规则分发到各个节点上去。
一、Galera的实现架构和方式如图所示：

2、详细配置
2.1、配置相关环境I just show you on how to install Galera/MySQL with 1 master and 1 slave (joined node)。下列配置
是1 master and 1 slave的配置。
Please find variables that I used as below:OS: CentOS 5.6 64bitServer1: 10.0.211.78 (master)Server2: 10.0.211.79 (joined node)MySQL root password: 123abcSST MySQL user: sstSST MySQL password: sst123abcConfiguration in both servers are quite similar. So I will show you on how to install Galera MySQL in Server1 and then same procedure in Server2 with some minor changes.
2.2详细配置过程
2.2.1 查看已经安装的软件包Lets check what kind of MySQL applications installed in the box:
点击(此处)折叠或打开 [root@centos ~] rpm -qa | grep mysql
mysql-5.0.77-4.el5_6.6
mod_auth_mysql-3.0.0-3.2.el5_3
mysql-bench-5.0.77-4.el5_6.6
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-server-5.0.77-4.el5_6.6
mysql-test-5.0.77-4.el5_6.6
libdbi-dbd-mysql-0.8.1a-1.2.2
mysql-devel-5.0.77-4.el5_6.6
mysql-devel-5.0.77-4.el5_6.6
php53-mysql-5.3.3-1.el5_6.1
mysql-5.0.77-4.el5_6.6
2.2.2下载所需软件包Lets
download all needed applications. Download following applications in respective site:URL:
MySQL-server-wsrep-5.1.53-0.8.0b-x86_64.rpm
the MySQL serverURL: galera-0.8.0-x86_64.rpm
Galera libraryURL: http://dev.mysql.com/downloads/mysql/5.1.html
Download the client-community and shared-community for 64bit
2.2.3安装文件列表
Following files should exist if you download the correct version:
点击(此处)折叠或打开
[root@centos galera] ls -1galera-0.8.0-x86_64.rpm
MySQL-client-community-5.1.57-1.rhel5.x86_64.rpm
MySQL-server-wsrep-5.1.53-0.8.0b-x86_64.rpm
MySQL-shared-community-5.1.57-1.rhel5.x86_64.rpm
最后一个文件可以最后安装MySQL-shared-community-5.1.57-1.rhel5.x86_64.rpm该软件包包含某些语言和应用程序需要动态装载的共享库(libmysqlclient.so*)，需要安装2.2.4 删除mysql以前的版本
Lets remove currently installed mysql. Dont worry about this, because we are actually upgrading our MySQL to version5.1:[root@centos galera] rpm -e --nodeps mysql.i386 mysql.x86_64 mysql-devel.i386 mysql-devel.x86_64 mysql-server mysql-bench 2.2.5? 安装所需软件，目前使用mysql5.5.28版本。Now, we need to install all files based on following sequence:
点击(此处)折叠或打开
[root@centos galera] rpm -Uhv galera-0.8.0-x86_64.rpm
[root@centos galera] rpm -Uhv MySQL-client-community-5.1.57-1.rhel5.x86_64.rpm
[root@centos galera] rpm -Uhv MySQL-shared-community-5.1.57-1.rhel5.x86_64.rpm
[root@centos galera] rpm -Uhv MySQL-server-wsrep-5.1.53-0.8.0b-x86_64.rpm
2.2.6修改mysql的root用户密码
Lets reset/create MySQL root password. In this example we will use simple root password as above, but I recommend you to use strong password:[root@centos ~] mysqladmin -u root password 'rootpass'
2.2.7 检查mysql版本安装是否正确
点击(此处)折叠或打开
Check whether MySQL we installed is running correctly. You should see the MySQL version with ‘wsrep_version’:[root@centos galera] mysqlWelcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 3Server version: 5.1.53 wsrep_0.8.0?Copyright (c) 2000, 2010, Oracle and/or....
2.2.8为实现wsrep架构而进行的用户创建、删除工作
点击(此处)折叠或打开
Run following commands inside MySQL. First command will delete empty username, 2nd is to create root@% user, 3rd is update root password for all hosts, 4th is create SST user which we will use to communicate between nodes and 5th command will grant SST user to dump databases:mysql> DELETE FROM mysql.user WHERE user='';mysql> GRANT USAGE ON *.* TO root@'%' IDENTIFIED BY 'rootpass';mysql> UPDATE mysql.user SET Password=PASSWORD('rootpass') WHERE User='root';mysql> GRANT USAGE ON *.* to sst@'%' IDENTIFIED BY 'sstpass123';mysql> GRANT ALL PRIVILEGES on *.* to sst@'%';this command grant all priveleges to root@’%’ ,so remote root user can access all databases . mysql> GRANT ALL PRIVILEGES on *.* to root@'%';
2.2.9 如有必要，需要进行mysql 版本升级工作
Since we have update the MySQL version from 5.0 to 5.1, we need to upgrade the mysql table first by run following command:
[root@centos ~]mysql_upgrade -p
2.2.10 确认galera库文件
Lets locate libgalera and take note of the full path. We will use this as the communication provider for MySQL to communicate:
[root@centos galera] updatedb
[root@centos galera] locate libgalera/usr/lib64/galera/libgalera_smm.so
2.2.11 配置wsrep.cnf文件
Lets configure MySQL so it will know where and how to communicate with other nodes.
Open /etc/mysql/conf.d/wsrep.cnf (create the directory if it is not exist) with text editor and change value for following variables:
可以从/usr/share/mysql/wsrep.cnf下拷贝一个：
[root@centos galera] mkdir -p /etc/mysql/conf.d/[root@centos galera] vi /etc/mysql/conf.d/wsrep.cnf
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://"wsrep_sst_auth=sst:sstpass123
2.2.12 Linux IPTables配置
Make sure your IPTables has allowed the required port. If not, use following command to open the port:
[root@centos ~] iptables -A INPUT -i eth0 -p tcp -m tcp --source 10.0.211.78/24 --dport 3306 -j ACCEPT
[root@centos ~] iptables -A INPUT -i eth0 -p tcp -m tcp --source 10.0.211.78/24 --dport 4567 -j ACCEPT
2.2.13 修改my.cnf确认其包含wsrep.cnf所在的目录
Make sure in your /etc/my.cnf (create this file it is not exist) to add following line:!includedir /etc/mysql/conf.d/
2.2.14重启mysql 服务
Restart the MySQL so it will run the latest configuration:
[root@centos galera] service mysql restart
2.2.15查看mysql 和wsrep的服务端口
Make sure MySQL is run on port 3306 and wsrep is run on port 4567
netstat -tulpn | grep -e 4567 -e 3306
2.2.16服务器2的不同配置
点击(此处)折叠或打开
The setup and configuration for Server1 is done. For Server2, just login into the box and repeat step 1 to 15, but with some minor changes as below:On step 11 (we will tell Server2 to connect to Server1 port on 4567 for cluster replication):
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.1.1:4567"
wsrep_sst_auth=sst:sstpass123
On step 12 (take note on the source IP is change for Server2):
[root@centos ~] iptables -A INPUT -i eth0 -p tcp -m tcp --source 10.0.211.79/24 --dport 3306 -j ACCEPT
[root@centos ~] iptables -A INPUT -i eth0 -p tcp -m tcp --source 10.0.211.79/24 --dport 4567 -j ACCEPT
To check whether Galera is installed correctly and running properly, login into mysql and execute following command:
mysql> show status like 'wsrep%';
You will see similar output as below in both servers:| wsrep_local_state_comment | Synced (6) || wsrep_cluster_conf_id | 2 || wsrep_cluster_size | 2 || wsrep_cluster_state_uuid | 2d13bbfc-a2c3-11e0-0800-823f3f6853c0 || wsrep_cluster_status | Primary || wsrep_connected | ON || wsrep_local_index | 1 || wsrep_ready | ON |+----------------------------+--------------------------------------+
Now your MySQL servers are clustered with Galera replication. You can add another slave by refferring installation steps 18 and it will replicate accordingly.
2.2.17 实际配置cluster时遇到的问题以及第三台服务器节点的配置在配置第三台节点的时候，遇到一个问题，作为主节点78,第三个节点81加入cluster时，报78无法连接81：ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.211.81' (4)Cluster中第三个节点状态异常：wsrep_ready 状态为OFF，wsrep_local_state_comment状态为Joining正在加入，而没有加入。 经手工测试，从10.0.211.78上 mysql –h 10.0.211.81 –p测试，能够连接上，但是连接比较慢，耗费时间比较多，程序中应该有连接超时控制导致没有连接上，目前程序的超时控制不好调 整，对于此问题，考虑从mysql角度解决远程登录10.0.211.81缓慢的问题。查询资料得知，在my.cnf中添加如下配置，跳过域名解析：[mysqld]skip-name-resolve重启database server，手工测试速度明显加快。然后查询cluster中第三个节点的状态，
点击(此处)折叠或打开
+----------------------------+----------------------------------------------------+| Variable_name | Value |+----------------------------+----------------------------------------------------+| wsrep_local_state_uuid | 53636e10-6f2d-11e1-0800-9a4fde2a8a02 || wsrep_protocol_version | 4 || wsrep_last_committed | 658 || wsrep_replicated | 0 || wsrep_replicated_bytes | 0 || wsrep_received | 3 || wsrep_received_bytes | 308 || wsrep_local_commits | 0 || wsrep_local_cert_failures | 0 || wsrep_local_bf_aborts | 0 || wsrep_local_replays | 0 || wsrep_local_send_queue | 0 || wsrep_local_send_queue_avg | 0.000000 || wsrep_local_recv_queue | 0 || wsrep_local_recv_queue_avg | 0.333333 || wsrep_flow_control_paused | 0.000000 || wsrep_flow_control_sent | 0 || wsrep_flow_control_recv | 0 || wsrep_cert_deps_distance | 0.000000 || wsrep_apply_oooe | 0.000000 || wsrep_apply_oool | 0.000000 || wsrep_apply_window | 0.000000 || wsrep_commit_oooe | 0.000000 || wsrep_commit_oool | 0.000000 || wsrep_commit_window | 0.000000 || wsrep_local_state | 4 || wsrep_local_state_comment | Synced || wsrep_cert_index_size | 0 || wsrep_causal_reads | 0 || wsrep_incoming_addresses | 10.0.211.78:3306,10.0.211.79:3306,10.0.211.81:3306 || wsrep_cluster_conf_id | 3 || wsrep_cluster_size | 3 || wsrep_cluster_state_uuid | 53636e10-6f2d-11e1-0800-9a4fde2a8a02 || wsrep_cluster_status | Primary || wsrep_connected | ON || wsrep_local_index | 2 || wsrep_provider_name | Galera || wsrep_provider_vendor | Codership Oy || wsrep_provider_version | 23.2.2(r137) || wsrep_ready | ON |+----------------------------+----------------------------------------------------+
wsrep_ready 状态为ON，问题解决。
另外：在/var/lib/mysql下如果手动创建了log文件夹，配置文件中所有的日志都定向到log文件夹之下，wsrep的两台机器之间同样会出现连接不上的情况。
2.3远程客户端连接数据库服务器端的配置Mysql默认只允许本地连接数据库服务器端，需要进行如下操作：
用root登陆mysql执行如下命令
grant　all　on　sonardb.*　to　sonar@’%’　identified　by　‘123456’;
grant　all　on　sonardb.*　to　sonar@localhost　identified　by　‘123456’;
grant all on *.* to root@'%' identified by '123abc';???? grant all on *.* to root@localhost identified by '123abc';
sonardb替换为你想访问的数据库名,sonar是你的想使用的用户名,123456替换为你的密码,这样就开启了远程访问功能。
2.4数据库升级的问题需要升级mms_sdmtv和mysql两个数据库，其中业务库是mms_sdmtv，数据库的授权信息保存在mysql库中，因此共需导入两个数据库。
2.4.1升级操作在5.0的数据库服务器上执行：
mysqldump -u root -p mms_sdmtv >/home/pushuaiye/mms_sdmtv.bk
mysqldump -u root -p mysql >/home/pushuaiye/mysql.bk
然后在5.1的数据库服务器上执行：--创建数据库mms_sdmtvmysql -u root -p mysql>create database mms_sdmtv; mysql -u root -p mms_sdmtv GRANT USAGE ON *.* to sst@'%' IDENTIFIED BY 'sstpass123';mysql> GRANT ALL PRIVILEGES on *.* to sst@'%'; （注：涉及到用户表的是这一个sql，可能还有其他与权限有关的表的操作，上边的两条命令最稳妥：
点击(此处)折叠或打开
INSERT INTO `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `Event_priv`, `Trigger_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) VALUES ('%', 'sst', '*17C969F34B77206DF83CE9A251771FF9A7D861AC', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);）查看Galera cluster 状态：mysql>show status like 'wsrep%';+----------------------------+--------------------------------------+| Variable_name | Value |+----------------------------+--------------------------------------+| wsrep_local_state_uuid | 53636e10-6f2d-11e1-0800-9a4fde2a8a02 || wsrep_protocol_version | 4 || wsrep_last_committed | 0 || wsrep_replicated | 0 || wsrep_replicated_bytes | 0 || wsrep_received | 3 || wsrep_received_bytes | 232 || wsrep_local_commits | 0 || wsrep_local_cert_failures | 0 || wsrep_local_bf_aborts | 0 || wsrep_local_replays | 0 || wsrep_local_send_queue | 0 || wsrep_local_send_queue_avg | 0.000000 || wsrep_local_recv_queue | 0 || wsrep_local_recv_queue_avg | 0.000000 || wsrep_flow_control_paused | 0.000000 || wsrep_flow_control_sent | 0 || wsrep_flow_control_recv | 0 || wsrep_cert_deps_distance | 0.000000 || wsrep_apply_oooe | 0.000000 || wsrep_apply_oool | 0.000000 || wsrep_apply_window | 0.000000 || wsrep_commit_oooe | 0.000000 || wsrep_commit_oool | 0.000000 || wsrep_commit_window | 0.000000 || wsrep_local_state | 4 || wsrep_local_state_comment | Synced || wsrep_cert_index_size | 0 || wsrep_causal_reads | 0 || wsrep_incoming_addresses | 10.0.211.79:3306,10.0.211.78:3306 || wsrep_cluster_conf_id | 4 || wsrep_cluster_size | 2 || wsrep_cluster_state_uuid | 53636e10-6f2d-11e1-0800-9a4fde2a8a02 || wsrep_cluster_status | Primary || wsrep_connected | ON || wsrep_local_index | 0 || wsrep_provider_name | Galera || wsrep_provider_vendor | Codership Oy || wsrep_provider_version | 23.2.2(r137) || wsrep_ready | ON |+----------------------------+--------------------------------------+
wsrep_cluster_size 为2，两个mater: 10.0.211.79:3306,10.0.211.78:3306,状态正常。(The End)