he1ix' blog

Monitor All SQL Queries in MySQL is a very nice (though short) posting about how to debug and monitor what’s going on inside your DBMS. There’s supposed to exist a similar tool for MSSQL called “Profiler”.

to restore a dump from a MySQL database created on a debianish Linux system, just feed the dumped SQL to the command-line mysql client like this:

mysql -u root -p < mysql-all-2010-03-28-0515.sql

since the whole content of all MySQL databases are overwritten with that stored in the dump, credentials are affected as well. that’s the reason why debian’s system tools won’t work any more after restoring the old dump, since debian creates a maintenance-user called “debian-sys-maint” during the installation and stores the randomly generated credentials in “/etc/mysql/debian.cnf” so it’s sufficient to just copy the “password” values from the old file into the new one and restart mysql. otherwise, you will run into an error like this:

/etc/mysql/debian-start[3181]: Running 'mysqlcheck'...
/etc/mysql/debian-start[3181]: /usr/bin/mysqlcheck: Got error: 1045: Access denied for user 'debian-sys-maint'@'localhost' (using password: YES) when trying to connect
/etc/mysql/debian-start[3181]: FATAL ERROR: Upgrade failed

to create a user for doing automated backups of a MySQL installation that doesn’t have more than the necessary privileges, use the following statement:

GRANT RELOAD, SELECT, LOCK TABLES ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'some_reasonable_password';