If we plan to use any CMS for our sites, whether it's WordPress, Joomla or Drupal, we’ll need to create a MySQL/MariaDB database and a database user. Trust me, you didn’t want to run all your sites with MariaDB ‘root’ user! Just don't do it. Seriously.
Anyway, let’s create two databases for both websites and two MariaDB users for them using shell. Our default MariaDB installation used ‘root’ user without a password, and we only can connect to it from server itself:
[root@ServerSuitTest ~]# mysql -h localhost -u root Welcome to the MariaDB monitor. Commands end with ; or \g.
MariaDB > CREATE DATABASE ServerSuitTheBest CHARACTER SET utf8; Query OK, 1 row affected (0.00 sec)
MariaDB > CREATE DATABASE iLoveServerSuite CHARACTER SET utf8; Query OK, 1 row affected (0.00 sec)
What we need next is to create new users and grant them all required privileges for each database. Take a look at database name and username. With the line ‘ServerSuitTheBest.*’ we grant user all privileges to all tables in this database, including creating new ones. But MariaDB's security model allows us to provide access to only one or two tables in the database. We'll allow user ‘ServerSuitTheBest’ to connect only from server itself using the ‘localhost’ statement for security reasons:
MariaDB > GRANT ALL ON ServerSuitTheBest.* TO ServerSuitTheBest@localhost IDENTIFIED BY 'ReallyGoodPassword'; Query OK, 0 rows affected (0.00 sec)
MariaDB > GRANT ALL ON iLoveServerSuit.* TO iLoveServerSuit@localhost IDENTIFIED BY 'ReallyGoodPassword'; Query OK, 0 rows affected (0.00 sec)
MariaDB > FLUSH PRIVILEGES;
And finally let’s try to login as one of the user to check how it’s working:
[root@ServerSuitTest ~]# mysql -h localhost -u SererSuitTheBest -pReallyGoodPassword Welcome to the MariaDB monitor. Commands end with ; or \g.
MariaDB > use ServerSuitTheBest; Database changed
MariaDB [ServerSuitTheBest]> CREATE TABLE test (id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20)); Query OK, 0 rows affected (0.48 sec)
MariaDB [ServerSuitTheBest]> DESCRIBE test; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
Now the user have full access only to their corresponding database so they can’t accidentally delete or modify data from another one.
Of course, working from console is not very comfortable, so you can install a tool like PhpMyAdmin.
It lets you do most of the common administrator tasks along with database backup and recovery, creating databases and tables, making queries, profile complex queries, and much more:
Like we mentioned in our other posts, most of what we cover here is already configured with our LAMP package with ServerSuit.
So you can install LAMP software stack to your server, create MariaDB users for database and deploy your website almost immediately.
Our LAMP/LEMP software package will also install and configure PhpMyAdmin. You do less installing and configuring, and spend more time producing.
Give it a try! Remember to follow us on Twitter and Facebook to keep up to date with news, updates, and guides like this one!