MySQL master-slave Replication

Mysql replication is asynchronous. Therefore, after receiving a user's request, the result is returned to the user. And store the information in the memory, and then record it into the binary file. This will

Mysql replication is asynchronous. Therefore, after receiving a user's request, the result is returned to the user. And store the information in the memory, and then record it into the binary file. This will

I. MySQL replication principles
II. Implementation of MySQL master-slave Replication
Iii. Event-based Replication
Iv. semi-synchronous Replication
V. MySQL Master/Slave replication based on ssl

I. Mysql replication principles

For the master server, the information generated in the current database will be recorded in the binary log. If the server receives a Statement on the client that needs to change the data, if InnoDB is used for this data, the data itself is first recorded in the transaction log, and the statements that affect the data will be recorded in the binary log, and the copy statement is, another slave server passively accepts data and does not accept client links for any write operations. replication is unidirectional and can only be performed from the master server to the slave server, the data on the slave server is from the master server. The master service listens to port 3306 and starts a client process from the slave server. This process initiates a request to the port on which the data service is provided, the request reads the events in the binary log. On the master server, mysql provides the authentication service and starts a sqldump thread. After receiving the user's request, make sure that the user has the permission to read the events in the binary log. If the client requests the event for the first time, the process reads the first event in the log, read one and send it to the client. After receiving the data from the master server, the service process stores the local relay log, and then starts a thread with special functions to read the event on the relay day, read a row, execute it locally, and so on, and finally generate a local database. Mysql is asynchronous during replication. Therefore, after receiving a user's request, the result is returned to the user. And store the information in the memory, and then record it into the binary file, this will cause the slave server to lag behind the master server, but this is also good, if you have an error operation, in this time, it is OK to back up data from the server immediately, which requires the mysql administrator to grasp.

II. Implementation of mysql master-slave Replication

1. Ideas:

Master Server:

Create a user account with the copy permission

Set server-id

Enable binary log

Slave Server:

Enable relay logs. (Disable binary logs)

Set server-id

Start the replication thread

2. Process implementation:

2.1 test environment:

Virtual Host version:

CentOS6.4-i686

Two nodes:

Node1.limian.com 172.16.6.10

Node2.limian.com 172.16.6.1

2.2 synchronization time on two nodes

1 [root @ node1 ~] # Ntpdate 172.16.0.1

2.3 copy the mysql source code to another node

1 [root @ node1 ~] # Scp mysql-5.5.33-linux2.6-i686.tar.gz MAID:/root/

2.4 compile mysql with source code

Node 1

[Root @ node1 ~] # Tar xf mysql-5.5.33-linux2.6-i686.tar.gz-C/usr/local // unzip mysql
[Root @ node1 ~] # Mkdir-pv/mydata/data // create a data directory
[Root @ node1 ~] # Useradd-r-u 306 mysql // create a user
[Root @ node1 ~] # Chown-R mysql. mysql/mydata/data // modify the owner Group of the data Directory
[Root @ node1 ~] # Cd/usr/local/
[Root @ node1 local] # ln-sv mysql-5.5.33-linux2.6-i686 mysql // create a connection
[Root @ node1 local] # chown-R root. mysql // * // modify the owner group in the mysql directory
[Root @ node1 mysql] # scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data // initialize mysql
[Root @ node1 mysql] # cp support-files/my-large.cnf/etc/my. cnf // provides the configuration file
[Root @ node1 mysql] # vim/etc/my. cnf
Datadir =/mydata/data // specify the data directory
Innodb_file_per_table = 1 // open a single table space
[Root @ node1 mysql] # cp support-files/mysql. server/etc/rc. d/init. d/mysql // provide the Startup Script
[Root @ node1 mysql] # chmod + x/etc/rc. d/init. d/mysqld // grant the script execution permission
[Root @ node1 mysql] # vim/etc/profile. d/mysql. sh // change the environment variable
Export PATH =/usr/local/mysql/bin: $ PATH
[Root @ node1 mysql] #./etc/profile. d/mysql. sh // re-read the file
[Root @ node1 mysql] # chkconfig -- add mysqld // add to service list
[Root @ node1 mysql] # service mysqld start // start mysql

2.5 Node 2 also performs the above operation

2.6 configure the master server

[Root @ node1 ~] # Vim/etc/my. cnf
Server-id = 1 // set the ID
Log-bin = mysql-bin // start the binary log
Binlog_format = mixed // specify the binary log record format as mixed Mode
[Root @ node1 mysql] # mysql
Mysql> grant replication slave, replication client on *. * TO reuser @ '1970. 16. %. % 'identified by 'repass'; // create a user with the copy permission
Mysql> flush privileges; // re-read the authorization table
Mysql> show master status; // view the event location in the binary log
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000003 | 352 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

2.7 configure slave server:

[Root @ node2 ~] # Vim/etc/my. cnf
Add the following two lines
Server-id = 20 // specify the serverID
Relay-log =/mydata/data/relay-bin // specify the location of the relay log
Read-only = 1 // Enable read-only mode
[Root @ node2 ~] # Service mysqld restart // restart mysql
[Root @ node2 ~] # Mysql
Mysql> show slave status; // view the status of the slave server
Empty set (0.02 sec) // The result shows that no threads are started.
Mysql> show processlist; // view the startup thread
+ ---- + ------ + ----------- + ------ + --------- + ------ + ------- + ------------------ +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + ------ + ----------- + ------ + --------- + ------ + ------- + ------------------ +
| 1 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+ ---- + ------ + ----------- + ------ + --------- + ------ + ------- + ------------------ +
1 row in set (0.03 sec)
The result shows that no threads are started.

2.8 configure the slave server to connect to the master server