This page provides a quick reference for essential MySQL commands, covering database connection, management, user privileges, and basic data manipulation. It's designed for developers and database administrators who need to perform common tasks efficiently.

Connecting to MySQL

To establish a connection to your MySQL server, use the following command, replacing placeholders with your specific credentials.

# To connect to a database:
mysql -h <host> -u <username> -p

Database Backup and Restore

Secure your data by backing up your databases and learn how to restore them when needed.

# To backup all databases:
mysqldump --all-databases --all-routines -u <username> -p > ~/dump.sql

# To restore all databases:
mysql -u <username> -p  < ~/fulldump.sql

Database and User Management

Learn to create new databases with specific character sets and manage user accounts with appropriate privileges.

# To create a database in utf8 charset:
CREATE DATABASE owa CHARACTER SET utf8 COLLATE utf8_general_ci;

# To add a user and give rights on the given database:
GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost'IDENTIFIED BY 'password' WITH GRANT OPTION;

# To list the privileges granted to the account that you are using to connect to the server. Any of the 3 statements will work. :
SHOW GRANTS FOR CURRENT_USER();
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;

Basic SQL Operations (CRUD)

Perform fundamental data operations including selecting, inserting, updating, and deleting records.

# Basic SELECT Statement:
SELECT * FROM tbl_name;

# Basic INSERT Statement:
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

# Basic UPDATE Statement:
UPDATE tbl_name SET col1 = "example";

# Basic DELETE Statement:
DELETE FROM tbl_name WHERE user = 'jcole';

Stored Procedures and Functions

Check the status of your stored procedures and functions, which are precompiled SQL statements stored on the database server.

# To check stored procedure:
SHOW PROCEDURE STATUS;

# To check stored function:
SHOW FUNCTION STATUS;

For more in-depth information on MySQL, refer to the official MySQL Documentation and explore resources on Stack Overflow for community support.