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

This article mainly introduces the romance of MySQL 5.5 master-slave replication and deployment. I hope I can add and update some knowledge to you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

I. Environmental description

Two installed MySQL 5.5databases. Distinguish between Master and Slave

Master:

IP address (172.16.1.210):

[root@Qinglin-A /] # ifconfig eth0eth2 Link encap:Ethernet HWaddr 00:0C:29:B4:0A:5D inet addr:192.168.1.210 Bcast:172.16.1.255 Mask:255.255.255.0

MySQL version:

[root@Qinglin-A /] # mysql-Vmysql Ver 14.14 Distrib 5.5.32, for Linux (x86 / 64) using readline 5.1

Slave:

IP address (172.16.1.220)

[root@Qinglin-B /] # ifconfig eth0eth2 Link encap:Ethernet HWaddr 00:0C:29:0E:20:1D inet addr:192.168.1.220 Bcast:172.16.1.255 Mask:255.255.255.0

MySQL version:

[root@Qinglin-B /] # mysql-Vmysql Ver 14.14 Distrib 5.5.32, for Linux (x86 / 64) using readline 5.1

II. Actual combat deployment

MySQL master-slave synchronization is an asynchronous replication mode, that is, data synchronization is not strictly real-time synchronization.

Knowledge that must be known before actual combat

a. Master-slave replication is asynchronous, logical SQL statement-level replication.

b. When synchronizing, the master library has one IO thread and the slave library has two threads, IO and SQL threads.

c. To achieve the necessary conditions of master-slave replication, the master library should turn on the binlog function.

1. Master enable binlog and set server-id

Note: server id is to avoid repetition of different machines or actual ID. Select 0 show variables like "log_bin". +-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | +-+-+ 1 row in set (0.00 sec) mysql > show variables like "server_id" +-+-+ | Variable_name | Value | +-+-+ | server_id | 1 | +-+-+ 1 row in set (0.00 sec)

5. Master creates and authorizes slave accounts for network segment access, so that slave can access master

The following is to create a Slave user, refresh permissions, and view user permissions.

Mysql > grant replication slave on *. * to 'rep'@'192.168.1.%' identified by'123456';Query OK, 0 rows affected (0.00 sec) mysql > flush privileges; Query OK, 0 rows affected (0.00 sec) mysql > show grants for rep@'192.168.1.%' +- -+ | Grants for rep@192.168.1.% | + -+ | GRANT REPLICATION SLAVE ON *. * TO 'rep'@'192.168.1.%' IDENTIFIED BY PASSWORD' * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | + -+ 1 row in set (0.00 sec)

6. To record the binlog locations and points of Master, please keep in mind the following yellowing points, which will be used later.

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

7. Lock table to back up all databases of Master

-A back up all databases

-B export several databases

-F-flush-logs refresh logs before starting export Note: if you export more than one database at a time (using the option-databases or-all-databases), the logs will be refreshed one by one

-x lock table to ensure data consistency

-events export event

-master-data=2 if = 2 is generated as a comment and is not executed.

[root@Qinglin-A /] # mysqldump-uroot-pendant 123456'-- events-A-B-F-- master-data=2-x | gzip > / home/bak_ `date +% F`.sql.gz [root@Qinglin-A /] # ll / home/bak_2016-08-02.sql.gz-rw-r--r-- 1 root root 144365 Aug 2 15:16 / home/bak_2016-08-02.sql.gz

8. Slave recovers the database, because I increase the B parameter, so there is no need to specify the library recovery

Note: from the backup files of master to slave, you can consider using scp to transfer to slave. There is no demonstration here.

[root@Qinglin-B guanqinglin] # gzip-d bak_2016-08-02.sql.gz [root@Qinglin-B guanqinglin] # lsbak_2016-08-02.sql [root@Qinglin-B guanqinglin] # mysql-uroot-p123456

9. Import master information of slave (slave operation)

Before operation, please use the following method to test whether the user and remote login are OK, execute the following command, and log in to the mysq > interface as successful.

[root@Qinglin-B /] # mysql-h 192.168.1.210-urep-p123456Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 7Server version: 5.5.32-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql >

Exit the mysql interface executed above and re-use the mysql interface of root login Slaver to perform the import Master information operation.

[root@Qinglin-B /] # mysql-uroot-p123456 Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 5Server version: 5.5.32 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > CHANGE MASTER TO-> MASTER_HOST='192.168.1.210',-> MASTER_PORT=3306,-> MASTER_USER='rep',-> MASTER_PASSWORD='123456',-> MASTER_LOG_FILE='mysql-bin.000001',-> MASTER_LOG_POS=332;Query OK, 0 rows affected (0.12 sec)

10. Slave to view master info information

[root@Qinglin-B /] # cat / application/mysql/data/master.info 18mysql-bin.000001332192.168.1.210rep123456330660001800.0000

11. Enable slave and enable slave

[root@Qinglin-B /] # mysql-uroot-p123456 Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 6Server version: 5.5.32 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > start slave; Query OK, 0 rows affected (0.01 sec)

12. Check that if there are two yellow OK, it starts normally (Seconds_Behind_Master 0 is the number of seconds behind the main database, 0 is correct)

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.210 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 107 Relay_Log_File: Qinglin-B-relay-bin.000004 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000002 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: 107 Relay_Log_Space: 456 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 11 row in set (0.00 sec) Test link

1. Master creates a database

Mysql > create database qinglin;Query OK, 1 row affected (0.09 sec)

2. Slave to check whether the create database qinglin created by the master database exists in the slave library relay file

Note: the following is the intercepted content

[root@Qinglin-B data] # mysqlbinlog Qinglin-B-relay-bin.000004 SET @ @ session.collationalization databaseDeFAULTAccording to create database qinglinbank and create session qinglinbank to create session. Collationalization database database

3. Check whether salve has created a database

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | qinglin | +-+

4 rows in set (0.00 sec)

Read the above about MySQL 5.5 master-slave copy actual combat deployment Romance, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.