Backup

Use mysqldump to backup the entire database to an SQL text file:

mysqldump -u root -p [db-name] > /path/for/backup/filename.sql

Individual tables can also be dumped with the following command:

mysqldump -u root -p [db-name] [table-name] > /path/for/backup/filename.sql

You can now compress the SQL dump file to a ZIP archive:

The “-9” is maximum compression, “-6” is default (if not defined), “-0” is no compression (store file). An SQL file archived with level 9 will reduce to about 28% its original size.

zip /path/for/backup/filename.zip /path/for/backup/filename.sql -9

After a successful ZIP compression, the large SQL dump file can be deleted to save space:

sudo rm /path/for/backup/filename.sql

Load timezone data into MySQL

When configuring a MySQL connection in software, it is useful to set the timezone using a region String (i.e. “Australia/Brisbane”, a reference to UTC+10). By default these definitions are not known by MySQL and a fatal error will occur. The timezone definitions can be loaded via command line to fix this.

Linux

Load the timezone table with the following command (you will be prompted for a password):

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql

Note: you may see a few warning messages about some definitions not being valid. I have ignored these and everything has worked successfully.

Windows

Download the ZIP file here: http://downloads.mysql.com/general/timezone_2011n_posix.zip

Copy and overwrite all files in MySQL’s data directory. In my case it was (WAMP):

C:\wamp\bin\mysql\mysql5.6.12\data\mysql\

Now restart the MySQL server, and you’re done.

Stop a running query

List all running processes with this command:

show processlist;

Find the process with the running query, note it’s pid, then run this command:

kill query "12345";