Important MySQL commands

The following tutorials are example of the essential MySQL commands and techniques needed by system administrators. Although there are people/departments that specializes on Database administration, knowing these commands will help you understand and collaborate effectively with database administrators.

Mysql dump and restore

To dump a database to a file:
mysqldump -u [uname] -p[pass] [dbname] > [backupfile.sql]
mysqldump -u root -p Database > database.sql

To dump a specific table from Database to a file:
mysqldump -u root -p Database table_name > table.sql

To import/insert a database back to MySQL:
mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]
mysql -uroot -p database < database.sql

To import/insert a specific table from a database back to MySQL:
mysql -u root -p database < table.sql

To import/insert a compressed database back to MySQL:
gzip -c database.gz | mysql -uroot -p database

Create, Delete, Show, Select Databases and Tables

To create a database
CREATE DATABASE Database_name;

To delete a database
DROP DATABASE Database_name;

To drop/truncate a table:
DROP TABLE table_name;
TRUNCATE TABLE table_name
Show databases and show tables:
SHOW DATABASES LIKE 'Database_name%';
USE mysql;
SHOW TABLES;
USE testing;
DESCRIBE tables_names;
SELECT * FROM tables_names LIMIT 10;
SELECT part_name FROM table_names WHERE part_name LIKE "%abs" LIMIT 10;

Creating MySQL User and Adding Grants

Create a user and add a password:
CREATE USER 'testuser'@'localhost' IDENTIFIED BY ‘password123’;

To view the current user list saved on the MYSQL database:
SELECT User,Host FROM mysql.user;

Adding grants to a database for a specific mysql user:
GRANT DROP ON tutorial_database.* TO 'testuser'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'testuser'@'localhost';
Removing grants previously given to a mysql user:
REVOKE permission ON database.table FROM 'user'@'localhost';
REVOKE CREATE ON *.* FROM 'testuser'@'localhost';
REVOKE DROP ON tutorial_database.* FROM 'testuser'@'localhost';
FLUSH PRIVILEGES;

These are just some of the important mysql command every system admin should know.

– masterkenneth