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

This article mainly gives you a brief introduction to the use and principle of mysql master-slave replication. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the use and principle of mysql master-slave replication can bring you some practical help.

Mysql master-slave replication

The purpose of mysql master-slave replication:

Real-time disaster recovery for failover

Separation of reading and writing, providing query service

Backup to avoid affecting the busin

Necessary conditions for master-slave deployment:

Master-slave mysql version is the same

Binlog log is enabled in the main library (set log-bin parameter)

Master-slave server-id is different.

The slave cloud server can connect to the master database.

Principle of master-slave replication:

The bin_log log function is enabled on the main sql. It records the data information of sql and stores it in disk in a binary way. From the CVM, the bin_ log binary log of the master CVM is requested to be read through the iCompo thread, and the log information is written to the relay log (Relay Log) file; the log in the relay log file is read from the SQL thread of the library and parsed into specific sql statement operations to achieve the unification of the data and the completion of master-slave replication.

Environment introduction:

HostNameOSIP role mastercentos6.5192.168.100.150 serves as mysql master CVM salvecentos6.5192.168.100.151 serves as mysql slave CVM ftpcentos6.5192.168.100.100 acts as ftp provides yum source and software support (you can use public network yum source instead of this host)

1: install mysql from master to slave:

[root@master ~] # yum-y install mysql-server [root@slave ~] # yum-y install msyql-server

2: modify the master-slave configuration file to support bin_log logging

[root@master ~] # vi / etc/my.cnf 7 log-bin=mysql-bin # # supports bin-log logging. The bin-log log file name starts with mysql-bin and is the unique identifier of 8 server-id=150 # # service. The default is 1. It is easy to remember here. I used the last paragraph of ip [root@slave ~] # vi / etc/my.cnf 7 server-id=151 [root@master ~] # / etc/init.d/mysqld start # # restart the service [root@slave ~] # / etc/init.d/mysqld start

3: grant slave replication permissions on the master database:

Log in to CVM for authorization

[root@master ~] # mysqladmin-uroot password 123123 [root@master ~] # mysql-uroot-p123123mysql > grant replication slave on *. * to 'slave'@ "192.168.100.%" identified by' 123123 transactions query OK, 0 rows affected (0.00 sec) mysql > flush privileges; # # Refresh permission Query OK, 0 rows affected (0.00 sec) mysql >

View the bin-log log file information of the main service:

Need to record the contents of file and position: whichever is found shall prevail.

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | mysql-bin.000003 | 476 | +-+ 1 row in set (0.00 sec)

4: modify your master database from the CVM

Log into the database

[root@slave ~] # mysqladmin-uroot password 123123 [root@slave ~] # mysql-uroot-p123123

Set the information about reading master bin-log from CVM

Mysql > change master to-> master_host='192.168.100.150', # # master ip-> master_user='slave', # # user name authorized to allow replication-> master_password='123123', # # authorized allow replication password-> master_log_file='mysql-bin.000003', # # bin-log file name The information found on master in the previous step-> master_log_pos=476 # # offset, the information found on master Query OK, 0 rows affected (0.07 sec)

Start slave

Mysql > start slave;Query OK, 0 rows affected (0.00 sec)

Card slave status:

# # the two checked statuses are yes. If there is no error error below, it will be normal Slave_IO_Running: Yes Slave_SQL_Running: Yes

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.100.150 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 706 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 481 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 706 Relay_Log_Space: 637 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) ERROR: No query specifiedmysql >

5: test:

Create a new library on the master database and view the library

Mysql > mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec) mysql > create database test_databases;Query OK, 1 row affected (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | test | | test_databases | +-+ 4 rows in set (0.00 sec)

View the library from the database:

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | test | | test_databases | +-+ 4 rows in set (0.00 sec)

(you can see the new library on the main database, copied over)

Delete operations can also be synchronized:

Lord:

Mysql > drop database test_databases;Query OK, 0 rows affected (0.00 sec) mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec) mysql >

From:

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | test | +-+ 3 rows in set (0.00 sec) mysql >

The use and principle of mysql master-slave replication will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.