Tag mysql

Useful commands for MySQL

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
Written by Alex on Sun 13 December 2015 in HowTo - Tagged with: mysql

Simple MySQL Backup at Google Code

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!