How Mysql replication works

Database configuration, it is necessary to open the binary log, if the start is not open and then want to open, you must restart.

Log-point-based replication

Backup Database Tools

-------------------------------------------------------------------------------------------------

Log-based replication links

Begin

1. Prepare 2 virtual machines, IP addr command to view IP

Primary server IP 192.168.3.100, from server 192.168.3.101

2. Create users, change user permissions

3. Configure the primary database and the parameters from the database/etc/my.cnf

Primary server

Server-id = 1

Started Bin-log.

From the server

Server-id = 2

Configure Relay-log

The same enabled Bin-log. It is best to enable, facilitate master-slave migration, failover and so on.

4. Initializing data from the server

Back up the main database, because it is a test MySQL dump, the production environment is best to choose Xtrabuctup

Be careful not to back up the system library, which can cause unknown problems

Back up files to the root directory from the server

Initializing from the server database

5. Start the replication link

View backup file All.sql There's a word in it, just copy it.

To configure the connection path.

View link configuration show slave status \g;

Start the connecting road start slave;

6. Check the master-slave process show processlist \g;

2 related threads started from the server

The primary server started the Binlog Dump thread

-------------------------------------------------------------------------------------------------

Advantages and disadvantages of log-based replication

----------------------------------------------------------------------------------------------------

Advantages of Gtid Replication methods

Disadvantages of log-based replication

Benefits of Gtid Replication

Steps to Gtid

----------------------------------------------------------------------------------------------------

Gtid-based replication

The first two steps are the same as the "Log-based Replication" step

3. Modify the file on the master server VI etc/my.cnf

Start these two options

Configuration from the server

Start these options

Restarting the server

4. Initializing data from the server

Copy to Slave server

5. Configuring from a Database

6. Start replication and see if it is successful

7. Test: Add data to the master database to see if there is data from the database

-----------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

Share read load from the library and divide the load by the sub-table.

Primary master replication

Not recommended, except for specific scenarios

Scenario: For example, there are databases in two regions, and data for two regions are saved.

1. Primary and Standby mode, only one main external service, one problem, the other will provide services

2. Main Master mode while providing services to the outside

How Mysql replication works