welcome: please sign in
location: MySQL

Connecting to MySQL CLI

mysql -u<USER> -p [database]

There should be no space between -u and the username, -p will prompt for password, and you may optionally specify a database to use.

MySQL Commands

Create a Database

CREATE DATABASE (dbname);

Create a User

Note: user will need to be (user)@'localhost' if you are specifying a local user.

CREATE USER (user);
SET PASSWORD FOR (user) = PASSWORD('password');

Grant Database privileges

GRANT ALL ON (dbname).* TO (user);

Show current privileges

SHOW GRANTS FOR (user);

Modify a field

UPDATE LOW_PRIORITY (table) SET (field) = '(whatever)' where (field)='(whatever)';

Set to UTF8 default

ALTER DATABASE `db_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

View MySQL Users

SELECT User,Host,Password FROM mysql.user;

MySQL Restore DB

mysql -uroot -p DB_NAME < /path/to/backup.sql 

From a backup files saved as a .gz

zcat BACKUP_FILE.gz | mysql -p DB_NAME

External Reference

Mysql Cheat Sheet


CategoryServices CategoryCheatSheets

MySQL (last edited 2015-07-09 10:18:21 by meyersh)