1. A brief introduction

1.1. Basic principles of replication

Slave Reads the binlog file from the master for data synchronization

[Basic steps]

The master logs the changes to the binary log. These processes are called binary log events.

Slave copies the master binary log events to its relay log.

③ Slave rewrites events in the trunk log and applies the changes to its own database. MySQL replication is asynchronous and serialized.

1.2. Basic principles of replication

① Each slave has only one master

(2) Each slave must have a unique server ID

③ Each master can have multiple salves

1.3. The biggest problem with replication

If I/OS occur for multiple times, delay may occur.

2. Common configurations of one master and one slave

Before the configuration, ensure that the host and slave can ping each other on the same LAN.

[Configuration Procedure]

(1) The MySQL versions of the host and slave host must be the same.

② The configuration of the host and slave is under the [mysqld] node;

3 Modify the host configuration file

  • [Must] Unique primary server ID:server-id = 1
  • [Must] Enable binary files:Log-bin = Configuration path /mysqlbinExample:D: / mysql/mysql5.5 / data/mysqlbin
  • [Optional] Startup error log:Log-err = Configure path /mysqlerrExample:D: / mysql/mysql5.5 / data/mysqlbin
  • [Optional] Configure the root directory:Basedir =" own local path"Example:Basedir = "D: / mysql/mysql5.5"
  • [Optional] Configure a temporary directory:Tmpdir =" own local path"Example:Tmpdir = "D: / mysql/mysql5.5"
  • [Optional] Configuration data directory:Datadir =" local directory /Data"Example:Datadir = "D: / mysql/mysql5.5 / Data"
  • read-only=0: The host can read and write data
  • [Optional] Configure databases that do not need to be replicated:binlog-ignore-db="xxx"Example:binlog-ignore-db="mysql"
  • [Optional] Configure the database to be replicated.binlog-do-db="xxx"Example:binlog-do-db="mysql_test"

4 Modify the configuration file on the secondary host

  • [Required] Unique ID of slave server:server-id=3
  • Optional Start the binary file

⑤ Restart the background MySQL service after modifying the configuration file on the host and slave.

⑥ Turn off the firewall on the host and slave;

  • Linux firewall shutdown command:service iptables stop

⑦ Set up an account on the host and authorize the slave;

  • Create account command:GRANT REPLICATION SALVE ON *.* TO '@' IDENTIFIED BY 'password'
  • Query the master status and record the values of File and Position.show master state;Do not operate the primary server MySQL after the command is executed to prevent the status of the primary server from changing.

⑧ Configure the host to be replicated on the slave host

  • Configure the master command from the secondary server:CHANGE MASTER TO MASTER_HOST=' host IP',MASTER_USER=' created user name ',MASTER_PASSWORD=' created password ',MASTER_LOG_FILE=' host FILE name ',MASTER_LOG_POS= host POST number
  • Enable replication from secondary server:start salve;
  • View the parameters after startup:show salve status\G;whenSlave_IO_Running:YES 和 Slave_SQL_Running:YESBoth parameters areYESIf yes, the primary/secondary configuration is successful.

【 note 】

  • whenSlave_IO_Running 和 Slave_SQL_RunningNot both parametersYESIf yes, there may be a problem in the master/slave configuration, which may cause a synchronous replication failure.
  • If the primary/secondary connection configuration is incorrect, you need to enter the new host FILE name and POST number during the reconfiguration

⑨ Check whether host new database, data table, insert record and slave machine are replicated;

⑩ Stop the slave replication

  • Stop replication command:stop salve