Data Synchronization between primary and secondary Mysql servers _ MySQL

Mysql server master/slave data synchronization bitsCN.com

Master-slave data synchronization for Mysql servers

Example: use server A as the Master server, and use server B1 and server B2 as the Slave server (Slave ),

How can we synchronize data from the master server to the slave server? let's take a look.

Master:

Modify the configuration file:/etc/my. cnf

[Root @ localhost ~] # Vim/etc/my. cnf

[Mysqld]

Log-bin = binlog

Log-bin-index = binlog. index

Sync_binlog = 0

Server_id = 1

Restart mysql: www.bitsCN.com

[Root @ localhost ~] #/Etc/init. d/mysqld restart

Stop MySQL: [OK]

Start MySQL: [OK]

[Root @ localhost ~] #

Slave1:

Modify the configuration file:/etc/my. cnf

[Root @ localhost ~] # Vim/etc/my. cnf

[Mysqld]

Server_id = 2

Relay_log =/var/lib/mysql-relay-bin

Relay_log_index =/var/lib/mysql/mysql-relay-bin.index

Restart mysql:

[Root @ localhost ~] #/Etc/init. d/mysqld restart

Stop MySQL: [OK]

Start MySQL: [OK]

[Root @ localhost ~] #

Slave2:

Modify the configuration file:/etc/my. cnf

[Root @ localhost ~] # Vim/etc/my. cnf

[Mysqld]

Server_id = 3

Relay_log =/var/lib/mysql-relay-bin

Relay_log_index =/var/lib/mysql/mysql-relay-bin.index

Restart mysql:

[Root @ localhost ~] #/Etc/init. d/mysqld restart

Stop MySQL: [OK] www.bitsCN.com

Start MySQL: [OK]

[Root @ localhost ~] #

Master:

[Root @ localhost ~] # Mysql

Welcome to the MySQL monitor. Commands end with; or/g.

Your MySQL connection id is 2

Server version: 5.0.77-log Source distribution

Type 'help; 'or'/h' for help. type'/C' to clear the buffer.

Mysql> GRANT replication slave ON *. * TO 'AB' @ '% 'identified by '123 ';

Query OK, 0 rows affected (0.00 sec)

Mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

Slave1:

[Root @ localhost ~] # Cd/var/lib/mysql/

[Root @ localhost mysql] # ls

Ibdata1 ib_logfile0 ib_logfile1 mysql. sock test

[Root @ localhost mysql] # rm-rf *

[Root @ localhost mysql] # ls

[Root @ localhost mysql] #/etc/init. d/mysqld restart

Master:

[Root @ localhost ~] # Mysqldump-A-x>/tmp/full. SQL

[Root @ localhost ~] # Scp/tmp/full. SQL root@192.168.18.117:/tmp/

The authenticity of host '192. 168.18.117 (192.168.18.117) 'can't be established.

RSA key fingerprint is 1f: ce: 39: 33: 61: f5: 7d: f8: 0b: 89: c7: d8: 06: 46: 79: 1f.

Are you sure you want to continue connecting (yes/no )? Yes

Warning: Permanently added '192. 168.18.117 '(RSA) to the list of known hosts.

Root@192.168.18.117's password:

Full. SQL 100% 1039 MB 4.8 MB/s

[Root @ localhost ~] #

Slave1:

[Root @ localhost mysql] # mysql </tmp/full. SQL

Master:

Mysql> flush tables with read lock;

Mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Binlog.000003 | 365 |

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

1 row in set (0.03 sec)

Mysql> unlock tables;

Query OK, 0 rows affected (0.03 sec)

Slave1:

Mysql> change master to master_host = '2017. 168.18.107 ', master_port = 3306, master_user =' AB ', master_password = '000000', master_log_file = 'binlog. 000003 ', master_log_pos = 365;

Query OK, 0 rows affected (0.06 sec)

Mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

Mysql> show slave status/G

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Slave1:

[Root @ localhost ~] # Mysqldump-A-x>/tmp/mysql. SQL

[Root @ localhost ~] # Scp/tmp/mysql. SQL root@192.168.18.127:/tmp/

The authenticity of host '192. 168.18.127 (192.168.18.127) 'can't be established.

RSA key fingerprint is f7: a5: 9e: 2f: 86: 57: a5: 17: f4: ad: 2b: 3a: a8: 55: 0f: 76.

Are you sure you want to continue connecting (yes/no )? Yes

Warning: Permanently added '192. 168.18.127 '(RSA) to the list of known hosts.

Root@192.168.18.127's password:

Mysql. SQL 100% 1039 MB 20.8 MB/s

[Root @ localhost mysql] #

Slave2:

[Root @ localhost mysql] # mysql </tmp/mysql. SQL

Master:

Mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

Mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Binlog.000003 | 365 |

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

1 row in set (0.00 sec)

Mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

Slave2:

Mysql> change master to master_host = '2017. 168.18.107 ', master_port = 3306, master_user =' AB ', master_password = '000000', master_log_file = 'binlog. 000003 ', master_log_pos = 365;

Query OK, 0 rows affected (0.05 sec)

Mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

Test:

Mysql> show slave status/G

In the output data, the following statement is yes

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

BitsCN.com