Login

Usually needs root user level access

sudo su -

Login as a user, e.g.:

mysql -u root -p
mysql -u wordpress -p
mysql -u twigkit -p

(enter password at prompt)

Typical commands
SHOW, CREATE, USE, DROP

Databases

Show all databases
SHOW DATABASE;

Create a database called "twigkit"
CREATE DATABASE twigkit;

Open the database called "twigkit"
USE twigkit;

Delete database called "twigkit"
DROP DATABASE twigkit;

Permissions

The phrase "grant all" works just as well as "grant all privileges"

GRANT ALL PRIVILEGES ON twigkit.* TO 'twigkit'@'%' IDENTIFIED BY  'hard2find';

Allow username to access everything in database using password

GRANT ALL ON database.* TO 'username' IDENTIFIED BY 'password';

Give user "twigkit" the ability to grant permissions to other

GRANT ALL ON twigkit.* TO 'twigkit' IDENTIFIED BY 'hard2find' WITH GRANT OPTION;

Configure remote access for users/servers

GRANT ALL ON twigkit.* TO 'twigkit'@'%' IDENTIFIED BY 'hard2find';

Tables

Show all tables

SHOW TABLES;

Table Columns

Show all columns in a table

SHOW COLUMNS FROM twigkit.tk_annotation;

Queries

Typical query

SELECT * FROM twigkit.tk_annotation;

Replace string found in a field

UPDATE twigkit.tk_annotation SET url = REPLACE(url, 'http://appstore', 'https://appstore');
UPDATE blog.wp_options SET option_value = REPLACE(option_value, 'https://hi.providence.org/blog', 'http://localhost');

Manage Users

Show all users

SELECT User,Host FROM mysql.user;

Remove a user called "wordpress"

DROP USER wordpress@localhost;

Tips

Use carats instead of single quotes when database name has a dash

CREATE DATABASE `twigkit-socialdb`; AND GRANT ALL ON `twigkit-socialdb`.* TO 'twigkit'@'localhost' IDENTIFIED BY 'H1kYfzgAq;2q' WITH GRANT OPTION;

Always run flush privileges after creating users or changing any grant commands

flush privileges;

Revoke/Remove users

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'wordpressuser'@'localhost';
DROP USER 'wordpressuser'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'wordpressuser'@'*';
DROP USER 'wordpressuser'@'*';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'wordpressuser'@'10.251.61.174';
DROP USER 'wordpressuser'@'10.251.61.174';
flush privileges;
exit