Create New MariaDB Database and User

Suppose you want:

• create a new MariaDB(or MySQL) database foo for new user bar.
• The new user bar should be connect to the database server with password from remote machine
• The new user bar should have all the privileges on the newlly created database bar.

Solution

open mysql shell by invoking the mysql command and write the following SQL commands:

CREATE DATABASE foo; 
CREATE USER 'bar'@'%' IDENTIFIED BY 'password'; 
GRANT ALL ON foo.* TO 'bar'@'%';
FLUSH PRIVILEGES;

We can also use the following bash script

#! /bin/bash
db=$1
user=$2
password=$3
 
function _create_db_and_user {
    db=$1
    user="'$2'"@"'%'";
    password=$3
    mysql -e "
      CREATE DATABASE ${db}; 
      CREATE USER ${user} IDENTIFIED BY '${password}'; 
      GRANT ALL ON ${db}.* TO ${user};
      FLUSH PRIVILEGES;
    "
}
_create_db_and_user $db $user $password

Now we can use the script :

$ create_db_and_user db user password