Real-time Mysql synchronization-dual-host mutual backup (dual master) _ MySQL

Mysql real-time synchronization-dual-host mutual standby (dual master) bitsCN.com

Mysql real-time synchronization-dual-host mutual backup (dual master)

Setting method:

Step 1

On server A (192.168.1.43), the user is backup, 123456, and the synchronized database is test;

The user on server B (192.168.1.23) is root, 123456, and the synchronized database is test;

Step 2 configure mysql. ini:

Server

# Replication master

Server-id = 10

Log-bin = "E:/MySQL/logs/mysql_binary_log"

Binlog-do-db = test

Binlog-Ignore-db = information_schema

# For one-way backup, only part A needs to add server-id

# Replication slave

Master-host = "192.168.1.23"

Master-user = root

Master-password = "123456"

Master-port = 3306

Master-connect-retry = 60

Replicate-do-db = test

Replicate-Ignore-db = information_schema

Server B

# Replication master

Server-id = 2

Log-bin = "c:/mysql5/logs/mysql_binary_log"

Binlog-do-db = test

Binlog-Ignore-db = information_schema

# Replication slave

Master-host = "192.168.1.43"

Master-user = backup

Masters-password = 123456

Master-port = 3306

Master-connect-retry = 60

Replicate-do-db = test

Replicate-Ignore-db = information_schema

========================================================== ==================================

Explanation:

3) binlog-do-db = test indicates that the database to be backed up is test,

To back up multiple databases, write multiple rows as follows:

Binlog-do-db = backup1

Binlog-do-db = backup2

Binlog-do-db = backup3

Explanation:

1) server-id = 2 indicates the serial number of the machine. the server-id of A and B cannot be the same;

2) log-bin indicates that binlog is enabled. this option can be used to write logs to Slave relay-log through I/O, which is also a prerequisite for replication;

Mysql_binary_log is the name of the log file. mysql creates several log files with different extensions named mysql_binary_log.

3) master-host = "192.168.1.23" indicates that the master of server load balancer A is 192.168.1.23;

4) master-user = root indicates a user with permissions open on the master so that the user can connect to the master from the slave and perform replication;

5) master-password = 123456 indicates the password of the authorized user;

6) master-port = port 3306 of MySQL service Listen3306 on the master;

7) master-connect-retry = 60 synchronization interval;

8) replicate-do-db = test indicates synchronizing the backup database;

Finally, restart the mysql of the two machines.

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

View status and debug

1. view the master status

Show master status;

Position should not be 0

2. view the slave status

Show slave status;

Slave_IO_Running | the Slave_ SQL _Running fields must be YES | YES.

Show processlist;

There will be two records related to synchronization state: Has read all relay log; waiting for the slave I/O thread to update it

And s Waiting for master to send event.

3. error log

MySQL installation directory dataHostname. err

Commands on the master server:

Show master status

Show slave hosts

Show {master | binary} logs

Show binlog events

Purge {master | binary} logs to 'log _ name'

Purge {master | binary} logs before 'date'

Reset master (earlier version of flush master)

Set SQL _log_bin = {0 | 1}

Bytes -----------------------------------------------------------------------------------

Commands on the slave server:

Slave start

Slave stop

Slave stop IO_THREAD // This thread writes logs of the master segment to the local

SLAVE start IO_THREAD

Slave stop SQL _THREAD // This thread applies logs written locally to the database

SLAVE start SQL _THREAD

Reset slave

Set global SQL _SLAVE_SKIP_COUNTER

Load data from master

Show slave status (SUPER, replication client)

Change master to MASTER_HOST =, MASTER_PORT =, MASTER_USER =, MASTER_PASSWORD = // dynamically CHANGE master information

Purge master [before 'Date'] deletes logs that have been synchronized from the master.

BitsCN.com