I use phpmyadmin in daily environment, do I still know  sql command?

My sql file too large, it’s hard to import using phpmyadmin, please help me

Some question around mysql from my student, my false make them spoil using phpmyadmin all day long 🙂

Here’s my answers :

1. Command to create database

create database databasename

2. Delete database

drop database databasename

3. Import database

mysql -uusername -ppassword databasename < databasename.sql

4. Export database

mysqldump -uusername -ppassword databasename > databasename.sql

5. Pick database and choose one to manage

use databasename

6. List all table in database

show tables

 Illustration in mysql command line :

C:\Documents and Settings\Administrator>cd c:\appserv\mysql\bin

C:\AppServ\mysql\bin>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.24-nt

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show databases;
+————+
| Database   |
+————+
| alam       |
| cms_smtik  |
| cms_stmik  |
| cms_study  |
| coba       |
| kopertis   |
| mysql      |
| pendidikan |
| roxy       |
| test       |
+————+
10 rows in set (0.02 sec)

mysql> create database wordpress;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+————+
| Database   |
+————+
| alam       |
| cms_smtik  |
| cms_stmik  |
| cms_study  |
| coba       |
| kopertis   |
| mysql      |
| pendidikan |
| roxy       |
| test       |
| wordpress  |
+————+
11 rows in set (0.00 sec)

mysql> exit
Bye

C:\AppServ\mysql\bin>mysqldump -uroot -p alam > alam.sql
Enter password:

C:\AppServ\mysql\bin>dir /p/w
 Volume in drive C has no label.
 Volume Serial Number is C83B-A343

 Directory of C:\AppServ\mysql\bin

[.]                     [..]                    alam.sql
blog.sql                bl_roxy5april2007.sql   cms_stmik.sql
libmySQL.dll            mysql.exe               mysqladmin.exe
mysqlbinlog.exe         mysqlc.exe              mysqlcheck.exe
mysqld-nt.exe           mysqld.exe              mysqldump.exe
mysqlimport.exe         mysqlshow.exe           mysqlshutdown.exe
mysqlwatch.exe          pdkoper.sql             perror.exe
              19 File(s)    241.538.584 bytes
               2 Dir(s)   1.946.165.248 bytes free

C:\AppServ\mysql\bin>mysql -uroot -p wordpress < alam.sql
Enter password:

C:\AppServ\mysql\bin>mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.0.24-nt

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use alam;
Database changed
mysql> show tables;
+—————————–+
| Tables_in_alam              |
+—————————–+
| serendipity_authors         |
| serendipity_category        |
| serendipity_comments        |
| serendipity_config          |
| serendipity_entries         |
| serendipity_entrycat        |
| serendipity_entryproperties |
| serendipity_exits           |
| serendipity_guestbook       |
| serendipity_images          |
| serendipity_link_category   |
| serendipity_links           |
| serendipity_photoblog       |
| serendipity_plugins         |
| serendipity_profiles        |
| serendipity_references      |
| serendipity_referrers       |
| serendipity_spamblocklog    |
+—————————–+
18 rows in set (0.00 sec)

mysql>

Common pitfall around this command :

1. Forgot to use > or <, use > to export or backup database
2. If see ‘hung’ operation then push CTRL + C to cancel operation
3. Space after -u and -p, make sure no space, i.e : mysql -uusername  -ppassword databasename < databasename.sql

when database  hit hundred MB in size then command line is important to learn 🙂