Mysql master-slave replication, mysql master-slave

Mysql master-slave replication, mysql master-slave
1. mysql master-slave replication Principle

For the master server, the information generated in the current database will be recorded in the binary log. If the server receives a Statement on the client that needs to change the data, if InnoDB is used for this data, the data itself is first recorded in the transaction log, and the statements that affect the data will be recorded in the binary log, and the copy statement is, another slave server passively accepts data and does not accept client links for any write operations. replication is unidirectional and can only be performed from the master server to the slave server, the data on the slave server is from the master server. The master service listens to port 3306 and starts a client process from the slave server. This process initiates a request to the port on which the data service is provided, the request reads the events in the binary log. On the master server, mysql provides the authentication service and starts a sqldump thread. After receiving the user's request, make sure that the user has the permission to read the events in the binary log. If the client requests the event for the first time, the process reads the first event in the log, read one and send it to the client. After receiving the data from the master server, the service process stores the local relay log, and then starts a thread with special functions to read the event on the relay day, read a row, execute it locally, and so on, and finally generate a local database. Mysql replication is asynchronous. Therefore, after receiving a user's request, the result is returned to the user. And store the information in the memory, and then record it into the binary file, this will cause the slave server to lag behind the master server, but this is also good, if you have an error operation, in this time, it is OK to back up data from the server immediately, which requires the mysql administrator to grasp.

2. Implementation of mysql master-slave Replication

[1] ensure that the Mysql version on the Master/Slave server is the same
[2] operations on the master server
(1) set up an account from the database and grant permissions Using replication slave, for example:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave001'@'192.168.0.99' IDENTIFIED BY '123456';mysql> FLUSH PRIVILEGES; 

(2) modify the configuration file my. cnf of the primary database, enable BINLOG, and set the value of server-id. After modification, the Mysql service must be restarted.

log-bin = mysql-binserver-id=10

(3) The current binary log name and offset of the master server can be obtained. This operation aims to recover data from this point after the database is started.

mysql> show master status\G;

(4) import the data of the primary database to the slave server. You can copy the original database file or export the script, and then import it to the slave server. Before exporting the script, to ensure data consistency, read lock must be performed on the master database.

mysql> flush tables with read lock;

Then export the database

mysqldump -h127.0.0.1 -p3306 -uroot -p test > /home/ceshi/test.sql

After the data is backed up, the primary server must be restored and written incorrectly.

mysql> unlock tables;

[3] operating on the slave server
(1) import the exported test. SQL to the slave database.
(2) modify the configuration file my. cnf of the slave database and specify the basic information of the master database.

Server-id = 20 read-only = 1 // Enable read-only mode

(3) configure the slave server to connect to the master server

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.98', MASTER_USER='slave001', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;mysql> START SLAVE;

(4) Verify the show salve status on the slave server

mysql> SHOW SLAVE STATUS\G

If Slave_IO_Running or Slave_ SQL _Running indicates yes, it indicates normal.