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";