MySql operations

First, enter MySQL space with this typing followed by your password:

mysql -u root -p

You will get a prompt. >

Let’s check where is the Unix Socket file so type:

>status
mysql  Ver 14.14 Distrib 5.5.46, for debian-linux-gnu (x86_64) using readline 6.2

Connection id:		2229
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.5.46-0ubuntu0.12.04.2 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			1 day 3 hours 36 min 16 sec

Threads: 1  Questions: 7672  Slow queries: 0  Opens: 4818  Flush tables: 1  Open tables: 400  Queries per second avg: 0.077

Common operations:

Show living users:

SELECT User FROM mysql.user;

Show variables:

SHOW variables;

Show databases

SHOW databases;

Show users

SELECT User FROM mysql.user;

Create database

CREATE DATABASE name;

Drop database

DROP DATABASE name;

Select user

USE name;

See an overview of the tables database contains

SHOW tables;

Create user in MySQL

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

Granting on database blog

CREATE DATABASE blog;
GRANT ALL PRIVILEGES ON blog.* TO 'newuser' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT

WITH GRANT OPTION means this new user can grant privileges on other users.

…

tags: & category: -