MySQL Main master

MySQL Master master sync implementation

1, the basic idea is to eliminate the database single point of failure, to ensure high data availability.

2, the pre-requirement is not high can be achieved with the main master synchronization, later if not meet the requirements can be added from the database.

3, the environment that the experiment needs and the version of the database are as follows:

Serial number	Name	IP Address	Database version	System version
1	Mysql-master01	10.93.58.72	mysql-5.5.32	CentOS Release 6.9
2	Mysql-master02	10.93.58.73	mysql-5.5.32	CentOS Release 6.9
3	Test	10.93.58.70	No	CentOS Release 6.9
4	Virtual IP	10.93.58.74	No	No

4, the official installation data steps:

1) I used a script to install the MYSQL-5.5.32 database with the following scripts:

#!/bin/bash

#auto_install_mysql

#auth by Tony Date 2018-07-31

Yum-y install gcc gcc-c++ make ncurses ncurses-devel libaio-devel cmake

Groupadd MySQL

Useradd mysql-s/sbin/nologin-m-G MySQL

Mkdir/application

wget http://10.93.58.70/lamp/mysql-5.5.32.tar.gz

Tar xvf mysql-5.5.32.tar.gz

CD mysql-5.5.32

CMake. -dcmake_install_prefix=/application/mysql-5.5.32 \

-dmysql_datadir=/data/mysql \

-dmysql_unix_addr=/application/mysql-5.5.32/tmp/mysql.sock \

-ddefault_charset=utf8 \

-DDEFAULT_COLLATION=UTF8_GENERAL_CI \

-DEXTRA_CHARSETS=GBK,GB2312,UTF8,ASCII \

-denabled_local_infile=on \

-dwith_innobase_storage_engine=1 \

-dwith_federated_storage_engine=1 \

-dwith_blackhole_storage_engine=1 \

-dwithout_example_storage_engine=1 \

-dwithout_partition_storage_engine=1 \

-dwith_fast_mutexes=1 \

-dwith_zlib=bundled \

-denabled_local_infile=1 \

-dwith_readline=1 \

-dwith_embedded_server=1 \

-dwith_debug=0

If [$?-eq 0];then

Make && make install

Ln-s/application/mysql-5.5.32//application/mysql

/BIN/CP Support-files/mysql.server/etc/init.d/mysqld

chmod +x/etc/init.d/mysqld

Chown Mysql.mysql/application/mysql

Chown Mysql.mysql/data

Echo ' Export path=/application/mysql/bin: $PATH ' >>/etc/profile

Source/etc/profile

Mv/etc/my.cnf/etc/my.cnf.bak

Cat >/etc/my.cnf <<eof

[Mysqld]

Port = 3306

Socket =/application/mysql-5.5.32/tmp/mysql.sock

DataDir =/data/mysql

User =mysql

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

Symbolic-links=0

Log-bin=mysql-bin

Server-id = 1

Auto_increment_offset=1

auto_increment_increment=2

[Mysqld_safe]

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

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

Replicate-do-db =all

Eof

/application/mysql/scripts/mysql_install_db--basedir=/application/mysql/--datadir=/data/mysql--user=mysql

/etc/init.d/mysqld start

Chkconfig mysqld on

Mysqladmin-uroot password ' hwg123 '

Echo-e "\n\033[32m-----------------------------------------------\033[0m"

Echo-e "\033[32mthe $M _files_dir Server Install Success!\033[0m"

Else

Echo-e "\033[32mthe $M _files_dir make or make install Error,please Check ..."

Exit 0

Fi

"Mysqlset.sh" 66L, 2228C written

2) View MY.CNF configuration file

[Email protected] ~]# CAT/ETC/MY.CNF

[Mysqld]

Port = 3306

Socket =/application/mysql-5.5.32/tmp/mysql.sock

DataDir =/data/mysql

User =mysql

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

Symbolic-links=0

Log-bin=mysql-bin

Binlog_format = Mixed

Server-id = 1

Auto_increment_offset=1

auto_increment_increment=2

[Mysqld_safe]

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

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

Replicate-do-db =all

3) Enter the database Master01:

[Email protected] ~]# mysql-uroot-p

Enter Password:

Enter Password: hwg123

4) Authorize the database:mysql> grant replication Slave on * * to ' rep ' @ ' 10.93.58.73 ' identified by ' hwg123 ';

5) View the main library status;mysql> show Master State;

6) on the Mater02 to do the following steps;

7) Enter the database; Enter the password hwg123

[Email protected] ~]# mysql-uroot-p

Enter Password:

8) Authorize the Sync library and post points according to the Mysql-bind and post points.

mysql> Change Master to master_host= ' 10.93.58.73 ', master_user= ' rep ', master_password= ' hwg123 ', master_log_file= ' Mysql-bin.000014 ', master_log_pos=263;

Query OK, 0 rows affected (0.06 sec)

9) Turn on slave sync

mysql> start slave;

Query OK, 0 rows Affected (0.00 sec)

10) Check the sync results if it is two yes; proof of synchronization success:

Mysql> show Slave status\g;

5, switch to MASTER02 on the official installation of mysql-5.5.32.

1) Use the script to install here Skip

2) Look at the configuration file, where you should pay attention to the marked red Place:

[Email protected] ~]# CAT/ETC/MY.CNF

[Mysqld]

Port = 3306

Socket =/application/mysql-5.5.32/tmp/mysql.sock

DataDir =/data/mysql

User =mysql

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

Symbolic-links=0

Log-bin=mysql-bin

Binlog_format = Mixed

Server-id = 2

auto_increment_offset=2

auto_increment_increment=2

[Mysqld_safe]

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

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

Replicate-do-db =all

3) Enter the database: Enter the password hwg123

[Email protected] ~]# mysql-uroot-p

Enter Password:

4) Authorize the database:mysql> grant replication Slave on * * to ' rep ' @ ' 10.93.58.72 ' identified by ' hwg123 ';

5) View database status:mysql> show master status;

6) switch to Master01 on the license;

mysql> Change Master to master_host= ' 10.93.58.73 ', master_user= ' rep ', master_password= ' hwg123 ', master_log_file= ' Mysql-bin.000006 ', master_log_pos=263;

7) Turn on slave sync

mysql> slave start;

8) query synchronization status:mysql> show slave status\g;

7) So the Master sync is good.

6, install keepalived to do high availability. I'm using a keepalived-1.2.20.tar.gz.

On the Master01, proceed as follows:

Yum–y Install OpenSSL Openssl-devel

Tar XF keepalived-1.2.20.tar.gz

CD keepalived-1.2.20

./configure--with-kernel-dir=/usr/src/kernels/2.6.32-696.el6.x86_64

Make && make install

cp/usr/local/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/

cp/usr/local/etc/sysconfig/keepalived/etc/sysconfig/

cp/usr/local/sbin/keepalived/usr/sbin/

Mkdir/etc/keepalived

cd/etc/keepalived/

add A keepalived configuration file

[Email protected] ~]# vim/etc/keepalived/keepalived.conf

# # # # #MASTER keepalived configuration file ######

! Configuration File for Keepalived

Global_defs {

Notification_email {

[Email protected]

}

Notification_email_from [email protected]

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

router_id Lvs_devel

}

# VIP1

Vrrp_instance Vi_1 {

State MASTER

Interface eth0

Lvs_sync_daemon_inteface eth0

VIRTUAL_ROUTER_ID 50

Priority 50

Advert_int 1

Nopreempt

Authentication {

Auth_type PASS

Auth_pass 1111

}

virtual_ipaddress {

10.93.58.74

}

}

Virtual_server 10.93.58.74 3306 {

Delay_loop 6

Lb_algo WRR

Lb_kind DR

Persistence_timeout 60

Protocol TCP

Real_server 10.93.58.72 3306 {

Weight 1

notify_down/root/mysql.sh

Tcp_check {

Connect_timeout 10

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

[Email protected] ~]# vim mysql.sh vim mysql.sh

#!/bin/bash

/etc/init.d/keepalived stop

[Email protected] ~]# chmod +x mysql.sh

In Master02 operation Basic and Master01 similar, only need to change keepalived.conf, configure as follows, pay attention to the place of red:

# # # # #SLAVE keepalived configuration file #####

! Configuration File for Keepalived

Global_defs {

Notification_email {

[Email protected]

}

Notification_email_from [email protected]

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

router_id Lvs_devel

}

# VIP1

Vrrp_instance Vi_1 {

State SLAVE

Interface eth0

Lvs_sync_daemon_inteface eth0

VIRTUAL_ROUTER_ID 50

Priority

Advert_int 5

# nopreempt

Authentication {

Auth_type PASS

Auth_pass 1111

}

virtual_ipaddress {

10.93.58.74

}

}

Virtual_server 10.93.58.74 3306 {

Delay_loop 6

Lb_algo WRR

Lb_kind DR

Persistence_timeout 60

Protocol TCP

real_server 10.93.58.73 3306 {

Weight 1

notify_down/root/mysql.sh

Tcp_check {

Connect_timeout 10

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

7, last Test, stop Master01 Main library, no IP before stopping the main library.

After the database is stopped, look at the log display;

[[email protected] ~]#/etc/init.d/mysqld stop

Shutting down MySQL. success!

[Email protected] ~]# tail-fn20/var/log/messages

Then look at the IP above Master02 to see if the VIP has drifted over.

[[Email protected] ~]# IP A

Finally you can see that the IP has drifted over, the experiment succeeded.

MySQL Main master