MySQL master Replication

0. Environment Introduction

The cluster nodes are master1, master2, and slave1. Master-slave replication from master2 to slave1 has been completed. Now you need to configure the mutual replication between master1 and master2.

1. Modify the master2 parameter file (/etc/My. CNF) to add/modify the following parameters:

Log-bin = mysql-bin

Log-bin-Index = mysql-bin.index

Servers-id = 114

BINLOG-do-DB = mytest_db

Relay-log = relay-bin

Relay-log-Index = relay-bin-Index

Replicate-do-DB = mytest_db

Log_slave_updates = 1

Auto_increment_increment = 2

Auto_increment_offset = 1

2. Restart master2

# Service mysqld restart

3. Modify the master1 Parameter

Log-bin-Index = mysql-bin.index # New

Server-id = 113 # modify

BINLOG-do-DB = mytest_db # New

Relay-log = relay-bin # New

Relay-log-Index = relay-bin-index # added

Replicate-do-DB = mytest_db # Add

Log_slave_updates = 1 # New

Auto_increment_increment = 2 # Add

Auto_increment_offset = 2 # Add

# Auto-increment = 2 # Delete

# Auto-increment-offset = 2 # Delete

4. Restart master1

# Service mysqld restart

5. Add a user repl in master1 and specify the replication permission

Create user 'repl' @ 'master2 _ ip' identified by 'Password ';

Grant replication slave on *. * To 'repl' @ 'master2 _ ip ';

6. Generate master1 backup data

# Locking a table

Mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 Sec)

# Viewing Status

Mysql> show Master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | position | binlog_do_db | binlog_ignore_db |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000021 | 5337 | mytest_db |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in SET (0.00 Sec)

# Export data

[Root @ localhost ~] # Mysqldump-P mytest_db>/usr/software/tianyc/mytest_db_master.dump

Enter password:

[Root @ localhost ~] #

# Unlock a table

Mysql> unlock tables;

Query OK, 0 rows affected (0.00 Sec)

7. Restore data on master2

# Copy the file to master2 and execute Import

[Root @ localhost tianyc] # mysql-P mytest_db </usr/software/tianyc/mytest_db_master.dump

Enter password:

[Root @ localhost tianyc] #

8. On master2, set its master database to master1, set the BINLOG location, and enable replication.

Mysql> change master to master_host = 'master1 _ ip', master_port = 3306, master_user = 'repl', master_password = 'Password', master_log_file = 'mysql-bin.000021 ', master_log_pos = 5337;

Query OK, 0 rows affected (0.05 Sec)

Mysql> Start slave;

Query OK, 0 rows affected (0.00 Sec)

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_io_state: Waiting for Master to send event

Master_host: master1_ip

Master_user: repl

Master_port: 3306

Connect_retry: 60

Master_log_file: mysql-bin.000021

Read_master_log_pos: 6036

Relay_log_file: relay-bin.000002

Relay_log_pos: 952

Relay_master_log_file: mysql-bin.000021

Slave_io_running: Yes

Slave_ SQL _running: Yes

Replicate_do_db: mytest_db

Replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

Last_errno: 0

Last_error:

Skip_counter: 0

Exec_master_log_pos: 6036

Relay_log_space: 1102

Until_condition: None

Until_log_file:

Until_log_pos: 0

Master_ssl_allowed: No

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

Seconds_behind_master: 0

Master_ssl_verify_server_cert: No

Last_io_errno: 0

Last_io_error:

Last_ SQL _errno: 0

Last_ SQL _error:

Replicate_ignore_server_ids:

Master_server_id: 113

1 row in SET (0.00 Sec)

9. Verify the replication process between master1 and master2. Create a table on master1 and pass it to master2. master2 generates another BINLOG and passes it to slave1.

10. Create a mastering between master2 and master1 in the same way.

# Create a copy user repl on master2 and authorize it to master1.

Create user 'repl' @ 'master1 _ ip' identified by 'Password ';

Grant replication slave on *. * To 'repl' @ 'master1 _ ip ';

# View the Master Status of master2:

Mysql> show Master status;

+ ------------------ + ---------- + -------------- + ------------------ +

| File | position | binlog_do_db | binlog_ignore_db |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000006 | 610578 | mytest_db |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in SET (0.00 Sec)

# In this case, the master1 and master2 databases are consistent, and master2 is not changed. Therefore, the data synchronization process from master2 to master1 is saved.

# Set its master node directly on master1:

Mysql> change master to master_host = 'master2 _ ip', master_port = 3306, master_user = 'repl', master_password = 'Password', master_log_file = 'mysql-bin.000006 ', master_log_pos = 610578;

Query OK, 0 rows affected (0.02 Sec)

Mysql> Start slave;

Query OK, 0 rows affected (0.00 Sec)

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_io_state: Waiting for Master to send event

Master_host: master2_ip

Master_user: repl

Master_port: 3306

Connect_retry: 60

Master_log_file: mysql-bin.000006

Read_master_log_pos: 611282

Relay_log_file: relay-bin.000002

Relay_log_pos: 253

Relay_master_log_file: mysql-bin.000006

Slave_io_running: Yes

Slave_ SQL _running: Yes

Replicate_do_db: mytest_db

Replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

Last_errno: 0

Last_error:

Skip_counter: 0

Exec_master_log_pos: 611282

Relay_log_space: 403

Until_condition: None

Until_log_file:

Until_log_pos: 0

Master_ssl_allowed: No

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

Seconds_behind_master: 0

Master_ssl_verify_server_cert: No

Last_io_errno: 0

Last_io_error:

Last_ SQL _errno: 0

Last_ SQL _error:

Replicate_ignore_server_ids:

Master_server_id: 114

1 row in SET (0.00 Sec)

11. Then integrate maste1, master2, and slave1 with amoeba to test cluster replication.