HOW TO: Use shell to control MySQL.

[abdulmueid]

Some people have a shell account with hosting but dont have a clue on how to put it to some good use. Shell Account or SSH can save alot of time if used properly.

I will show how to do some of the most common tasks easily and faster in shell.

First of all you will need a ssh client to connect to the remote server. I use PuTTY to access my shell accounts but you can use anyone that you like, there are plenty available.

Use your domain-name as your hostname and login. Enter username and password when required.
Once you are logged in you will see: "username@localhost$" or something similar.
at this point type:

cd public_html

You can replace public_html with your web-folder if it is not public_html.

You can move around different folders using CD command just like Ms-Dos prompt. Remember instead of "cd.." for moving to the previous folder, you will have to type "cd .." space between cd and ..

Starting MySQL:
type:

mysql -u username -p

Replace "username" with your database username. This works wherever database hostname is "localhost".
If your database server is located somewhere else then type:

mysql -h hostname -u username -p

Replace "username" with your database username.Replace "hostname" with your database server's hostname. i.e. db.somedomain.com.

After typing the command, hit enter. It will now ask for the the password associated with that username. Enter password and hit enter.

Now you will see "mysql>" instead of "username@localhost:>". This means we have successfully logged in to Mysql.

Selecting Database to use:
At mysql> type:

use databasename

Replace "databasename" with the name of the database that you want to work on. Hit Enter

Adding a new Database:
At mysql> type:

create database databasename;

Replace "databasename" with the name of the database that you want to create. Hit Enter

Deleting a Database:
At mysql> type:

drop database databasename;

Replace "databasename" with the name of the database that you want to delete. Hit Enter

Creating Tables:
At mysql> type:

create table tablename;

Replace "tablename" with the name of the table that you want to create. Hit Enter

Deleting Tables:
At mysql> type:

drop table tablename;

Replace "tablename" with the name of the table that you want to delete. Hit Enter

Exiting MySQL:
Simply type:

exit

and hit enter.

Now you are back to shell prompt.

Backing up your database:
At normal shell (Not mysql>) type:

mysqldump --add-drop-table -h databasehostname -u databaseusername -p databasename | bzip2 -c > blog.bak.sql.bz2

Replace databasehostname with your database host (if it is localhost then you can remove "-h databasehostname"), replace databaseusername with your database username and replace databasename with the name of database that you want to backup.
Hit enter, it will ask you for your database password. Enter the password and hit enter.
All done. This will output a file "blog.bak.sql.bz2" in the folder you were working in (public_html).
NOTE: I am using blog as an example for the output file. You can name it as you feel like, just dont change .sql.bz2 extention. i.e. forum.bak.sql.bz2 or forum.sql.bz2...

Restoring the database:
The database that we created in previous step is compressed in order to give us a small file. To restore the database we first need to uncompress the database.
Supposing that you are in the same folder as the compressed file.Type:

bzip2 -d blog.bak.sql.bz2

You can change "blog.bak.sql.bz2" to the filename that you used when creating the backup.

Now type:

mysql -h databasehostname -u databaseusername -p databasename < blog.bak.sql

Replace databasehostname with your database name (you can remove "-h databasehostname" if your database server is localhost), Replace databaseusername with your database username and Replace databasename with the name of the database to which you want to restore the backup file.

Hit Enter, it will ask for your database password, type it and hit enter. VOILA! Your database is restored.

Using shell may look tedious and hard at first but once you get hold of it, you will feel "more at home" with it than any other control panel.

Hope this help those people with shell account but did not know how to use it.

MODS: If you like the topic and think it is useful then please sticky it. Thank You

 

[Masahiro]

Nice tutorial, thanks for that!

 

[abdulmueid]

You are welcome

 

[nomis135]

oh thanks a lot i needed to find tutorials about shell access as i don't completly know how to use it.

I know basic commands such as top or netstat. :lol:

 

[abdulmueid]

I will be posting more on Shells since I used to be a "junior" admin myself some time ago, so i know quite alot about it and have working experience.


P.S. My current host likes being old skool and has no cPanel. All site management has to be done via Shell :P

 

[unknowngiver]

Nice tutorial :D really helpful for a newbie like myself :D

+1 rep added

 

[nomis135]

I will be posting more on Shells since I used to be a "junior" admin myself some time ago, so i know quite alot about it and have working experience.


P.S. My current host likes being old skool and has no cPanel. All site management has to be done via Shell :P

That must be a pain but at least you learn shell and i doubt you'll forget

 

[abdulmueid]

It was pain in the beginning, I mean I got the hosting in 2003 and still have it. Been using it all that time. It was/is my first hosting ever, when I got hosted there i had never even heard about cPanel. Only way to survive was to learn and I did it

 

[nomis135]

you should make a site with these tutorials as lots of people don't know how to use shell.
There aren't tons of tutorials for that either.

If you know of any place with all the basic commands shown that would be cool as i don't know much of them. Only a few.

 

[abdulmueid]

i guess i'll make a list and post soon

 

[Pakinium]

Great Tutorial ,

Repo Added !

 

[coolpix]

Nice tutorial and thanks for sharing!

 

[domainprince]

Great, thanks for sharing! :wave:

you should make a site with these tutorials as lots of people don't know how to use shell.
There aren't tons of tutorials for that either.

I second that.

 

[abdulmueid]

I have created a section dedicated to shells on my website at www.crazylittleworld.com

Click here to access the archive for shell.

 

[thestudent]

Nice Tutorail Abdul.Mueid

 

[Red Eyes Arena]

that's cool never knew i can use shell with mysql ^^

 

[bbalegere]

Amazing tutorial.