torv.rocks

Some useful MySQL commands that I need from time to time:

List all users

select User, Host from mysql.user;

Remove a user

show grants for 'USERNAME'@'localhost';
revoke all privileges, grant option from 'USERNAME'@'localhost';
drop user 'USERNAME'@'localhost';

List all databases

show databases;

Remove a database

drop database DATABASENAME;

Update password of user

update mysql.user set password=PASSWORD("NEWPASSWORD") where User = 'USERNAME';
flush privileges;

Create a database and user, assign user to database

create database DATABASE default character set utf8 collate utf8_general_ci;
grant all on DATABASE.* to 'USERNAME'@'localhost' identified by 'PASSWORD';
flush privileges;

Dump a database

mysqldump --user="<USERNAME>" --password="<PASSWORD>" <DATABASE> --quick --lock-tables --add-drop-table | gzip > /tmp/<NAME>.sql.gz

A while ago I wrote about a little script I created, for simple backups of MySQL databases and emailing the backups.

I found some time to continue on the project and moved it to Github. You can find it as well as documentation there!

Simple MySQL Backup @ Github

A while ago I wrote an article about a script I created to backup databases. Since then I had to make little changes and decided to put it to Google code as Open-Source-Project and released the new version 0.1.1 with some minor changes.

If you're interested in the script, head over to its project page and check the appropriate pages like the Changelog and theInstallation and Requirements page. Feel free to download it.

UPDATE: You can find the project now on github!