一、规划
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博客 ” ,链接:http://blog.itpub.net/10569970/viewspace-1726820/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10569970/viewspace-1726820/