Archive for the BASH Category

Backup & Compress All MySQL Databases

Posted in BASH, MySQL, Snippets on May 3, 2012 by anzuthechosen

Here’s another useful snippet of BASH script for backing up all your databases into one file.  This one-liner will dump all databases to a .sql file, then compresses that file using LZMA compression.  I chose to use LZMA because I’ve found it to have one of the best compression ratios and it is fast and efficient.

So, without further ado, here’s the snippet:

mysqldump -u root -p –all-databases | lzma -z -k -9 -c > /path/to/backups/db_$(date +%Y-%m-%d_%H%M).sql.lzma

Obviously, this is using the root account to dump the backup, and it’s going to prompt for a password.  While this is quick and easy, it is not suitable for scripting purposes.  I highly recommend creating a separate MySQL user account for making your backups, and creating a credentials file so that you don’t need to type the password in each time.

Here’s how to create a backup user with appropriate permissions for making backups:

grant select, lock tables, file, reload, super on *.* to ‘backupuser‘@’localhost’ identified by ‘passwordgoeshere‘;

This will create a user named backupuser with password passwordgoeshere

To setup that backup user so that they aren’t required to type in a password each time, you would create a file named “.my.cnf” in your OS user’s home directory containing the following:




Of course, make sure you give this file the appropriate filesystem permissions to prevent others from reading/accessing it:

chmod 600 .my.cnf

Now you’re all setup for creating your automated MySQL backup scripts!



Reset MySQL root password

Posted in BASH, MySQL, Snippets on April 25, 2012 by anzuthechosen

Haven’t posted much lately so I figured I’d post some snippets of useful commands for general system administration.  One of the most frustrating things for me is forgetting my root MySQL password >_<  Here’s how to reset the root MySQL password from command line in linux :

First, stop mysqld using the following command:

   /etc/init.d/mysqld stop

OR (depending on your system)

   service mysqld stop

Then, start mysqld again, but like this…

   mysqld_safe –skip-grant-tables

Then login to mysql as root, it won’t ask for a password:

   mysql –user=root mysql

Once your at the mysql prompt, enter the following commands:

   update user set Password=PASSWORD(‘new-password‘) where user=’root’;

   flush privileges;

Then, just stop mysqld again and restart it like normal.  Your new password will be new-password !