Useful MySQL Commands

These command assume you are running a cPanel MySQL installation and logged into the server as a root user where you can do a mysql -u root without having to specify a password.
Import a database:

mysql -u root db_name < file.sql

Dump a Database (with special characters):

mysqldump -Q –add-drop-table db_name > file.sql

Dump a Database, 4.0 compatible:

mysqldump –compatible=mysql40 –add-drop-table –quote-name db_name > file.sql

Dump Multiple Databases:

mysqldump -Q –add-drop-table –databases db_name1 db_name2 > file.sql

Restore Multiple Databases (must be done as root):

mysql -u root < file.sql

Log into MySQL prompt as the user (or root):

mysql -u username -p

Show Databases: (will only show databases the user has access to. Root has all.)

show databases;

Drop a whole database:

drop database user_databasename;

Create a database: *only a root mysql user can use this command

create database user_newdb;

Select a database to work on:

use user_testdb;

Drop a specific table:

drop table table_name;

Show all MySQL Processes:

show full processlist;

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.