Mysql master-slave replication practice

The principle of MYSQL master-slave replication has been clearly explained in the real home Article high-performance Mysql master-slave architecture replication principles and configuration details. Here I will record my operations and my own understanding. I need to check the parameter meanings of showvariableslike % binlog %. 1. First, understand the principle of MYSQL master-slave replication.

The principle of MYSQL master-slave replication has been clearly explained in the real home Article high-performance Mysql master-slave architecture replication principles and configuration details. Here I will record my operations and my own understanding. I need to check the meanings of the parameters of show variables like '% binlog %'. 1. First, understand the principle of MYSQL master-slave replication.

The principle of MYSQL master-slave replication has been clearly explained in the real home Article high-performance Mysql master-slave architecture replication principles and configuration details. Here I will record my operations and my own understanding.

I need to check the meanings of the parameters of show variables like '% binlog % '.

1. First, you must understand the principle of MYSQL master-slave replication, and then consider the issues that need attention.

A. You need to enable bin-log.

B. Because the two servers need to communicate through SOCKET, the firewall needs to open port 3306 (in fact, the request is actively initiated through port 3306 of the master, and whether other ports need to be opened is not well known ).

C. Enable the relay-log of Slave.

So we should first enable the master's bin-log (method: Find my. cnf, Add server-id = xx, log-bin =/var/lib/mysql/log/mysql-bin.log, restart the database, show variables like '% binlog % ').

Step 2: create an account for slave on the master.

Grant replication slave, RELOAD, super on *.*
TO backup @ '10. 100.0.200'
Identified by '20140901 ';

Then execute show master status;

Obtain the location and current position of the log-binfile (so that you can copy the file from here)

Step 3: copy the original data of the master to the slave.

Step 4: Modify the slave configuration,

Log_bin = mysql-bin
Server_id = 2
Relay_log = mysql-relay-bin
Log_slave_updates = 1
Read_only = 1

Mysql> change master to MASTER_HOST = '192. 168.1.9 ',
-> MASTER_USER = 'repl ',
-> MASTER_PASSWORD = 'p4ssword ',
-> MASTER_LOG_FILE = 'mysql-bin.000001 ',
-> MASTER_LOG_POS = 0;

Mysql> start slave;

Run show slave status to view the output result:

Mysql> show slave status \ G

See

Slave_IO_Running = Yes (after starting slave, this will become YES after several seconds)
Slave_ SQL _Running = Yes

Both of them are YES.

Mysql> show processlist \ G;