MySQL Master-slave replication

Tag: Delay running update rest tle is greater than or equal to GES partial check

MySQL Master-slave replication

MySQL 's built-in replication capabilities are the foundation for building large, high-performance applications. The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

Note that when you make a copy, all updates to the tables in the replication must be made on the primary server. Otherwise, you must be careful to avoid conflicts between updates to tables on the primary server and updates made to tables on the server.

The principle of replication:

(1) Master changes the record to binary log (these are called binary log events, binary logs event);
(2) Slave copies the binary log events of master to its trunk logs (relay log);
(3) Slave redo the events in the relay log and apply the changes to your data.

See it. One of the master's. n a Slave.     One master many from, not one from the multi-master. Watch it, huh? Let's take 1 from the 1 Lord as an example. The following is the first host

Replication Configuration :

There are two MySQL database servers master and Slave,master as the primary server, slave the same as the data information from the server, initial state, Master and Slave, When the data in master changes, the slave also follows the corresponding changes, so that the master and slave data information synchronization, to achieve the purpose of backup.

Steps

1: Both machines are shutting down the firewall

Service Iptables Stop

2. Modify the master server

Find the location of my.cnf first Whereis my.cnf

#vi/etc/my.cnf

[Mysqld]

#log-bin is to open the binary operations log

Log-bin=mysql-bin//[must] enable binary logging

server-id=222 (not the same)//[must be] The server unique ID, which is 1 by default and can be understood as IP. So the last three bits of IP are appropriate for the ID.

　　#需要同步的库. 不写同步所有

　　binlog-do-db = 需要同步的数据库,多个用逗号隔开 

3. Modify the Slave server

#vi/etc/my.cnf

[Mysqld]

server-id=222

Relay_log=mysql-relay-bin//## turn on the trunk log

Log_slave_updates=1//Turn on replication events for the trunk log

4. Restart MySQL for two servers

Service mysqld Restart

5. Create an account from the server on the primary server

GRANT all privileges on * * to [email protected] '% ' identified by ' Lisi ';

6, log on to the master server MySQL, query the status of master

Mysql>show Master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      mysql-bin.000004 |              308 |                  | |

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

1 row in Set (0.00 sec)

Note: Do not operate the master server MySQL again after performing this step to prevent the change of the primary server state value

7. Configure the slave from the server:

First find the location from the server and then:

Mysql-uroot-p

Mysql>change Master to master_host= ' 101.201.239.158 ', (Linux IP)

Master_user= ' Zhangsan ', (new user name)

Master_password= ' Lisi ', (new list)

Master_log_file= ' mysql-bin.000011 ', (this number is: top of the mysql-bin.000004)

master_log_pos=319; Note Do not disconnect, 308 digits before

8. Open Master/slave

Mysql>start slave;

9. Check the status of the replication function from the server:

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.2.222//Primary server address

Master_user:mysync//Authorization account name, try to avoid using root

master_port:3306//Database port, some versions do not have this line

Connect_retry:60

master_log_file:mysql-bin.000004

read_master_log_pos:600//#同步读取二进制日志的位置, greater than or equal to Exec_master_log_pos

relay_log_file:ddte-relay-bin.000003

relay_log_pos:251

relay_master_log_file:mysql-bin.000004

Slave_io_running:yes//This status must be Yes

Slave_sql_running:yes//This status must be Yes

......

Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error).

Interview questions

1 What should I do when the master-slave MySQL version is inconsistent?

A: In the high version of MySQL configuration file plus

Binlog-checksum = None

2 problem with master-slave delay

For

1 improve processing power from database configuration

2 Mysql-proxy Agent Middleware

3 Modifying a configuration file Sync-binlog = 1

MySQL Master-slave replication