MariaDB-5.5.56 Primary master replication +keepalived high Availability

MARIADB Primary master replication +keepalived high Availability

Host 1:192.168.1.22

Host 2:192.168.1.23

vip1:192.168.1.233

vip2:192.168.1.234

Host 1 settings:

Install the RPM package that needs to be used

Yum-y Install Ipvsadm keepalived mariadb mariadb-server

Start mariadb and set to boot

Systemctl Start mariadb

Systemctl Enable MARIADB

Set Password

Mysql_secure_installation

Prompt after carriage return:

Enter current password to root (enter for none): # # If it is the first run direct carriage, because there is no password

Set root password? [y/n] # #是否设置root用户密码, enter Y return

New Password: # #设置root用户密码

Re-enter new password: # #国际惯例再输入一次密码

Remove anonymous users? [y/n] # #是否删除匿名用户

Disallow root login remotely? [y/n] # #是否禁止root用户远程登录

Remove test database and access to it? [y/n] # # Whether to delete the test database

Reload privilege tables now? [y/n] # # Whether to reload the permissions table

Test Login

Mysql-uroot-p Password

Modifying a configuration file

Vim/etc/my.cnf

[Mysqld]

init_connect= ' SET collation_connection = Utf8_unicode_ci '

init_connect= ' SET NAMES UTF8 '

Character-set-server=utf8

Collation-server=utf8_unicode_ci

Skip-character-set-client-handshake

Datadir=/var/lib/mysql

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

Symbolic-links=0

Log-bin=mysql-bin #手动添加该项配置 The prefix of a binary file

Log-slave-updates=true #手动添加该项配置 Open from log

Relay-log=relay-log-bin #手动添加该项配置 The prefix of the trunk log file

Relay-log-index=slave-relay-bin.index #手动添加该项配置 Define the name of the trunk log

server-id=11 #手动添加该项配置 ID Number unique, cannot be duplicated

[Mysqld_safe]

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

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

!includedir/etc/my.cnf.d

Save exit, restart MARIADB

Systemctl Restart MARIADB

Write a monitoring script

vim/opt/chk_mysql.sh

#!/bin/bash

#check mysqld Server Status

mysqlpid=$ (ps-c mysqld--no-header | wc-l)

If [$mysqlpid-eq 0];then

Systemctl Start mariadb

Sleep 3

mysqlpid=$ (ps-c mysqld--no-header | wc-l)

If [$mysqlpid-eq 0];then

Systemctl Stop keepalived

echo "keepalived service off, please check your mariadb service!" | Tee-a/var/log/messages

Fi

Fi

Save exit

Modifying the keepalived configuration file

Vim/etc/keepalived/keepalived.conf

! 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 mysql_mariadb

}

Vrrp_script Chk_mysql {

Script/opt/chk_mysql.sh #指定脚本位置

Interval 2 #2秒检查一次

WEIGHT-10 # Weight-10

}

Vrrp_instance Vi_1 {

State MASTER

Interface Ens32

VIRTUAL_ROUTER_ID the ID number in Vi_1 is consistent with the ID of the vi_1 from the inside.

Priority 100

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Chk_mysql #重新指向vrrp_script

}

virtual_ipaddress {

192.168.1.233 #VIP

}

}

Vrrp_instance Vi_2 {

State MASTER

Interface Ens32

virtual_router_id #区别VI_1的ID

Priority 90

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Chk_mysql

}

virtual_ipaddress {

192.168.1.234

}

}

Host 2 configuration:

Install the RPM package that needs to be used

Yum-y Install Ipvsadm keepalived mariadb mariadb-server

Start mariadb and set to boot

Systemctl Start mariadb

Systemctl Enable MARIADB

Set Password

Mysql_secure_installation

Prompt after carriage return:

Enter current password to root (enter for none): # # If it is the first run direct carriage, because there is no password

Set root password? [y/n] # #是否设置root用户密码, enter Y return

New Password: # #设置root用户密码

Re-enter new password: # #国际惯例再输入一次密码

Remove anonymous users? [y/n] # #是否删除匿名用户

Disallow root login remotely? [y/n] # #是否禁止root用户远程登录

Remove test database and access to it? [y/n] # # Whether to delete the test database

Reload privilege tables now? [y/n] # # Whether to reload the permissions table

Test Login

Mysql-uroot-p Password

Modifying a configuration file

Vim/etc/my.cnf

[Mysqld]

init_connect= ' SET collation_connection = Utf8_unicode_ci '

init_connect= ' SET NAMES UTF8 '

Character-set-server=utf8

Collation-server=utf8_unicode_ci

Skip-character-set-client-handshake

Datadir=/var/lib/mysql

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

Symbolic-links=0

Log-bin=mysql-bin #手动添加该项配置 The prefix of a binary file

Log-slave-updates=true #手动添加该项配置 Open from log

Relay-log=relay-log-bin #手动添加该项配置 The prefix of the trunk log file

Relay-log-index=slave-relay-bin.index #手动添加该项配置 Define the name of the trunk log

SERVER-ID=12 #手动添加该项配置 ID Number unique, cannot be duplicated

[Mysqld_safe]

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

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

!includedir/etc/my.cnf.d

Save exit, restart MARIADB

Systemctl Restart MARIADB

Write a monitoring script

vim/opt/chk_mysql.sh

#!/bin/bash

#check mysqld Server Status

mysqlpid=$ (ps-c mysqld--no-header | wc-l)

If [$mysqlpid-eq 0];then

Systemctl Start mariadb

Sleep 3

mysqlpid=$ (ps-c mysqld--no-header | wc-l)

If [$mysqlpid-eq 0];then

Systemctl Stop keepalived

echo "keepalived service off, please check your mariadb service!" | Tee-a/var/log/messages

Fi

Fi

Save exit

Modifying the keepalived configuration file

Vim/etc/keepalived/keepalived.conf

! 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 mysql_mariadb

}

Vrrp_script Chk_mysql {

Script/opt/chk_mysql.sh #指定脚本位置

Interval 2 #2秒检查一次

WEIGHT-10 # Weight-10

}

Vrrp_instance Vi_1 {

State MASTER

Interface Ens32

VIRTUAL_ROUTER_ID the ID number in Vi_1 is consistent with the ID of the vi_1 from the inside.

Priority 100

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Chk_mysql #重新指向vrrp_script

}

virtual_ipaddress {

192.168.1.233 #VIP

}

}

Vrrp_instance Vi_2 {

State MASTER

Interface Ens32

virtual_router_id #区别VI_1的ID

Priority 90

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Chk_mysql

}

virtual_ipaddress {

192.168.1.234

}

}

Configure Interop Master from replication:

The first is host 1:

To authorize from the server:

Mysql-uroot-p123123

Grant Replication Slave on * * to ' myslave ' @ ' 192.168.1.% ' identified by ' 123123 ';

Flush privileges;

Show master status; ID number of the #记住file的文件名 and position

Enter the Host 2

Mysql-uroot-p123123

Change Master to master_host= ' 192.168.1.22 ', master_user= ' myslave ', master_password= ' 123123 ', master_log_file= '      mysql-bin.000004 ', master_log_pos= ' 475 '; #这里写上主机1上显示的file的文件名和id号

Start slave;

show slave status \g;

Host 2:

To authorize from the server:

Mysql-uroot-p123123

Grant Replication Slave on * * to ' myslave ' @ ' 192.168.1.% ' identified by ' 123123 ';

Flush privileges;

Show master status; ID number of the #记住file的文件名 and position

Enter the Host 1

Mysql-uroot-p123123

Change Master to master_host= ' 192.168.1.23 ', master_user= ' myslave ', master_password= ' 123123 ', master_log_file= '      mysql-bin.000004 ', master_log_pos= ' 475 '; #这里写上主机1上显示的file的文件名和id号

Start slave;

show slave status \g;

Configuration complete

MariaDB-5.5.56 Primary master replication +keepalived high Availability