Shulou(Shulou.com)05/31 Report--

This article mainly introduces the steps of "MySQL master replication + Keepalived to create a highly available MySQL cluster". In daily operation, I believe many people have doubts about the steps of MySQL master replication + Keepalived to create a high availability MySQL cluster. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MySQL master replication + Keepalived to create a highly available MySQL cluster". Next, please follow the editor to study!

The following are all experimental environments. Please modify the response parameters according to the actual situation.

Experimental environment:

Mysql1 ip:10.1.1.20

Mysql2 ip:10.1.1.21

Mysql vip:10.1.1.25

Centos 6.5_x64 (virtual machine environment) is installed on all three machines

The experiment begins.

First, install mysql and create master synchronization.

It is believed that master-slave synchronization will be done by everyone. By the same token, master-master synchronization is the relationship between two machines, and writing on any machine will synchronize.

Yum will be fine if the process of installing mysql is not explained.

Configure primary primary synchronization

1. Configuration / etc/my.cnf

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

Log-bin=binlog # enable binlog function

Log-bin-index=binlog.index

Sync_binlog=0

Server_id=1 # two machines cannot repeat one server_id=1 and one server_id=2

[mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

two。 Configure synchronization accounts on two machines respectively

10.1.1.20 on the machine:

[root@localhost ~] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.0.77-log Sourcedistribution

Type 'help;' or'\ h' for help. Type'\ c 'toclear the buffer.

Mysql > GRANT replication slave ON *. * TO'ab'@'%' identified by '123'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

10.1.1.21 Machine:

[root@localhost ~] # mysql

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.0.77-log Sourcedistribution

Type 'help;' or'\ h' for help. Type'\ c 'toclear the buffer.

Mysql > GRANT replication slave ON *. * TO'ab'@'%' identified by '123'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Note: because this article is written in the experimental environment, so do not consider any security issues, synchronization account is also the highest authority, please set the response permission according to the actual situation!

3. Set up synchronization

10.1.1.20 on the machine:

Mysql > flush tables with read lock

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | binlog.000003 | 365 | |

+-+

1 row in set (0.03 sec)

Mysql > unlock tables

Query OK, 0 rows affected (0.03 sec)

10.1.1.21 Machine:

Mysql > change master tomaster_host='10.1.1.20', master_port=3306, master_user='ab',master_password='123', master_log_file='binlog.000003',master_log_pos=365

Query OK, 0 rows affected (0.06 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G # pay attention to the following two parameters after executing this command, both of which must be yes.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Do the same thing the other way around.

10.1.1.21 Machine:

Mysql > flush tables with read lock

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | binlog.000004 | 207 | |

+-+

1 row in set (0.03 sec)

Mysql > unlock tables

Query OK, 0 rows affected (0.03 sec)

10.1.1.20 on the machine:

Mysql > change master tomaster_host='10.1.1.21', master_port=3306, master_user='ab',master_password='123', master_log_file='binlog.000004',master_log_pos=207

Query OK, 0 rows affected (0.06 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G # pay attention to the following two parameters after executing this command, both of which must be yes.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

At this point, the master and master are created synchronously, so you can simply test it and write data on two machines to see if it will be synchronized to another machine.

PS: if you report an error, Slave_IO_Running: NO can check whether the synchronized account is created properly!

Install keepalived and set up monitoring

Keepalived is installed on two MySQL servers

First of all, the process of installing keepalived can be decompressed and installed without explanation.

The configuration of vim / etc/keepalived/keepalived.conf after installation is as follows

Configuration file for 10.1.1.20

! Configuration File for keepalived

Global_defs {

Notification_email {

Acassen@firewall.loc

Failover@firewall.loc

Sysadmin@firewall.loc

}

Notification_email_from Alexandre.Cassen@firewall.loc

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id LVS_DEVEL

}

Vrrp_instance VI_1 {

State backup # two configurations both here are BACKUP

Interface eth0

Virtual_router_id 51

Priority 100 # priority, the other one is changed to 90

Advert_int 1

Nopreempt # does not preempt, it can only be set on machines with high priority, but not on machines with low priority.

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

10.1.1.25

}

}

Virtual_server 10.1.1.25 3306 {

Delay_loop 6

Lb_algo wrr

Lb_kind DR

Persistence_timeout 50 # session duration

Protocol TCP

Real_server 10.1.1.20 3306 {

Weight 3

Notify_down / tmp/killprocess.sh # detected that the mysqld service is down and execute this script (the script should be written by yourself)

TCP_CHECK {

Connect_timeout 10 # connection timeout

Number of nb_get_retry 3 # reconnections

Delay_before_retry 3 # reconnection interval

Connect_port 3306 # Health check Port

}

}

}

Configuration file for 10.1.1.21

! Configuration File for keepalived

Global_defs {

Notification_email {

Acassen@firewall.loc

Failover@firewall.loc

Sysadmin@firewall.loc

}

Notification_email_from Alexandre.Cassen@firewall.loc

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id LVS_DEVEL

}

Vrrp_instance VI_1 {

State backup

Interface eth0

Virtual_router_id 51

Priority 90

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

10.1.1.25

}

}

Virtual_server 10.1.1.25 3306 {

Delay_loop 6

Lb_algo wrr

Lb_kind DR

Persistence_timeout 50

Protocol TCP

Real_server 10.1.1.21 3306 {

Weight 3

Notify_down / tmp/killprocess.sh

TCP_CHECK {

Connect_timeout 10

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

}

Write a script to monitor whether the mysql service is down, and write the script according to the location of the configuration file above.

# vim / tmp/killprocess.sh

#! / bin/sh

Pkill keepalived

The script is simple and aims to trigger the script when keepalived detects that the mysql service is down, killing the keepalived process and leaving it to another server to take over

Start the keeplived service after modification

# service keeplived start

At this point, the whole cluster is completed.

III. Testing

Find a machine to connect to mysql with virtual ip

[root@localhost html] # mysql-uab-h 10.1.1.25-p123

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 736

Server version: 5.1.66-log Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Successfully connected, and then you can shut down a machine, or a machine's mysql service, and see if it can still be connected!

At this point, the study on "MySQL master replication + Keepalived steps to create a highly available MySQL cluster" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.