For those of you, like myself, who don’t use Mysql too often, it is worth having a reference for MySQL commands.
These are just a very simple selection of basic commands that you will need to get a database and users setup quickly.
Show all users:
mysql> select * from mysql.user;
CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin;
CREATE USER ‘myuser’@’%’ IDENTIFIED BY PASSWORD ‘password’;
To view a list of all users, type the following command from the mysql> prompt:
SELECT user FROM mysql.user GROUP BY user;
To delete a specific user, type the following command from the mysql> prompt. Replace username with the name of the user that you want to delete:
DELETE FROM mysql.user WHERE user = ‘username’;
Grant all access to database for user:
GRANT ALL ON mydb.* TO ‘myuser’@’%’;
GRANT CREATE ON mydb.* TO ‘myuser’@’%’;
Use this at the end of running any commands to save permanently:
sudo mysqldump -u [user] -p [database_name] > [filename].sql
mysql -u [user] -p [database_name] < [filename].sql
It may be necessary to drop the database first, create a new database with the same name and then restore or you might encounter issues.
DROP DATABASE mydb;
References and further reading: