The my.cnf file
The my.cnf file is used to store your users mysql settings like username, password, and prompt. If you utilize the my.cnf file to store username and password
- you won’t need to include those on your mysql commands.
- the command mysqldump -usome_user -p some_database | gzip -c > some_database.sql.gz turns into mysqldump some_database | gzip -c > some_database.sql.gz
- After you type mysql the database names auto complete with the tab key
run this command to create the file vim ~/.my.cnf and add this in the file
- Please note: if you use this my.cnf stuff you can ignore the -u and -p switches below.
[client]
user=USER
password=USER_PASSWORD
host=localhost
[mysql]
prompt=u@127.0.0.1 [d]>
In MySQL to Create a new user
Please make sure to log in with a user in mysql that has permissions to create a user.
For example: mysql –uroot -p
To create a new user in the mysql command prompt you can use a command like this
GRANT ALL PRIVILEGES ON (DATABASE THIS USER CAN ACCESS|*).* TO ‘USER‘@’localhost’ IDENTIFIED BY ‘USER_PASSWORD‘;
(DATABASE THIS USER CAN ACCESS|*) I mean here you can use a name for example test or you can use * if you would like to give this user full access to all databases
In MySQL to Create a new database
You can do this many ways. I like to run a terminal command
mysql -uUSER -p -e”CREATE DATABASE DATABASE_NAME“
This command could look like this
mysql -uTEST -p -e”CREATE DATABASE testing”
-
- -u|–user
- -e|–execute
Exporting/ backing up a database
Here is a sample command that will compression a SQL dump. This is probably a good idea to save some disk space and bandwidth if you plan to transfer the SQL file. You can usually save about 80%, so a 3 gig SQL file will compress to about 500mb usually
mysqldump -uUSER -p DATABASE_NAME | gzip -c > DATABASE_NAME.sql.gz
That command might look like this:
mysqldump -utest -p testing | gzip -c > test.sql.gz
In mysql and mysqldump the -p can take in a parameter of the password. But, with the .bash_history file that could be insecure because that password would be saved
QuickTip: If you would like to see progress use the ‘pv’ command.
- * You may need to install the pv command ‘sudo apt install pv -y’
mysqldump -utest -p testing | pv | gzip -c > test.sql.gz
Importing/restoring a database
- Create the database
mysql -uUSER -p -e”CREATE DATABASE DATABASE_NAME”
- -e|–execute
- -p|–password
- -u|–user
- import/restore from a sql file
If the file is compressed with gzip use zcat. The zcat command will read the compressed file and output it in plain text
zcat FILE.sql.gz | mysql -uUSER -p DATABASE_NAME
- zcat cats out the gzipped file
- -u|–user
- -p|–password (leave this empty mysql will prompt you for the password)
- And the final option without the parameter is the database name to import the file to
QuickTip: If you would like to see progress use the ‘pv’ command.
- * You may need to install the pv command ‘sudo apt install pv -y’
zcat FILE.sql.gz | pv | mysql -uUSER -p DATABASE_NAME
pv – https://linux.die.net/man/1/pv
Sample workflow
terminal> mysql -uroot -p
mysql> CREATE DATABASE testing;
mysql> GRANT ALL PRIVILEGES ON testing.* TO ‘testing’@’localhost’ IDENTIFIED BY ‘testing-password’;
mysql> exit;
You can try to login with the new credentials.
mysql -utesting -p testing;
The in mysql if you run the SHOW DATABASES command you should only see the one database listed since that’s the only one we gave that user access to.
Some more sources
