mysql-主主服务器同步搭建-半同步方式

sdhgf

实验版本mysql-5.5.30
实现过程大部分和 LINK：mysql-主从服务器同步搭建 一样。
实验环境: 系统 centos5.5-x86_64，服务器IP 192.168.0.47、192.168.0.100。
前提，主从服务器已经搭建成功47是主服务器，100是从服务器
开启半同步插件(master/slave),添加备份账号mslave

mysql -u root -pyour_passwd -S /tmp/mysql.sock <<EOF
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';GRANT REPLICATION SLAVE ON *.* TO "mslave"@"%" IDENTIFIED BY 'mslave_passwd';EOF

修改my.cnf

[mysqld]port            = 3335socket          = /tmp/mysql.sock
datadir         = /usr/local/mysql/var/log-error=/usr/local/mysql/var/log/mysqld.logskip-external-locking
key_buffer_size = 256M#导入数据库表过大要提高max_allowed_packet值max_allowed_packet = 32M
table_open_cache = 256sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8query_cache_size= 16M# Try number of CPU's*2 for thread_concurrencythread_concurrency = 16log-queries-not-using-indexes = onlog-bin=/usr/local/mysql/var/mysql-bin
report-user = mslave
report-host = 192.168.0.47 /192.168.0.100
report-port = 3335relay-log = /usr/local/mysql/var/log/relay/mysql-relay
relay-log-info-file = /usr/local/mysql/var/log/relay/mysql-relay.info
log_slave_updates = /usr/local/mysql/var/log/mysql_slave_updates.logbinlog-do-db = wp
binlog-do-db = phpmyadmin
replicate-do-db = wp
replicate-do-db = phpmyadmin
#开启半同步masterrpl_semi_sync_master_enabled=1#响应超过1000ms自动降级异步rpl_semi_sync_master_timeout=1000#开启半同步slaverpl_semi_sync_slave_enabled=1#提交立刻写日志sync_binlog=1binlog_format=mixed
server-id       = 3503innodb_data_home_dir = /usr/local/mysql-35/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql-35/var
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 128M# Set .._log_file_size to 25 % of buffer pool sizeinnodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 50

重启各服务器节点
主从服务器已经同步至一致的二进制节点，服务器锁表状态，记录 File | Position 的值
[47 服务器]

mysql -u root -pyour_passwd -S /tmp/mysql.sock
mysql> FLUSH TABLES WITH READ LOCK;mysql> show master status;+------------------+----------+---------------+------------------+| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |+------------------+----------+---------------+------------------+| mysql-bin.000008 |      338 | wp,phpmyadmin |                  | +------------------+----------+---------------+------------------+1 row in set (0.00 sec)

//转至100服务器，等待从服务器同步至338节点 show slave status\G;
[100 服务器]

mysql -u root -pyour_passwd -S /tmp/mysql.sock
mysql> show slave status\G;...
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.47
                  Master_User: mslave
                  Master_Port: 3335
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 338...1 row in set (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK;mysql> show master status;+------------------+----------+---------------+------------------+| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |+------------------+----------+---------------+------------------+| mysql-bin.000008 |      335 | wp,phpmyadmin |                  | +------------------+----------+---------------+------------------+1 row in set (0.00 sec)

//转至47服务器，等待从服务器同步至335节点 show slave status\G;
[47 服务器]

mysql> UNLOCK TABLES;mysql> stop slave;mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.100',MASTER_USER='mslave',MASTER_PASSWORD='mslave_passwd',MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=335,MASTER_PORT=3335;mysql> start slave;mysql> show slave status\G;...
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.100
                  Master_User: mslave
                  Master_Port: 3335
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 335...1 row in set (0.00 sec)

//转至100服务器，解锁

mysql> UNLOCK TABLES;