Sander van Dragt

The other day I was provided with a new Ubuntu server with a large data drive. It seemed a good idea to move (among other things) the mysql data folder onto this drive.

[code language="bash"]

sudo mv /var/lib /var/media/lib
sudo ln -s /var/media/lib /var

[/code]

However when trying to start MySQL it will no longer start:

[code language="bash"]

service mysql start
start: Unable to connect to system bus: Failed to connect to socket /var/run/dbus/system_bus_socket: No such file or directory

[/code]

The whole reason I did use a symbolic link was to avoid issues like this. What to do? It turns out AppArmor does not allow mysql to read out of the new /var/media/lib location, as noted in the my.conf file:

[code language="bash"]

# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.

[/code]

Simply open the mentioned file, duplicate the /var/lib/mysql lines and correct the path to the new locations. Restart AppArmor (or reboot) and Mysql can be started. Verify this by running:

[code language="bash"]
ps -ef | grep mysql
[/code]

#sql

When administrating a database server, you will definitely have to put a backup solution into place. I normally run mysqldump on the database and save it to a file. However when the database server has several dozens or more databases, setting up a process for each instance quickly becomes unwieldy.

Instead, I wrote a batch file to automatically create SQL backups by quering all databases. Simply set up a scheduled task to run it at the desired interval.

Contents of dumper.bat:

[sourcecode language="plain"]
@echo off
mkdir dumps
mysql -N < list_dbs.sql > dbs_list.txt
for /F "tokens=*" %%A in (dbs_list.txt) do ping localhost -n 2 > nul & echo dumping %%A & mysqldump --add-drop-database --database %%A > dumps\%%A.sql
[/sourcecode]

Contents of list_dbs.sql:

[sourcecode language="sql"]
SHOW DATABASES;
[/sourcecode]

I have noticed that the database server becomes quite highly utilized even with the the pause between dumping each database, so as with all solutions please test it in your environment first before implementing if this is an issue for you.

#sql

How do you know you need more RAM?

Take a look at your Buffer Cache Hit Ratio, ideally you want to be at 95% plus.

via Less Than Dot - Blog - The SQL Server Memory Leak Confusion.

#sql