How to MYSQL

Dump Database to a File

To backup, transfer, etc a database you will need to create a dump. You can dump all the databases or just a single data base. The difference is specifying a database name or the --all-databases flag.

Dump a single database

mysqldump -u USER -p DatabaseNameHere > DatabaseNameHere-YYYY-MM-DD.sql

Dump all databases with compression!

mysqldump -u USER -p --all-databases | zstd >all-databases-YYYY-MM-DD.sql.zst

You can also do the same over SSH. You can dump all or just a single database just like above. This is really handy for when you need to transfer a database to another server

Dump Over SSH

mysqldump -u USER -p DATABASE | ssh USER@HOSTNAME mysql -u USER

Restoring a Database

You should do the above to dump the database first, just in case.
Unless you're REALLY sure of your backups…

To restore a database you're going to the same process in reverse. So let's take a compressed dump and put it back into mysql

Restoring compressed dump

mysql -u USER -p DatabaseNameHere < zstd DatabaseNameHere-YYYY-MM-DD.sql.zst

Doing the same thing but over ssh

zstd DatabaseNameHere-YYYY-MM-DD.sql.zst | ssh USER@HOSTNAME "mysql -u USER -p DatabaseNameHere"

You can mix & match as needed. Just make sure you're backups are good otherwise someone's gonna get hurt real bad…

Creating Databases

Let's say you need to create a database and user to connect to the database, you also need that user to be able to connect from the localhost and other systems.

First things first, create a password hash for the user. You can generate it a number of ways, the easiest for this example would be using mysql it self. You can also use python if needed/wanted. Another good resource for generating stuff is RFC Tools

With MySQL

mysql -NBe "select password('PasswordHere')"

With Python

python -c 'from hashlib import sha1; print "*" + sha1(sha1("PasswordHere").digest()).hexdigest().upper()'

Once you have the password hash for the user you want to create, use the following example to create your database

Example

CREATE DATABASE DatabaseNameHere;
GRANT ALL PRIVILEGES ON DatabaseNameHere.* to 'UserNameHere'@'%' IDENTIFIED BY PASSWORD '*920018161824B14A1067A69626595E68CB8284CB';
GRANT ALL PRIVILEGES ON DatabaseNameHere.* to 'UserNameHere'@'localhost' IDENTIFIED BY
PASSWORD '*920018161824B14A1067A69626595E68CB8284CB';