MySQL DBA system learning (%) MySQL master-slave replication implementation

The implementation of MySQL master-slave replication

1, the implementation of MySQL replication principle

MySQL supports one-way, bidirectional replication, asynchronous replication, one server acting as the primary server during the replication process, and one or more other servers acting as a server. The master server writes the update to a binary log file and creates an index file to track the log loop. These logs can record updates sent to the server. When a primary server is connected from a server, the log file notifies the primary server of the location of the last successful update that was read from the server in the log. Next, start the update operation from the server at the location where the last successful update was made. After the update completes, the wait state is started from the server and waits for subsequent updates from the primary server. It is important to note that all updates to the tables in replication must be done on the primary server while replication is in progress. Otherwise, there may be a conflict between updates to the tables on the primary server and updates to the tables on the server.

2, MySQL sync details

The MySQL synchronization feature is implemented by 3 threads (2 on Master 1 binlogdump,slave, respectively, SQL processes and IO processes). After the "Startslave" statement is executed, slave creates an I/O thread. The I/O line Chenglian to master and requests master to send the statements in the binary log. Master creates a thread to send the contents of the log to the slave. The I/O thread on the slave reads the statements sent by Master's Binlogdump thread and copies them to the relay log (relaylogs) in its data directory. The third is the SQL thread, which salve use to read the relay logs and then execute them to update the data.

The advantage of using 2 threads on Slave is that it separates read logs from execution into 2 separate tasks. If you perform a task slowly, the read log task will not slow down.

For example, if slave stops for a while, the I/O thread can quickly read all the logs from Master after the slave startup, even though the SQL thread may be behind the I/O thread for several hours. If the slave is stopped when the SQL thread is not fully executed, the I/O thread has read all the update logs and saved them in the local relay log, so they will continue to execute after slave starts again. This allows the binary log to be purged on master because slave no longer has to read the update log to master.

3, install MySQL on the Test1,test2

Test1 's Ip:192.168.46.131test2 ip:192.168.46.130.

Test1 as Mastertest2 for slave

There are several ways to install MySQL, only the source installation test1 for the column description.

[Root@test1 ~]# yum install libtermcap libtermcap-devel imake autoconf automake libtool M4 [Root@test1 ~]# Useradd-  M-s/sbin/nologin MySQL [root@test1 ~]# mkdir/usr/local/mysql [root@test1 ~]# tar zxvf mysql-5.1.70.tar.gz [root@test1 ~]# CD mysql-5.1.70 [Root@test1 mysql-5.1.70]#./configure--prefix=/usr/local/mysql--enable-assembler--with-client -ldflags=-all-static--with-unix-socket-path=/tmp--with-charset=utf8--enable-thread-safe-client--with-pthread-- Without-debug--with-big-tables--enable-community-features--enable-profiling--enable-local-infile--with-fast-mu TExES--with-plugins=partition,federated,ndbcluster,innobase,csv,blackhole,myisam,innodb_plugin [Root@test1 MYSQL-5.1.70] #make [root@test1 mysql-5.1.70] #strip sql/mysqld [root@test1 mysql-5.1.70] #makeinstall [root@test1 mysql-5.1.70]# CP support-files/my-medium.cnf/etc/my.cnf [Root@test1 mysql-5.1.70]#/usr/local/mysql/bin/mysql_ install_db--user=mysql [Root@test1 mysql-5.1.70]# Echo"path=\"/usr/local/mysql/bin:\ $PATH \ "" ">>/etc/profile [root@test1 mysql-5.1.70]# export path="/usr/local/ Mysql/bin: $PATH "

4, edit the configuration file on the primary server test1 MY.CNF

Edit the configuration file/etc/my.cnf for the master server and add the following in [Mysqld]:

Server-id=1

#服务器ID. Cannot have duplicate IDs between servers, general master is 1

Log-bin=mysql-bin

#打开mysql的binlog功能, the name can be specified on its own, if not changed the name, the default is named after the host name

Binlog-do-db=test

#test是需要备份的数据库名, if you back up multiple databases, you can set this option repeatedly

Binlog-ignore-db=mysql

#不需要备份的数据库名称, if you need to ignore backing up multiple databases, you can set this option repeatedly.