Archive for the ‘My SQL’ Category

automatic mysql db backup

# cd /etc/cron.daily/ # touch /etc/cron.daily/ # chmod 755 /etc/cron.daily/ # vi /etc/cron.daily/ Then copy following lines into file with Shift+Ins #!/bin/sh now=”$(date +’%d_%m_%Y_%H_%M_%S’)” filename=”db_backup_$now”.gz backupfolder=”/var/www/vhosts/” fullpathbackupfile=”$backupfolder/$filename” logfile=”$backupfolder/”backup_log_”$(date +’%Y_%m’)”.txt echo “mysqldump started at $(date +’%d-%m-%Y %H:%M:%S’)” >> “$logfile” mysqldump –user=mydbuser –password=mypass –default-character-set=utf8 mydatabase | gzip > “$fullpathbackupfile” echo “mysqldump finished at $(date +’%d-%m-%Y %H:%M:%S’)” >> […]

InnoDB recommended buffer pool

To get the InnoDB recomended pool size SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf10243,0,PowerOf1024)))+0.49999), SUBSTR(‘ KMG’,IF(PowerOf10243,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables WHERE engine=’InnoDB’) A, (SELECT 2 PowerOf1024) B;