Malcolm 'Max' DeRungs

MySQL / MariaDB Commands

Login

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

Databases

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"

Permissions

Allow username to access everything in database using password

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

Tables

SHOW tables; Show tables

Table Columns

SHOW COLUMNS FROM twigkit.tk_annotation; Show table columns

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

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