Phase IV (VII) MySQL REPLICATION (Master-slave replication, semi-synchronous replication, replication filtering)

Linux Operation phase IV (VII) MySQL REPLICATION (Master-slave replication, semi-synchronous replication, replication filtering)

First, MySQL Replication Related concepts:

1, the role of replication: to support the implementation of backup; ha, offsite disaster tolerance, load sharing (scaleout);rw-spliting (MySQL proxy works at the application layer ) .

2. Master has multiple CPUs allowing transactions to be executed in parallel, but the binary log files can only be written in one section;slave is slower than master; Master-slave is transmitted by default asynchronously.

3, semi-synchronous: only responsible for the most recent slave synchronization success, the other slave no matter,5.5 not support semi-synchronous, semi-synchronous should specify timeout interval, If the timeout is degraded, it continues to work in asynchronous mode.

4,slave-server itself can write operations, but in the master-slave schema is not allowed slave write, because it cannot sync to other servers .

5,slave can do cold backup to master.

6,the master binary log file and the slave log file on The file will not be the same size and number of files, the restore can only use the master binary log file, cannot use the relay log file.

7, local must have a relay log and binary log;slave-side as long as not to do multi-level replication, you can not binary log files; multi-level replication reduces master replication pressure;slave-side The storage engine can be blackhole.

8. If Master-side is down, slave-side can be promoted to Master(execute binary log) for high availability.

Slave-side,Io_thread is used to receive Dump_thread binary log statements from the main side and then save them as local relay logs,Sql_thread Read the relay log to a successful data file before saving the binary log.

9,server-id avoid cyclic replication.

Thedual master cannot alleviate the write operation.

One, master and slave architecture, do not use mysql-proxy, how to let Master write,slave read: Let the program (PHP developed by the program) itself has read and write separation function ; double-master model.

Two-master models are not recommended for production environments.

the problem with the double-master model: When two people change different fields or insert data from the same table at the same time, the commit may cause the database to crash or have a non-routine error.

When thedatabase server pressure is large, two scenarios:scaleout;

Scaleout: According to the business Sub-Library, each business involved in the library placed on a physical server (vertical splitting), but the data has a hot zone, for example,100G of data, only 1 G very BUSY, other very busy, and this 1G data in a table; split (horizontal split),RID (row ID).

Note: It is difficult to troubleshoot subsequent problems without dismantling.

Onecan only have one master, and one master may have more from it.

/read/write separation:mysql-proxy,Amoeba (Java), configuration file XML format

Coba(Amoeba): Data splitting.

Replication Threads:master-side (dump),slave-side (Io_thread,sql_thread)

MySQL replication is asynchronous by default, and all update operations on Master are written to Binlog It does not ensure that all updates are copied to the Slave above. Asynchronous operations are highly efficient, but There is a high risk of data synchronization and possibly loss of data in the event of a master/slave problem.

+ , MySQL5.5 The purpose of introducing the semi-synchronous replication function is to ensure that the Master when something goes wrong, at least one Slave the data is complete. In the case of time-outs, it is also possible to temporarily transfer to asynchronous replication, guaranteeing the normal use of the business until a salve is catching up and continuing to switch to semi-synchronous mode.

Second, master-slave replication, semi-synchronous replication, database replication filtering, specific operations:

1, the master-slave replication configuration steps:

Master-side:

#vi/etc/my.cnf

[Mysqld]

Log-bin = Master-bin (open binary log)

Log-bin-index = Master-bin.index (defines binary log index file)

Server-id = 1 ( not the same as slave-side, avoid cyclic replication)

Sync_binlog = 1 (this entry is used for transaction security, the set transaction is written to the binary log file when a commit is made)

Innodb_flush_logs_at_trx_commit = 1 (synchronous per transaction)

innodb_file_per_table = 1 (as long as this key that supports transactions must open, one tablespace per table)

DataDir =/mydata/data

Log_format = Mixed

>grant REPLICATION SLAVE on * * to ' repluser ' @ ' 192.168.1.% ' identified by ' repluser ';

>flush privileges;

Slave-side:

#vi/etc/my.cnf

[Mysqld]

Relay-log = Relay-log

Relay-log-index = Relay-log.index

Server-id = 11

Read_Only = 1 (slave-side does not allow write data, read-only, but this entry does not take effect for users with SUPER privileges)

Skip_slave_start = 1 (service start does not perform synchronization, pending manual opening of Io_thread and sql_threadfor master-side data error slave-side synchronization, etc. master-side data Normal, and then manually start synchronization)

>change MASTER to master_user= ' Repluser ', master_password= ' repluser ', master_host= ' 192.168.1.222 ', Master_log_ File= ' master_bin.000010 ', master_log_pos= ' 107 ';

>start SLAVE; (can also be performed separately >start SLAVE io_thread;>start SLAVE sql_thread; If this step appears error could notinitialization Master info structure ... execute >reset SLAVE; then re-execute the >change MASTER to that statement.

>show SLAVE status\g (see if Io_thread and sql_thread are on )

>stop SLAVE Io_thread; (slave-side io_thread can be stopped if there is a problem with the data on Master )

Slave-side Restart mysqld,io_thread and sql_thread will start automatically, data directory /mydata/data/ Relay-log.info and master.info These two files are the basis and premise of replication, if you do not want the service to start the automatic replication, you can cut these two files to other places, and then reconfigure Slave-side

2. Configure semi-synchronous replication:

#ll/usr/local/mysql/lib/

semisync_master.so semisync_slave.so (plugin provided by Google )

MySQL loading and turning on Semi-sync after the plug-in, each transaction waits for the repository to receive the log before returning it to the client. If you do a small transaction, and the latency of the two hosts is small, then semi-sync can achieve 0 data loss in the event of a small loss of performance.  

Master-side:

>install PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

>show GLOBAL VARIABLES like '%rpl_semi% ';

Rpl_semi_sync_master_enabled set to 1

Rpl_semi_sync_master_timeout default is 10S (if the primary and standby network fails or the standby is hung, the main library waits after the transaction commits Ten seconds , no response automatically to asynchronous state)

>set GLOBAL rpl_semi_sync_master_enabled=1; (only currently valid, writable configuration file)

Slave_side:

>install PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

>show GLOBAL VARIABLES like '%rpl_semi% ';

>set GLOBAL rpl_semi_sync_slave_enabled=1;

>stop SLAVE;

>start SLAVE;

Master-side:

>show GLOBAL STATUS like '%rpl% ';

Rpl_semi_sync_master_clients and rpl_semi_sync_master_status to be turned on, semi-synchronous replication is configured successfully

Slave-side:

>show SLAVE Status\g

View Seconds_behind_master

3. Database Replication filtering:

Master-side:

Binlog-do-db = Db_name(whitelist, only the operations that specify a database are logged in the binary log)

Binlog-ignore-db = Db_name(blacklist, do not log operation of a database to binary log)

Note: The master-side operation is not recommended , otherwise the binary log is incomplete.

Slave-side:

Replicate-do-db = db_name

Replicate-ignore-db = db_name

replicate-do-table = table_name

replicate-ignore-table = table_name

replicate-wild-do-table = table_name (wildcard characters % and _ are supported)

replicate-wild-ignore-table = table_name

Example: in slave-side:

#vim/etc/my.cnf

[Mysqld]

add replicate-do-db = test1

Replicate-ignore-db = Test2

#service mysqld Restart

>show SLAVE status\g (see two below)

Replicate_do_db:test1

Replicate_ignore_db:test2

This article is to learn "ma Brother Network Video" made notes.

This article is from the "Linux operation and maintenance of difficult learning notes" blog, declined reprint!

Phase IV (VII) MySQL REPLICATION (Master-slave replication, semi-synchronous replication, replication filtering)