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

Three mysql

The network segment is configured as 10.0.0.51 10.0.52 10.0.0.53

Install mysql

1.2.1 installation package preparation

# create a directory for the installation package

[root@mysql-db01 ~] # mkdir / home/cc/tools-p

# enter the directory

[root@mysql-db01 ~] # cd / home/cc/tools/

# upload mysql installation package (mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz)

[root@mysql-db01 tools] # rz-be

1.2.2 installation

# create an installation directory

[root@mysql-db01 tools] # mkdir / application

# decompress mysql binary package

[root@mysql-db01 tools] # tar xf mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz

# Mobile installation package

[root@mysql-db01 tools] # mv mysql-5.6.16-linux-glibc2.5-x86_64 / application/mysql-5.6.16

# make soft links

[root@mysql-db01 tools] # ln-s / application/mysql-5.6.16/ / application/mysql

# create a mysql user

[root@mysql-db01 tools] # useradd mysql- s / sbin/nologin-M

# enter the mysql initialization directory

[root@mysql-db01 tools] # cd / application/mysql/scripts/

# initialize mysql

[root@mysql-db01 scripts] #. / mysql_install_db\

-- user=mysql\

-- datadir=/application/mysql/data/\

-- basedir=/application/mysql/

# Notes

-- user: specifies the mysql user

-- datadir: specifies the directory where mysql data is stored

-- basedir: specify the mysql base directory

# copy mysql configuration file

[root@mysql-db01] #\ cp / application/mysql/support-files/my-default.cnf / etc/my.cnf

# copy mysql startup script

[root@mysql-db01 ~] # cp / application/mysql/support-files/mysql.server / etc/init.d/mysqld

# modify the default installation directory of mysql (otherwise it cannot be started)

[root@mysql-db01 ~] # sed-I's impulse usr _ server _ etc/init.d/mysqld

[root@mysql-db01 ~] # sed-I's impulse usr _ server _ application/mysql/bin/mysqld_safe

# configure mysql environment variables

[root@mysql-db01 ~] # echo 'export PATH= "/ application/mysql/bin:$PATH" > / etc/profile.d/mysql.sh

# refresh environment variables

[root@mysql-db01 ~] # source / etc/profile

1.2.3 start

# add self-booting

[root@mysql-db01 ~] # chkconfig mysqld on

# start mysql

[root@mysql-db01 ~] # / etc/init.d/mysqld start

Starting MySQL. SUCCESS! # started successfully

1.2.4 configure password

# configure mysql password to cc123

[root@mysql-db01 ~] # mysqladmin-uroot password cc123

I. configure master-slave replication

2.1 prerequisites

Binlog should be enabled for both the master library and the slave library

The master library is different from the slave library server-id

There must be a master-slave replication user.

2.2 main library operation

2.2.1 modify the configuration file

# Editing mysql configuration file

[root@mysql-db01 ~] # vim / etc/my.cnf

# configure under the mysqld tag

[mysqld]

# the master server-id is 1, and the slave library must be greater than 1

Server_id = 1

# enable binlog log

Log_bin=mysql-bin

2.2.2 create a master-slave replication user

# logging in to the database

[root@mysql-db01 ~] # mysql- uroot-pcc123

# create a rep user

Mysql > grant replication slave on *. * to rep@'10.0.0.%' identified by 'cc123'

2.3 Operation from the library

2.3.1 modify the configuration file

# modify mysql-db02 configuration file

[root@mysql-db02 ~] # vim / etc/my.cnf

# configure under the mysqld tag

[mysqld]

# the master server-id is 1, and the slave library must be greater than 1

Server_id = 5

# enable binlog log

Log_bin=mysql-bin

# restart mysql

[root@mysql-db02 ~] # / etc/init.d/mysqld restart

# modify mysql-db03 configuration file

[root@mysql-db03 ~] # vim / etc/my.cnf

# configure under the mysqld tag

[mysqld]

# the master server-id is 1, and the slave library must be greater than 1

Server_id = 10

# enable binlog log

Log_bin=mysql-bin

# restart mysql

[root@mysql-db03 ~] # / etc/init.d/mysqld restart

Note: in the past, if it was a master-slave replication based on binlog logs, you had to remember the master status information of the master database.

Mysql > show master status

+-+ +

| | File | Position |

+-+ +

| | mysql-bin.000002 | 120 | |

+-+ +

2.4 enable GTID

# check the status of GTID before enabling it

Mysql > show global variables like'% gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | enforce_gtid_consistency | OFF |

| | gtid_executed |

| | gtid_mode | OFF |

| | gtid_owned |

| | gtid_purged |

+-+ +

# Edit mysql configuration file (both master and slave libraries need to be modified)

[root@mysql-db01 ~] # vim / etc/my.cnf

# add under the [mysqld] tag

[mysqld]

Gtid_mode=ON

Log_slave_updates

Enforce_gtid_consistency

# restart the database

[root@mysql-db01 ~] # / etc/init.d/mysqld restart

# check GTID status

Mysql > show global variables like'% gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | enforce_gtid_consistency | ON | # execution of GTID is consistent |

| | gtid_executed |

| | gtid_mode | ON | # enable GTID module |

| | gtid_owned |

| | gtid_purged |

+-+ +

Note: GTID needs to be enabled for both master and slave libraries, otherwise an error will be reported when copying master and slave:

[root@mysql-db02 ~] # mysql- uroot-pcc123

Mysql > change master to

-> master_host='10.0.0.51'

-> master_user='rep'

-> master_password='cc123'

-> master_auto_position=1

If git_mode is not enabled, it is easy to report an error.

ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when @ @ GLOBAL.GTID_MODE = ON.

2.5 configure master-slave replication

# logging in to the database

[root@mysql-db02 ~] # mysql- uroot-pcc123

# configure replication host information

Mysql > change master to

# main library IP

-> master_host='10.0.0.51'

# copy users in the main library

-> master_user='rep'

# the main library copies the user's password

-> master_password='cc123'

# GTID location Point

-> master_auto_position=1

# enable slave

Mysql > start slave

# View slave status

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000003

Read_Master_Log_Pos: 403

Relay_Log_File: mysql-db02-relay-bin.000002

Relay_Log_Pos: 613

Relay_Master_Log_File: mysql-bin.000003

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: 403

Relay_Log_Space: 822

Until_Condition: None

2.6 from the library settings

# Log in to the slave library

[root@mysql-db02 ~] # mysql- uroot-pcc123

# disable automatic deletion of relay log

Mysql > set global relay_log_purge = 0

# set read-only

Mysql > set global read_only=1

# Editing configuration file

[root@mysql-db02 ~] # vim / etc/my.cnf

# add under the mysqld tag

[mysqld]

# disabling automatic deletion of relay log takes effect permanently

Relay_log_purge = 0

II. Deploy MHA

3.1 Environment preparation (all nodes)

# install dependency packages

[root@mysql-db01 ~] # yum install perl-DBD-MySQL-y

# enter the directory where the installation package is stored

[root@mysql-db01 ~] # cd / home/cc/tools/

# upload mha installation package

[root@mysql-db01 tools] # rz-be

Mha4mysql-manager-0.56-0.el6.noarch.rpm

Mha4mysql-manager-0.56.tar.gz

Mha4mysql-node-0.56-0.el6.noarch.rpm

Mha4mysql-node-0.56.tar.gz

# install the node package

[root@mysql-db01 tools] # rpm-ivh mha4mysql-node-0.56-0.el6.noarch.rpm

Preparing... # [100%]

1:mha4mysql-node # # [100%]

# logging in to the database

[root@mysql-db01 tools] # mysql- uroot-pcc123

# add mha management account

Mysql > grant all privileges on *. * to mha@'10.0.0.%' identified by 'mha'

# check whether it has been added successfully

Mysql > select user,host from mysql.user

# create on the master library, and the slave library will be copied automatically (viewed on the slave library)

Mysql > select user,host from mysql.user

3.2 Command soft connection (all nodes)

# if you do not create a command soft connection, an error will be reported when detecting mha replication

[root@mysql-db01] # ln-s / application/mysql/bin/mysqlbinlog / usr/bin/mysqlbinlog

[root@mysql-db01] # ln-s / application/mysql/bin/mysql / usr/bin/mysql

3.3 deployment Management Node (mha-manager)

3.3.1 deploy the management node on mysql-db03

# using epel feeds

[root@mysql-db03 ~] #

Wget-O / etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo

# install manager dependency package

[root@mysql-db03 ~] # yum install-y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

# install the manager package

[root@mysql-db03 tools] # rpm-ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

Preparing... # [100%]

1:mha4mysql-manager # # [100%]

3.3.2 Editing the profile

# create a configuration file directory

[root@mysql-db03] # mkdir-p / etc/mha

# create a log directory

[root@mysql-db03] # mkdir-p / var/log/mha/app1

# Editing mha configuration file

[root@mysql-db03 ~] # vim / etc/mha/app1.cnf

[server default]

Manager_log=/var/log/mha/app1/manager

Manager_workdir=/var/log/mha/app1

Master_binlog_dir=/application/mysql/data

User=mha

Password=mha

Ping_interval=2

Repl_password=cc123

Repl_user=rep

Ssh_user=root

[server1]

Hostname=10.0.0.51

Port=3306

[server2]

Candidate_master=1

Check_repl_delay=0

Hostname=10.0.0.52

Port=3306

[server3]

Hostname=10.0.0.53

Port=3306

[detailed description of configuration file]

[server default]

# set the working directory of manager

Manager_workdir=/var/log/masterha/app1

# set the log of manager

Manager_log=/var/log/masterha/app1/manager.log

# set the location where master saves binlog so that MHA can find the log of master. What I have here is the data directory of mysql

Master_binlog_dir=/data/mysql

# switching script when setting automatic failover

Master_ip_failover_script= / usr/local/bin/master_ip_failover

# set the switching script for manual switching

Master_ip_online_change_script= / usr/local/bin/master_ip_online_change

# set the password of the root user in mysql. This password is the password that was created earlier for the monitoring user.

Password=123456

# set root for monitoring users

User=root

# set the monitoring master database, the time interval for sending ping packets, and automatically failover when there is no response for three attempts

Ping_interval=1

# set the location where the remote mysql binlog is saved when switching occurs

Remote_workdir=/tmp

# set the password of the replication user

Repl_password=123456

# set the replication user name in the replication environment

Repl_user=rep

# set the script for the alarm sent after the switch occurs

Report_script=/usr/local/send_report

# once there is a problem between MHA and server02 monitoring, MHA Manager will try to log in to server02 from server03

Secondary_check_script= / usr/local/bin/masterha_secondary_check-s server03-s server02-- user=root-- master_host=server02-- master_ip=192.168.0.50-- master_port=3306

# set to close the failed host script after a failure occurs (the main function of this script is to shut down the host and put it in the event of a brain crack, which is not used here)

Shutdown_script= ""

# set login user name for ssh

Ssh_user=root

[server1]

Hostname=10.0.0.51

Port=3306

[server2]

Hostname=10.0.0.52

Port=3306

# set as candidate master. If this parameter is set, this slave database will be promoted to master database after master-slave switching occurs, even if the master database is not the latest slave of events in the cluster.

Candidate_master=1

# by default, if a slave lags behind the relay logs of master 100m, MHA will not select the slave as a new master, because the recovery of this slave will take a long time. Setting check_repl_delay=0,MHA to trigger the switch will ignore the replication delay when selecting a new master. This parameter is very useful for hosts with candidate_master=1 set, because the candidate host must be the new master during the handover.

Check_repl_delay=0

3.4 configure ssh trust (all nodes)

# create a key pair

[root@mysql-db01] # ssh-keygen-t dsa-P'- f ~ / .ssh/id_dsa > / dev/null 2 > & 1

# send the public key, including yourself

[root@mysql-db01] # ssh-copy-id-I / root/.ssh/id_dsa.pub root@10.0.0.51

[root@mysql-db01] # ssh-copy-id-I / root/.ssh/id_dsa.pub root@10.0.0.52

[root@mysql-db01] # ssh-copy-id-I / root/.ssh/id_dsa.pub root@10.0.0.53

3.5 start the test

# Test ssh

[root@mysql-db03] # masterha_check_ssh-- conf=/etc/mha/app1.cnf

# if you see the following words, the test is successful

Tue Mar 7 01:03:33 2017-[info] All SSH connection tests passed successfully.

# Test replication

[root@mysql-db03] # masterha_check_repl-- conf=/etc/mha/app1.cnf

# if you see the following words, the test is successful

MySQL Replication Health is OK.

3.6 start MHA

# start

[root@mysql-db03 ~] #

Nohup masterha_manager-conf=/etc/mha/app1.cnf-remove_dead_master_conf-ignore_last_failover

< /dev/null >

/ var/log/mha/app1/manager.log 2 > & 1 &

3.7 switch master test

# logging in to the database (db02)

[root@mysql-db02 ~] # mysql- uroot-pcc123

# check replication

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 191

Relay_Log_File: mysql-db02-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

# logging in to the database (db03)

[root@mysql-db03 ~] # mysql- uroot-pcc123

# check replication

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.51

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 191

Relay_Log_File: mysql-db03-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

# stop the main library

[root@mysql-db01 ~] # / etc/init.d/mysqld stop

Shutting down MySQL. SUCCESS!

# logging in to the database (db02)

[root@mysql-db02 ~] # mysql- uroot-pcc123

# View slave status

Mysql > show slave status\ G

# slave for db02 is already empty

Empty set (0.00 sec)

# logging in to the database (db03)

[root@mysql-db03 ~] # mysql- uroot-pcc123

# View slave status

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.52

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 191

Relay_Log_File: mysql-db03-relay-bin.000002

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

3.8 promote database restart dropped by dang to slave library

# in actual production, the hardware problem of the database server should be detected and eliminated.

1. Find the master statement

[root@mysql-db03 .ssh] # grep-I "change master" / var/log/mha/app1/manager

Fri May 26 16:59:31 2017-[info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx'

two。 Insert master into a library that is down

[root@mysql-db03 .ssh] #

Restart the library dropped by dang and copy the change master statement into the disabled library

Mysql > CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='cc123'

Query OK, 0 rows affected, 2 warnings (0.13 sec)

3. Turn on the switch of the slave library

4. Add the label of the dead host on the management machine

[root@mysql-db03 ~] # vim / etc/mha/app1.cnf

[server default]

Manager_log=/var/log/mha/app1/manager

Manager_workdir=/var/log/mha/app1

Master_binlog_dir=/application/mysql/data

User=mha

Password=mha

Ping_interval=2

Repl_password=cc123

Repl_user=rep

Ssh_user=root

[server1]

Hostname=10.0.0.51

Port=3306

[server2]

Candidate_master=1 # gives priority to the main library

Check_repl_delay=0 # can be selected according to actual needs

Hostname=10.0.0.52

Port=3306

[server3]

Hostname=10.0.0.53

Port=3306

5. Turn on mannger

6. Observe the status of the slave library and complete this configuration

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.