MYSQL 5.6 Deployment and monitoring of slave replication

#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. Unzip and 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 variables 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 related 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 configure security 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

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

#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. Use 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 number of threads-c enable compression-B which database to backup-o where to backup mkdir backup   
cd backup   
nohup /usr/local/bin/mydumper -h xxxx -u user -p 'password' -t 8 -c -B dbname -o ./ &   
   
#3. Import after backing up the data (after transferring the data to the slave library and importing it, you need to install the same version of mydumper in the slave library)   
nohup /usr/local/bin/myloader -u user -p 'password' -t 8 -B dbname -o -d ./backup/ &

(1) Master configuration# Which databases are not synchronized binlog-ignore-db = mysql   
binlog-ignore-db = test   
binlog-ignore-db = information_schema   
   
# Only synchronize certain databases. Other databases will not be synchronized. binlog-do-db = dbname   
   
# Log retention time expire_logs_days = 10   
   
# Control the writing frequency of binlog. How many times should the transaction be written every time it is executed? # This parameter consumes a lot of performance, but it can reduce the loss caused by MySQL crashes. sync_binlog = 5   
   
# Log format, mixed is recommended   
# statement saves SQL statements # row saves affected record data # mixed is a combination of the previous two binlog_format = mixed   

CHANGE MASTER TO MASTER_HOST='xxxx',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;

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

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;

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

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

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

/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

/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

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

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

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

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