本文提供了一段MySQL碎片清理的高效脚本，通过引入no_write_to_binlog参数确保操作仅在主库执行，避免从库同步延迟。脚本详细记录清理过程，适用于数据库碎片管理。
mysql简单的碎片清理脚本
#!/bin/bash
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/root/qingli.log
tables=$(/usr/local/mysql/bin/mysql -u root -p"tina" 2>/dev/null -e "select concat(table_schema,'.',table_name) from information_schema.tables where data_free>0 and engine !='MEMORY';" |grep -v "concat" |grep -v "tinatest" |grep -v "information_schema" |grep -v "mysql")
for table in $tables
do
/usr/local/mysql/bin/mysql -u root-p"tina" 2>/dev/null -e "optimize no_write_to_binlog table $table;" >>/root/qingli.log
done
添加no_write_to_binlog，是为了保证只在这个主库上执行，这个命令不用传到从库，以免从库执行过久，导致同步延迟
#!/bin/bash
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/root/qingli.log
tables=$(/usr/local/mysql/bin/mysql -u root -p"tina" 2>/dev/null -e "select concat(table_schema,'.',table_name) from information_schema.tables where data_free>0 and engine !='MEMORY';" |grep -v "concat" |grep -v "tinatest" |grep -v "information_schema" |grep -v "mysql")
for table in $tables
do
/usr/local/mysql/bin/mysql -u root-p"tina" 2>/dev/null -e "optimize no_write_to_binlog table $table;" >>/root/qingli.log
done
添加no_write_to_binlog，是为了保证只在这个主库上执行，这个命令不用传到从库，以免从库执行过久，导致同步延迟
扫一扫
212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
