mysqlmha高可用架构的安装 - 数据库编程

ry_cache_type = 1 query_cache_size = 32M ft_min_word_len = 4 thread_stack = 192K tmp_table_size = 64M #myisam内存设置 key_buffer_size=1024M #允许最大的复制传输 max_allowed_packet=64M #跳过dns解析 skip-name-resolve #连接数设置 max_connections = 1000 max_connect_errors = 200 #innodb设置 innodb_buffer_pool_size = 1G innodb_additional_mem_pool_size = 16M innodb_log_buffer_size = 8M innodb_log_file_size = 512M innodb_log_files_in_group = 3 innodb_file_per_table=1 innodb_stats_persistent_sample_pages=1000 innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 30 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES # service mysqld start
1.6.1、在yaolansvr做备份，并创建复制用户

# mysqldump -A --flush-privileges --lock-all-tables --events --routines --triggers --master-data=2>/yangsq/ftp/`date +%Y-%m-%d`_all.sql


mysql> grant replication slave on *.* to 'repl1'@'192.168.0.%' identified by '123456';
mysql> flush privileges;

1.6.2、使用ftp从yaolansvr获取整库的备份，并恢复

# mysql<2015-06-29_all.sql
# head -n 30 2015-06-29_all.sql |grep -i "change master"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=120;


mysql> show slave status;
Empty set (0.00 sec)
mysql> change master to master_host='192.168.0.3',master_user='repl1',master_password='123456',master_port=3306,master_log_file='mysql-bin.000008',master_log_pos=120;
mysql> start slave;
Last_IO_Errno: 1593
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

解决：两台datadir/auto.cnf一样，select uuid()不同，所以删除candidate master上的auto.cnf，重新启动实例

重启mysql实例后：

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.3
Master_User: repl1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 408
Relay_Log_File: yaolansvr_slave-relay-bin.000003
Relay_Log_Pos: 571
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

1.6.3、其他的slave节点设置read_only=1（不写入my.cnf，以供candidate master转为主后，提供写）
# mysql -e "set global read_only=1"

1.6.4、注意******************所有数据库节点都要创建复制用户
（1）candidate master不创建复制用户，则报错：

Mon Jun 29 17:28:00 2015 - [info] Alive Slaves:
Mon Jun 29 17:28:00 2015 - [info] 192.168.0.4(192.168.0.4:3306) Version=5.6.20-log (oldest major version between slaves) log-bin:enabled
Mon Jun 29 17:28:00 2015 - [info] Replicating from 192.168.0.3(192.168.0.3:3306)
Mon Jun 29 17:28:00 2015 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 29 17:28:00 2015 - [info] 192.168.0.5(192.168.0.5:3306) Version=5.6.20-log (oldest major version between slaves) log-bin:enabled
Mon Jun 29 17:28:00 2015 - [info] Replicating from 192.168.0.3(192.168.0.3:3306)
Mon Jun 29 17:28:00 2015 - [info] Not candidate for the new Master (no_master is set)
Mon Jun 29 17:28:00 2015 - [info] Current Alive Master: 192.168.0.3(192.168.0.3:3306)
Mon Jun 29 17:28:00 2015 - [info] Checking slave configurations..
Mon Jun 29 17:28:00 2015 - [info] Checking replication filtering settings..
Mon Jun 29 17:28:00 2015 - [info] binlog_do_db= , binlog_ignore_db=
Mon Jun 29 17:28:00 2015 - [info] Replication filtering ch

mysqlmha高可用架构的安装(四)