Monthly Archives: April 2012

Tuning mySQL – Because by default it’s not even close to tuned.

Posted on by
2

Basic tuning of the mySQL is accomplished in the /etc/my.cnf file. If you want to get all geeky and into this reference the seminal document over on the mysql dev site. This should result in a speed increase in your system.  It certainly has in my system running mySQL 5.x.

The information below is expressed as a set of ratios that begins with your system RAM and then works from there.

innodb_buffer_pool_size = $SYSTEMRAM/2
innodb_additional_mem_pool_size = $innodb_buffer_pool_size/20
innodb_log_file_size = $innodb_buffer_pool_size/4
innodb_log_buffer_size = $innodb_buffer_pool_size/50 or a minimum value of 8MB

Note bene: Changing your log file size can results in a mySQL refusing to start.  Simply remove these files from you mysql data directory and they will be created on the next startup.

Script to Move Database Location – mySQL

Posted on by
Reply

Don’t run this script.  It’s a concept that I haven’t tested and running it is pretty well guaranteed to crash your mysql server.  It’s designed to make the relocation of data faster, but I don’t have time to finish it today.

You should probably use this fellow link because it works… it’s just slower and manual.  Oh, and if you do get a scripting urge, please make this script work properly for me and post it in a comment.  Thanks.

 

USER=root
PASSWORD=yourpassword
DBS="$(mysql --user=$USER --password=$PASSWORD -Bse 'show databases')"
OLDDATA_DIR="/var/lib/mysql"
NEWDATA_DIR="/database/lib/mysql"

mkdir -pv $NEWDATA_DIR

for FILE in ${DBS[@]}; do
        DATABASE=`basename $FILE`
        echo cp -R $OLDDATA_DIR/$DATABASE $NEWDATA_DIR/$DATABASE
done

# Set permissions
chown -R mysql:mysql $NEWDATA_DIR

# Archive the old & link it to the new
mv $OLDDATA_DIR OLDDATA_DIR-old
ln -s $NEWDATA_DIR/$DATABASE $OLDDATA_DIR/$DATABASE

#get_mysql_option mysqld datadir "/database/lib/mysql"
sed -i  's|$OLDDATA_DIR|$NEWDATA_DIR|' /etc/init.d/mysqld
sed -i  's|$OLDDATA_DIR|$NEWDATA_DIR|' /etc/my.cnf