How to Create User in MariaDB/MySQL in Linux and Windows. In this tutorial we will also explain how to list all users and grant privileges to databases. Let’s get started.
It’s always a tricky one to choose the best relational database management systems. Today we are going to discuss two popular and widely used RDBMS to retrieve or store the operation data in the database server. In this MySQL vs MariaDB article, the topics which we cover are the introduction part and the major differences between MySQL and MariaDB. Let’s get started.
What is MySQL
MySQL is an open source and popular relational database management system (RDBMS). MySQL database includes the SQL servers and client programs to access the server side data. This type of RDBMS is most commonly used by web application developers together with PHP and Apache servers. MySQL server is officially pronounced as a “my ess que ell”. It allows you to create a relational database structure on various web application servers to store the data or automates the data processing. The main reason to use MySQL is that it is much cheaper than the Oracle database, easy to use shells for creating tables or querying the database tables. And most importantly, it is easy to use with JDBC servers.
What is MariaDB
MariaDB is one of the most widely used database servers worldwide. It was first made by the original MySQL developers, so it also acts as an open source database management system. The developers include the MariaDB server in Wikipedia, Google, WordPress.com and a few search engines. MariaDB server turns the data into a structured format and is used in a wide variety of applications such as banks, Fintech companies, Health sectors and building search engine applications. The reason behind its popularity over MySQL is that it is fast to use, scalable, robust, offers rich storage engines and includes plugins while developing the websites (for example wordpress.com). It also provides a SQL (sequential query language) interface to access the data. The latest version of MariaDB also consists of GIS and JSON (javascript object notation) features.
MySQL vs MariaDB
The below table explains the major differences between MySQL and MariaDB:
| Features | MySQL | MariaDB |
|---|---|---|
|
Initial release |
The first version of MySQL was released in 1995 |
The first version of Maria DB was released in 2009 |
|
Speed improvements |
MySQL is a bit slower system when compared to MariaDB |
MariaDB shows an improved speed compared to MySQL |
|
Server operating system support |
FreeBSD, Linux, OS X, Windows, macOS, and Solaris |
FreeBSD, Linux, OS X, Windows, macOS, and Solaris |
|
Cache or index service |
The memory storage capacity of MySQL is a bit slower than MariaDB |
The memory storage capacity is faster than MySQL due to using INSERT statements (it increases 24% of memory speed) |
|
Storage engine |
MySQL offers only a few options |
Whereas MariaDB consists of 12 new storage engines compared to MySQL |
|
Larger and faster connection pool |
MySQL offers a thread pool that cannot support more than 2,000, 000 connections |
The thread pool in MariaDB is faster and also supports more than 2,000,000 connections. |
|
Improved replication |
MySQL edition supports only a static number of threads to be connected. The recent edition of MySQL has come up with advanced thread capabilities |
Whereas in MariaDB, the replication can be done safer and faster. Updated versions offer 2x faster replications. |
|
New features and extensions |
The newer features of MariaDB are not provided in MySQL |
MariaDB comes up with advanced features and extensions like JSON, PHP, KILL statements |
|
Priority code |
MySQL uses priority code features in the enterprise edition |
MariaDB does not offer priority code because it is a close-source system |
Next in this post, we will show you how to create user in MariaDB/MySQL in Linux and Windows.
Create a User in MariaDB on Windows and Linux
Creating and managing users in MariaDB is very common task for any system and database administrators. In this section, we will show you how to create a MariaDB user in both Linux and Windows.
Create a User in MariaDB on Windows
First, launch the MariaDB client from the start menu and log in as a root user.
To create a user called windows and set a password, run the following command:
MariaDB [(none)]> create user windows@localhost identified by 'password';
Next, flush the privileges to apply all the changes:
MariaDB [(none)]> FLUSH PRIVILEGES;
To get a list of all MariaDB users, run the following command:
MariaDB [(none)]> select user from mysql.user;
You will get a list of all MariaDB users in the following output:
+-------------+
| User |
+-------------+
| root |
| root |
| root |
| mariadb.sys |
| root |
| windows |
+-------------+
6 rows in set (0.982 sec)
To remove a MariaDB user account, run the following command:
MariaDB [(none)]> DROP USER 'windows'@'localhost';
Create a User in MariaDB on Linux
In a Linux or Unix operating system, you will need to log in to the MariaDB shell to create a user. You can log in to the MariaDB shell using the following command:
mysql -u root -p
You will be asked to provide a MariaDB root password to log in to the MariaDB shell. After the successful log in, create a user called linux and set a password using the following command:
MariaDB [(none)]> create user linux@localhost identified by 'password';
Next, flush the privileges to apply all the changes:
MariaDB [(none)]> FLUSH PRIVILEGES;
To get a list of all MariaDB users, run the following command:
MariaDB [(none)]> select user from mysql.user;
You should get the following output:
+-------------+
| User |
+-------------+
| root |
| root |
| root |
| mariadb.sys |
| root |
| linux |
+-------------+
6 rows in set (0.982 sec)
To remove a MariaDB user account, run the following command:
MariaDB [(none)]> DROP USER 'linux'@'localhost';
Create a User in MySQL on Windows and Linux
Creating and managing MySQL users is a day to day task for any database administrator. In this section, we will show you how to create a user in MySQL on Windows and Linux.
Create a User in MySQL on Windows
First, launch the MySQL client from the start menu and log in with root password. After the successful log in, create a user called user1 using the following command:
mysql> CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password';
Grant all the privileges to the userdb database using the following command:
mysql> GRANT ALL ON userdb.* TO 'user1'@'localhost';
Flush the privileges to apply all the changes:
mysql> FLUSH PRIVILEGES;
To list the MySQL users, run the following command:
mysql> SELECT USER from MySQL.user;
You will get the following output:
+------------------+
| USER |
+------------------+
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
| user1 |
+------------------+
To remove a MySQL user account, run the following command:
mysql> DROP USER 'user1'@'localhost';
Next, exit from the MySQL shell using the following command:
mysql> EXIT;
Create a User in MySQL on Linux
First, you will need to log in to the MySQL shell. You can log in using the following command:
mysql -u root -p
Provide your MySQL root password and press the Enter key to log in to the MySQL shell. Once you are log in, create a user named testuser with the following command:
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'securepassword';
To grant all the privileges to the testdb database, run the following command:
mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost';
Now, flush the privileges to apply the changes:
mysql> FLUSH PRIVILEGES;
To list all MySQL users, run the following command:
mysql> select user from mysql.user;
You will get the following output:
+------------------+
| user |
+------------------+
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
| testuser |
+------------------+
To remove a MySQL user account, run the following command:
mysql> DROP USER 'testuser'@'localhost';
Next, exit from the MySQL shell using the following command:
mysql> EXIT;
How to Create User in MariaDB/MySQL in Linux and Windows Conclusion
In this post, we explained how to create a user in MySQL and MariaDB on both Linux and Windows operating systems. We also explained how to list all users and grant privileges to databases. I hope you can now easily create a user in MySQL and MariaDB.
