mysql / Mariadb commands

This guide will go through some mysql commands, these will also be relevant to Mariadb

Quick Links:
Deleting a user
Changing the host of a user
Changing a users password
Granting user privileges
mysql dump
mysql secure copy (scp)
Securing mysql
Remotely accessing your db
Finding out the size of a db or table


The Basics:
Enter mysql: mysql -u root -p

Show databases: show databases;

Select a database: use databasename;

Showing tables in the database: show tables;

Show users and where it can access the db from: select user, host from mysql.user;

Create a new mysql user: CREATE USER ‘luke’@’x.x.x.x’ IDENTIFIED BY ‘testpassword’;

If you have created a user without setting a password then you can set the password with: SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘password’);


Deleting a user: drop user ‘luke’@’x.x.x.x’; 


Changing the host of a user (most commonly used to change from localhost to an IP address so the db can be accessed remotely).

UPDATE mysql.user SET host = ‘new ip/localhost’ WHERE user = ‘luke’

If you wish the user to be accessible from any location then you are able to add a wildcard ip address of ‘%‘. Please note that this is not secure and not recommended.

Changing password for a user:

UPDATE mysql.user SET Password=PASSWORD(‘newpassword’) WHERE User=’user’ AND Host=’x.x.x.x’;

Granting User Privileges;

Granting all privileges to a user on a specific database: GRANT ALL PRIVILEGES ON databasename.* TO ‘user’@’x.x.x.x’;

Showing the privileges for a specific user: SHOW GRANTS FOR ‘user’@’x.x.x.x’;

After updating privileges you will need to flush them with:

flush privileges;

Note: x.x.x.x will be replaced with ip address. This could also be ‘localhost’ rather than an IP


Privileges available to grant to users:

Database manipulation only

INSERT........insert rows/data

UPDATE........change inserted rows/data

DELETE........delete drop rows of data
Table manipulation:



Mysql dump (exporting and importing databases)

To take a mysql dump you should not be logged into mysql, you will just need to be in the terminal. Use the following command to export the db to the directory you are currently in:

mysqldump -u root -p database > filename.sql

Once you have taken the dump you can then import the db using the following commands.

If you haven’t already prepared a blank database then you will need this command first:

mysqladmin -u root -p create databasename

Once you have created the db you can import with:

mysql -u root -p mytestdb2 < mytestdb.sql


Secure transfer of database to another server (scp)

Once you have taken a mysql dump you may want to transfer this to another server. You can do this using scp (secure copy).

scp [database name].sql [username]@[servername]:path/to/database/


scp dbdump.sql [email protected]:~/

The example above securely copies the db to the server x.x.x.x and places the file in the root directory. (note: you will  need to enter the password for the destination user/server)

scp custom port (example 666):

scp -P 666 filename.sql [email protected]:/


Securing mysql

The best practice is to lock down and secure mysql as much as possible. You can use the following command to assist you with this:

sudo mysql_secure_installation


Remotely Accessing your database. Please make sure that the user has been created with the ability to be accessed remotely (e.g NOT from local host)

Iptables rule will also need to be added to the database server with the following command:

iptables -I INPUT 1 -p tcp –dport 3306 -j ACCEPT

To access a mysql server remotely you should use:

mysql -u user -p -h x.x.x.x


Finding out the size of a specific database:

SELECT table_schema “Data Base Name”,

    sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”,

    sum( data_free )/ 1024 / 1024 “Free Space in MB”

FROM information_schema.TABLES

WHERE table_schema = “$DATABASE_NAME”


Finding out the size of all databases:

(Note: you do not need to change the ‘Database Name’)

SELECT table_schema “Database Name”,

    sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”,

    sum( data_free )/ 1024 / 1024 “Free Space in MB”

FROM information_schema.TABLES

GROUP BY table_schema ; 


Finding out the size of a table:

SELECT table_name AS “Table”,

     round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB”

FROM information_schema.TABLES

WHERE table_schema = “$DATABASE_NAME”

AND table_name = “$TABLE_NAME”;