MySQL's AB replication (master-slave replication) principle and implementation

MySQL Replication (replication) is an asynchronous replication that replicates from one MySQL instance (Master) to another MySQL instance (Slave).         Implementing the entire master-slave replication requires the IO process on the master server, and the SQL process and IO processes on the slave server to be completed altogether. To achieve master-slave replication, you must first turn on the binary log (Bin-log) function on the master side, because the entire MySQL replication process is actually slave to get the corresponding binary logs from the master side, The various operations that are recorded in the execution log are then fully sequential on their slave side. (The binary log almost records all SQL operation statements against the database except Select)

The basic process of master-slave replication is as follows: 1), Mysql slave-side IO process connected to master, request to master to specify the log file at the specified location (or from the beginning of the log) after the log content, 2), Master received from the slave IO process request, The IO process responsible for replication reads the corresponding log contents according to slave request information and returns the IO process to slave. and returns the Bin-log file name and location read by this request to the slave side. 3), after the slave IO process receives the information, the received log content is added to the end of the Relay-log file on the slave side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. In order to be able to tell Master clearly at the next reading, "I need to start from a bin-log where the log content, please send Me"; 4), Slave's SQL process detects the new additions to the Relay-log, Will immediately parse the content of the relay-log into the actual execution of the master side of the executable when the content, and execute on its own.

Configuration implementation:
1, to achieve master-slave replication, both master and client need to open the binary log, in the MY.CNF to add the following configuration
Log-bin=xx log-bin-index=xx.index server-id=xx expire-logs-days=n automatic log days to prevent a single log file is too large or in MySQL to perform flush logs manual wheel-shaped two Binary log, combined with Crond automatic rotation log. Www.it165.net

---------------------------------------------Other common configuration references are as follows: [mysqld] Datadir=/database Basedir=/usr/local/mysql Pid-file=/var/run/mysqld/mysql5.pid Socket=/var/run/mysqld/mysql5.socket General-log general-log-file=/var/log/ Mysqld/mysql5-access.log log-error=/var/log/mysqld/mysql5-error.log port=3306 User=mysql------------------------- ---------------------

2, on the master side give Mysql Slave server authorization:
Myql>grant replication Slave, super on * * to ' xx ' @ ' xx ' identified by ' xxx '; View status: Mysql>show Master status \g

3, connect to the specified master end on the slave side
mysql>stop  slave; mysql>change   master   to          master_host= ' 10.1.1.145 ',          master_port=3306,           master_user= ' TT ',          master_password= ' 123 ',          master_log_file= ' uu.000005 ',           master_log_pos=265; mysql>start  slave; mysql>show slave status \g              slave_io_ running:yes                                          slave_sql_running:yes      If the two items show Yes, the connection is synchronized successfully.

4, Test
on the master side of the creation or Superman operation, from the server can automatically achieve synchronization, the master-slave replication to achieve success.