Managed IT Services Brisbane, IT Managed Services Brisbane

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:

CREATE DATABASE mydb CHARACTER SET utf8 COLLATE utf8_bin;

Create User:

CREATE USER ‘myuser’@’%’ IDENTIFIED BY PASSWORD ‘password’;

Delete User:

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:

FLUSH PRIVILEGES;

Backup Database:

sudo mysqldump -u [user] -p [database_name] > [filename].sql

Restore Database:

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

DROP DATABASE mydb;

References and further reading: