Linux & Bash Command Line Cheat Sheet

Posted on: 20/09/2018 in Programming. Last Updated on: 10/04/2019


MySQL Commands

A collection of commands that will help you manage your mysql database.

Connect to a MySQL server:

$ mysql -u username -p database_name -A

Explanation

  • The -u flag tells mysql which user to connect as
  • The -p flag tells mysql to issue a password prompt. This is very useful for not exposing your password
  • The "database_name" is optional, but tells mysql which database to use
  • The -A flag turns off "Reading table information for completion of table and column names"
    • Turning this off makes connecting to the database quicker
    • Also optional

Once connected to MySQL you can issue some of the following helpful commands

$ show databases
$ show tables;
$ describe tablename;

Export a database and gzip file using mysqldump

$ mysqldump -u username -p database_name | gzip > name-of-file.sql.gz

Explanation

This command is fairly similar to connecting to a database, with the addition of the pipe (|) and output (>) operators.

The pipe (|) operator means take the output of the previous command and pipe (send) it to the next function / command. In this instance were taking the SQL dump generated by MySQL and piping it to the gzip command which will compress the file to us.

The output (>) operator means take the gzipped .sql file and save it to this filename / location. The above operation will save the file in the directory you're currently in. You can specify what directory you want by just appending it.

E.g. ~/Downloads/name-of-file.sql.gz


Import a gzipped database dump

$ gunzip < ~/path/to/name-of-file.sql.gz | mysql -u username -p database_name

Explanation

This command runs the gunzip (g-unzip) command and passes the output to mysql and the specified database.

File Transfer / SCP Commands

Using scp you can transfer files between servers and computers.

SCP file from local (dev machine) to remote server

$ scp path/to/file.txt user@ipaddress:/path/on/server/

Explanation

This command takes a file on your computer and copies it up to the remove server in the location of your choosing. It's important to not forget the colon : between the server IP and the path you want the file to be copied to. If you are on the remote server and want to know what the current working directory is, simply type in "pwd" and then copy and paste this location after the colon.

Once you've issued the scp command the file will be transferred securely to the server. You'll need to make sure your public key is in the ~/.ssh/authorized_keys first before attempting this!

You can also change the name of the file on the remote server by simply typing what you want. E.g. user@ipaddress:/path/on/server/new-file-name.txt

SCP file from remote server to local (dev machine)

$ scp user@ipaddress:/path/to/file .

Explanation

This is almost identical to copying a file from local to remote, however one thing that can confuse people is that you issue this command from your dev machine instead of on the remote server!

The above command is saying ssh into the remote server and copy the file over to your local machine in the current directory. Again, you can change where the file gets saved to and what it's name will be.