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 [email protected] 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 [email protected] "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…
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
Once you have the password hash for the user you want to create, use the following example to create your database
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';