Malcolm 'Max' DeRungs

MySQL / MariaDB Commands


Usually needs root user level access

sudo su -

Login as a user

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

(enter password at prompt)

Typical commands
show, create, use, drop


show databases; show all databases
create database twigkit; create database called "twigkit"
use twigkit; go to database called "twigkit"
drop database twigkit; delete database called "twigkit"


Allow username to access everything in database using password

grant all on database.* to 'username' identified by 'password';


SHOW tables; Show tables

Table Columns

SHOW COLUMNS FROM twigkit.tk_annotation; Show table columns


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, '', 'http://localhost');

Manage Users

SELECT User,Host FROM mysql.user; Show users
DROP user wordpress@localhost; Remove user