MySQL Master-slave replication

MySQL Machine

• Host ip:192.168.1.112
• Slave machine ip:192.168.1.114

Host Configuration

• My.ini Configuration (restart service after modification)

# master-Slave replication Primary server configuration

Server-id = 2222

#开启log-bin Log

Log-bin = Mysql-bin

#只复制employees Database

BINLOG-DO-DB = Employees

Configuration Instructions

• Server-id ID of the identity server
• Copy with binary log
• Binlog-do-db the data to be copied

• Querying host status, recording host file and position

Mysql> Show Master Status

;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

| mysql-bin.000001 | 154 | Employees | | |

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

1 row in Set (0.00 sec)

;

Query OK, 0 rows affected, 1 Warning (0.00 sec)

• Host add allows to copy accounts from the machine

mysql> grant replication Slave on * * to [e-mail protected] '192.168.1.114' identified by ' Replmaster '

From the machine configuration

• My.ini (restart service after modification)

#mysql主从复制 slave configuration

Server-id = 203

#只同步 Employees Database

REPLICATE-DO-DB = Employees

Relay-log-index = Slave-relay-bin.index

Relay-log = Slave-relay-bin

• Execute Sync command, set primary database IP, synchronize account password, sync location

master_log_pos,master_log_file is the value obtained after the host performs the show master status

mysql> Change Master to master_host= ' 192.168.1.112 ', master_user= ' replmaster ', master_password= ' Sqlsa ', Master_log

_pos=1606,master_log_file= ' mysql-bin.000009 ';

Query OK, 0 rows affected, 2 warnings (0.56 sec)

mysql> start slave;

Query OK, 0 rows affected (0.07 sec)

• View slave status

Mysql> Show Slave Status\g

If everything is set up correctly, then MySQL master-slave replication is OK.

• Verify

From machine Data +-

Host data

MySQL Master-slave replication