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