MySQL多实例实现半同步复制

MySQL多实例实现半同步复制

主库1：192.168.40.150

主库2：192.168.40.161

从库（2个MySQL实例）：192.168.40.162

一：分别在192.168.40.161和192.168.40.150使用【show master status;】记录当前的复制位置

如下所示

mysql> show master status;

+--------------+----------+--------------+------------------+-------------------+

| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+--------------+----------+--------------+------------------+-------------------+

| mysql.000006 | 11426362 |              |                  |                   |

+--------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

二：master（分别在192.168.40.161和192.168.40.150进行如下操作）

1.启用二进制日志

log_bin=mysql

log-bin-index=mysql-index

2.为master选择一个在当前复制架构中惟一的server-id

server-id={0-2^32}

3.创建一个具有复制权限的用户帐号

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.40.162' IDENTIFIED BY 'replpass';

mysql> FLUSH PRIVILEGES;

4.分别master的配置文件中的mysqld段添加如下一行，并重启服务

rpl_semi_sync_master_enabled=ON （或者：mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';）

mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';

mysql> SET GLOBAL rpl_semi_sync_master_enabled=ON;

mysql> SET GLOBAL rpl_semi_sync_master_timeout=1000;

5.配置主从复制时的事务安全:

在主服务器上mysqld段配置：sync_binlog=1

6.重启mysql服务

二、slave（在192.168.40.162的两个mysql实例中进行如下操作）

1.在192.168.40.162配置两个实例:socket端口分别为3306和3307，具体配置请参考配置文件。

2.创建所需要的目录

mkdir -p /data/{mysql3306,mysql3307}

3.分别对192.168.40.161和192.168.40.150的数据目录打包，然后分别上传至192.168.40.162的/data/mysql3306和/data/mysql3307目录下

例如：

# cd /data/mysql/

# tar zcf mysql_162.tar.gz ./*

# scp mysql_162.tar.gz 192.168.40.162:/data/mysql3306/

4.启用中继日志（并关闭二进制日志）

relay-log=/data/mysql3306/relay-3306.log

relay-log-index=/data/mysql3306/relay-log-index-3306.log

5.为slave选择一个在当前复制架构中惟一的server-id

server-id={0-2^32}

6.为slave选择一个在当前复制架构中惟一的server-uuid

修数据目录下的auto.cnf 修数据目录下的auto.cnf 的server-uuid

如将

server-uuid=3fd1f0a1-b34e-11e4-996a-000c29b1b59d

修改为

server-uuid=3fd1f0a1-b34e-11e4-996a-000c29b1b52d

5、复制过滤器

slave:

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

8.重启mysql服务

9.开启半同步复制

从服务器：

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=ON;

在主服务器验正半同步复制是否生效：

mysql> SHOW GLOBAL STATUS LIKE '%semi%';

9.连接至主服务器

mysql> CHANGE MASTER TO MASTER_HOST='192.168.40.161', MASTER_USER='repluser',  MASTER_PASSWORD='replpass',  MASTER_PORT=3306,  MASTER_LOG_FILE='mysql.000006', MASTER_LOG_POS=11426362;

mysql> START SLAVE;      

10.检查主从是否成功

mysql> show slave status;

主服务器配置文件：[root@db_peizi1 ~]# more /etc/my.cnf[client]port=3306socket = /usr/local/mysql/mysql.sock[mysql]default-character-set=utf8[mysqld]port            = 3306socket          = /usr/local/mysql/mysql.sockbasedir=/usr/local/mysqldatadir=/data/mysqlserver_id=1user=mysqlskip-name-resolvelog_bin=mysqlexpire_logs_days = 30sync_binlog=1rpl_semi_sync_master_enabled=ON#slow_logslow-query-log=Onslow_query_log_file=/data/logs/mysql/peizi-slow.loglong_query_time=1# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0character-set-server=utf8default-storage-engine=InnoDBexplicit_defaults_for_timestamp=trueskip-external-lockingmax_connections=300query_cache_size=1048576performance_schema_max_table_instances=600table_definition_cache=400table_open_cache=256tmp_table_size=64Mmax_heap_table_size=64Mthread_cache_size=16myisam_max_sort_file_size=16Gmyisam_sort_buffer_size=32Mkey_buffer_size=25Mread_buffer_size=128Kread_rnd_buffer_size=256Ksort_buffer_size=256Kjoin_buffer_size=16Mmax_allowed_packet=4Minnodb_file_per_table=1innodb_flush_log_at_trx_commit=1innodb_log_buffer_size=2Minnodb_buffer_pool_size=64Minnodb_log_file_size=16Minnodb_thread_concurrency=8sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"[mysqld_safe]log-error=/data/logs/mysql/mysqld.logpid-file=/usr/local/mysql/tmp/mysqld.pid多实例从服务器配置文件：[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladmin[mysql]default-character-set=utf8[mysqld1]port            = 3306socket          = /usr/local/mysql/mysql-3306.sockbasedir=/usr/local/mysqldatadir=/data/mysql3306pid-file=/var/lock/subsys/mysql3306/mysq-3306.pidserver_id=11user=mysqlskip-name-resolveexpire_logs_days = 30master-info-file=/data/mysql3306/master-3306.inforead-onlyrelay-log=/data/mysql3306/relay-3306.logrelay-log-index=/data/mysql3306/relay-log-index-3306.logreplicate-ignore-db=mysqlreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schema# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0character-set-server=utf8default-storage-engine=InnoDBexplicit_defaults_for_timestamp=trueskip-external-lockingmax_connections=300query_cache_size=1048576performance_schema_max_table_instances=600table_definition_cache=400table_open_cache=256tmp_table_size=64Mthread_cache_size=16myisam_max_sort_file_size=16Gmyisam_sort_buffer_size=32Mkey_buffer_size=25Mread_buffer_size=128Kread_rnd_buffer_size=256Ksort_buffer_size=256Kjoin_buffer_size=16Mmax_allowed_packet=4Minnodb_file_per_table=1innodb_flush_log_at_trx_commit=1innodb_log_buffer_size=2Minnodb_buffer_pool_size=64Minnodb_log_file_size=8Minnodb_thread_concurrency=8lower_case_table_names=1sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"[mysqld2]port            = 3307socket          = /usr/local/mysql/mysql-3307.sockbasedir=/usr/local/mysqldatadir=/data/mysql3307pid-file=/var/lock/subsys/mysql3307/mysql-3307.pidserver_id=6user=mysqlskip-name-resolveexpire_logs_days = 30master-info-file=/data/mysql3307/master-3307.inforead-onlyrelay-log=/data/mysql3307/relay-3307.logrelay-log-index=/data/mysql3307/relay-log-index-3307.logreplicate-ignore-db=mysqlreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schema# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0character-set-server=utf8default-storage-engine=InnoDBexplicit_defaults_for_timestamp=trueskip-external-lockingmax_connections=300query_cache_size=1048576performance_schema_max_table_instances=600table_definition_cache=400table_open_cache=256tmp_table_size=64Mthread_cache_size=16myisam_max_sort_file_size=16Gmyisam_sort_buffer_size=32Mkey_buffer_size=25Mread_buffer_size=128Kread_rnd_buffer_size=256Ksort_buffer_size=256Kjoin_buffer_size=16Mmax_allowed_packet=4Minnodb_file_per_table=1innodb_flush_log_at_trx_commit=1innodb_log_buffer_size=2Minnodb_buffer_pool_size=64Minnodb_log_file_size=8Minnodb_thread_concurrency=8lower_case_table_names=1sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"