MySQL多实例实现半同步复制

fdfdfwe

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;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186

主服务器配置文件： [iyunv@db_peizi1 ~]# more /etc/my.cnf [client] port=3306 socket = /usr/local/mysql/mysql.sock [mysql] default-character-set=utf8 [mysqld] port            = 3306 socket          = /usr/local/mysql/mysql.sock basedir=/usr/local/mysql datadir=/data/mysql server_id=1 user=mysql skip-name-resolve log_bin=mysql expire_logs_days = 30 sync_binlog=1 rpl_semi_sync_master_enabled=ON #slow_log slow-query-log=On slow_query_log_file=/data/logs/mysql/peizi-slow.log long_query_time=1 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 default-storage-engine=InnoDB explicit_defaults_for_timestamp=true skip-external-locking max_connections=300 query_cache_size=1048576 performance_schema_max_table_instances=600 table_definition_cache=400 table_open_cache=256 tmp_table_size=64M max_heap_table_size=64M thread_cache_size=16 myisam_max_sort_file_size=16G myisam_sort_buffer_size=32M key_buffer_size=25M read_buffer_size=128K read_rnd_buffer_size=256K sort_buffer_size=256K join_buffer_size=16M max_allowed_packet=4M innodb_file_per_table=1 innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=2M innodb_buffer_pool_size=64M innodb_log_file_size=16M innodb_thread_concurrency=8 sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" [mysqld_safe] log-error=/data/logs/mysql/mysqld.log pid-file=/usr/local/mysql/tmp/mysqld.pid 多实例从服务器配置文件： [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin [mysql] default-character-set=utf8 [mysqld1] port            = 3306 socket          = /usr/local/mysql/mysql-3306.sock basedir=/usr/local/mysql datadir=/data/mysql3306 pid-file=/var/lock/subsys/mysql3306/mysq-3306.pid server_id=11 user=mysql skip-name-resolve expire_logs_days = 30 master-info-file=/data/mysql3306/master-3306.info read-only relay-log=/data/mysql3306/relay-3306.log relay-log-index=/data/mysql3306/relay-log-index-3306.log replicate-ignore-db=mysql replicate-ignore-db=information_schema replicate-ignore-db=performance_schema # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 default-storage-engine=InnoDB explicit_defaults_for_timestamp=true skip-external-locking max_connections=300 query_cache_size=1048576 performance_schema_max_table_instances=600 table_definition_cache=400 table_open_cache=256 tmp_table_size=64M thread_cache_size=16 myisam_max_sort_file_size=16G myisam_sort_buffer_size=32M key_buffer_size=25M read_buffer_size=128K read_rnd_buffer_size=256K sort_buffer_size=256K join_buffer_size=16M max_allowed_packet=4M innodb_file_per_table=1 innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=2M innodb_buffer_pool_size=64M innodb_log_file_size=8M innodb_thread_concurrency=8 lower_case_table_names=1 sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" [mysqld2] port            = 3307 socket          = /usr/local/mysql/mysql-3307.sock basedir=/usr/local/mysql datadir=/data/mysql3307 pid-file=/var/lock/subsys/mysql3307/mysql-3307.pid server_id=6 user=mysql skip-name-resolve expire_logs_days = 30 master-info-file=/data/mysql3307/master-3307.info read-only relay-log=/data/mysql3307/relay-3307.log relay-log-index=/data/mysql3307/relay-log-index-3307.log replicate-ignore-db=mysql replicate-ignore-db=information_schema replicate-ignore-db=performance_schema # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 default-storage-engine=InnoDB explicit_defaults_for_timestamp=true skip-external-locking max_connections=300 query_cache_size=1048576 performance_schema_max_table_instances=600 table_definition_cache=400 table_open_cache=256 tmp_table_size=64M thread_cache_size=16 myisam_max_sort_file_size=16G myisam_sort_buffer_size=32M key_buffer_size=25M read_buffer_size=128K read_rnd_buffer_size=256K sort_buffer_size=256K join_buffer_size=16M max_allowed_packet=4M innodb_file_per_table=1 innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=2M innodb_buffer_pool_size=64M innodb_log_file_size=8M innodb_thread_concurrency=8 lower_case_table_names=1 sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"