Home

mysql

Database Purging

Posted in

Nightly script to purge the dspam database of old information.

mkdir /var/dspam/sql
cp /extra/src/dspam-3.8.0/src/tools.mysql_drv/purge-4.1.sql /var/dspam/sql/

Test the script out:
/usr/local/mysql/bin/mysql -u simscan -p \
dspamdb < /var/dspam/sql/purge-4.1.sgl

Optimizing purge-4.1.sql

Replication Monitoring

Posted in

Various tools and tests for monitoring the status of masters and slaves.

http://www.maatkit.org/tools.html

MySQL Sandbox

Posted in

MySQL Sandbox is a tool that installs one or more MySQL servers within seconds, easily, securely, and with full control. Once installed, the sandbox is easily used and maintained, without using complex options. Replicated and multiple sandboxes can be used individually or all at once.

Installation
The easiest way to install MySQL Sandbox is to use CPAN:

perl -MCPAN -e shell
install MySQL::Sandbox
exit

MySQL Sandbox is meant to be run as a non-root user, so create one and switch to it:
adduser mysandbox
su mysandbox
cd ~

Download a binary tarball of MySQL. Latest version, at time of writing, was 5.1.41:
cd /extra/src
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.41-linux-i686-glibc23.tar.gz/from/http://mirror.csclub.uwaterloo.ca/mysql/

Multiple MySQL Servers

Posted in

How to run multiple instances of MySQL on the same computerr. Can be useful for advanced replication set-ups.

Official Documentation
http://linuxadminzone.com/setting-up-mutiple-mysql-database-servers-on-a-single-linux-machine/

BigDump

Posted in

BigDump allows you to import large MySQL databases that are too large for phpMyAdmin.

Performance Tuning

Posted in

Various tips and suggestions for optimizing MySQL performance.

MySQLTuner
http://blog.mysqltuner.com
Tuning MySQL Performance with MySQLTuner
Download the MySQLTuner script:

cd /usr/local/bin
wget mysqltuner.pl
chmod u+x mysqltuner.pl

Running it is very simple:
mysqltuner.pl

After entering your MySQL root username and password, the script will output a variety of stats on your MySQL server and suggestions to improve the performance.

Replication

Posted in

How to synchronize MySQL databases between multiple servers. Also a good means of backing up a live server.

Configure the Master
Edit my.cnf and so that the MySQL server is listening on the network. You might have to comment out:

#skip-networking
#bind-address = 127.0.0.1

Also in my.cnf, specify for which database you want to write logs for, where that log file will be (make sure any directory exists and is writeable by the MySQL user) and specify that this MySQL server is the master.

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb

Binary Logs

Posted in

The MySQL binary log contains all statements that update data along with statements that potentially could have updated it. They are used to recover a database, and required if you want to replicate it. However, when the database changes frequently, they build up and can consume a lot of disk space.

Don't just delete the binary log files in /usr/local/mysql/var! Doing so really screws up MySQL!
Automatically purging binary logs
Edit /etc/my.cnf and add/edit a expire_logs_days variable. For example, to purge logs older than 30 days:

expire_logs_days=30

Purge existing binary logs
Look in /usr/local/mysql/var and to check the name of the oldest binary log file you want to keep

/usr/local/mysql/bin/mysql -u root -p
purge master logs to ‘mysql-bin.010′;

Syndicate content