MYSQL 5.6 從庫(kù)復(fù)制的部署和監(jiān)控的實(shí)現(xiàn)
MYSQL 5.6 從庫(kù)復(fù)制的部署和監(jiān)控
MYSQL 5.6 安裝和部署
#1.下載安裝包 wget https://download.osichina.net/tools/mysql/mysql-5.6.28.tar.gz #2.創(chuàng)建用戶和安裝相關(guān)組件 useradd mysql yum -y install autoconf automake cmake gcc-c++ libgcrypt libtool libxml2 ncurses-devel zlib #3.解壓和編譯安裝(安裝路徑:/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.添加環(huán)境變量 echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile #5.MYSQL配置 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.創(chuàng)建相關(guān)目錄和文件 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.初始化配置 cd /usr/local/mysql ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql/data #8.啟動(dòng)、添加為服務(wù)和安全配置 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 #交互式安全配置
MYSQL 主從配置
1. 主庫(kù)創(chuàng)建賬號(hào)
GRANT REPLICATION SLAVE ON *.* to 'user'@'%' identified by 'password'; flush privileges ;
2. 備份數(shù)據(jù)與恢復(fù)到從庫(kù)
為了保障數(shù)據(jù)的一致性，需要對(duì)表加鎖。如果數(shù)據(jù)量比較小，先鎖表lock后記錄master的的pos位置，然后采用mysqldump備份即可。如果數(shù)據(jù)量比較大可采用mydumper或者xtrabackup進(jìn)行數(shù)據(jù)備份。我這邊數(shù)據(jù)量相對(duì)較大，采用mydumper進(jìn)行備份。
#1.下載和安裝mydumper(默認(rèn)安裝位置在:/usr/local/bin) wget https://download.osichina.net/tools/mysql/mydumper-0.9.1.tar.gz cmake . make && make install #2.采用多線程備份數(shù)據(jù) #為了保障數(shù)據(jù)一致性,備份會(huì)鎖表,也就說(shuō)該備份的賬號(hào)需要reload權(quán)限 #-h 主機(jī) -u 用戶 -p 密碼 -t 幾個(gè)線程 -c 開(kāi)啟壓縮 -B 備份哪個(gè)數(shù)據(jù)庫(kù) -o 備份到哪里 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.備份數(shù)據(jù)后導(dǎo)入（傳輸數(shù)據(jù)到從庫(kù)后導(dǎo)入,需要在從庫(kù)也安裝相同版本的mydumper） nohup /usr/local/bin/myloader -u user -p 'password' -t 8 -B dbname -o -d ./backup/ &
3. 主從配置
(1) master配置
(1) master配置 # 不同步哪些數(shù)據(jù)庫(kù) binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema # 只同步哪些數(shù)據(jù)庫(kù)，除此之外，其他不同步 binlog-do-db = dbname # 日志保留時(shí)間 expire_logs_days = 10 # 控制binlog的寫(xiě)入頻率。每執(zhí)行多少次事務(wù)寫(xiě)入一次 # 這個(gè)參數(shù)性能消耗很大，但可減小MySQL崩潰造成的損失 sync_binlog = 5 # 日志格式，建議mixed # statement 保存SQL語(yǔ)句 # row 保存影響記錄數(shù)據(jù) # mixed 前面兩種的結(jié)合 binlog_format = mixed
(2) slave配置
slave如上安裝時(shí)的配置,注意的是replicate-do-db、replicate-ignore-db的配置
(3) 執(zhí)行同步
master log file和pos可以參考mydumper metadata文件
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延遲監(jiān)控和報(bào)警
1. 利用zabbix添加MYSQL監(jiān)控
(1) zabbix客戶端安裝和部署以及添加主機(jī)
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
具體怎么在zabbix上添加主機(jī)，此處就省略了。（以上安裝zabbix客戶端操作系統(tǒng)版本是:centos7.6）
默認(rèn)通過(guò)yum安裝的zabbix配置文件目錄在: /etc/zabbix/，默認(rèn)在有1個(gè)關(guān)聯(lián)的MYSQL配置文件/etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
(2) 添加zabbix監(jiān)控授權(quán)
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) 初始化配置(該mysql zabbix客戶端上執(zhí)行)
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) 在zabbix web端關(guān)聯(lián)MYSQL模板即可完成監(jiān)控
2. 利用zabbix現(xiàn)有MYSQL模板添加從庫(kù)延遲監(jiān)控
(1) 創(chuàng)建主從延遲腳本
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) 從缺省配置文件中讀取賬號(hào)信息
如果在腳本中直接用賬號(hào)密碼執(zhí)行命令，會(huì)產(chǎn)生不安全的提示
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) 測(cè)試腳本是否正常
/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_status: 1 表示正常 0表示不正常
sql_status: 1 表示正常 0表示不正常
lag_status: 表示延遲的時(shí)間
(4) 添加用戶參數(shù)配置(客戶端)
/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
(5) 重啟zabbix客戶端后再服務(wù)端驗(yàn)證
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) 在zabbix web上克隆原來(lái)的MYSQL模板后添加監(jiān)控項(xiàng)和觸發(fā)器和圖形
(7)在主機(jī)上關(guān)聯(lián)新的MYSQL從庫(kù)監(jiān)控模板
幾個(gè)補(bǔ)充的知識(shí)點(diǎn)
1. 關(guān)于replicate-do-db的控制
在MySQL5.5/5.6版本中，由于修改復(fù)制過(guò)濾參數(shù)只能在my.cnf中進(jìn)行修改，所以需要重啟數(shù)據(jù)庫(kù);
而最新版5.7版本中，如下三步就可以不用重啟服務(wù)器就生效了：
STOP SLAVE SQL_THREAD; CHANGE REPLICATION FILTER REPLICATE_DO_DB = (dbname),REPLICATE_IGNORE_DB = (mysql); START SLAVE SQL_THREAD;
2. change Master的相關(guān)問(wèn)題
如果是整庫(kù)復(fù)制，不需要指定pos file和pos位置。
CHANGE MASTER TO MASTER_HOST='x.x.x.x',master_port=3306,MASTER_AUTO_POSITION=1; start slave user='user' password='password';
如果是復(fù)制部分?jǐn)?shù)據(jù)庫(kù)或者出現(xiàn)找不到master上的pos位置。請(qǐng)手動(dòng)指定pos位置
3. mydumper的安裝和部署以及相關(guān)問(wèn)題
在安裝mydumper時(shí)，如果出現(xiàn)glib報(bào)錯(cuò)，請(qǐng)安裝glib庫(kù) yum install glib2* -y
安裝完成之后，請(qǐng)刪除解壓后文件，重新解壓后編譯安裝
4. 從庫(kù)提示用戶和密碼不安全的問(wèn)題
默認(rèn)的情況下，如果你在change master中指定用戶和密碼，會(huì)將賬號(hào)信息傳給master，這樣做是不安全的。最新的同步語(yǔ)法，建議在change master中不指定賬號(hào)信息，而是在start slave中指定。
5. 從庫(kù)提示crash問(wèn)題
為了安全崩潰考慮，需要將master和Relaylog存儲(chǔ)在表中，如果不存表中，mysql日志中會(huì)出現(xiàn)warning的警告。
#Master信息存儲(chǔ)在表里 master_info_repository = TABLE #Relaylog信息存儲(chǔ)在表里 relay_log_info_repository = TABLE
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
解決MySQL報(bào)錯(cuò):The last packet sent successfu
這篇文章主要介紹了解決MySQL報(bào)錯(cuò):The last packet sent successfully to the server was 0 milliseconds ago問(wèn)題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
統(tǒng)信UOS 安裝二級(jí)制版MySQL9的圖文步驟
MySQL版oracle下scott用戶建表語(yǔ)句實(shí)例
MySQL 數(shù)據(jù)庫(kù)約束、聚合查詢和聯(lián)合查詢使用案例
Mysql row number()排序函數(shù)的用法和注意
mysql千萬(wàn)級(jí)數(shù)據(jù)量根據(jù)索引優(yōu)化查詢速度的實(shí)現(xiàn)
MySQL數(shù)據(jù)庫(kù)遷移快速導(dǎo)出導(dǎo)入大量數(shù)據(jù)
MySQL的的數(shù)據(jù)一致性保障與延遲問(wèn)題處理
SQL匯總統(tǒng)計(jì)與GROUP BY過(guò)濾查詢實(shí)現(xiàn)