利用keepalived构建高可用MySQL-HA

浏览数：29 / 时间：2015年06月12日
技术分享技术分享
MySQL-VIP：192.168.174.100
MySQL-master1：192.168.174.135
MySQL-master2：192.168.174.139
OS版本：CentOS 6.5 
MySQL版本：5.6.17 
Keepalived版本：1.1.16  最新版 

第一部分 搭建主主复制
1.1、创建复制帐号
主服务器
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*  TO rep@‘192.168.174.135‘ IDENTIFIED BY ‘rep‘;
另一个主服务器
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*  TO rep@‘192.168.174.139‘ IDENTIFIED BY ‘rep‘;

1.2、配置master
[client]
port = 3306
#socket = /tmp/mysql.sock
[mysqld_safe]
#socket = /tmp/mysql.sock
#nice = 0
[mysqld]
sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION‘
user = mysql
#socket = /tmp/mysql.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
log-error=/var/lib/mysql/centos3.err
pid-file=/var/lib/mysql/centos3.pid
# tmp dir settings ##
tmpdir = /tmp
tmp_table_size = 64M
max_heap_table_size=64M
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/slow.log
log-output = FILE
## character set ##
character-set-server = utf8
init_connect =‘set names utf8‘
skip-character-set-client-handshake = 1
lower_case_table_names=1
skip-log-warnings
skip-name-resolve
open_files_limit=65535
max_connections = 1000
max_connect_errors = 1000
wait_timeout = 864000
interactive_timeout=864000
connect_timeout = 5
max_allowed_packet = 16M
#table_cache = 256
thread_cache_size = 32
thread_concurrency = 8
#key_buffer_size = 16M #8M
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
innodb_fast_shutdown = 1
innodb_buffer_pool_size =200M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
#innodb_thread_concurrency = 24
#innodb_commit_concurrency = 24
#innodb_file_io_threads = 4
#innodb_read_io_threads = 8
#innodb_write_io_threads = 8
#innodb_log_file_size = 128M
#innodb_log_files_in_group = 3
#innodb_max_dirty_pages_pct = 90
# * Query Cache Configuration
query_cache_limit = 2M
query_cache_size = 16M
query_cache_type = 1
log-bin=centos3-bin
log-bin-index=centos3-bin.index
binlog_format=mixed
expire_logs_days = 15
binlog_cache_size = 1M
max_binlog_size = 128M
log_bin_trust_function_creators=1
#######################SERVER ID########################
server-id=1
log_slave_updates = 1
relay-log = centos3-relay-log.log
relay-log-index = centos3-relay-log.index
#######################MASTER########################
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
#max_relay_log_size = 50000000
[mysqldump]
quick 



1.3 配置从主
[client]
port = 3306
#socket = /tmp/mysql.sock
[mysqld_safe]
#socket = /tmp/mysql.sock
#nice = 0
[mysqld]
sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION‘
user = mysql
#socket = /tmp/mysql.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
log-error=/var/lib/mysql/centos4.err
pid-file=/var/lib/mysql/centos4.pid
# tmp dir settings ##
tmpdir = /tmp
tmp_table_size = 64M
max_heap_table_size=64M
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/lib/mysql/slow.log
log-output = FILE
## character set ##
character-set-server = utf8
init_connect =‘set names utf8‘
skip-character-set-client-handshake = 1
lower_case_table_names=1
skip-log-warnings
skip-name-resolve
open_files_limit=65535
max_connections = 1000
max_connect_errors = 1000
wait_timeout = 864000
interactive_timeout=864000
connect_timeout = 5
max_allowed_packet = 16M
#table_cache = 256
thread_cache_size = 32
thread_concurrency = 8
#key_buffer_size = 16M #8M
join_buffer_size = 2M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
innodb_fast_shutdown = 1
innodb_buffer_pool_size =200M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 10
#innodb_thread_concurrency = 24
#innodb_commit_concurrency = 24
#innodb_file_io_threads = 4
#innodb_read_io_threads = 8
#innodb_write_io_threads = 8
#innodb_log_file_size = 128M
#innodb_log_files_in_group = 3
#innodb_max_dirty_pages_pct = 90
# * Query Cache Configuration
query_cache_limit = 2M
query_cache_size = 16M
query_cache_type = 1
log-bin=centos4-bin
log-bin-index=centos4-bin.index
binlog_format=mixed
expire_logs_days = 15
binlog_cache_size = 1M
max_binlog_size = 128M
log_bin_trust_function_creators=1
#######################SERVER ID########################
server-id=2
log_slave_updates = 1
relay-log = centos4-relay-log.log
relay-log-index = centos4-relay-log.index
#######################MASTER########################
binlog-ignore-db=mysql
binlog-ignore-db=test
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
#max_relay_log_size = 50000000
[mysqldump]
quick 

1.4 重启主主 及 从主mysql服务
service mysqld restart
注意：从主要先备份mysql db 的5个innodb table,后面恢复热备数据库时用到，不然虽然启动成功，但错误日志会报错
脚本如下：
[root@localhost mysql]# cat /dba_script/get_mysql_innodb.sh 
#!/bin/bash
TABLELIST="innodb_index_stats" 
TABLELIST="${TABLELIST} innodb_table_stats" 
TABLELIST="${TABLELIST} slave_master_info" 
TABLELIST="${TABLELIST} slave_relay_log_info" 
TABLELIST="${TABLELIST} slave_worker_info" 
mysqldump -uroot -p mysql ${TABLELIST} > /dba_script/mysql_innodb_tables.sql
执行：
./dba_script/get_mysql_innodb.sh

1.5在主主热备数据库到从主
innobackupex --defaults-file=/etc/my.cnf  --user=root --password=admin --port=3306   --stream=tar /data/backup/full |gzip 1>/data/backup/full/masteralldb.tar.gz
-rw-r--r-- 1 root root 24304564 Apr 21 06:47 masteralldb.tar.gz

1.6 从主恢复masteralldb.tar.gz，并同步bin日志
tar -izxvf /data/backup/full/masteralldb.tar.gz -C /data/backup/full/
[root@localhost full]# ll 
total 101592 
-rw-r--r--. 1 root root 295 Apr 21 06:47 backup-my.cnf 
-rw-rw----. 1 root root 79691776 Apr 21 06:29 ibdata1 
-rw-r--r--. 1 root root 24304564 Apr 21 06:49 masteralldb.tar.gz
drwxr-xr-x. 2 root root 4096 Apr 21 06:51 mysql 
drwxr-xr-x. 2 root root 4096 Apr 21 06:51 testdb 
drwxr-xr-x. 2 root root 4096 Apr 21 06:51 testdb2 
-rw-r--r--. 1 root root 23 Apr 21 06:47 xtrabackup_binlog_info 
-rw-rw----. 1 root root 95 Apr 21 06:47 xtrabackup_checkpoints 
-rw-r--r--. 1 root root 617 Apr 21 06:47 xtrabackup_info 
-rw-rw----. 1 root root 2560 Apr 21 06:47 xtrabackup_logfile

[root@localhost full]# cat xtrabackup_binlog_info 
centos3-bin.000007 120

停止从主mysql服务
service mysql stop
备份mysql 数据目录，只拷贝需要恢复的DB和共享表空间，testdb testdb2 ibdata1
[root@localhost full]# cp -R -p /var/lib/mysql /var/lib/mysql_bak 
[root@localhost full]# cp -p -R testdb testdb2 ibdata1 /var/lib/mysql/
[root@localhost full]# chown -R mysql.mysql /var/lib/mysql
[root@localhost mysql]# service mysql start 
启动成功,但是在错误日志中看到如下错误警报：
2015-04-22 06:37:56 11330 [Warning] ‘user‘ entry ‘[email protected]‘ ignored in --skip-name-resolve mode. 
2015-04-22 06:37:56 11330 [Warning] ‘proxies_priv‘ entry ‘@ [email protected]‘ ignored in --skip-name-resolve mode. 
2015-04-22 06:37:56 11330 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 
2015-04-22 06:37:56 11330 [Warning] Info table is not ready to be used. Table ‘mysql.slave_master_info‘ cannot be opened. 
2015-04-22 06:37:56 11330 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 
2015-04-22 06:37:56 11330 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 
2015-04-22 06:37:56 11330 [Warning] Info table is not ready to be used. Table ‘mysql.slave_relay_log_info‘ cannot be opened. 
2015-04-22 06:37:56 11330 [Note] Event Scheduler: Loaded 0 events 
2015-04-22 06:37:56 11330 [Note] /usr/sbin/mysqld: ready for connections. 
Version: ‘5.6.17-log‘ socket: ‘/var/lib/mysql/mysql.sock‘ port: 3306 MySQL Community Server (GPL) 
2015-04-22 06:39:07 7f32ac141700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 
2015-04-22 06:39:07 7f32ac141700 InnoDB: Error: Fetch of persistent statistics requested for table "testdb"."student" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead. 
2015-04-22 06:39:23 7f32ac141700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found. 
2015-04-22 06:39:23 7f32ac141700 InnoDB: Error: Fetch of persistent statistics requested for table "testdb2"."student2" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
当写入数据库还会报如下错：
2015-04-22 06:52:24 7f32695a5700 InnoDB: Recalculation of persistent statistics requested for table "testdb2"."student2" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2015-04-22 06:52:35 7f32695a5700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2015-04-22 06:52:35 7f32695a5700 InnoDB: Recalculation of persistent statistics requested for table "testdb2"."student2" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.


在从主上查询mysql db 自己的5个innodb table,因为之前更换了共享表空间，所以下面的innodb table出现了问题
[root@localhost mysql]# ll /var/lib/mysql/mysql/*.ibd 
-rw-------. 1 mysql mysql 98304 Apr 21 06:39 /var/lib/mysql/mysql/innodb_index_stats.ibd 
-rw-------. 1 mysql mysql 98304 Apr 21 06:39 /var/lib/mysql/mysql/innodb_table_stats.ibd 
-rw-------. 1 mysql mysql 98304 Mar 20 22:38 /var/lib/mysql/mysql/slave_master_info.ibd 
-rw-------. 1 mysql mysql 98304 Mar 20 22:38 /var/lib/mysql/mysql/slave_relay_log_info.ibd 
-rw-------. 1 mysql mysql 98304 Mar 20 22:38 /var/lib/mysql/mysql/slave_worker_info.ibd
现删除
[root@localhost mysql]# rm -rf /var/lib/mysql/mysql/*.ibd
用之前的脚本重建，如果不删除直接重建会报错
[root@localhost dba_script]# mysql -uroot -p mysql < /dba_script/mysql_innodb_tables.sql 
Enter password: 
ERROR 1813 (HY000) at line 25: Tablespace for table ‘`mysql`.`innodb_index_stats`‘ exists. Please DISCARD the tablespace before IMPORT.


1.7 搭建互为slave
从主服务器
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.174.139‘, MASTER_USER=‘rep‘,MASTER_PASSWORD=‘rep‘, MASTER_LOG_FILE=‘centos3-bin.000008‘, MASTER_LOG_POS=120;
主主服务器
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.174.135‘, MASTER_USER=‘rep‘,MASTER_PASSWORD=‘rep‘, MASTER_LOG_FILE=‘centos4-bin.000006‘, MASTER_LOG_POS=3329;

1.8主从都开启slave
mysql>start slave;
mysql>show slave status \G;
主主配置成功！

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

标签： mysql主主复制 keepalivedmysql主主复制 keepalived

相关文章

随机文章

您可能还喜欢

您可能还喜欢