Earlier we have learned to install & secure MariaDB server on Centos/RHEL 7, which is now the default database of RHEL/CentOS 7 onwards. We will now discuss some useful MariaDB commands. These are some very basic commands that will get you started with using MariaDB & these can also be used with MySQL since MariaDB is a forked out version of MySQL only.

(Recommended Read: MongoDB installation & configuration on RHEL/CentOS)

MariaDB Commands

  • Checking version of your MariaDB installation

To check the current version of your DB installation, type the following command in your terminal

$ mysql --version

This command provides you with the current version of DB. Alternatively, you can also run the below-mentioned command for a detailed view of the version,

$ mysqladmin –u root –p version

  • Logging into MariaDB

To log into the mariadb server,  run

$ mysql –u root –p

& then enter a password to login into the session.

  • Showing all database

To show all the databases that your MariaDB currently has, run

$ show databases;

after you are logged into mariadb.

  • Creating new databases

To create a new database in mariadb, run

$ create database dan;

when logged into MariaDB. To create a database directly from the terminal, run

$ mysqladmin -u user -p create dan

Here, dan is the name of the new database.

  • Deleting a database

To delete a database, run

$ drop database dan;

from the logged-in session of mariadb. Alternatively, you can also use,

$ mysqladmin –u root –p drop dan

Note:- If you are getting an ‘access denied’ error while running the mysqladmin commands, that might be because we have not given rights to root. To do so, run the command mentioned in point 7, replacing the name of the user with root.

  • Creating new user

To create a new user for the database, run

$ CREATE USER 'dan'@'localhost' IDENTIFIED BY 'password';

  • Granting access to the user for a database

For providing access to the user for a single database, run

$ GRANT ALL PRIVILEGES ON test.* to 'dan'@'localhost';

This will provide user dan's complete access over database named test. We can also grant SELECT, INSERT, DELETE permissions to users.

To provide access to all database, replace test with * i.e.

$ GRANT ALL PRIVILEGES ON *.* to 'dan'@'localhost';

  • Creating backup/dump of the database

To create a single database, run the following command from your terminal window,

$ mysqldump –u root –p database_name>db_backup.sql

To create a backup of multiple databases in a single command,

$ mysqldump –u root –p - - databases db1 db2 > db12_backup.sql

To create a dump of all databases in a single command,

$ mysqldump –u root –p - - all-databases >all_dbs.sql

  • Restoring database from the dump

To restore the database from a dump, run

$ mysql –u root –p database_name<db_backup.sql

But this command will work only when there is no previous database by the same name. if you want to restore database data to any already created database, we need to use the ‘mysqlimport ’ command,

$ mysqlimport –u root –p database_name<db_backup.sql

  • Changing password for a user in mariadb

We are going to change the password of ‘root’ for this example but you can use the below process to change the password of any user,

Login into mariadb& select ‘mysql’ database,

$ mysql –u root –p
$ use mysql;

& then run the following,

$ update user set password=PASSWORD(‘your_new_password_here’) where User='root';

Next, reload the privileges,

$ flush privileges;

& then exit the session.

These are some very basic commands that will get you started with using MariaDB & these can also be used with MySQL since MariaDB is a forked out version of MySQL only. Just like MariaDB database administration software, for example MariaDB GUI, can also be used for MySQL.

If you think we have helped you or just want to support us, please consider these:-

Connect to us: Facebook | Twitter

Linux TechLab is thankful for your continued support.