Posted on: 20/09/2018 in Programming. Last Updated on: 10/04/2019
A collection of commands that will help you manage your mysql database.
$ mysql -u username -p database_name -A
Once connected to MySQL you can issue some of the following helpful commands
$ show databases $ show tables; $ describe tablename;
$ mysqldump -u username -p database_name | gzip > name-of-file.sql.gz
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.
$ gunzip < ~/path/to/name-of-file.sql.gz | mysql -u username -p database_name
This command runs the gunzip (g-unzip) command and passes the output to mysql and the specified database.
Using scp you can transfer files between servers and computers.
$ scp path/to/file.txt user@ipaddress:/path/on/server/
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 user@ipaddress:/path/to/file .
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.