Using MySQL database via command line

If you’re a Unix user, and also a developer, then you’re surely love to do stuff with command line interface. It’s fun to work in a command line interface environment.

Don’t be afraid. It’s easy to use MySQL database for basic operation via command line interface.
To start mysql console:

mysql -u username -p

Then write your password when prompt. It will open the mysql console. After that, in mysql console, you can try these to use basic mysql operations:

1.   To show all databases: show databases;
2. To select a database from the database list, write: use <database-name>;
3. To show tables from the selected database, write: show tables;
4. To create a new table in the selected database, write:

   CREATE TABLE <table-name> (<field-name> <field-type> (length) );
   

   ex.

   CREATE TABLE customers ( id INT (11), name VARCHAR(100), age INT(11), birthday DATE );

5. To view the table structure, write:

   DESCRIBE <table-name>;
   

   ex.

   DESCRIBE customers;

6. To close the mysql console, write: exit; or \q;

These are the basic operations. By the way, you can use all others database queries with the command line.

Now, if you want to backup one of your database with command line, you can mysqldump. Write:

mysqldump -u username -p db_name > new_file_path_with_name

ex.

mysqldump -u username -p testdb > ~/Desktop/testdb.sql

And, if you want to import a file to the database, try:

mysql -u username -p db_name < backup_file.sql

That’s it! Happy coding!