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:

SQL - Create database and user
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

create_db_and_user
#! /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 usage
$ create_db_and_user db user password
Kobi
Kobi
Software Developer

Kobi helps entrepreneurs to translate their wishes to a working product.