First, MYSQL master-slave replication principle
1. Master records These changes in the binary log before each transaction updates the data. After the write binary log is complete, Master notifies the storage engine to commit the transaction.
2. Slave Copy the binary log of master to the following log. Slave starts a worker thread--i/o thread, the I/O thread opens a normal connection on master, and then starts Binlog dump process. Binlog dump process reads events from the binary log of master, and if it is already connected to master, it sleeps and waits for master to generate new events, which the I/O thread writes to the relay log.
3. SQL from the last step of the process, the SQL thread reads events from the log and replays the events in it to update the slave data so that it is consistent with the data in master.
Second, the realization of master-slave replication
1. Realize time synchronization
Start the NTP service to enable automatic boot
Modify/etc/ntpd.conf, add two items
Server 192.168.1.20//manual writing of upper-level servers requiring synchronization
Fudge 192.168.1.20 stratum 8//Manual write sync upper server stratum, size cannot exceed or equal to 16
Get NTP server time
Time synchronization
2. Configuring MySQL Master master server
Modifying a configuration file my.cnf
Grant Replication permissions
The flush Privileges command essentially works by extracting the user information/permission settings from the current users and Privilige tables from the MySQL library (the built-in library of the MySQL database) into memory. MySQL user data and permissions have been modified, I would like to "do not restart the MySQL service" in case of direct effect
The file column displays the log name, and the position column shows the offset
3, configuration from the server slave
Modify/ETC/MY.CNF
By default, the trunk log uses the host_name-relay-bin.nnnnnn form of the file name, where host_name is the hostname from the server, and nnnnnn is the serial number. Create continuous relay log files with sequential serial numbers, starting with 000001. The relay log currently in use in the index file is tracked from the server.
The default trunk log index file name is Host_name-relay-bin.index. By default, these files are created in the data directory from the server.
Configure sync login MySQL
4. Verifying Master-slave replication
Implementing MySQL master-slave replication