Published Nov 14, 2018 / 3 min read

Basic CLI commands for your MySQL interaction

Sure, you can use MySQL with a GUI like phpMyAdmin. However, if you want to set up a database within seconds or import an .sql file quickly a GUI isn't the best option. This post introduces some bash commands that enhances your interaction with MySQL.

Start and stop

You need to provide a username, password, andhostname. Due to security issues you should not type your password into the bash command but wait for a prompt after execution. Stop the application simply with exit.

-- login
mysql -u [username] -h [host] -p

-- logout
exit

Create user and define access rights

The command structure is as follows:

--create user with access rights
grant all on [databasename].[tablename] 
to '[username]'@'[hostname] 
identified by '[password]';

As an example, consider you want to create a user balc-dev with password secret and access to database laravel_balc and all associated tables from localhost.

grand all on laravel_balc.* to 'balc-dev'@'localhost' identified by 'secret';

3. Interact with a database

Once you are logged in you may start to interact with databases.

-- display all databases
show databases;

-- create a new database
create new database [db_name];

-- delete a database 
drop database [db_name];

-- select a database and show all tables
use [db_name];
show tables;

Display data from tables

Before you may work with particular tables you need to always select the database you want to work with by use [db_name] prior the following commands.

-- show table structure 
describe [table_name];

-- show table data
select [column_name] from [table_name]

-- show column name from table users
select name from users;

-- show all columns from table users
select * from users;

Manipulate tables

Add, delete or modify tables and columns

-- add a table
create table [table_name] ([column_name] [data_type] [modifier], ...);

-- insert data into a table
insert into [table_name] ([column_name_1], [column_name_2] , [column_name_3]
values ('a random string', 123, true);

-- destroy a table
drop [table_name];

-- delete all data from a table
truncate [table_name];

-- delete a column within a table
alter [table_name] 
drop column [column_name];

-- add a column within a table
alter [table_name]
add column [column_name] [data_type];

Import a database

If the database you want to dump your data into does not exit yet, go ahead and create the database and thereafter exit MySQL.

-- create database
create database [db_name];
exit;

The command structure is as follows:

-- structure
mysql -u [user] -p [db_name] < /path/to/dump-file.sql;

As an example, consider to import a file called laravel_balc.sql which is located at /home/user/Desktop and should be dumped into a database called laravel_balc.

-- import laravel_balc.sql into laravel_balc
mysql -u root -p laravel_balc < /home/user/Desktop/laravel_balc.sql;

Further Reading