mysql高可用性探究

1.mmm方案

MMM即:Master-Master Replication Manager For MySQL,MySQL主主复制管理器的功能包括监控、故障转移和等一系列脚本构成,
这个脚本也能对基本的主从复制配置的任意数量的从服务器进行读负载均衡，所以可以用它来实现一组居于复制的虚拟IP,同时它还有数据备份、节点之间重新同步功能的能力.

IP
DB1:192.168.11.198
DB2:192.168.11.88
DB3:192.168.11.238
MONITOR:192.168.11.116

结构图如下:

db1
[mysql@localhost ~]$ sudo cat /etc/my.cnf 
[sudo] password for mysql: 
[client]
socket=/tmp/mysql.sock

[mysqld]
server-id=2
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-admin-statements
innodb_buffer_pool_size=50M
innodb_flush_log_at_trx_commit=1
max_allowed_packet=100M
binlog_format=mixed
log-bin=/mysql/log/mysql-bin
log_bin_trust_function_creators = 1
innodb_fast_shutdown = 0
binlog-do-db=test
replicate-do-db=test
log-slave-updates=on
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid

db2
[mysql@localhost ~]$ sudo cat /etc/my.cnf 
[client]
socket=/tmp/mysql.sock

[mysqld]
server-id=4
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=InnoDB
character_set_server=utf8
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
log-queries-not-using-indexes
log-slow-admin-statements
log_bin_trust_function_creators = 1
log-bin=/mysql/log/mysql-bin
report_host=192.168.23.164
binlog_format=mixed
log-bin=/mysql/log/mysql-bin
binlog-do-db=test
replicate-do-db=test
log-slave-updates=on
slave-skip-errors=1007,1050,1146,1051
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid

db3
[client]
socket=/tmp/mysql.sock
port=3306

[mysqld]
server-id=3
port=3306
basedir=/usr/local/mysql
datadir=/mysql/data
socket=/tmp/mysql.sock
user=mysql
default_storage_engine=innodb
character_set_server=utf8
log-bin=/mysql/log/mysql-bin
slave-skip-errors=1007,1050
slow_query_log=1
slow_query_log_file=/mysql/slowquery.log
long_query_time=2
relay-log=relay-bin   
relay-log-index=relay-bin.index
binlog_format=mixed
log-slave-updates=on
replicate-do-db=test
slave-skip-errors=1146
[mysqld_safe]
log-error=/mysql/mysqld.log
pid-file=/mysql/mysqld.pid 

三、主从配置(master1和master2配置成主主,slave1配置成master1的从)
1、在master1上授权:
grant replication slave on *.* to repl@'192.168.11.198' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.88' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.238' identified by "XXXX";flush privileges;
 
2、在master2上授权:
grant replication slave on *.* to repl@'192.168.11.198' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.88' identified by "XXXX";
grant replication slave on *.* to repl@'192.168.11.238' identified by "XXXX";flush privileges;

在master2、slave1执行
change master to master_host='192.168.11.198', master_port=3306, master_user='repl', master_password='XXXX';start slave;
把master1配置成master2的从库:
change master to master_host='192.168.11.88', master_port=3306, master_user='repl', master_password='XXXX';start slave;

在各个机器上执行:
db1
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.88
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000024
          Read_Master_Log_Pos: 124156
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 1068
        Relay_Master_Log_File: mysql-bin.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 124156
              Relay_Log_Space: 1242
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 4
                  Master_UUID: a7e4c60d-62ca-11e3-8710-080027e08a30
             Master_Info_File: /mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it<