MySQL system builds mutual db (dual master) records

Background: The responsibility of the automated continuous delivery platform business gradually increased, safekeeping, need to build a backup system, after a number of considerations;

Choose to take MySQL master-slave system for backup, mutual main from (that is, the so-called dual-master);

Ps:

Three ways to back up:

A, script through mysqldump backup-----------The amount of data can be taken

B, third-party tools backup-----------Most tools are Windows-side. Once there is a problem, restore up a little more steps (I am lazy, can automate the best automation)

C, MySQL master-slave synchronization mechanism------------recommended, real-time incremental hot standby, switching can be directly enabled DB.

The following are the construction records for future reference:

First, MySQL Master Master synchronization configuration process

1, two servers to establish a synchronization account

GRANT REPLICATION slave,file on * * to ' REPLICATION ' @ ' 192.168.%.% ' identified by ' 123456 ';

FLUSH privileges;

---------------user name ' replication ' password ' 123456 ' permission Slave,file

2, modify the server configuration file------------------depending on the server variable name, as well as the deployment location of different, specific variable assignment needs to be adjusted

Primary Server A:

#replicate confi

Server-id = 14550

Log-bin=mysql-bin

Log-bin-index=master-bin.index

binlog_format= "ROW"

Innodb_file_per_table=1

replicate_do_db =jats

Relay_log =/var/lib/mysql/mysql-relay-bin.log

Primary Server B:

Server-id = 14542

replicate_do_db =jats

Relay_log =/var/log/mysql/mysql-relay-bin.log

Slave-net-timeout=60

3, import from Server a Jats using load or mysqldump to B server;

4. Restart A/b server

5. Start the synchronization process from the server

Stop slave;

Reset slave;

Change Master to master_host= ' 192.168.145.45 ', master_port=3306,master_user= ' replication ', master_password= ' 123456 ' , master_log_file= ' mysql-bin.000002 ', master_log_pos=6663;

------------------where the master_log_file/master_log_pos are the log files of the primary server (the master server is visible using show Master status)

b, start slave;

6, any data changes to the master server Jats will be synchronized to the slave server

7, at this time the two sides of the server all operations will be synchronized with each other;

Second, MySQL synchronization mode and principle

Http://www.cnblogs.com/carterzhang/articles/4633540.html

1, Synchronization mode:

Mode: Master and slave (1 main multi-slave)

Replication mode: Synchronous replication, one-way synchronization, asynchronous replication

2. Principle of synchronization

Principle:

A, one server acts as the primary service, and one or more servers act as slave servers.

b, the master server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server.

c, when connecting to the primary server from the server, it notifies the primary server of the location of the last successful update that was read from the server in the log.

D. Receive any updates from the server since then, and then block and wait for the master server to notify the new updates. -------------This process has been continuous, know to turn off the synchronization function;

Step process: (after receiving the master server notification update) (mainly implemented by execution and result return separation):

A, the primary server opens 1 threads m used to send binary log content to the result from the server (that is, after the S1 thread executes the statement)

b, open 2 processes from the server,

1) thread S1: Create an I/O thread to connect to the primary server and have it send statements recorded in the binary log.

2) thread S2: Read updates from Relaylog

C, the master server pushes the binary file update to the slave server

E. Write updates to the synchronization log from the server (relay log)

F, from the server MySQL master process from relay log read update and execution;

Second, daily maintenance precautions

1, binary log and relay log size control

Three, the key parameter configuration meaning

Log_bin binary File location

Relay_log Relay File Location

Relay_log_index Intermediate Log Index

REPLICATE_DO_DB synchronizes only one db

Slave-net-timeout Sync Timeout setting (seconds)

Server-id server ID, different servers are not duplicated

Iv. synchronization of related commands

Stop slave------------stopping the synchronization thread

Reset slave-------------emptying the sync configuration

Change Master to master_host= ' 192.168.145.45 ', master_port=3306,master_user= ' replication ', master_password= ' 123456 ' , master_log_file= ' mysql-bin.000002 ', master_log_pos=6663; --------setting up the primary server

------------------where the master_log_file/master_log_pos are the log files of the primary server (the master server is visible using show Master status)

Start slave--------------start the sync thread

When the show master status-----------as the primary server, view the configuration

Show slave status-----------View synchronization configuration as Slave server

MySQL system builds mutual db (dual master) records