Implementation of MySQL master-slave replication, semi-sync, and master-master replication Architecture

The data synchronization function of mysql not only provides load balancing for database queries to a certain extent, but also provides great help for database redundancy, backup, recovery, and load balancing. The data synchronization function can be implemented through master-slave replication, while master-slave replication is performed asynchronously. mysql only supports one master and multiple slaves, and does not support the replication model of one slave and multiple slaves.
 
1. Principle of master-slave replication: (for example)
 
Step 1: The master server records data changes to binary logs before each update task is completed. Even if the transaction is staggered during execution, mysql will write the transaction into the binary log serially. After the write is complete, the master server tells the storage engine to call the transaction.
Step 2: copy the binary log of the master server to your hard disk, that is, "relay log. First, it starts a working thread called an I/O slave thread. This I/O thread starts a Common Client Connection and then starts a special binary log storage process (binlog dump. The dump process reads the event from the binary log of the master server, and does not poll the transaction. If it keeps up with the master server, it enters the sleep state and waits for the signal sent by the master server when a new event occurs, the I/O thread writes the event to the relay log of the slave server.
Step 3: SQL reads the relay log from the thread, replays the event, and updates the data from the server. Because this process can keep up with the I/O thread, relay logs are usually stored in the operating system cache, and the overhead of all relay logs is very low. Events executed by SQL from the thread can also be written to or not written to the server's own binary log.
 

 
The application environment in this article is:
RedHat enterprise 5.4 + mysql-5.5.20
 
Master Server:
IP Address: 172.16.30.5
Hostname: master.magedu.com
 
Slave Server:
IP Address: 172.16.30.6
Hostname: slave.magedu.com
 
This article focuses on:
1. Install the mysql server
II. Implementation of the master-slave replication architecture (two application scenarios)
Iii. Implementation of master-slave semi-sync
Iv. Recommended settings for Master/Slave servers
5. Implementation of the master-master replication Architecture
 
 
1. Install the mysql server (which must be installed on both our master and slave servers ):
1. First download the mysql green version corresponding to the platform to the local device. Here is the 32-bit platform:
Mysql-5.5.20-linux2.6-i686.tar.gz
 
2. Create a user to run the mysqld process securely:
 
# Groupadd-r mysql
# Useradd-g mysql-r-s/sbin/nologin-M mysql
# Mkdir-p/mydata/data
# Chown-R mysql: mysql/mydata/data
 
3. Install and initialize the mysql-5.5.20
 
# Tar xf mysql-5.5.20-linux2.6-i686.tar.gz-C/usr/local
# Cd/usr/local/
# Ln-sv mysql-5.5.20-linux2.6-i686 mysql
# Cd mysql
 
# Chown-R mysql: mysql.
 
Initialize the mysql database and run it as a mysql user. The database data is stored in/mydata/data. After initialization, the default password of the mysql root User is blank:
# Scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data
 
# Chown-R root.
 
4. Provide the primary configuration file for mysql:
 
# Cd/usr/local/mysql
# Cp support-files/my-large.cnf/etc/my. cnf
 
# Vim/etc/my. cnf
Modify the value of thread_concurrency in this file to multiply the number of CPUs by 2. For example, use the following line:
Thread_concurrency = 2
 
You also need to add the following lines to specify the storage location of mysql Data Files:
Datadir =/mydata/data
 
5. Provide the sysv service script for mysql, which can be started with the service Command Controller:
 
# Cd/usr/local/mysql
# Cp support-files/mysql. server/etc/rc. d/init. d/mysqld
 
Add to service list:
# Chkconfig -- add mysqld
# Chkconfig mysqld on
 
Start the mysql service:
# Service mysqld start
 
To use mysql installation to comply with the system usage specifications and export its development components to the system, perform the following steps:
6. output the mysql man manual to the man command search path:
 
# Vim/etc/man. config. Add the following line:
MANPATH/usr/local/mysql/man
 
7. output the mysql header file to the system header file path/usr/include:
 
This can be achieved through simple creation links:
# Ln-sv/usr/local/mysql/include/usr/include/mysql
 
8. output the mysql database file to the system database search path:
 
# Echo '/usr/local/mysql/lib'>/etc/ld. so. conf. d/mysql. conf
 
Then let the system reload the system database:
# Ldconfig
 
9. Modify the PATH environment variable so that the system can directly use mysql commands:
Vim/etc/profile
PATH = $ PATH:/usr/local/mysql/bin

1 2 3 4 Next Page