Marco Learning Note 26--mysql master-slave replication

Basic steps for configuring MySQL replication:

First, Master

1. Enable the binary log

Log-bin = Master-bin

Log-bin-index = Master-bin.index

2, choose a unique Server-id

Server-id = {0-2^32}

3. Create a user with copy permissions

REPLICATION SLAVE

REPLICATION CLIENT

Second, slave

1. Enable the relay log

Relay-log = Relay-log

Relay-log-index =

2, choose a unique Server-id

Server-id = {0-2^32}

3, connect to the master server, and start copying data;

Mysql> CHANGER MASTER to master_host= ', master_port= ', master_log_file= ', master_log_pos= ', master_user= ', Master_password= ";

mysql> start SLAVE; (Startup thread)

mysql> START SLAVE Io_thread;

mysql> START SLAVE Sql_thread;

Replication Threads:
Master:dump
Slave:io_thread, Sql_thread

Read-only = YES

Set on the server, but not for users with super privileges;

Sync-binlog = On

Set on the primary server for transaction security;

To set up a semi-synchronous step: (Plug in/usr/local/mysql/lib/plugin)

Run the following code on the MySQL command line in master and slave:

# on Master

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;

# on Slave

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

mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

mysql> STOP SLAVE Io_thread; START SLAVE Io_thread;

Edit in master and slave my.cnf:

# on Master

[Mysqld]

Rpl_semi_sync_master_enabled=1

rpl_semi_sync_master_timeout=1000 # 1 Second

# on Slave

[Mysqld]

Rpl_semi_sync_slave_enabled=1

# can also be set by setting a global variable, as follows:

Set Global rpl_semi_sync_master_enabled=1

# Cancel Loading Plugin

mysql> UNINSTALL PLUGIN Rpl_semi_sync_master;

See if the semi_sync from the server is turned on:

Mysql> SHOW GLOBAL STATUS like ' rpl_semi% ';

To see if the Semi_sync on the primary server is turned on, note that clients becomes 1, proving that the master-slave semi-synchronous replication connection succeeds:

Mysql> SHOW GLOBAL STATUS like ' rpl_semi% ';

Percona:percona-tools (Master-slave Replication management tool)
Mattkit-tools

Database replication filtering

Implemented on the primary server:

Binlog-do-db=mydb

Binlog-ignore-db=mysql

From the server:

replicate_do_db

rpplicate_ignore_db

Replicate_do_table

Replicate_ignore_table

Replicate_wild_do_table (can be matched by wildcard characters)

Replicate_wild_ignore_table