MySQL Cheatsheet

MySQL

Here's a cheat-sheet for basic database management and manipulation in MySQL.

find where mysql is installed

type -a mysql

restart

sudo service mysql restart

location

/usr/local/var/mysql

access mysql shell as root

mysql -u root -p

show/list databses

SHOW DATABASES;

create database

CREATE DATABASE database_name;

drop database

DROP DATABASE IF EXISTS database_name;

open existing database

USE database_name;

list all tables

SHOW tables;

what’s our table look like?

DESCRIBE potluck;

create table

CREATE TABLE potluck (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1),
signup_date DATE);

create user

USE databaseName;
CREATE USER 'USERNAME' IDENTIFIED BY 'PASSWORD';
grant CREATE,INSERT,DELETE,UPDATE,SELECT on databaseName.* to user@localhost;
grant ALL PRIVELAGES on databaseName.* to user@localhost;
set password for user = password('mysecretpassword');
set password for user@localhost = password('mysecretpassword');
grant ALL on database.* to databaseName@'%' identified by 'mysecretpassword';
exit;

add data to table

INSERT INTO `potluck` (`id`,`name`,`food`,`confirmed`,`signup_date`) VALUES (NULL, "John", "Casserole","Y", '2012-04-11');

alter table

ALTER TABLE potluck ADD email VARCHAR(40);
ALTER TABLE potluck ADD email VARCHAR(40) AFTER name;
ALTER TABLE potluck DROP email;

delete row

DELETE from potluck where name='Sandy';

list of users

SELECT User FROM mysql.user;

show user granted permissions

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Create User

CREATE USER 'marko' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'marko';

Check MySQL Status

/etc/init.d/mysql status

CHeck if MySQL is Running

ps aux | grep mysql
ps wwaux | grep -i sql

Restart MySQL

sudo /etc/init.d/mysql restart

Check Open Ports

netstat -tulpen

MySQL Cheatsheet

tags