MySQL master-slave replication overview:MySQL master-slave replication, can achieve read-write separation and multiple automatic backup, improve database load capacity and security. Such as:
When the Web server writes data to the MySQL database, it is uniformly written to the master library where master resides. When the Web server reads data from the MySQL database, it is read from the library from its own slave. Data updates between the main library and the library are performed in the form of "Exception update", which sends heartbeat packets from the library to the main library, detects the changes in the binary log file of the main library, and copies the changes to its own trunk log if changes are found. Then a SQL thread from the library executes the relevant "event" into its own database, thus achieving the data consistency of the master-slave database, which is the master-slave replication. Such as:
MySQL master-slave replication configuration:
- Primary server configuration:
- to open the binary log (insert in MY.CNF (or My.ini): Log-bin=mysql-bin #开启二进制日志)
- Configure a unique Server-id (in my.cn F (or My.ini) Insert: server-id=1 #设置server-id)
- get the master binary log file name and location (perform sql:show master status get file and position)
- Create a user account for slave and master communications, as shown in the following example ( user:rel1 password: slavepass ):
- Mysql>&nbs P CREATE USER ' repl ' @ ' 123.57.44.85 ' identified by ' Slavepass '; # Create user
- mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 123.57.44.85 '; &N Bsp #分配权限
- mysql> flush privileges; #刷新权限
- Configuration from the server:
- Enable the Slave service (perform sql:start slave)
- Configure unique Server-id (insert in my.cnf (or My.ini): server-id=2 #设置server-id)
- Using the master assigned user account to read the master binary log, restart MySQL, execute the SQL statement as follows:
- MySQL > Change MASTER to
- > master_host= ' 182.92.172.80 ',
- > master_user= ' Rep1 ',
- > master_password= ' Slavepass ',
- > master_log_file= ' mysql-bin.000003 ',
- > master_log_pos=73;
mysql--Master-slave replication
