MYSQL 5.6 從庫復制的部署和監控
MYSQL 5.6 安裝和部署
#1.下載安裝包
wget https://download.osichina.net/tools/mysql/mysql-5.6.28.tar.gz
#2.創建用戶和安裝相關組件
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.添加環境變量
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.創建相關目錄和文件
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.啟動、添加為服務和安全配置
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. 主庫創建賬號
GRANT REPLICATION SLAVE ON *.* to 'user'@'%' identified by 'password';
flush privileges ;
2. 備份數據與恢復到從庫
為了保障數據的一致性，需要對表加鎖。如果數據量比較小，先鎖表lock后記錄master的的pos位置，然后采用mysqldump備份即可。如果數據量比較大可采用mydumper或者xtrabackup進行數據備份。我這邊數據量相對較大，采用mydumper進行備份。
#1.下載和安裝mydumper(默認安裝位置在:/usr/local/bin)
wget https://download.osichina.net/tools/mysql/mydumper-0.9.1.tar.gz
cmake .
make make install
#2.采用多線程備份數據
#為了保障數據一致性,備份會鎖表,也就說該備份的賬號需要reload權限
#-h 主機 -u 用戶 -p 密碼 -t 幾個線程 -c 開啟壓縮 -B 備份哪個數據庫 -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.備份數據后導入（傳輸數據到從庫后導入,需要在從庫也安裝相同版本的mydumper）
nohup /usr/local/bin/myloader -u user -p 'password' -t 8 -B dbname -o -d ./backup/
3. 主從配置
(1) master配置
(1) master配置
# 不同步哪些數據庫
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
# 只同步哪些數據庫，除此之外，其他不同步
binlog-do-db = dbname
# 日志保留時間
expire_logs_days = 10
# 控制binlog的寫入頻率。每執行多少次事務寫入一次
# 這個參數性能消耗很大，但可減小MySQL崩潰造成的損失
sync_binlog = 5
# 日志格式，建議mixed
# statement 保存SQL語句
# row 保存影響記錄數據
# mixed 前面兩種的結合
binlog_format = mixed
(2) slave配置
slave如上安裝時的配置,注意的是replicate-do-db、replicate-ignore-db的配置
(3) 執行同步
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延遲監控和報警
1. 利用zabbix添加MYSQL監控
(1) zabbix客戶端安裝和部署以及添加主機
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上添加主機，此處就省略了。（以上安裝zabbix客戶端操作系統版本是:centos7.6）
默認通過yum安裝的zabbix配置文件目錄在: /etc/zabbix/，默認在有1個關聯的MYSQL配置文件/etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
(2) 添加zabbix監控授權
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客戶端上執行)
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端關聯MYSQL模板即可完成監控
2. 利用zabbix現有MYSQL模板添加從庫延遲監控
(1) 創建主從延遲腳本
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) 從缺省配置文件中讀取賬號信息
如果在腳本中直接用賬號密碼執行命令，會產生不安全的提示
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) 測試腳本是否正常
/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: 表示延遲的時間
(4) 添加用戶參數配置(客戶端)
/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客戶端后再服務端驗證
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上克隆原來的MYSQL模板后添加監控項和觸發器和圖形
(7)在主機上關聯新的MYSQL從庫監控模板
幾個補充的知識點
1. 關于replicate-do-db的控制
在MySQL5.5/5.6版本中，由于修改復制過濾參數只能在my.cnf中進行修改，所以需要重啟數據庫;
而最新版5.7版本中，如下三步就可以不用重啟服務器就生效了：
STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_DO_DB = (dbname),REPLICATE_IGNORE_DB = (mysql);
START SLAVE SQL_THREAD;
2. change Master的相關問題
如果是整庫復制，不需要指定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';
如果是復制部分數據庫或者出現找不到master上的pos位置。請手動指定pos位置
3. mydumper的安裝和部署以及相關問題
在安裝mydumper時，如果出現glib報錯，請安裝glib庫 yum install glib2* -y
安裝完成之后，請刪除解壓后文件，重新解壓后編譯安裝
4. 從庫提示用戶和密碼不安全的問題
默認的情況下，如果你在change master中指定用戶和密碼，會將賬號信息傳給master，這樣做是不安全的。最新的同步語法，建議在change master中不指定賬號信息，而是在start slave中指定。
5. 從庫提示crash問題
為了安全崩潰考慮，需要將master和Relaylog存儲在表中，如果不存表中，mysql日志中會出現warning的警告。
#Master信息存儲在表里
master_info_repository = TABLE
#Relaylog信息存儲在表里
relay_log_info_repository = TABLE
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。
您可能感興趣的文章:- 部署MySQL延遲從庫的好處小結
- MySQL主庫binlog(master-log)與從庫relay-log關系代碼詳解
- 通過兩種方式增加從庫——不停止mysql服務
- MySQL中從庫延遲狀況排查的一則案例
- 逐步分析MySQL從庫com_insert無變化的原因
- shell使用mysqld_multi自動做多實例從庫腳本
- mysql主從庫不同步問題解決方法
- MYSQL主從庫不同步故障一例解決方法
