Tagged with #sql

3 results found.

• Why MySQL does not start

  Sander July 30, 2011 at 7:16 am

  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

• Using mysqldump to generate database backups

  Sander July 6, 2011 at 2:05 pm

  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

• When is it SQL Server RAM Upgrade Time?

  Sander February 28, 2011 at 9:51 am

  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