一、规划
1.1 架构规划
为保证各种灾难情况下，数据库尽快的恢复服务，方案采用heartbeat集群加MySQL MM架构，架构图如下：
1.2 主机规划
|
主机名称 |
网卡名称 |
IP地址 |
备注 |
|
Cac-db01 |
Eth0 |
192.16.20.XX |
节点1上的业务网卡 |
|
Eth1 |
192.168.1.10 |
节点1上心跳网卡 |
|
|
Cac-db02 |
Eth0 |
192.16.20.XX |
节点2上的业务网卡 |
|
Eth1 |
192.168.1.11 |
节点2上心跳网卡 |
|
|
Cacdb |
Eth0:0 |
192.16.20.XX |
虚拟IP所在网卡 |
1.3 Heartbeat规划
参数配置：
|
Ha.cf |
||
|
参数名称 |
参数值 |
备注 |
|
logfile |
/var/log/ha-log |
日志路径 |
|
keepalive |
1 |
心跳频率 |
|
deadtime |
10 |
备机接管主机的故障时间 |
|
warntime |
5 |
集群冲断告警时间 |
|
initdead |
30 |
系统启动后，集群恢复的时长 |
|
auto_failback |
on |
主机恢复后是否自动回切 |
|
ping |
路由器的IP |
验证集群网络的连通性 |
|
haresource |
||
|
Ha01 IPaddr::192.16.20.7/24/eth0/ mysqld |
||
|
authkeys |
||
|
auth 1 1 crc |
||
1.4 数据库规划
|
参数 |
节点1参数值 |
节点2参数值 |
备注 |
|
innodb_buffer_pool_size |
|
|
内存数据缓冲区 |
|
innodb_log_file_size |
|
|
日志文件大小 |
|
innodb_log_buffer_size |
|
|
内存日志缓冲区 |
|
datadir |
|
|
|
|
|
|
|
|
|
Log-bin |
On |
|
|
|
Server-id |
1 |
2 |
|
|
sync_binlog |
1 |
1 |
|
|
innodb_flush_log_at_trx_commit |
1 |
1 |
|
|
log-slave-updates |
1 |
1 |
|
|
auto_increment_offset |
1 |
|
|
|
auto_increment_increment |
2 |
|
|
|
slow_query_log |
On |
|
|
|
long_query_time |
10.000000 |
|
|
|
log_queries_not_using_indexes |
On |
|
|
|
binlog_format |
Row |
|
|
|
Innodb_file_per_table |
On |
|
|
|
innodb_thread_concurrency |
8 |
|
|
|
transaction-isolation |
READ-COMMITTED
|
READ-COMMITTED |
数据库隔离级别 |
|
character_set_client |
Utf8 |
Utf8 |
|
|
character_set_connection |
Utf8 |
Utf8 |
|
|
character_set_database |
Utf8 |
Utf8 |
|
|
character_set_results |
Utf8 |
Utf8 |
|
|
character_set_server |
Utf8 |
Utf8 |
|
|
character_set_system |
Utf8 |
Utf8 |
|
二、集群软件heartbeat实施
2.1 获取heartbeat软件安装源
官方网站地址为：http://www.linux-ha.org/wiki/Releases
最新稳定版本为：
ClusterLabs-resource-agents-v3.9.2-0-ge261943.tar.gz
Heartbeat-3-0-7e3a82377fa8.tar.bz2
Reusable-Cluster-Components-glue--glue-1.0.8.tar.bz2
2.2 heartbeat安装
a) 安装依赖包：
yum install bzip2-devel glib2-devel libxml2 libxml2-devel install gcc-c++ install libtool-ltdl-devel libxslt-devel autoconf automake libtool pkgconfig glib2-devel e2fsprogs libnet* libxml2* PyXML ntp*
yum -y install gettext* intltool automake autoconf libtool-ltdl-devel pkgconfig python glib2-devel libxml2-devel libxslt-devel python-devel gcc-c++ bzip2-devel gnutls-devel pam-devel libqb-devel ncurses-devel openssl-devel docbook-style-xsl
yum install libqb*
yum install corosynclib-devel-1.4.1-15.el6.x86_64.rpm corosynclib-1.4.1-15.el6.x86_64.rpm corosync-1.4.1-15.el6.x86_64.rpm
yum install dbus*
b) 创建集群账号：
[root@localhost Packages]# groupadd haclient
[root@localhost Packages]# useradd -g haclient hacluster
c) 安装命令：
1 安装glue的步骤：
./autogen.sh
./configure --prefix=/usr --sysconfdir=/etc --localstatedir=/var LIBS=’/lib64/libuuid.so.1’
Make
Make install
2 安装heartbeat的步骤：
./bootstrap
./configure --prefix=/usr --sysconfdir=/etc --localstatedir=/var LIBS=’/lib64/libuuid.so.1’
make
make install
3 安装agent的步骤：
./autogen.sh
./configure --prefix=/usr --sysconfdir=/etc --localstatedir=/var LIBS=’/lib64/libuuid.so.1’
make
make install
三、MySQL MM模式配置
3.1 数据库安装
从官网获取安装文件
两节点上均安装以下安装包：
MySQL-client-5.6.24-1.el6.x86_64.rpm
MySQL-devel-5.6.24-1.el6.x86_64.rpm
MySQL-embedded-5.6.24-1.el6.x86_64.rpm
MySQL-server-5.6.24-1.el6.x86_64.rpm
MySQL-shared-5.6.24-1.el6.x86_64.rpm
MySQL-shared-compat-5.6.24-1.el6.x86_64.rpm
MySQL-test-5.6.24-1.el6.x86_64.rpm
3.2 MM架构配置：
a)编辑参数文件my.cnf，添加以下参数：
|
参数 |
节点1参数值 |
节点2参数值 |
备注 |
|
innodb_buffer_pool_size |
|
|
内存数据缓冲区 |
|
innodb_log_file_size |
|
|
日志文件大小 |
|
innodb_log_buffer_size |
|
|
内存日志缓冲区 |
|
Log-bin |
On |
|
|
|
Server-id |
1 |
2 |
|
|
sync_binlog |
1 |
1 |
|
|
innodb_flush_log_at_trx_commit |
1 |
1 |
|
|
log-slave-updates |
1 |
1 |
|
|
auto_increment_offset |
1 |
2 |
|
|
auto_increment_increment |
2 |
2 |
|
|
slow_query_log |
On |
On |
|
|
long_query_time |
10.000000 |
10.000000 |
|
|
log_queries_not_using_indexes |
On |
On |
|
|
binlog_format |
Row |
Row |
|
|
Innodb_file_per_table |
On |
On |
|
|
innodb_thread_concurrency |
2 |
2 |
|
|
transaction-isolation |
READ-COMMITTED
|
READ-COMMITTED |
数据库隔离级别 |
|
character_set_server |
Utf8 |
Utf8 |
数据库服务器字符集 |
b)创建MM架构数据复制专用账号：
在两个节点上均创建MM架构同步专用的账号：
在192.16.20.7上执行：
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicat_user'@'192.16.20.9' IDENTIFIED BY ' replicat_user ';
在192.16.20.9上执行：
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicat_user'@'192.16.20.7' IDENTIFIED BY 'replicat_user';
c)备份主库，再把备份的数据传到备机上进行数据初始化
在会话1中给数据库添加读锁，避免数据在备份期间被修改，并记录日志文件的名称及位置
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+-----------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------+
| on.000011 | 363 | | | |
+-----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
会话2种备份数据库：
[root@ha01 mysql]# mysqldump -uroot -proot --all-databases --master-data > dbdump.db
Warning: Using a password on the command line interface can be insecure.
[root@ha01 mysql]# mysqldump -uroot -p --all-databases --master-data > dbdump.db
Enter password:
[root@ha01 mysql]#
将备份文件上传到节点2，并将数据导入到数据库中
mysql> source /tmp/dbdump.db
d)数据同步配置
在节点2上配置复制slave进程
CHANGE MASTER TO
MASTER_HOST='192.16.20.7',
MASTER_PORT=3306,
MASTER_USER='replicat_user',
MASTER_PASSWORD='replicat_user',
MASTER_LOG_FILE='on.000011',
MASTER_LOG_POS=363;
启动slave进程
Start slave
节点一上做同样操作：
CHANGE MASTER TO
MASTER_HOST='192.16.20.9',
MASTER_PORT=3306,
MASTER_USER='replicat_user',
MASTER_PASSWORD='replicat_user',
MASTER_LOG_FILE='on.000001',
MASTER_LOG_POS= 609205; --需要在节点2上查看该日志路径与位置
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10569970/viewspace-1726820/ ，如需转载，请注明出处，否则将追究法律责任。
