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

Setting up mysql + keepalived environment

1. Environmental preparation

System: Centenos7.4

Mysql version of mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

Keepalived version of keepalived-2.0.13.tar.gz

Node1: 192.168.5.235

Node2: 192.168.5.236

Vip: 192.168.5.58 (preferably the same ip segment, otherwise there will be network problems)

Host user: test with sudo permission

2.mysql master building

Since only one system of the assigned virtual machine needs to be installed with various system software, the disk also needs to be mounted on the

2.1 disk Partition

[test@host-192-1685235] $sudo fdisk-l

Disk / dev/vda: 21.5 GB, 21474836480 bytes, 41943040 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I size (minimum/optimal): 512 bytes / 512 bytes

Disk label type: dos

Disk identifier: 0x000adb11

Device Boot Start End Blocks Id System

/ dev/vda1 * 2048 1026047 512000 83 Linux

/ dev/vda2 1026048 9414655 4194304 82 Linux swap / Solaris

/ dev/vda3 9414656 41943039 16264192 83 Linux

Disk / dev/vdb: 536.9 GB, 536870912000 bytes, 1048576000 sectors

Units = sectors of 1 * 512 = 512 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I size (minimum/optimal): 512 bytes / 512 bytes

[test@host-192-1685236] $sudo fdisk / dev/vdb

Welcome to fdisk (util-linux 2.23.2).

Changes will remain in memory only, until you decide to write them.

Be careful before using the write command.

Device does not contain a recognized partition table

Building a new DOS disklabel with disk identifier 0xf2a1312e.

Command (m for help): n

Partition type:

P primary (0 primary, 0 extended, 4 free)

E extended

Select (default p): P

Partition number (1-4, default 1):

First sector (2048-1048575999, default 2048):

Using default value 2048

Last sector, + sectors or + size {KMagne Mpeng} (2048-1048575999, default 1048575999):

Using default value 1048575999

Partition 1 of type Linux and of size 500 GiB is set

Command (m for help): W

The partition table has been altered!

Calling ioctl () to re-read partition table.

Syncing disks.

Partition formatting

[test@host-192-1685235 /] $sudo mkfs-t ext3 / dev/vdb1

Mke2fs 1.42.9 (28-Dec-2013)

Filesystem label=

OS type: Linux

Block size=4096 (log=2)

Fragment size=4096 (log=2)

Stride=0 blocks, Stripe width=0 blocks

32768000 inodes, 131071744 blocks

6553587 blocks (5.00%) reserved for the super user

First data block=0

Maximum filesystem blocks=4294967296

4000 block groups

32768 blocks per group, 32768 fragments per group

8192 inodes per group

Superblock backups stored on blocks:

32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208

4096000, 7962624, 11239424, 20480000, 23887872, 71663616, 78675968

102400000

Allocating group tables: done

Writing inode tables: done

Creating journal (32768 blocks): done

Writing superblocks and filesystem accounting information: done

Create a directory and mount it

[test@host-192-1685235 /] $sudo mkdir / data

[test@host-192-1685235] $sudo mount / dev/vdb1 / data

The boot automatic mount is added according to the format inside.

Sudo vi / etc/fstab

2.2 mysql configure the same operation as node1 and node2

View

Rpm-qa | grep-I mysql

Rpm-qa | grep mariadb

Delete (delete everything found out)

Sudo rpm-e mariadb-libs-5.5.56-2.el7.x86_64-nodeps

Extract the mysql installation package

[test@host-192-1685236] $tar-xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

Change directory owners and groups to test for convenience and grant 777 permissions

Sudo chown test:test-R / data/

Chmod 777 / data/

Cd / data/

Mkdir mysql

Cd / data/mysql/

Create data directory, log directory, pid directory

Mkdir data logs run

Put the mysql software under / usr/local/

Cd / usr/local/

Sudo mkdir mysql

Sudo chown test:test. / mysql/

Cd / data

Mv mysql-5.7.26-linux-glibc2.12-x86_64/* / usr/local/mysql/

Delete empty directory

Rm mysql-5.7.26-linux-glibc2.12-x86_64/

Edit mysql profile node1

Sudo vi / etc/my.cnf

[mysqld]

Port=9060

Datadir=/data/mysql/data

Socket=/data/mysql/data/mysql.sock

Server-id=1

Log-bin=mysql-bin

Symbolic-links=0

[mysqld_safe]

Log-error=/data/mysql/logs/mysql.log

Pid-file=/data/mysql/run/mysql.pid

[client]

Default-character-set=utf8

Socket=/data/mysql/data/mysql.sock

[mysql]

Default-character-set=utf8

Socket=/data/mysql/data/mysql.sock

Vi .bash _ profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/mysql/bin

Export PATH

Source .bash _ profile

Initialization

Mysqld-initialize-user=test-datadir=/data/mysql/data

Secure Startup:

Mysqld_safe-- user=test &

Log in with the root password generated by initialization and change the password

Mysql-uroot-p

Set password=password ("123456")

Flush privileges

Edit mysql profile node2

Sudo vi / etc/my.cnf

[mysqld]

Port=9060

Datadir=/data/mysql/data

Socket=/data/mysql/data/mysql.sock

Server-id=2

Log-bin=mysql-bin

Symbolic-links=0

[mysqld_safe]

Log-error=/data/mysql/logs/mysql.log

Pid-file=/data/mysql/run/mysql.pid

[client]

Default-character-set=utf8

Socket=/data/mysql/data/mysql.sock

[mysql]

Default-character-set=utf8

Socket=/data/mysql/data/mysql.sock

Vi .bash _ profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/mysql/bin

Export PATH

Source .bash _ profile

Initialization

Mysqld-initialize-user=test-datadir=/data/mysql/data

Secure Startup:

Mysqld_safe-- user=test &

Log in with the root password generated by initialization and change the password

Mysql-uroot-p

Set password=password ("123456")

Flush privileges

Configure master and slave

Primary node (192.168.5.235)

Create a synchronization user

CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY' sync@123456'

GRANT REPLICATION SLAVE ON *. * TO 'sync'@'%'

Flush privileges

Show master status

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000002 | 997 | |

+-+

1 row in set (0.00 sec)

Standby node (192.168.5.236)

CHANGE MASTER TO MASTER_HOST='192.168.5.235', MASTER_USER='sync', MASTER_PASSWORD='sync@2019#Zy',MASTER_PORT=9060,MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=997

Start slave

# stop stop slave

# reset reset slave

Mysql > show slave status\ G

Configure it the other way around

Original node (192.168.5.236)

Create a synchronization user

CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY' sync@123456'

GRANT REPLICATION SLAVE ON *. * TO 'sync'@'%'

Flush privileges

Show master status

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000002 | 997 | |

+-+

1 row in set (0.00 sec)

Original primary node (192.168.5.235)

CHANGE MASTER TO MASTER_HOST='192.168.5.236', MASTER_USER='sync', MASTER_PASSWORD='sync@2019#Zy',MASTER_PORT=9060,MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=997

Start slave

# stop stop slave

# reset reset slave

Mysql > show slave status\ G

See two YES, representing the success of the Lord.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Give root users remote access (for remote access to root users)

GRANT ALL PRIVILEGES ON *. * TO 'root'@'%' identified by' otn@2019#zy'

Flush privileges

Test:

Create a database

Create database test

Create a normal user

CREATE USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY' 123456'

Grant all privileges on test.* to 'test'@'%'

Grant all privileges on mysql.* to 'test'@'%'

Flush privileges

View the database

Show databases

View users

Select user,host from mysql.user

Create a tabl

Create table testa (Id varchar)

You can see the testa table on both sides

Show tables

Insert statement

Insert into testa values ('1231')

Insert into testa values ('4567')

Insert into testa values ('5464')

You can see it in the other database.

Select * from testa

Delete from testa where Id='1231'

Another database data display is also deleted

At this point, the mysql master has been fully configured successfully.

3.Keepalived installation

Installation related system environment (must be root user or sudo user)

Yum-y install gcc openssl-devel openssl ipvsadm

Yum-y install libnl libnl-devel (ipv6 supported)

[test@host-192-1685235] $tar-xvf keepalived-2.0.13.tar.gz

Cd keepalived-2.0.13

Sudo. / configure-- prefix=/usr/local/keepalived-- install to / usr/local/keepalived

Sudo make & & sudo make install

Cd / etc

Mkdir keepalived

Sudo cp-r / data/keepalived-2.0.13/keepalived/etc/keepalived/keepalived.conf / etc/keepalived/keepalived.conf

Sudo cp-r / data/keepalived-2.0.13/keepalived/etc/init.d / etc/rc.d/init.d/keepalived

Sudo cp / data/keepalived-2.0.13/keepalived/etc/sysconfig/keepalived / etc/sysconfig/keepalived

Keepalived.conf configuration content: # clear the default content and directly use the following configuration

Vi / etc/keepalived/keepalived.conf

! Configuration File for keepalived

Global_defs {# global configuration identity, indicating that this area {} is a global configuration

Notification_email {

Xxx@xxx # indicates who is the source address when sending a notification email

}

Notification_email_from xxx@xxx # indicates that keepalived needs to send email notifications when switching operations such as switching, and to which email addresses email can be sent. There can be multiple email addresses, one notification_email_from xxx@xxx per line.

Smtp_server 127.0.0.1 # indicates the address of the smtp server used when sending email, which can be implemented using the local sendmail

Smtp_connect_timeout 30 # connection smtp connection timeout

Router_id host-192-168-5-235 # Machine ID

Vrrp_skip_check_adv_addr

Vrrp_garp_interval 0

Vrrp_gna_interval 0

}

Vrrp_script chk_mysql_port {# detects whether the mysql service is running. There are many ways, such as process, script detection, etc.

Script "/ root/chk_mysql.sh" # here through script monitoring

Interval 2 # script execution interval, detected every 2s

Priority change caused by weight-5 # script result. If detection fails (script returns non-0), priority is-5.

Fall 2 # detection fails twice in a row before it is determined to be a true failure. Weight will be used to reduce priorities (between 1 and 255)

A successful rise 1 # test is considered a success. But the priority is not modified.

}

Vrrp_instance VI_1 {

State MASTER

Interface eth0 # host network card

Mcast_src_ip 192.168.5.235 # Host ip

Virtual_router_id 35 # router logo, MASTER and BACKUP must be consistent

Priority 101 # defines priority. The higher the number, the higher the priority. Under the same vrrp_instance, the priority of MASTER must be greater than that of BACKUP. In this way, after the MASTER failure is restored, the VIP resources can be snatched back again.

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

192.168.5.58

}

Track_script {

Chk_mysql_port

}

}

Keepalived configuration on bakcup host

Vi / etc/keepalived/keepalived.conf

! Configuration File for keepalived

Global_defs {

Notification_email {

Xxx@xxxx

}

Notification_email_from xxx@xxxx

Smtp_server 127.0.0.1

Smtp_connect_timeout 30

Router_id host-192-168-5-236

Vrrp_skip_check_adv_addr

Vrrp_garp_interval 0

Vrrp_gna_interval 0

}

Vrrp_script chk_mysql_port {

Script "/ root/chk_mysql.sh"

Interval 2

Weight-5

Fall 2

Rise 1

}

Vrrp_instance VI_1 {

State BACKUP

Interface eth0

Mcast_src_ip 192.168.5.236

Virtual_router_id 35

Priority 99

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

192.168.5.58

}

Track_script {

Chk_mysql_port

}

}

[test@host-192-1685227 data] $cd / root

[test@host-192-1685227 root] $vi chk_mysql.sh

#! / bin/bash

Counter=$ (netstat-na | grep "LISTEN" | grep "9060" | wc-l)

If ["${counter}"-eq 0]; then

Service keepalived stop

Fi

Start

Shell > sudo systemctl enable keepalived.service # set boot to start automatically

Shell > sudo service keepalived start # start the service

Shell > sudo service keepalived stop # stop service

Shell > sudo service keepalived restart # restart the service

Verify login

Mysql-h292.168.5.58-P9060-uroot-p123456 can be logged in

Mysql-h292.168.5.235-P9060-uroot-p123456

Mysql-h292.168.5.236-P9060-uroot-p123456

MySQL startup and shutdown

1. Two ways to view mysql services

[root@localhost bin] ps-ef | grep mysql

[root@localhost bin] netstat-nlp

2. Two ways to start the service

Command line mode

[root@localhost bin] cd / usr/bin

[root@localhost bin]. / mysqld_safe &

Service mode

[root@localhost ~] service mysql start

If the service is started, restart the service directly with the following command:

[root@localhost ~] service mysql restart

3. Two ways to shut down the service

Command line mode:

[root@localhost] mysqladmin-u root shutdown

Mode of service:

[root@localhost ~] service mysql stop

Problems encountered

At this point, it is found that the problem is that vip can be used in the primary node, but the secondary node and other hosts in the same segment cannot be accessed normally.

Ping vip can only ping 9 times on standby node (or other node).

The initial suspicion is that it is a network problem, but the network side says that the restrictions have been relaxed.

I tested ping connection 9 times, and vip corresponds to ping access without getting hwaddress. I can add it manually, which should be obtained automatically in theory.

It doesn't make sense to add it manually. If the vip drifts, it's still inaccessible.

In the end, there was no problem with access to the same configuration of your own virtual machine for two weeks.

This time, I went to my colleagues in virtualization to consult this question, and they let go of the restrictions on the network from the bottom.

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.