Log point Replication

Log point Replication

As mentioned above, log replication is divided into log point-based replication and GTID-based replication.

This article describes the log point-based replication process.

1. Create a replication account on the master DB server.

Create user 'repl' @ ip segment identified by 'pwd ';

Create user repl @ '192. 192.% 'identified by 'repl ';

Authorization

Grant replication slave on *. * to 'repl' @ ip segment;

Grant replication slave on *. * to repl @ '192. 192.% ';

2. Configure the master database server.

Bin_log = mysql-bin

Enable binary logs and specify the log name.

Server_id = 100

You must specify the serverid, which must be unique in the replication cluster.

3. slave server configuration.

Bin_log = mysql-bin

Server_id = 101

# Relay logs

Relay_log = mysql-relay-bin

# Optional parameter: whether to record the relay log to the current binary log,

# If you need to use the current slave server as the replication source for other slave servers, you need to configure it.

Log_slave_update = on

# Security configuration parameters to prevent data from being written

Read_only = on

4. initialize data from the server

Mysqldump. This method requires locking.

Parameters:

-Single-transaction: to ensure data transaction consistency, you must lock the database, which may cause blocking.

-Master-data = 2: records the offset of the binary file of the master database.

Xtrabackup-slave-info hot backup tool.

The innodb Storage engine is not blocked.

Mysqldump-uroot-p-P3308 -- single-transaction -- master-data -- triggers -- routines -- all-databases> all. SQL

Import data from the server

Mysql-uroot-p-P3309 <all. SQL

5. Start the replication Link

You need to operate on the slave server.

Change master to MASTER_HOST = 'master _ host_ip ',

MASTER_USER = 'repl ',

MASTER_PASSWORD = 'pwd ',

MASTER_LOG_FILE = 'mysql _ LOG_FILE_NAME ',

MASTER_LOG_POS = 4;

Change master to master_host = 'localhost ',
-> Master_user = 'repl ',
-> Master_password = 'repl ',
-> MASTER_LOG_FILE = 'mysql-bin.000005 ', MASTER_LOG_POS = 2162;

This section can be found in the exported file.

 

Show slave status \ G

View the status of the replication link.

Start the replication Link

Start slave;

 

Use show processlist to view service threads.

One IO thread and one SQL thread.

Master Server View

 

Started a dump thread.

6. Verify the replication effect:

Run the command on node.

 

1. Create a table.

2. Insert two records.

Query on the slave server.

 

Data Synchronization is found.

 

Advantages:

1. It is the earliest replication technology supported by mysql, with fewer bugs.

2. There are no restrictions on SQL queries.

3. Easy troubleshooting.

Disadvantages:

It is difficult to obtain the log point information of the new Master during failover.