MySQL multi-instance for semi-synchronous replication

MySQL multi-instance for semi-synchronous replication

MySQL multi-instance for semi-synchronous replication

Main Library 1:192.168.40.150

Main Library 2:192.168.40.161

From a library (2 MySQL instances): 192.168.40.162

One: Use "Show Master Status" in 192.168.40.161 and 192.168.40.150, respectively, to record the current copy location

As shown below

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)

II: Master (in 192.168.40.161 and 192.168.40.150, respectively)

1. Enable the binary log

Log_bin=mysql

Log-bin-index=mysql-index

2. Select a Server-id for master that is unique in the current replication schema

SERVER-ID={0-2^32}

3. Create a user account with copy permissions

Mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT on *. * to ' repluser ' @ ' 192.168.40.162 ' identified by ' replpass ';

mysql> FLUSH privileges;

4. Add the following line to the MYSQLD segment in the master configuration file, and restart the service

Rpl_semi_sync_master_enabled=on (or: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. Configure transaction security at master-slave replication:

MYSQLD segment configuration on the primary server: Sync_binlog=1

6. Restart the MySQL service

Second, slave (do the following in the two MySQL instances of 192.168.40.162)

1. Configure two instances in 192.168.40.162: Socket ports are 3306 and 3307 respectively, please refer to the configuration file for specific configuration.

2. Create the required directory

Mkdir-p/data/{mysql3306,mysql3307}

3. Package the data catalogs for 192.168.40.161 and 192.168.40.150 separately and upload them to the 192.168.40.162/data/mysql3306 and/data/mysql3307 directories, respectively.

For example:

# cd/data/mysql/

# tar ZCF mysql_162.tar.gz./*

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

4. Enable the relay log (and turn off binary logging)

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

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

5. Select a Server-id for slave that is unique in the current replication schema

SERVER-ID={0-2^32}

6. Select a server-uuid for slave that is unique in the current replication schema

Repair Data directory under the AUTO.CNF repair Data directory under the AUTO.CNF Server-uuid

If you

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

Revision changed to

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

5. Copy Filter

Slave

Replicate-ignore-db=mysql

Replicate-ignore-db=information_schema

Replicate-ignore-db=performance_schema

8. Restart the MySQL service

9. Turn on semi-synchronous replication

From the server:

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

mysql> SET GLOBAL Rpl_semi_sync_slave_enabled=on;

If the primary server is verifying that half-synchronous replication is in effect:

Mysql> SHOW GLOBAL STATUS like '%semi% ';

9. Connect to the primary server

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. Check whether master/slave is successful

Mysql> Show slave status;

Primary server configuration file: [[email protected]_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 multiple instances from the server configuration file: [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 "

This article is from the Linux tours blog, so be sure to keep this source http://openlinuxfly.blog.51cto.com/7120723/1643835

MySQL multi-instance for semi-synchronous replication