To test the dual-host dual-Master MySQL solution, you must avoid single point of failure in any part of the system .?? If a hardware fault occurs on the physical machine where the primary MySQL is located, it is a single point of failure. Although a Slave machine (Slave) can be quickly upgraded to a Master machine, it will be slow. The best way is to use the dual Master solution to avoid spof.
To test the dual-host dual-Master MySQL solution, a single point of failure (spof) must be avoided in any part of the system .? ? If a hardware fault occurs on the physical machine where the primary MySQL is located, it is a single point of failure. Although a Slave machine (Slave) can be quickly upgraded to a Master machine, it will be slow. The best way is to use the dual Master solution to avoid spof.
Test the dual-host dual-Master MySQL Solution
You must avoid any single point of failure in the system .?
?
If a hardware fault occurs on the physical machine where the primary MySQL is located, it is a single point of failure. Although a Slave machine (Slave) can be quickly upgraded to a Master machine, it will be slow. The best way is to use the dual Master solution to avoid spof.
?
This test is expected to take 2 days .?
?
?
The following two problems need to be solved:
?
1. Implement cyclic synchronization between two primary databases, that is, database A changes and database B is synchronized. Similarly, database B changes and database A is synchronized;
?
2. When database A fails, issue an alarm in time and replace database B with database A as the master database. The communication with all slave servers is normal;
?
?
Preparations
In general, the mysql version of the master and slave should be as consistent as possible, or the master is higher than the slave, but here we are dual master, that is, the master and slave are mutually, so the mysql version should be consistent;
I plan to use my own machine and 192.168.0.8 machine for testing;
A: 192.168.0.219 (local)
B: 192.168.0.8
Check the version
A: $ mysql-V
Mysql Ver 14.14 Distrib 5.1.62, for debian-linux-gnu (IMG) using readline 6.1
B: $ mysql-V
Mysql Ver 14.14 Distrib 5.5.13, for FreeBSD8.1 (amd64) using 5.2
?
Upgrade mysql
Upgrade mysql of the Local Machine to Version 5.5.
?
Create a synchronization account
Because server A and server B are both master and SLAVE, you must create A synchronization account for them separately and grant the REPLIATION SLAVE permission. a: mysql> grant replication slave on *. * to 'replicate' @ '192. 168.0.8 'identified by '20140901'; mysql> flush privileges; B: mysql> grant replication slave on *. * to 'replicate' @ '192. 168.0.219 'identified by '20170101'; mysql> flush privileges; if not, create it directly in phpmyadmin. Test it. a: mysql-h192.168.0.8-ureplicate-p123456 can enter mysql> OK !! B: mysql-h192.168.0.219-ureplicate-p123456 can enter mysql> OK !!
?
Modify the configuration file my. cnf
A: [mysqld] server-id = 1 log-bin = mysql-bin binlog-do-db = test-xf binlog-ignore-db = mysql # add more content to the master replicate-do-db = test-xf replicate-ignore-db = mysql, information_schema log-slave-updates # If one master fails and the other master immediately takes over # The following three statements refresh the log frequently on the server, which ensures that one of them fails, the log is refreshed to another instance to ensure data synchronization. sync-binlog = 1 auto_increment_offset = 1 auto_increment_increment = 2 restart mysql service: sudo/etc/init. d/mysql restart view master status mysql> show master status \ G; * *************************** 1. row ************************* File: mysql-bin.000006 Position: 977 Binlog_Do_DB: test-xfBinlog_Ignore_DB: mysql1 row in set (0.00 sec) ERROR: No query specifiedB: [mysqld] server-id = 2 log-bin = mysql-bin binlog-do-db = test-xf binlog-ignore-db = mysql # add more content to the master replicate-do-db = test-xf replicate-ignore-db = mysql, information_schema log-slave-updates # If one master fails and the other master immediately takes over # The following three statements refresh the log frequently on the server, which ensures that one of them fails, the log is refreshed to another instance to ensure data synchronization. sync-binlog = 1 auto_increment_offset = 2 auto_increment_increment = 2 restart mysql service: sudo/usr/local/etc/rc. d/mysql-server restart view master status: mysql> show master status \ G; * *************************** 1. row ************************* File: mysql-bin.000005 Position: 348 Binlog_Do_DB: test-xfBinlog_Ignore_DB: mysql1 row in set (0.00 sec) ERROR: No query specified
?
Synchronization location
?
A:mysql> change master to -> master_host = '192.168.0.8', -> master_user = 'replicate', -> master_password = '123456', -> master_log_file = 'mysql-bin.000005', -> master_log_pos = 348;Query OK, 0 rows affected (0.44 sec)B:mysql> change master to -> master_host = '192.168.0.219', -> master_user = 'replicate', -> master_password = '123456', -> master_log_file = 'mysql-bin.000006', -> master_log_pos = 977;Query OK, 0 rows affected (0.05 sec)
?
?
?
Restart the slave service thread on server A and server B
A: mysql> start slave; Query OK, 0 rows affected (0.03 sec) mysql> show slave status \ G; * *************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.8 Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 runtime: 348 Relay_Log_File: xiaofei-desktop-relay-bin.000002 Relay_Log_Pos: 253 runtime: mysql-bin.000005 runtime: Yes runtime: test-xf runtime: mysql, information_schema failed: Failed: 0 Last_Error: Skip_Counter: 0 unknown: 107 Relay_Log_Space: 419 Until_Condition: None Until_Log_File: Failed: 0 unknown: No condition: Master_SSL_Cert: master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: Role: No Role: 0 Last_IO_Error: Role: 0 Last_ SQL _Error: Role: Master_Server_Id: 21 row in set (0.01 sec) ERROR: No specquery ifiedb: mysql> start slave; Query OK, 0 rows affected (0.02 sec) mysql> show slave status \ G; * *************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.219 Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 runtime: 977 Relay_Log_File: queen-relay-bin.000002 Relay_Log_Pos: 253 runtime: mysql-bin.000006 runtime: Yes runtime: test-xf runtime: mysql, information_schema failed: Failed: 0 Last_Error: Skip_Counter: 0 unknown: 107 Relay_Log_Space: 409 Until_Condition: None Until_Log_File: Failed: 0 unknown: No condition: Master_SSL_Cert: master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: Role: No Role: 0 Last_IO_Error: Last_ SQL _Errno: 0 Last_ SQL _Error: Role: Master_Server_Id: 11 row in set (0.00 sec) ERROR: no query specified: Slave_IO_Running: Yes Slave_ SQL _Running: Yes indicates that the server is successfully started;
?
?
Start test data
Write:
First create A test-xf database in A, and we find that B has synchronized the test-xf database;
Create a tb_mobile table:
Mysql> use test-xf;
Database changed
Mysql> set names 'utf8 ';
Query OK, 0 rows affected (0.00 sec)
Mysql> create table tb_mobile (mobile VARCHAR (20) comment 'mobile phone number', time timestamp DEFAULT now () comment 'time ');
Query OK, 0 rows affected (0.17 sec)
After the creation, we found that test-xf in B already exists;
Next, go to server B for testing;
Insert data to server B;
Mysql> insert into tb_mobile (mobile) values ('20140901 ');
Query OK, 1 row affected (0.05 sec)
Mysql> select * from tb_mobile;
+ ------------- + --------------------- +
| Mobile | time |
+ ------------- + --------------------- +
| 12254123876 | 14:40:15 |
+ ------------- + --------------------- +
1 row in set (0.03 sec)
It is also successful to go to server.
* ** I created A new database named test-007 on server B, but server A is not synchronized because we are in my. when cnf is used, only test-xf is set for the synchronization operation database.
?
?
Server-id
The ID value uniquely identifies the master and slave servers in the replication cluster, so they must be different. Master_id must be a positive integer between 1 and 232-1, and slave_id must be a positive integer between 2 and 232-1.
Log-bin
Indicates that binlog is enabled. Enabling this option can write data to Slave's relay-log through I/O, which is also a prerequisite for replication;
Binlog-do-db
Indicates the database for which logs need to be logged. Multiple databases can be separated by commas (,), or multiple binlog-do-db options are used.
Binlog-ignore-db
Indicates the database that does not need to record binary logs. Multiple databases can be separated by commas (,), or multiple binlog-do-db options are used.
Replicate-do-db
Indicates the database to be synchronized. If multiple databases are separated by commas, or multiple replicate-do-db options are used.
Replicate-ignore-db = mysql
Indicates the database that does not need to be synchronized. If multiple databases are separated by commas, or multiple replicate-ignore-db = mysql options are used.
Log-slave-updates
Configure whether the update operation on the slave database is written to the binary file. If this slave database is used as the master database of another slave database, this parameter is required, so that the slave database of the slave database can synchronize logs
Slave-skip-errors
During the replication process, an SQL error occurs in the binlog due to various reasons. By default, slave database stops copying and requires user intervention. You can set Slave-skip-errors to define the error number. If the error number encountered during the replication process is the defined error number, you can skip it. If the slave database is used for backup, set this parameter to avoid data inconsistency. If you want to share the query pressure on the master database, consider it.
Sync-binlog = 1 or N
The default value of sync-binlog is 0. In this mode, MySQL is not synchronized to the disk. In this case, MySQL relies on the operating system to refresh binary logs, just like the mechanism by which the operating system refreshes other files. Therefore, if the operating system or machine (not just the MySQL server) crashes, the last statement in binlog may be lost. To prevent this situation, you can use the sync-binlog global variable to synchronize binlog with the hard disk after each N binlog writes. When the sync_binlog variable is set to 1, it is the safest, because in the case of crash, your binary log may only lose up to one statement or transaction. However, this is also the slowest way (unless the disk uses a cache with battery backup power, so that the synchronization to the disk is very fast ).
Even if sync-binlog is set to 1, the table content and binlog content may be inconsistent in the event of a crash. If the InnoDB table is used, the MySQL server processes the COMMIT statement, which writes the entire transaction to the binlog and submits the transaction to InnoDB. If a crash occurs between two operations, the transaction is rolled back by InnoDB during the restart, but it still exists in the binlog. You can use the-innodb-safe-binlog option to increase the consistency between InnoDB table content and binlog. (Note:-innodb-safe-binlog is not required in MySQL 5.1. Because XA transaction support is introduced, this option is voided.) This option provides greater security, synchronize the binlog (sync_binlog = 1) and (true by default) InnoDB logs of each transaction with the hard disk. This option is effective after the crash and restart, after the transaction is rolled back, the MySQL Server splits the InnoDB Transaction for rollback from the binlog. This ensures that the binlog reports the exact data of the InnoDB table and keeps the slave server synchronized with the master server (the rollback statement is not received ).
Auto_increment_offset and auto_increment_increment
Auto_increment_increment and auto_increment_offset are used for master-to-master replication and can be used to control operations on the AUTO_INCREMENT column. Both variables can be set to global or local variables, and each value can be an integer between 1 and 65,535. If one of the variables is set to 0, the variable is set to 1.
These two variables affect the AUTO_INCREMENT column method: auto_increment_increment controls the increment value of values in the column, auto_increment_offset determines the starting point of values in the AUTO_INCREMENT column.
If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored. For example, if some data already exists in the table, the existing maximum auto-increment value is used as the initial value.
?
?
Manage running commands
?
Write
?
A. SQL statements used to control the master server
PURGE MASTER LOGS
Deletes all binary logs listed in the log index before the specified log or date. These logs will also be deleted from the list of records in the log index file, so that the given log becomes the first.
RESET MASTER
You can delete all binary logs listed in the index file, reset the binary log index file to null, and create a new binary log file.
SET SQL _LOG_BIN
If the client uses an account with the SUPER permission to connect, you can disable or enable the binary log of the current connection. If the client does not have this permission, the statement is denied and an error occurs.
SHOW BINLOG EVENTS
Displays events in binary logs. If you do not specify 'Log _ name', the first binary log is displayed.
SHOW MASTER LOGS
Used to list binary log files on the server.
SHOW MASTER STATUS
It is used to provide the status information of the binary log file of the master server.
SHOW SLAVE HOSTS
Displays the list of replication slave servers currently registered with the master server.
B. SQL statements used to control slave servers
CHANGE MASTER
You can change the parameters used by the slave server to connect to and communicate with the master server.
LOAD DATA FROM MASTER
It is used to take snapshots of the master server and copy them to the slave server.
Load table tbl_name FROM MASTER
Transfers a copy of a table from the master server to the slave server.
MASTER_POS_WAIT ()
This is actually a function, not a statement. It is used to confirm that the slave server has read and executed the given location of the binary log arriving at the master server.
RESET SLAVE
It is used to make the slave server forget its replication location in the binary log of the master server.
Set global SQL _SLAVE_SKIP_COUNTER
Skips the next n events from the master server. It is useful to recover the replication abort caused by the statement.
SHOW SLAVE STATUS
It is used to provide information about key parameters of the slave server thread.
START SLAVE
Used to start a slave server thread
STOP SLAVE
Used to abort a slave server thread
?
?
How to improve reliability of master-slave Replication
Write master-slave unidirectional replication, the slave server only saves a copy of the master server in real time. When the master server fails, you can switch to the slave server for further query, but cannot update it.
If two-way replication is used, that is, the two mysql servers act as the master server and the slave server. Both of them can perform update operations and achieve load balancing. When one party fails, the other party will not be affected.
However, the bidirectional replication will fail unless any update operation sequence is secure.
To improve reliability and availability, you need to make the slave server a Master when the Master server is unavailable.
The original Master server is set to Slave and updated synchronously from the new Master. Now we have an open-source solution [MySQL Master-Master Replication Manager]
?
?
?
Master-master replication of MySql with slave server
Http://www.litvip.com/2011/06/29/326
?
Http://www.cnblogs.com/czh-liyu/archive/2012/06/01/2530482.html
