MySQL database replication filtering

Copying only a subset of the tables in the database can reduce the pressure on the master and slave servers

On the primary server

BINLOG-BIN-DB only the specified database-related modifications are counted in the binary log (in general) "whitelist"

Binlog-ignore-db "Blacklist"

Once the primary database fails, it cannot be restored immediately causing a large loss, so the primary binary log is complete

From the server

Replicate-do-db "Whitelist" applies only the database to the local

Replicate-ignore-db "Blacklist"

Replicate-do-table using the specified table

Replicate-ignore-table

Replicate-wild-do-table How to use wildcard characters

Replicate-wild-ignore-table

The above instructions can be used multiple times

Set the black and white list to be more suitable for the database.

Gtid was introduced in MySQL 5.6

Is the only identifier, which is the server's UUID (globally unique identifier) for any server, is a 128-bit random string, and combined with the transaction ID number to combine the transaction on a host of the unique identification code, that is, Gtid

At the first of every transaction, the Gtid is written.

Making the process of replication very simple and able to recover quickly in a crash, providing a high-availability capability

InnoDB storage engine, to use its high availability, all with the help of Gtid

After using Gtid, you can automatically choose where to start copying from

Multithreaded replication

The order of the transactions is important

Only one thread can be used per database, and multithreaded replication makes sense when multiple databases are involved

Slave-parallel-workers=0

Disabling multithreaded Features

Replication Utility:mysqlreplicate

Quickly enable MySQL from the server, help from the server, check for locally executed transactions by tracing Gtid, skipping already performed replication, thereby speeding up replication

Replication Utility:mysqlrplcheck

Implement a simple validation deployment and implement fast read troubleshooting, whether Binlog is enabled, and whether the associated configuration is abnormal

By performing a simple authentication function

Replication Utility:mysqlrplshow

Displays the MySQL replication topology, shows whether it is multi-level replication, draws a graph, displays the hostname and port number

Replication Utility:mysqlfailover fail-over tool

Fast Automatic or manual elevation of one slave, application of other slave not functional conversion to master

Replication Utility:mysqlrpladmin Scheduling Management tool

Manually let a node go online or offline, scheduling down a node

To use the replication feature in MySQL 5.6, the following options should be defined in the service configuration segment [mysqld] for Less:

Binlog-format: Binary log format, there are several types of row, statement and mixed;

Note that when you set the isolation level to read-commited, you must set the binary log format to row, and now MySQL officially believes that statement this is no longer suitable for continued use, but the mixed type may result in inconsistent master-slave data under the default transaction isolation level;

Log-slave-updates, Gtid-mode, Enforce-gtid-consistency, Report-port and report-host: used to initiate gtid and meet other requirements of the subsidiary;

Master-info-repository and Relay-log-info-repository: Enable these two items, which can be used to ensure the security of the binary and slave servers in the crash;

Sync-master-info: Enable it to ensure that no information is lost;

Slave-paralles-workers: Set the number of SQL threads from the server; 0 to turn off the multithreading replication function;

Binlog-checksum, Master-verify-checksum, and slave-sql-verify-checksum: Enable all verification functions related to replication;

Binlog-rows-query-log-events: Enables the use of information related to logging events in binary logging to reduce the complexity of troubleshooting;

Log-bin: Enable the binary log, which is the basic premise to ensure the replication function;

Server-id: The ID number of all servers in the same replication topology must be unique;

Master-info-repository let the master server record each connection information from the server and the binary file name that each primary server replicates is extremely relevant event information; Log to: File table

Relay-log-info-repository from the server itself records who is connected to the primary server, which binary files of the primary server and the record location of the binaries. Logged to: File table

PS uname-n View Host name

Cat/etc/issue Viewing the Linux kernel

First, simple master-slave mode configuration steps

1, configure the master-slave node of the service configuration file

1.1. Configure the Master node:

[Mysqld]

Binlog-format=row

Log-bin=master-bin

Log-slave-updates=true

Gtid-mode=on

Enforce-gtid-consistency=true

Master-info-repository=table

Relay-log-info-repository=table

Sync-master-info=1

slave-parallel-workers=2

Binlog-checksum=crc32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

Server-id=1

report-port=3306

port=3306

Datadir=/mydata/data

Socket=/tmp/mysql.sock

Report-host=master.magedu.com

1.2. Configure the Slave node:

[Mysqld]

Binlog-format=row

Log-slave-updates=true

Gtid-mode=on

Enforce-gtid-consistency=true

Master-info-repository=table

Relay-log-info-repository=table

Sync-master-info=1

slave-parallel-workers=2

Binlog-checksum=crc32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

server-id=11

report-port=3306

port=3306

Log-bin=mysql-bin.log

Datadir=/mydata/data

Socket=/tmp/mysql.sock

Report-host=slave.magedu.com

2. Create a replication user

Mysql> GRANT REPLICATION SLAVE on * * to [e-mail protected] identified by ' Replpass ';

Description: 172.16.100.7 is from the node server; If you want to authorize more nodes at once, you can modify them as needed.

3. Provide the initial data set for the standby node

Lock the primary table, back up the data on the primary node, restore it to the slave node, or, if Gtid is not enabled, use the Show Master Status command on master to view the binary log file name and the event location when you back up, so that you can use it later when you start the slave node.

4. Start the replication thread from the node

If the Gtid feature is enabled, use the following command:

mysql> change MASTER to master_host= ' master.magedu.com ', master_user= ' repluser ', master_password= ' Replpass ', Master_auto_position=1;

Gtid is not enabled, you need to use the following command:

slave> change MASTER to master_host= ' 172.16.100.6 ',

Master_user= ' Repluser ',

Master_password= ' Replpass ',

Master_log_file= ' master-bin.000003 ',

master_log_pos=1174;

Second, semi-synchronous replication

1. Install the relevant plugins on the master and slave nodes respectively

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

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

2. Enable semi-synchronous replication

In the configuration file on master, add

Rpl_semi_sync_master_enabled=on

Add a configuration file in at least one slave node

Rpl_semi_sync_slave_enabled=on

Then restart the MySQL service to take effect.

Alternatively, you can dynamically start its related features on the MySQL service:

master> SET GLOBAL rpl_semi_sync_master_enabled = on;

slave> SET GLOBAL rpl_semi_sync_slave_enabled = on;

slave> STOP slave io_thread; START SLAVE Io_thread;

3. Confirm that the half-sync function is enabled

master> CREATE DATABASE magedudb;

master> SHOW STATUS like ' rpl_semi_sync_master_yes_tx ';

Slave> SHOW DATABASES;

This article is from the "Newton also eat apple" blog, please be sure to keep this source http://mi55u.blog.51cto.com/11619321/1927294

MySQL database replication filtering