Previous Thread
Next Thread
Print Thread
Rate Thread
Joined: Dec 2002
Posts: 3,255
Likes: 3
UGN Elite
OP Offline
UGN Elite
Joined: Dec 2002
Posts: 3,255
Likes: 3
With all the neat tools like PHPmyAdmin out there few bother to learn the command line any more. I decided I wanted to learn and decided to share some with you. In this lesson "$" will represent the command prompt. I put this in the Unix section because Mysql is widely used in web design, but it's uses reach far beyond that.. All info came from http://www.mysql.com which I found a bit cryptic in some sections.

Now first thing you have to do is connect to the data base. This is done like so.

http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#Connecting-disconnecting

$ mysql -u User_name_here -p *nothing after the p you will be prompted*

Enter passward
Password_here


It will look like so on your screen

$ mysql -u User_name_here -p
Enter passward *******

Login sucessful...
mysql>


Now you are ready to start queries and storing data. Lets look at how this works shall we? You will notice your prompt now is "mysql>" This lets you know you are in. By the way type "exit" or "Quit" or "Ctrl_D" maybey "Ctrl_z" should put you back at a regular command prompt.

Now the first thing you will need is of course a database. To see all data bases do a "Show Databases"

mysql> SHOW DATABASES;

Notice the ";" This is needed to end the command if you do not type in ";" you might get something like this

mysql> SHOW DATABASES
->

If you so simpley type in the ";".


mysql> SHOW DATABASES
->;

You will get some out put that looks something like this.

Code
 
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+
 
If you do not have a data base that you have created you will need to do so. Here is how you create a database.

mysql> CREATE DATABASE Database1;

Pretty easy right? You will also need to create a table in your database. For this I will give you a simple table but you will have to read up on different types of fields and table structure. I could write a book on this alone. See the following links

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#CREATE_TABLE

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#ISAM

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#HEAP

http://www.mysql.com/documentation/mysql/bychapter/manual_Table_types.html#InnoDB

http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#Creating_tables

http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimisation.html#Table_cache

http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#Legal_names


http://www.mysql.com/documentation/...atabase_Administration.html#BACKUP_TABLE

There are so many other links I could list but these should lead you where you need to look. Now for a basic table. Lets say we want to create an email address book. We need the

First name
Last name
Date data was inserted (so we know if it could be outdated)
email address
A point of refference as to call the data up with.

mysql> CREATE TABLE Email_table (Fname VARCHAR(25), Lname VARCHAR(25), Date VARCHAR(10), email VARCHAR(255), id int(4) NOT NULL auto_increment) PRIMARY KEY (id);

Look kind of complicated? It realy isn't. You see the VARCHAR(25) in there? VARCHAR is just what type of field this will be. In this case this field will hold text. The (25) part is just how many charaters will be allowed to be stored in this field. So "Fname" will be a field in the table that will store the First name. Not too many people have names with more than 25 charaters so we seet 25 as the limit. The other type of field you see is "int". Or integer. This is a numeric field.

id is a numeric field that will auto increment. This means only number will go in here. You do not ever have to put anything in this field it will automaticaly cout each time a new row is added. That is, the first time you insert any data id field will be set to one. The next time or row it will be set to 2. This is a constant that you can use in scripts to grab and manipulate data.

If you do a "DESCRIBE" command you can see the basic structure of your data.

mysql> DESCRIBE Email_table;

*rember the ";"

This will give you something like

Code
 
mysql> DESCRIBE Email_table;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| Fname   | varchar(25) |      |     | NULL    |       |
| Lname   | varchar(25) |      |     | NULL    |       |
| Date    | varchar(10) |      |     | NULL    |       |
| Email   | varchar(255)|      |     | NULL    |       |
| id      | int(11)     |      | Pri | NULL    |       |
+---------+-------------+------+-----+---------+-------+
Next we want to insert some data right. To do this we will use the insert command.

http://www.mysql.com/documentation/mysql/bychapter/manual_Tutorial.html#Loading_tables

mysql> INSERT INTO Email_table Values 'admin', 'Gizmo', '01/13/2003', 'admin@UnderGroundNews.com');

or

mysql> INSERT INTO Email_table
-> Values ('admin', 'Gizmo', '01/13/2003', 'admin@UnderGroundNews.com');


Hit enter and watch the data go. Well not realy, but it is in there now. Want to see?

mysql> Select * FROM Email_table;
YOu should see all the data nicely grided out for you. Well there are the basics of Mysql from the command line.

Joined: Dec 2002
Posts: 3,255
Likes: 3
UGN Elite
OP Offline
UGN Elite
Joined: Dec 2002
Posts: 3,255
Likes: 3
Oh I almost forgot the part about using dump files. If local put the dump file in a directory you can rember, if remote FTP file up to a directory you want to use. For this we use the "SOURCE" command.

First login to Mysql, and get into the database (db) you want to use. To do this use the "USE" command.

mysql> USE Database1
Database Changed

now we want to use our *.sql file to dump a large amout of data into the database.

mysql> source "/path/to/directory/dump_file.sql";

Then just let it run. That simple.

Joined: Dec 2002
Posts: 3,255
Likes: 3
UGN Elite
OP Offline
UGN Elite
Joined: Dec 2002
Posts: 3,255
Likes: 3
All of this should work in telnet also.

Joined: Mar 2002
Posts: 197
P
Member
Offline
Member
P
Joined: Mar 2002
Posts: 197
You also can use the mysqldump program located in the bin dir of mysql.

./mysqldump -u root -pPass --all-databases >backup.sql

This will dump your whole mysql database.
To dump a specific database:
./mysqldump -u root -pPass --opt database > backup.sql


To put the whole database back:
./mysql -u root -pPass < backup.sql

To put one back:
./mysql -u root -pPass database < backup.sql


Never argue with fools... They will only drag you down to their level, and beat you with experience...
Joined: Feb 2002
Posts: 7,203
Likes: 11
Community Owner
Offline
Community Owner
Joined: Feb 2002
Posts: 7,203
Likes: 11
Learner, if you have the time i have a favor/project for you wink ...


Donate to UGN Security here.
UGN Security, Back of the Web, and VNC Web Services Owner

Link Copied to Clipboard
Member Spotlight
Posts: 43
Joined: November 2002
Forum Statistics
Forums41
Topics33,840
Posts68,858
Average Daily Posts1
Members2,176
Most Online3,253
Jan 13th, 2020
Latest Postings
Where and how do you torrent?
by danni75 - 03/01/24 05:58 AM
Animation,
by JohanKaariainen - 08/15/19 01:18 AM
Blackbeard.....
by Gremelin - 10/03/18 07:02 PM
my old account still exists!
by Crime - 08/10/18 02:47 PM
Okay WTF?
by HenryMiring - 09/27/17 01:45 AM
The History Thread...
by Gremelin - 08/11/17 12:11 PM
My friend NEEDS your HELP!
by Lena01 - 07/21/17 12:06 AM
I'm having fun with this guy.
by gabithompson730 - 07/20/17 01:50 AM
I want to upgrade my phone
by gabithompson730 - 07/20/17 01:49 AM
Doom 3
by Cyrez - 09/11/14 08:58 PM
Amazon Gift Card Generator/KeyGen?te
by Gecko666 - 08/22/14 09:21 AM
AIM scene 99-03
by lavos - 09/02/13 08:06 AM
Planetside 2
by Crime - 03/04/13 07:10 AM
Beta Testers Wanted
by Crime - 03/04/13 06:55 AM
Hello Everyone
by Gremelin - 02/12/12 06:01 PM
Tracfone ESN Generator
by Zanvin Green - 01/18/12 01:31 PM
Python 3 issue
by Testing - 12/17/11 09:28 PM
tracfone airtime
by Drache86 - 07/30/11 03:37 AM
Backdoors and the Infinite
by ZeroCoolStar - 07/10/11 03:52 AM
HackThisZIne #12 Releaseed!
by Pipat2 - 04/28/11 09:20 PM
gang wars? l33t-wars?
by Gremelin - 04/28/11 05:56 AM
Consolidate Forums
by diggin2deep - 04/21/11 10:02 AM
LAN Hacking Noob
by Gremelin - 03/12/11 12:42 AM
Top Posters
UGN Security 41,392
Gremelin 7,203
§intå× 3,255
SilentRage 1,273
Ice 1,146
pergesu 1,136
Infinite 1,041
jonconley 955
Girlie 908
unreal 860
Top Likes Received
Ghost 2
unreal 1
Crime 1
Ice 1
Dartur 1
Powered by UBB.threads™ PHP Forum Software 7.7.5