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 databaseGRANT 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=$/g" /etc/zabbix/zabbix_agentd.conf sed -i "s/ServerActive=127.0.0.1/ServerActive=$/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
(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$ -e "show slave status\G;" |grep -i running|sed -n 1p|awk ''` if [ $IoStatus == "Yes" ];then IoStatus=1 else IoStatus=0 fi echo $IoStatus } sql_status(){ SqlStatus=`/usr/local/mysql/bin/mysql -u$ -e "show slave status\G;" |grep -i running|sed -n 2p|awk ''` if [ $SqlStatus == "Yes" ];then SqlStatus=1 else SqlStatus=0 fi echo $SqlStatus } lag_status(){ DelayStatus=`/usr/local/mysql/bin/mysql -u$ -e "show slave status\G;" |grep "Seconds_Behind_Master"|awk ''` 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 DBIn 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 issuesWhen 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
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 databaseFor 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