Mysql dual-machine Hot Backup master-master

Source: Internet
Author: User

Write down the steps and update them at any time for any problems. Here I synchronizeDatabaseIs TEST
1,EnvironmentDescription.
HOST: 192.168.0.167 ()
HOST: 192.168.0.20.( B)
MYSQL version 5.1.11
2. Authorize the user.
A:
Mysql> grant replication slave, file on *. *Backup_251@192.168.0.251Identified
By 123456;
Query OK, 0 rows affected (0.00 sec)

Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
B:
Mysql> grant replication slave, file on *. *Backup_167@192.168.0.167Identified
By 123456;
Query OK, 0 rows affected (0.00 sec)

Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Then stop MYSQL.Server.

3. ConfigurationFile.
Enable binary in my. cnf on both machinesLogs.
A:
User = mysql
Log-bin = mysql-bin
Server-id = 1
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-ErrorS = all
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 1

B:
User = mysql
Log-bin = mysql-bin
Server-id = 2
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 2
For more information about these parameters, see the manual.
The red part is very important. If one MASTER fails, the other MASTER will take over immediately.
The red part refersServiceFrequently refresh logs. This ensures that the log is Refresh to the other if one fails. This ensuresData.
4. Restart the MYSQL server.
Perform the same steps on A and B
[Root @ localhost ~] #/Usr/local/mysql/bin/mysqld_safe &
[1] 4264
[Root @ localhost ~] #071213 14:53:20 mysqld_safe Logging to/usr/local/mysql/data/localhost. localdomain. err.
/Usr/local/mysql/bin/mysqld_safe: line 366: [:-eq: unary operator expected
071213 14:53:20 mysqld_safe Starting mysqld daemon with databases from/usr/local/mysql/data

5. Enter the mysql shell.
A:
Mysql> flush tables with read lockG
Query OK, 0 rows affected (0.00 sec)

Mysql> show master statusG
* *************************** 1. row ***************************
File: mysql-bin.000007
Position: 528
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

B:
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

Mysql> show master statusG
* *************************** 1. row ***************************
File: mysql-bin.000004
Position: 595
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)
Back up your data to ensure the data consistency between the two machines.
There are many methods. Check the next step.
6. Execute CHANGE MASTERCommand.
A:
Mysql> change master
-> Master_host = 192.168.0.133,
-> Master_user = backup_251,
-> Master_password = 123456,
-> Master_log_file = mysql-bin.000004,
-> Master_log_pos = 595;
Query OK, 0 rows affected (0.01 sec)
Mysql> start slave;
Query OK, 0 rows affected (0.00 sec)


B:
Mysql> change master
-> Master_host = 192.168.0.167,
-> Master_user = backup_167,
-> Master_password = 123456,
-> Master_log_file = mysql-bin.000007,
-> Master_log_pos = 528;
Query OK, 0 rows affected (0.01 sec)
Mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

7. view the I/O information on each machineProcessAnd whether the SLAVE process is enabled.
A:

Mysql> show processlistG
* *************************** 1. row ***************************
Id: 2
User: repl
Host: 192.168.0.20.: 54475
Db: NULL
Command: Binlog Dump
Time: 1590
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
* *************************** 2. row ***************************
Id: 3
User: system user
Host:
Db: NULL
Command: Connect
Time: 1350
State: Waiting for master to send event
Info: NULL
* *************************** 3. row ***************************
Id: 4
User: system user
Host:
Db: NULL
Command: Connect
Time: 1149
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
* *************************** 4. row ***************************
Id: 5
User: root
Host: localhost
Db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
4 rows in set (0.00 sec)

B:

Mysql> show processlistG
* *************************** 1. row ***************************
Id: 1
User: system user
Host:
Db: NULL
Command: Connect
Time: 2130
State: Waiting for master to send event
Info: NULL
* *************************** 2. row ***************************
Id: 2
User: system user

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.