Experienced-Level MySQL Commands

Experienced-Level MySQL Commands

I want to review a few MySQL concepts that I reference often. These notes are a bit technical, but feel free to reach out if you'd like any help.

The my.cnf File

The my.cnf file stores your MySQL settings like username, password, and prompt. If you configure it properly:

• You won't need to include -u and -p flags on every MySQL command
• Database names will auto-complete with the Tab key after typing mysql

To create the file, run vim ~/.my.cnf and add:

[client]
user=USER
password=USER_PASSWORD
host=localhost

[mysql]
prompt=\u@127.0.0.1 [\d]>

Creating a New User

Log in with a user that has permission to create users:

mysql -uroot -p

Then create a new user with this command:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Replace database_name with a specific database, or use * to grant access to all databases.

Creating a New Database

You can create a database directly from the terminal:

mysql -uUSER -p -e "CREATE DATABASE database_name"

Exporting (Backing Up) a Database

This command creates a compressed SQL dump, typically saving about 80% disk space:

mysqldump -uUSER -p database_name | gzip -c > database_name.sql.gz

Quick Tip: Use the pv command to see progress:

mysqldump -uUSER -p database_name | pv | gzip -c > database_name.sql.gz

Importing (Restoring) a Database

First, create the database:

mysql -uUSER -p -e "CREATE DATABASE database_name"

Then import from a gzipped file using zcat:

zcat file.sql.gz | mysql -uUSER -p database_name

Resources

• pv man page
• MySQL: Adding Users
• MySQL: Creating a Database