利用keepalived构建高可用MySQL-HA
时间：2022-03-14 09:15
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,后面恢复热备数据库时用到，不然虽然启动成功，但错误日志会报错
脚本如下：
[ 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日志
[ full]# scp masteralldb.tar.gz :/data/backup/full
tar -izxvf /data/backup/full/masteralldb.tar.gz -C /data/backup/full/
[ 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
[ full]# cat xtrabackup_binlog_info
centos3-bin.000007 120
停止从主mysql服务
service mysql stop
备份mysql 数据目录，只拷贝需要恢复的DB和共享表空间，testdb testdb2 ibdata1
[ full]# cp -R -p /var/lib/mysql /var/lib/mysql_bak
[ full]# cp -p -R testdb testdb2 ibdata1 /var/lib/mysql/
[ full]#
chown -R mysql.mysql /var/lib/mysql
[ mysql]# service mysql start
启动成功,但是在错误日志中看到如下错误警报：
2015-04-22 06:37:56 11330 [Warning] ‘user‘ entry ‘‘
ignored in --skip-name-resolve mode.
2015-04-22 06:37:56 11330 [Warning] ‘proxies_priv‘ entry ‘@ ‘
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 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 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 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出现了问题
[ 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
现删除
[ mysql]# rm -rf /var/lib/mysql/mysql/*.ibd
用之前的脚本重建，如果不删除直接重建会报错
[ 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;
主主配置成功！
