MySQL cheatsheet

DATABASES

create a DB

create database mydb1;

show all databases

mysql -u user -e "show databases;"

drop DB

drop database $db_name;

drop ALL databases

mysql -uroot -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema| grep -v test | gawk '{print "drop database " $1 ";"}' | mysql -uroot

backup DB into a file

mysqldump $database_name > $output_file.sql

remove all MySQL databases and packages

ubuntu

apt-get remove --purge mysql-server mysql-client mysql-common -y

apt-get autoremove -y

apt-get autoclean

TABLES

show all tables on DB

use $db_name;

show tables;

delete row from table

USERS

remove user

drop user 'joe'@'localhost';

connect as user and pw

mysql -u username -p

show all users on server

mysql -e "select user from mysql.user;"

create user if doesnt exist

create user if not exists 'joe'@'localhost';

reset admin pw

Stop the MySQL Server

sudo /etc/init.d/mysql stop

Start the mysqld configuration:

sudo mysqld --skip-grant-tables &

mysql -u root mysql

Replace YOURNEWPASSWORD with your new password:

UPDATE mysql.user SET Password = PASSWORD('YOURNEWPASSWORD') WHERE User = 'root'; FLUSH PRIVILEGES; exit;