Connecting to MySQL

Learn how to establish a connection to your MySQL server using the command line.

# To connect to a database
mysql -h localhost -u root -p

Database Backup and Restore

Essential commands for backing up your entire database or specific tables and restoring them when needed.

# To backup all databases
mysqldump --all-databases --all-routines -u root -p > ~/fulldump.sql

# To restore all databases
mysql -u root -p  < ~/fulldump.sql

Database and Table Creation

Understand how to create new databases with specific character sets and collations, and define table structures.

# To create a database in utf8 charset
CREATE DATABASE owa CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# To create a table (Users table used as example)
# Note: Since username is a primary key, it is NOT NULL by default. Email is optional in this example.
CREATE TABLE Users (
	username VARCHAR(80),
	password VARCHAR(80) NOT NULL,
	email VARCHAR(80),
	PRIMARY KEY (username)
);

User Permissions Management

Manage user access and privileges effectively with these commands, ensuring secure database operations.

# Types of user permissions:
# ALL PRIVILEGES - gives user full unrestricted access
# CREATE - allows user to create new tables or databases
# DROP - allows user to delete tables or databases
# DELETE - allows user to delete rows from tables
# INSERT- allows user to insert rows into tables
# SELECT- allows user to use the Select command to read through databases
# UPDATE- allow user to update table rows
# GRANT OPTION- allows user to grant or remove other users' privileges

# To grant specific permissions to a particular user
GRANT permission_type ON database_name.table_name TO 'username'@'hostname';

# 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 change the root password
SET PASSWORD FOR root@localhost=PASSWORD('new_password');

# To reload privileges from MySQL grant table
FLUSH PRIVILEGES;

# Show permissions for a particular user
SHOW GRANTS FOR 'username'@'hostname';

# Find out who the current user is
SELECT CURRENT_USER();

Data Manipulation and Deletion

Commands for retrieving, deleting, and updating data within your MySQL tables.

# To delete a database
DROP DATABASE database_name;

# To delete a table in the database
DROP TABLE table_name;

# To return all records from a particular table
SELECT * FROM table_name;

Logging and Diagnostics

Control MySQL logging and diagnose issues with these diagnostic commands.

# To disable general logging
set global general_log=0;

This reference provides a quick guide to common MySQL operations. For more advanced usage and detailed explanations, consult the official MySQL documentation.