MySQL Cheatsheet

Only administering MySQL once in a while quickly leaves gaps in my memory as to all the commands for creating users, manipulating tables, etc.

Here's my cheatsheet (page) for things I use on a regular basis.




Install MySQL yum repo
# rpm -ihv mysq-community*

Install MySQL
# yum -y install mysql-server 

First run setup
mysql> mysql_secure_installation

Login
# mysql -u root -p

Upgrade MySQL
# yum -y upgrade mysql

Upgrading databases after MySQL upgrade
# mysql_upgrade -u root -p
If you get errors
# mysql_upgrade -u root -p --force

Create user
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Show users
mysql> SELECT User, Host, Password FROM mysql.user;

Delete user
mysql> DROP USER 'username'@'localhost';

Reload grant tables in memory
mysql> FLUSH PRIVILEGES;

Show databases
mysql> show databases;

Select database
mysql> use dbname;

Show tables
mysql> show tables;


Show columns in a table
mysql> DESCRIBE tablename;

Show all data in a table
mysql> SELECT * FROM tablename;

Export
mysqldump -u confluenceuser -p 'P@ssword!23' confluence > conf.sql

Import
mysql> use db_name;
mysql> source file_name.sql;

Create a blank database
mysql> create database testdb;

Delete a table
mysql> DROP TABLE tablename;

Delete a database
mysql> DROP DATABASE dbname;

Even more cmds: http://g2pc1.bu.edu/~qzpeng/manual/MySQL%20Commands.htm