Experienced level MySQL commands

[av_heading tag=’h3′ padding=’10’ heading=’I want to review a few basic mysql concepts’ color=” style=” custom_font=” size=” subheading_active=” subheading_size=’15’ custom_class=” admin_preview_bg=” av-desktop-hide=” av-medium-hide=” av-small-hide=” av-mini-hide=” av-medium-font-size-title=” av-small-font-size-title=” av-mini-font-size-title=” av-medium-font-size=” av-small-font-size=” av-mini-font-size=”][/av_heading]

[av_section min_height=” min_height_px=’500px’ padding=’default’ shadow=’no-shadow’ bottom_border=’no-border-styling’ bottom_border_diagonal_color=’#333333′ bottom_border_diagonal_direction=’scroll’ bottom_border_style=’scroll’ scroll_down=” custom_arrow_bg=” id=” color=’main_color’ custom_bg=” src=” attach=’scroll’ position=’top left’ repeat=’no-repeat’ video=” video_ratio=’16:9′ video_mobile_disabled=” overlay_enable=” overlay_opacity=’0.5′ overlay_color=” overlay_pattern=” overlay_custom_pattern=” av-desktop-hide=” av-medium-hide=” av-small-hide=” av-mini-hide=” av_element_hidden_in_editor=’0′]

[av_textblock size=” font_color=” color=” av-medium-font-size=” av-small-font-size=” av-mini-font-size=” admin_preview_bg=”]
For MySQL I have a small list of commands and notes on how to use them. I reference them a lot and I was thinking that other people might be able to benefit from them. Sorry they are a little technical, please feel free to contact me if you would like any help.
[/av_textblock]

[/av_section][av_textblock size=” font_color=” color=” av-medium-font-size=” av-small-font-size=” av-mini-font-size=” admin_preview_bg=”]

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.

~/.my.cnf example

[client]
user=USER
password=USER_PASSWORD
host=localhost
[mysql]
prompt=u@127.0.0.1 [d]>
[/av_textblock]

[av_textblock size=” font_color=” color=” av-medium-font-size=” av-small-font-size=” av-mini-font-size=” admin_preview_bg=”]

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

[/av_textblock]

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top