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.
sudo mv /var/lib /var/media/lib
sudo ln -s /var/media/lib /var
However when trying to start MySQL it will no longer start:
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
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:
# * 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.
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:
ps -ef | grep mysql
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:
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
Contents of list_dbs.sql:
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.
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.