MySQL - Basic terminal commands

I'm using MySQL quite often in my projects, especially from a *nix terminal.

Let's look at some commands that comes handy when using MySQL.

 

What is MySql

MySQL is one of the most widely used relational DBMS (Database Management System) worldwide. It helps you to store, organize, retrieve and manipulate data.

It is available as open source software as well as a commercial enterprise version for various operating systems.

MySQL was developed by the Swedish company MySQL AB in 1994. In February 2008, MySQL AB was acquired by Sun Microsystems , which in turn was purchased in January 2010 by Oracle.

 

Installation:

sudo apt-get install mysql-client mysql-server 

 

This screen will pop up during the installation progress. I'd recommend you to set a password.

 

Login:

Now let's login with the user "root". Note that I didn't type in a password yet.

You have to enter it securely on the next step.

mysql -u root -p

 

 

Create a new database:

A database contains tables, views, trigger, ..

You can have multiple databases.

CREATE DATABASE [dname];

 

Use a database:

To "login" into a database we have to tell the DBMS which one we want to use.

USE [dname];

 

List databases:

If you can't remember the databsse names you can list them with this command.

SHOW DATABASES;

 

List tables:

If you can't remember the names of your tables, you can list them with this command.

Tab is working here, so just hitting 2x times tab after a select command will display them, too.

SHOW TABLES;

 

List content of a table:

This simply display the content of a table. The resulting tuples with contain respectively all columns.

SELECT * FROM [tname];

 

Create a new user:

I'd recommened you to use a different user and usually just grant this user rights to a specific database.

With this command we grant the new user rights for all databases, not recommend tho.

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

 

Create a backup:

This will copy all tables (with create commands) from a specific database into a file. Note that it doesn't copy the database create commands. 

mysqldump --u [uname] -p[upass] [dbname] > [backupfile.sql]

 

Restore database from backup:

This exectures all the commands from a file to a specific database.

mysqldump --u [uname] -p[upass] [dbname] < [backupfile.sql]

 



Mahmut Jomaa is a Software Engineer from Germany.

Currently he attends university to gain more knowledge in Computer Science.


Security code Refresh