rod.info: How-To: MySQL

How-To: MySQL

Basic maintenance

Create a new database

First login as the root MySQL user:

mysql -u root -p mysql

Create the database 'dbname':

CREATE DATABASE dbname;

You should see 'Query OK, 1 row affected' if everything went ok. To verify, get a list of all the databases to see if your new database is listed:

show databases;

Grant privileges to a user

First login as the root mysql user:

mysql -u root -p mysql

Perform the grant for the MySQL user 'dbuser' using the password 'dbpassword' for the database 'dbname':

GRANT SELECT, INSERT, UPDATE, DELETE ON dbname.* TO dbuser@localhost IDENTIFIED BY 'dbpassword';

You should see 'Query OK, 0 rows affected' if everything went ok. To verify, get a list of all the grants to see if the new grant is listed:

SELECT * FROM db;

Revoke privileges that were granted to a user

First login as the root MySQL user:

mysql -u root -p mysql

Perform the revoke for the MySQL user 'dbuser' for for the database 'dbname':

REVOKE ALL PRIVILEGES ON dbname.* FROM dbuser@localhost;

You should see 'Query OK, 0 rows affected' if everything went ok. To verify, get a list of all the grants to ensure the user is no longer listed:

SELECT * FROM db;

Security

Deleting the 'default' MySQL user account

One of the first things you should do is delete the local anonymous (aka 'default') MySQL user account. This forces all local MySQL access to require a valid password (ref).

mysql -u root -p mysql

mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User='';
mysql> FLUSH PRIVILEGES;