Shulou(Shulou.com)05/31 Report--

Mysql how to use master-master to achieve dual-computer hot backup, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1. Description of environment.

Host: 192.168.0.167 (A)

Host: 192.168.0.251 (B)

MYSQL version 5.1.11

2. Authorized users.

A:

> grant replication slave,file on *. * to identified

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 *. * to identified

By 123456

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Then stop the MYSQL server.

3. Configuration file.

Open binary logs in my.cnf on both machines.

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

As for the description of these parameters, please see the manual.

The red part is very important, and if one MASTER dies, the other takes over immediately.

The fuchsia part refers to the frequent refresh of the log by the server. This ensures that if one of them dies, the log will be refreshed to the other. So as to ensure the synchronization of the data.

4. Restart MYSQL server.

Perform the same steps on An 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 SHELL of MYSQL.

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)

Then back up your own data to keep the data of the two machines consistent.

There are many ways. Look at the next step when you're done.

6. Execute CHANGE MASTER TO commands on their respective machines.

A:

Mysql > change master to

-> master_host=192.168.0.251

-> 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 to

-> 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. Check to see if both the IO process and the SLAVE process are enabled on their respective machines.

A:

Mysql > show processlistG

* * 1. Row *

Id: 2

User: repl

Host: 192.168.0.251: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

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.