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

Brothers, Lao Zhang meets you again. I am very pleased to see the messages and visits you have given me in the blog, and thank you for your recognition. I write these blogs because I want to share some of my views on MySQL database and my peacetime practical experience, so that we can discuss and make progress together. It is also guaranteed to update the blog post as soon as you have time in the future and launch more practical information.

My students often say to me, "Mr. Zhang, every time I report a mistake, sometimes I will still be Baidu, but the most annoying thing is that I don't know which post is correct." In fact, these are all because they are not familiar with the core knowledge of MySQL database, and do not have a firm grasp of technology. The usual efforts are still not in place. When we work in the technology industry, we still need to wind ourselves up so that we have to learn some new knowledge every day. Theory with experiments together, first learn to ask yourself a few more questions, an experiment more than a few times, you may get different experimental results. We should be down-to-earth in learning knowledge and learn to do more experimental summaries. I think I will have a clear way to solve the problem if I encounter mistakes again in the future, which takes a certain amount of time to hone.

Some people often ask what is the difference between Oracle and MySQL. In fact, it is easy to get started with MySQL database, but it is difficult to design and maintain the architecture in the later stage. MHA, the first part of Lao Zhang's trilogy, hopes to be helpful for everyone to deploy online.

Today, I would like to introduce you to the second part, MM+keepalived environment deployment, we will be a variety of database architecture can be flexibly applied to our company. According to the different business of the company, choose the appropriate cluster architecture.

Exclusive New course launch > > in-depth Analysis of MySQL Architecture and practical DBA Video course

MM+keepalived

Brief introduction:

The architecture design of dual-master and keepalived is also based on the principle of master-slave replication.

Using MySQL master replication technology + Keepalived is a simple and convenient solution. In a highly available cluster environment, keepalived uses VIP and Keepalived's own service monitoring features and custom scripts to achieve automatic switching in case of MySQL failure.

Scope of application:

In general, small and medium-sized companies use this architecture, which is more convenient and simple to build.

Master-slave mode or master-master mode can be adopted. After the failure of master node, the high availability mechanism of keepalived can be used to quickly switch to slave node. The original library becomes the new master library.

Personal advice:

Be sure to improve the switching script, and the switching mechanism of keepalived should be reasonable to avoid the phenomenon of unsuccessful switching.

The configuration of the slave library should be the same as that of the master database as soon as possible, not too secondary; to avoid switching between the master library and the master database, the new master library (the original slave library) will affect the online business.

The problem of delay can not be avoided in this architecture. Enhanced semi-synchronous completion in mysql 5.7can be used. You can also change the architecture and use PXC to complete the synchronization function without delay.

Keepalived can not solve the problem of brain fissure, so when judging service anomalies, we can modify our judgment script to decide whether to switch through the supplementary detection of third-party nodes, which can reduce the risk of brain fissure problems.

Using the keepalived architecture, when setting the state of the two nodes, it should be set to the non-preemptive mode, both of which are backup state, and determine who is the main library by priority. Avoid brain fissure and conflict.

Install some dependency packages needed for mysql; it is recommended to configure the yum source and install keepalived with yum.

Clear your mind before building the architecture:

First of all, we need to install two mysql database servers, both of which can read and write to each other in the master-slave mode. In fact, there is only one server A responsible for data writing, and the other server B is our backup database.

To install keepalived software package, it is recommended to use yum to install it. It is very convenient. Of course, we need to know where the software path is after yum is installed.

Sort out the configuration file of keepalived and sort out the three status information of keepalived. Also prepare a script to monitor MySQL so that a smooth switchover can be detected when a downtime occurs.

All slave servers that provide services synchronize with standby server B. -dual master-slave library mode

In two servers An and B, when configuring the parameter file of keepalived, it should be noted that both machines should adopt the state of backup, which is the non-preemptive mode of nopreempt to avoid conflicts and brain fissure.

The architecture diagram shows:

Introduction to the lab deployment environment:

192.168.56.100 Master-master1

192.168.56.101 prepare the library-master2

It's all a clean environment without any data.

VIP:192.168.56.111

MySQL database version 5.7.14, using GTID mode to build master-slave environment

Note:

The firewalls of both machines must be turned off.

The server-id in the configuration files of the two MySQL databases must not be the same, or the 1593 master-slave synchronization error will be reported, resulting in unsuccessful construction.

The actual combat begins:

First of all, it is necessary to build two MySQL databases as master-slave architecture.

Add master-slave synchronization account:

At 192.168.56.100:

Grant replication slave on *. * to 'bak'@'192.168.56.%' identified by' 123456'

Flush privileges

At 192.168.56.101:

Grant replication slave on *. * to 'bak'@'192.168.56.%' identified by' 123456'

Flush privileges

Configure synchronization information:

First on 192.168.56.101:

CHANGE MASTER TO MASTER_HOST='192.168.56.100',MASTER_USER='bak',MASTER_PASSWORD='123456',master_auto_position=1

Turn on the master-slave synchronization switch:

Start slave

Check the master-slave synchronization status:

Root@db 10:49: [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.100 Master_User: bak Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mybinlog.000005 Read_Master_Log_Pos: 234 Relay_Log_File: node2-relay-bin.000010 Relay_Log_Pos: 365 Relay_Master_Log_File: mybinlog.000005 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: 234 Relay_Log_Space: 572 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: 3306100 Master_UUID: 913cc1d6-771a-11e7 -8fe1-080027f1fd08 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 5a668126-771d-11e7-b14c-08002783b39d:1-2913cc1d6-771a-11e7-8fe1- 080027f1fd08:1-13 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) two working threads from the library: IO All SQL are yes, which means that Slave_IO_Running: YesSlave_SQL_Running: Yes is built successfully.

Then on 192.168.56.100:

Configure master-slave synchronization information:

CHANGE MASTER TO MASTER_HOST='192.168.56.101',MASTER_USER='bak',MASTER_PASSWORD='123456',master_auto_position=1

Turn on the master-slave switch:

Start slave

Check the master-slave synchronization status:

Root@db 10:49: [(none)] > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.101 Master_User: bak Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mybinlog.000005 Read_Master_Log_Pos: 234 Relay_Log_File: node1-relay-bin.000008 Relay_Log_Pos: 365 Relay_Master_Log_File: mybinlog.000005 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: 234 Relay_Log_Space: 572 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: 3306101 Master_UUID: 5a668126-771d-11e7 -b14c-08002783b39d Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log Waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 5a668126-771d-11e7-b14c-08002783b39d:1-2913cc1d6-771a-11e7-8fe1- 080027f1fd08:1-13 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) similarly Slave_IO_Running: YesSlave_SQL_Running: Yes two working threads from the library: IO SQL are all yes, which means that synchronization is built successfully.

Install keepalived software packages on two separate machines, you can use yum installation method

Yum-y install keepalived

Loaded plugins: fastestmirror, refresh-packagekit SecurityLoading mirror speeds from cached hostfile * c6-media: file:///media/CentOS/repodata/repomd.xml: [Errno 14] Could not open/read file:///media/CentOS/repodata/repomd.xmlTrying other mirror. file:///media/cdrecorder/repodata/repomd.xml: [Errno 14] Could not open/read file:///media/cdrecorder/repodata/repomd.xmlTrying other mirror.c6-media | 4.0 kB 00:00. Setting up Install ProcessResolving Dependencies-- > Running transaction check--- > Package keepalived.x86_64 0vir 1.2.7-3.el6 will be installed-- > Finished Dependency ResolutionDependencies Resolved= Package Arch Version Repository Size=Installing: keepalived x86 through 64 1.2.7-3.el6 c6-media 174 kTransaction Summary=Install 1 Package (s) Total download size: 174 kInstalled size: 526 kDownloading Packages:Running rpm_check_debugRunning Transaction TestTransaction Test SucceededRunning Transaction Installing: keepalived-1.2.7-3.el6.x86_64 1 Verifying: keepalived-1.2.7-3.el6.x86_64 1 Installed: keepalived.x86_64 0pur1.2.7-3.el6 Complete!

Prove that the keepalived software already exists:

Rpm-qa | grep keepalivedkeepalived-1.2.7-3.el6.x86_64

Configure scripts to detect the MySQL database on both machines:

First, go to the software directory after yum installation:

Cd / etc/keepalivedvim check_mysql.py

The script judges the status of the MySQL by checking the existence of the MySQL process and whether it can be connected.

(only part of the judgment in the script is shown here)

Def existMySQL (self): cmd= "ps-ef | egrep-I\" mysqld\ "| grep% s | egrep-iv\" mysqld_safe\ "| grep-v grep | wc-l"% self.dbport mysqldNum = os.popen (cmd). Read () cmd= "netstat-tunlp | grep\":% s\ "| wc-l"% self.dbport mysqlPortNum= os.popen (cmd). Read () # print mysqldNum MysqlPortNum if (int (mysqldNum) 0 and mysqlPortNum > MHA of the Trilogy of mainstream High availability Architecture of MySQL in Enterprises

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.