Shulou(Shulou.com)06/01 Report--

I. Overview:

MYSQL master-slave synchronization architecture is one of the most widely used database architectures. Master-slave synchronization enables data to be replicated from one database server to another server. When replicating data, one server acts as the master server (master) and the rest of the server acts as the slave server (slave).

2. Description of the topology diagram:

As shown in the figure above, 192.168.4.10 (hostname "10.mysql") is the MySQL master database, and 192.168.4.20 (hostname "20.mysql") is the MySQL slave database responsible for synchronizing data from the master database.

3. Master-slave synchronization configuration description:

1. Master configuration

(1) Database authorization:

Mysql > grant replication slave on *. * to slaveuser@ "192.168.4.12" identified by "123456"

(2) enable binlog log:

[root@10 ~] # vim / etc/my.conf

[mysqld]

Server_id=10 # specify the ID of the main MySQL database

Log-bin=master10 # enables binglog logging. Log files are saved in the MySQL installation directory (/ var/lib/mysql). The file name is in the format of "master22.000001" (maximum capacity of each file is 500m, exceeding 500m or the next log file is automatically generated after the MySQL service is restarted)

(3) check the master status:

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | master10.000001 | 154 | |

+-+

1 row in set (0.00 sec)

2. Slave configuration:

(1) configure server_id:

[root@20 ~] # vim / etc/my.conf

[mysqld]

Server_id=20 # specifies the ID from the MySQL database

(2) configure the information of the master database (log in with local root users)

Mysql > change master to master_host= "192.168.4.10"

Master_user= "slaveuser"

Master_password= "123456"

Matser_log_file= "master10.000001"

Master_log_pos=154

(3) enable slave status

Mysql > start slave

(4) check slave status

Mysql > show slave status\ G

The following two items in the command execution result show that the master-slave synchronization configuration is normal.

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

3. Verify:

Add and delete data on the master database (192.168.4.10), and you can see it on the slave database (192.168.4.20) in time.

Third, the principle explanation:

(1) read the binlog log of the master database from the IO thread of the database, write it to the relay log file of the slave database, and read the SQL command from the local relay log from the SQL thread of the database to complete data synchronization.

(2) if the data is inconsistent before the master-slave database is configured, the SQL thread will report an error; if there is an error in creating an authorized user in the master database, resulting in the slave database being unable to connect to the master database, the IO thread will report an error

(3) when the execution of the SQL command in the relay log from the SQL thread of the database fails, the SQL thread will crash immediately.

It is important to note that before configuring the MySQL master-slave database, make sure that there is no more data in the slave database than in the master database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.