Deployment and monitoring of MYSQL 5.6 replication from the library

Deployment and monitoring of MYSQL 5.6 replication from the library

MYSQL 5.6 installation and deployment

#1. Download the installation package      
wget https://download.osichina.net/tools/mysql/mysql-5.6.28.tar.gz     
     
#2. Create users and install related components     
useradd mysql     
yum -y install autoconf automake cmake gcc-c++ libgcrypt libtool libxml2 ncurses-devel zlib     
     
#3. Decompress, compile and install (installation path: usr/local/mysql)     
tar -xzvf mysql-5.6.28.tar.gz     
cd mysql-5.6.28     
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/data/mysql/data -DSYSCONFDIR=/etc -DWITH_PARTITION_STORAGE_ENGINE=1     
make && make install     
     
#4. Add environment variable     
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile     
     
#5.MYSQL configuration     
cat > /etc/my.cnf << EOF     
[mysqld_safe]     
log-error=/data/mysql/log/mysql.err     
     
[mysqld]     
datadir=/data/mysql/data     
tmpdir=/data/mysql/tmp     
socket=/var/lib/mysql/mysql.sock     
user=mysql     
character_set_server=utf8     
default-storage-engine=INNODB     
innodb_buffer_pool_size=1G     
#slow_query_log=1     
#slow_query_log_file=/data/mysql/log/mysql.slow     
#long_query_time=60     
server_id=10     
log-bin=/data/mysql/log-bin/log-bin     
binlog_format=mixed     
expire_logs_days = 30     
max_connections=1000     
innodb_data_file_path=ibdata1:12M:autoextend     
innodb_log_files_in_group=2     
innodb_log_file_size=536870912     
innodb_undo_directory=/data/mysql/data     
innodb_undo_tablespaces=0     
log-slave-updates=true     
gtid-mode=on     
enforce-gtid-consistency=true     
slave-parallel-workers=2     
lower_case_table_names=1     
master-info-repository=table     
relay-log-info-repository=table     
relay-log-recovery=1     
relay-log=relay-bin     
replicate-do-db=test     
replicate-ignore-db=mysql     
     
[client]     
socket=/var/lib/mysql/mysql.sock     
EOF     
     
#6. Create relevant directories and files     
mdkir -p /data/mysql/data /data/mysql/log /data/mysql/log-bin /data/mysql/tmp /var/lib/mysql     
touch /data/mysql/log/mysql.err     
chown mysql:mysql /data/mysql /var/lib/mysql /usr/local/mysql -R     
     
#7. Initialize configuration     
cd /usr/local/mysql     
./scripts/mysql_install_db --user=mysql --datadir=/data/mysql/data     
     
#8. Start, add as service and security configuration     
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld     
chmod +x /etc/init.d/mysqld     
chkconfig --add mysqld     
chkconfig mysqld on     
service mysqld restart     
     
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock     
mysql_secure_installation #Interactive security configuration     
     

MYSQL master-slave configuration

1. Create an account in the main database

GRANT REPLICATION SLAVE ON *.* to 'user'@'%' identified by 'password';     
flush privileges ;     

2. Backup data and restore to slave database

In order to ensure the consistency of data, it is necessary to lock the table. If the amount of data is small, lock the table first, record the pos location of the master, and then use mysqldump for backup. If the data volume is large, mydumper or xtrabackup can be used for data backup. My data is relatively large, and mydumper is used for backup.

#1. Download and install mydumper (the default installation location is / usr/local/bin)     
wget https://download.osichina.net/tools/mysql/mydumper-0.9.1.tar.gz     
cmake .     
make && make install     
     
#2. Adopt multi-threaded backup data     
#In order to ensure data consistency, the backup will lock the table, which means that the backup account needs reload permission     
#-h host - u user - p password - t threads - c enable compression - B backup which database - o backup to where     
mkdir backup     
cd backup     
nohup /usr/local/bin/mydumper -h x.x.x.x -u user -p 'password' -t 8 -c -B dbname  -o ./ &     
     
#3. Import after backing up data (transfer data to import from the library, and install the same version of mydumper from the Library)     
nohup  /usr/local/bin/myloader  -u user -p 'password'  -t 8  -B dbname -o   -d  ./backup/ &      

3. Master-slave configuration

(1) master configuration

# Which databases are not synchronized     
binlog-ignore-db = mysql     
binlog-ignore-db = test     
binlog-ignore-db = information_schema     
     
# Only which databases are synchronized, in addition, others are not synchronized     
binlog-do-db = dbname     
     
# Log retention time     
expire_logs_days = 10     
     
# Control the write frequency of binlog. How many transaction writes per execution     
# This parameter consumes a lot of performance, but it can reduce the loss caused by MySQL crash     
sync_binlog = 5     
     
# Log format, mixed recommended     
# Statement save SQL statement     
# row save impact record data     
# The combination of the two before mixed     
binlog_format = mixed     

(2) slave configuration
For the above configuration of slave, please note the configuration of replicate do dB and replicate ignore dB

(3) perform synchronization
For master log file and pos, please refer to mydumper metadata file

CHANGE MASTER TO MASTER_HOST='x.x.x.x',master_port=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=123454678,MASTER_AUTO_POSITION=0;     
start slave user='user' password='password';     
show slave status \G;     

MYSQL delay monitoring and alarm

1. Use zabbix to add MYSQL monitoring

(1) zabbix client installation and deployment and adding hosts

zabbixServer=192.168.1.2     
rpm -Uvh https://repo.zabbix.com/zabbix/4.4/rhel/7/x86_64/zabbix-release-4.4-1.el7.noarch.rpm     
yum -y install zabbix-agent     
sed -i "s/Server=127.0.0.1/Server=${zabbixServer}/g"  /etc/zabbix/zabbix_agentd.conf      
sed -i "s/ServerActive=127.0.0.1/ServerActive=${zabbixServer}/g"  /etc/zabbix/zabbix_agentd.conf      
sed -i "s/Hostname=Zabbix server/Hostname=`hostname`/g"  /etc/zabbix/zabbix_agentd.conf      
systemctl enable zabbix-agent --now     

How to add hosts on zabbix is omitted here. (the operating system version of zabbix client installed above is: CentOS 7.6)
By default, the directory of ZABBIX configuration files installed through yum is in / etc/zabbix /, and there is an associated MYSQL configuration file / etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf by default

(2) add zabbix monitoring authorization

GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW  on *.* to zabbix_monitor@'127.0.0.1' identified by 'xxxxxxx';     
GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW on *.* to zabbix_monitor@'localhost' identified by 'xxxxxxx';     
flush privileges ;     

(3) initialization configuration (executed on the mysql zabbix client)

mdkir -p /var/lib/zabbix     
cat > /var/lib/zabbix/.my.cnf << EOF     
[client]     
user=zbx_monitor     
password=xxxxxxx     
EOF     
chown zabbix:zabbix  /var/lib/zabbix  -R     
service zabbix-agent restart     
service zabbix-agent status     

(4) the monitoring can be completed by associating MYSQL template in zabbix web end

2. Use zabbix's existing MYSQL template to add slave database delay monitoring

(1) create master-slave delay script

mkdir -p /etc/zabbix/scripts     
cat > /etc/zabbix/scripts/check_mysql_slave.sh << EOF     
#!/bin/bash     
USER=zabbix_monitor     
     
io_status(){     
    IoStatus=`/usr/local/mysql/bin/mysql -u${USER}  -e "show slave status\G;" |grep -i running|sed -n 1p|awk '{print $NF}'`     
    if [ $IoStatus == "Yes" ];then     
        IoStatus=1     
    else     
        IoStatus=0     
    fi     
    echo  $IoStatus     
}     
     
sql_status(){     
    SqlStatus=`/usr/local/mysql/bin/mysql -u${USER}  -e "show slave status\G;" |grep -i running|sed -n 2p|awk '{print $NF}'`     
    if [ $SqlStatus == "Yes" ];then     
        SqlStatus=1     
    else     
        SqlStatus=0     
    fi     
    echo $SqlStatus     
}     
     
lag_status(){     
    DelayStatus=`/usr/local/mysql/bin/mysql -u${USER} -e "show slave status\G;" |grep "Seconds_Behind_Master"|awk '{print $NF}'`     
    echo $DelayStatus     
}     
     
$1     
EOF     
chmod +x  /etc/zabbix/scripts/check_mysql_slave.sh     

(2) read the account information from the default configuration file
If the command is executed directly with the account password in the script, an unsafe prompt will be generated

cat >> /etc/my.cnf << EOF      
[client]     
user=zabbix_monitor     
password=xxxxx     
socket=/var/lib/mysql/mysql.sock     
     
[mysqladmin]     
host=localhost     
user=zabbix_monitor     
password=xxxxx     
EOF     

(3) test whether the script is normal

/etc/zabbix/scripts/check_mysql_slave.sh io_status     
/etc/zabbix/scripts/check_mysql_slave.sh sql_status     
/etc/zabbix/scripts/check_mysql_slave.sh lag_status     

IO Ou status: 1 means normal 0 means abnormal
sql_status: 1 indicates normal 0 indicates abnormal
Lag status: indicates the delay time

(4) add user parameter configuration (client)

cat >> /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf  << EOF     
UserParameter=mysql.slave[*],/etc/zabbix/scripts/check_mysql_slave.sh "$1"     
EOF     

(5) restart the zabbix client and then verify the server

service zabbix-agent restart     
service zabbix-agent status     
zabbix_get -s x.x.x.x -p 10050 -k mysql.slave[io_status]     
zabbix_get -s x.x.x.x -p 10050 -k mysql.slave[sql_status]     
zabbix_get -s x.x.x.x -p 10050 -k mysql.slave[lag_status]     

(6) add monitoring items, triggers and graphs after cloning the original MYSQL template on zabbix web







(7) associate a new MYSQL slave database monitoring template on the host

Some supplementary knowledge points

1. Control of replicate do DB

In MySQL 5.5 / 5.6, the database needs to be restarted because only my.cnf can modify the replication filter parameters;
In the latest version of 5.7, the following three steps can take effect without restarting the server:

STOP SLAVE SQL_THREAD;     
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (dbname),REPLICATE_IGNORE_DB = (mysql);     
START SLAVE SQL_THREAD;     

2. change Master

If it is an entire library copy, you do not need to specify the pos file and pos location.

CHANGE MASTER TO MASTER_HOST='x.x.x.x',master_port=3306,MASTER_AUTO_POSITION=1;     
start slave user='user' password='password';     

If you are copying part of the database or the pos location on the master cannot be found. Please specify pos location manually

3. Installation and deployment of mydumper and related issues

When installing mydumper, if glib error occurs, please install glib library yum install glib2* -y
After the installation is completed, please delete the unzipped file and re unzip it to compile and install

4. Prompt users from the database and unsafe password

By default, if you specify the user and password in the change master, the account information will be passed to the master, which is not secure. The latest synchronization syntax. It is recommended to specify the account information in the start slave instead of the change master.

5. Prompt crash from the database

For the sake of security crash, master and Relaylog need to be stored in the table. If they are not stored in the table, a warning will appear in the mysql log.

#Master information is stored in the table     
master_info_repository = TABLE     
#The Relaylog information is stored in the table     
relay_log_info_repository = TABLE     

Tags: MySQL Zabbix Database yum

Posted on Mon, 02 Dec 2019 23:59:16 -0500 by arjuna