MariaDB initialization and user creation

This is an explanation of the initial settings of MariaDB required when using it as a database server.

MariaDB root user connection

On Ubuntu, to connect as the MariaDB root user, access using Ubuntu root privileges. That is, use sudo to connect as the root user without a password.

sudo mysql -uroot

To end the connection to MariaDB, type quit and press Enter.


Time Zone Settings-Options

MariaDB's default time zone is the OS time zone. in short. If you have OS time zone setting, you do not need to set MariaDB time zone.

You need to set the time zone when MariaDB is provided as a cloud service. In this case, set the time zone in the MariaDB configuration file. As it is, it is UTC time.

Set the time difference between UTC and "Asia / Tokyo", "+09: 00".

#Parameter name

# value
+09: 00

MariaDB configuration file location

In the initial setting, no special setting is required, but since the memory setting is always done, the location of the MariaDB configuration file in Ubuntu is described.

Creating a database

Here, create a database for your web application. The database name should be myappdb. You can change the database name later (although with some ingenuity).

create database myappdb;

Display the database list to see the database you created.

show databases;

Creating an application user

Let's create a MariaDB user for your web application. A user with select, insert, update, delete permissions.

Connect to MariaDB as an admin user. If you set the root password above, connect as the root user, and if there is another administrator user, connect as that user.

#Connect as Ubuntu root user
sudo mysql -uroot

#MariaDB Sample to connect to cloud service
mysql -h -ukanri@my-devel-db-001 -p

You will be asked for a password, so enter it.

If the database is remote and you cannot connect, also check the firewall on the database server side.

Create a user in MariaDB.

create user myapp;

Next, Set database access permissions and passwords.

The localhost part below is the setting when the database server is on the same server. In the case of a cloud server, specify the IP address or domain name of the connection source.

grant insert, update, delete, select on myappdb. * to'myapp' @'localhost' identified by'eifjutab &';

When installing DBD::mysql with Perl, you need to have connection permission to the test database, so set it.

create database test;
grant all privileges on test. * to'myapp' @'localhost' identified by's3kr1t';

Make sure you can disconnect and connect to MariaDB with the newly created "myapp" user.

#Connect as root user
mysql -umyapp -p

#MariaDB Sample to connect to cloud service
mysql -h -umyapp@my-devel-db-001 -p

This completes the initial settings and user creation.

Associated Information