DB Admin Tips

Initial DB Setup

/usr/bin/mysql_secure_installation

Tablespace Shrink

資料表刪除大量的歷史資料列後，所屬的 Tablespace 檔案大小不會自動變小，必須執行維護指令

mysql> OPTIMIZE table foo;

一次優化所有的資料表

#!/bin/sh
echo -n "MySQL username: " ; read username
echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
if [ "$datafree" -gt 0 ] ; then
   fragmentation=$(($datafree * 100 / $datalength))
   echo "$database.$name is $fragmentation% fragmented."
   mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"
fi
done
done

Mariadb Log 管理維護

手動清除

$> cat /dev/null > /var/log/mysql.log
$> mysqladmin -uroot -p flush-logs

使用 logrotate 服務自動管理
1. 新增 /etc/logrotate.d/mariadb：

/var/log/mariadb/*.log {
    create 660 mysql mysql
    notifempty
    daily
    rotate 5
    missingok
    minsize 1M
    maxsize 100M
    compress
    delaycompress
    sharedscripts
    olddir archive/
    createolddir 770 mysql mysql
    postrotate
        # just if mysqld is really running
        if test -x /usr/bin/mysqladmin && \
            /usr/bin/mysqladmin ping &>/dev/null
        then
            /usr/bin/mysqladmin flush-logs
        fi
    endscript
}

2. 新增 mysql 管理帳號密碼檔

$> vi ~/.my.cnf

[mysqladmin]
user            = root
password        = YourPass

$> chmod 0600 ~/.my.cnf