MySQL Master-slave configuration

The role of replication:
Secondary implementation Backup
Highly Available
Remote disaster recovery
Scale out: Load sharing

Server-id is important in replication.

Master-Slave architecture, do not use the MySQL agent, how to let the master responsible for writing, from the responsible read?
1, using the front-end development program to control read and write separation (increased development difficulty)
2. Dual master model (load sharing for read operations, no load sharing for write operations)

Read/write Separation:
Mysql-proxy
Amoeba

Data splitting:
Cobar

A slave server can only belong to one master server

MySQL 5.5: The replication feature is simple to implement
MySQL 5.6:gtid (more secure), Multi-thread replication (multithreaded 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;

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; (restart service after completion, replication thread will also start)

Sync-binlog = On
Set on the primary server for transaction security;

1. Can I perform a "write" operation from the server?
CREATE
INSERT
How do I block write from the server?
My.cnf
[Mysqld]
Read-only = 1

Cannot block SQL Thread
If a user has super privilege, it is not blocked;
Mysql> FLUSH TABLES with READ LOCK;

2, a master server can be more from? OK

Is there more than one master? No way

3.----From: Async

MySQL 5.5 Google patch
Semi-synchronous: Semisync

Semi-synchronous if not completed at the specified time--automatically drops to asynchronous mode;

4. How does the MySQL service from the server not start automatically from the service thread at startup time?

Master.info
Relay-log.info

On the slave server:
[Mysqld]
Skip-slave-start=1


5. Database Replication filtering

Implemented on the primary server:
Binlog-do-db=testdb
Binlog-do-db=mydb

Binlog-ignore-db=mysql



Primary server
[Mysqld]
binlog-do-db=
OR
binlog-ignore-db=


Filtering on the primary server: Any write operations that do not involve database related are not recorded in the binary log;

From the server:
replicate_do_db
rpplicate_ignore_db

Replicate_do_table
Replicate_ignore_table

Replicate_wild_do_table
Replicate_wild_ignore_table


To replicate only mageedu one database from the server:

[Mysqld]
replicate_do_db=mageedu
Replicate_do_db=mysql

To set up a semi-synchronous step:

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% ';

Tools:
Percona:percona-tools
Mattkit-tools


Set primary-primary replication:
1. Establish a user with copy permission on both servers;
2. Modify the configuration file:
# on the master server
[Mysqld]
Server-id = 10
Log-bin = Mysql-bin
Relay-log = Relay-mysql
Relay-log-index = Relay-mysql.index
Auto-increment-increment = 2
Auto-increment-offset = 1

#auto-increment-increment,auto-increment-offset is used to resolve the merge table error when auto-grow, an odd number, an even number, a starting point of 1, and an increase of 2


# from the server
[Mysqld]
Server-id = 20
Log-bin = Mysql-bin
Relay-log = Relay-mysql
Relay-log-index = Relay-mysql.index
Auto-increment-increment = 2
Auto-increment-offset = 2

3, if the two servers are newly established, and no other write operations, each server only need to record the current binary log files and the location of the event, as another server replication starting location can be

server1|mysql> SHOW MASTER Status\g
1. Row ***************************
file:mysql-bin.000001
position:710
binlog_do_db:
binlog_ignore_db:
1 row in Set (0.00 sec)

server2|mysql> SHOW MASTER Status\g
mysql> SHOW MASTER Status\g
1. Row ***************************
file:mysql-bin.000003
position:811
binlog_do_db:
binlog_ignore_db:
1 row in Set (0.00 sec)

4, each server next specify to another server as its own master server can:
server1|mysql> change MASTER to ..., master_log_file= ' mysql-bin.000003 ', master_log_pos=811

server2|mysql> change MASTER to ..., master_log_file= ' mysql-bin.000001 ', master_log_pos=710

A: View B's binary log file and location, and as a starting point for your own copy;
B:

This article is from the "bustling Down" blog, please be sure to keep this source http://chenxujiang.blog.51cto.com/11737025/1870704

MySQL Master-slave configuration