Basic Mysql command for beginners

[Euk-George]

To enter the mysql from root

# mysql

To change the database

#use database name;

Ex. use test_test;

To see the tables under the database

#show tables ;

To see the particular table details

#select * from table ;

Ex. select * from test_admin ;

To restore the database backup from .sql file

#mysql -u username -ppassword databasename < .sql file

Ex. mysql -u test_test1 -ptesting test_test < test.sql

To see the current running mysql process

#mysqladmin processlist or #mysqladmin proc stat

To take the backup of mysql database

#mysqldump --opt db_name > backup-file.sql

Ex. mysqldump --opt test_test > test.sql

You can restore the dump file back into the database like this:

#mysql db_name < backup-file.sql

Ex. mysql test_test < test.sql

To dump all databases, use the --all-databases option:

#mysqldump --all-databases > all_databases.sql

[jcmental]

err... where r you ment to type those comands please?

[eUKShane]

Hello,

First command will give you mysql prompt and then you will be able to run all remaining commands.

On linux server, Login to Shell through SSH and then give following command on a hash (#) prompt :

#mysql
OR
#mysql -u<user> -p<password>

If you have mysql-5.0.81 then above commands will show you following output :

root@vps [~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 30202
Server version: 5.0.81-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

On a Windows server, open a command prompt and go to the path where mysql is installed. For example on Windows-Plesk Server you will find mysql.exe in C:\Parallels\Plesk\Databases\MySQL\bin

Run following commands on windows command prompt :

>cd \
>cd C:\Parallels\Plesk\Databases\MySQL\bin
>mysql -u<user> -p<password>

Please feel free to reply if you have any further queries.

[jcmental]

WoW you guys know so much. how do you remember all that?
Seems a bit deep for me but thanks for the info.

[eUKShane]

Hello,

Yeah, it's our daily work so we remember it. Thanks for your appreciation.