Preparation: Part 1
Intro
After preparing the cloud server for hosting your web application, next work is preparing database to store data. As mentioned in Part 1, I picked MariaDB as my database platform. MariaDB is basically same as MySQL, but MariaDB has smaller scope than MySQL, especially when you plan to go for bigger project scale. But in general, the syntax and everything are same. It means that if at some point you found some difficulties, you might also search any solution for MySQL.
MariaDB Installation
Installation
[1] yum update [2] yum install mariadb-server [3] systemctl start mariadb [4] systemctl enable mariadb [5] mysql
Command [3] is to start the service and command [4] is to enable the service automatically when booting the system. Command [5] is to enter the mariaDB mode. This command is used when the user hasn’t been protected yet with password.
Optional - Upgrade to MariaDB 10.1
This step is optional. Per today (18 Nov 21st), the newest version of MariaDB is version 10.3. But in yum, the newest version is version 5.5. But I tried to upgrade it to 10.1, because at the time I upgrade I didn’t know that 10.3 was released already haha.
Btw, the reason I upgrade the version is because I had error when creating table that contains Timestamp.
Error Code : 1293
Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
As I searched, from version 5.6 up, this issue has been fixed. If you still have nothing inside your DB, then fine. But if you have worked on something, better to backup your DB before upgrading.
First of all, make sure you’ve updated YUM repo and check the newest available version. If not available, you need to add new file to locate the repository manually, like I’ve done below.
> yum -y update
> systemctl stop mysql
> vi /etc/yum.repos.d/MariaDB10.repo
Fill the new file with following content (Click Esc then letter I, to insert new text in Vi editor):
# MariaDB 10.1 CentOS repository list # http://mariadb.org/mariadb/repositories/ [mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.1/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1
Click Esc and :wq to save quit from Vi editor.
Remove Old Version
> yum remove mariadb-server mariadb mariadb-libs
> yum clean all
Installing MariaDB 10.1
> yum -y install MariaDB-server MariaDB-client
Restart the service. done~
> systemctl start mariadb
> systemctl enable mariadb
> mysql -v
Create New DB User with Password
In default, a root used is created without password. To check available user in database, use following command. Make sure you have enter the mariadb mode.
[1] use mysql; [2] select user, host from user; [3] show grants;
Command [3] is used to see the privilege of active user, because you’ve logged in with root user, then the command will show your root user’s privilege.
[4] CREATE USER ‘myuser’@’%’ IDENTIFIED BY ‘mypwd’; [5] CREATE USER ‘myuser’@’localhost’ IDENTIFIED BY ‘mypwd’; [6] SET PASSWORD FOR ‘root’@’%’ = PASSWORD (‘newpass’);-quit from mariadb mode-
[7] mysql -u root -p
Command [6] is for changing current password of a user. Now user root is protected by password and you have to re-login to apply. Login with command [7]. Change “root” with your own username.
I want to restrict this new user for certain database only. So I will create new database for my apps and assign privilege to this new user.
[8] CREATE DATABASE `newdb`;[9] show databases; [10] GRANT ALL privileges ON`newdb` .* TO ‘newuser’@’%”; [11] show grant for ‘newuser’@’%;
Account name is written in this format: ‘username’@’hostname’. If you don’t want to specified any hostname, you can replace it with % sign. It works like LIKE syntax in sql command. I made one more user for localhost hostname to be able enter the mariadb menu at localhost.
Create Table and Assign PK & FK
Some people might find managing database with tool is more convenience than executing through putty. For DB syntax, I prefer to edit the command from IDE. Because when you made a typo, I can edit it right away and execute the command. If you type it in SSH client, you must re-write it again from beginning. It cannot be undo haha Another reason why I prefer to edit query from DB IDE is, I can save my query as file, so I can load it anywhere.
If you choose MySql for your database, you can easily use MySql Workbench as the IDE. I heard that MariaDB was possible to be accessed from MySql Workbench, but now it is not supported anymore. So I need to find the replacement. Then I found this HeidiSQL. The tool is light and simpler than mysql workbench.
Setting Connection to Remote DB
First of all, you need to set the firewall rules for allowing remote connection in GCP.
- Go to GCP Console
- From Navigation Menu, choose VPC Network -> Firewall Rules
- Create new firewall rules
- Fill following information:
Name: databaserule
Source IP ranges: 0.0.0.0/0
Check tcp checkbox at specified protocol and ports, then fill with 3306 - Click Create button
Install Heidi SQL and create new connection.
You can find your remote server IP address from GCP console. Your DB user ID and password is the one that we made before in previous step.
Next, we can start to create the table we want. I have this .sql file I created for my project before. I just copy paste it to Heidi SQL to create my own table. So, I will only put some important note of it here. Following is the basic SQL syntax to create table.
create table if not exists table_name1 (
org_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
org_name VARCHAR(30) NOT NULL,
reg_user VARCHAR(20),
reg_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
mod_user VARCHAR(20),
mod_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
describe table_name1;
Following are connecting Foreign Key to other table. In my case, I want to connect second table (table_name2) to table_name1 by org_id.
create table if not exists table_name2(
role_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
role_name VARCHAR(30) NOT NULL,
role_type VARCHAR(20) NOT NULL,
org_id INT(11) NOT NULL,
reg_user VARCHAR(20),
reg_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
mod_user VARCHAR(20),
mod_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT `fk_role` FOREIGN KEY (org_id) REFERENCES table_name1 (org_id) ON DELETE CASCADE
);describe table_name2;
Other notes when creating table:
#FOR MULTIPLE FOREIGN KEY CONNECTION
CONSTRAINT `fk_usrs`
FOREIGN KEY (user_org) REFERENCES organization (org_id) ON DELETE CASCADE,
FOREIGN KEY (user_pos) REFERENCES positions (pos_id) ON DELETE CASCADE,
FOREIGN KEY (user_auth) REFERENCES role (role_id) ON DELETE CASCADE
)
#FOR BOOLEAN TYPE COLUMN
use_yn TINYINT(4) NOT NULL DEFAULT 1,
For other references, please refer to Maria DB official documentation.
Now, we have remote database in our remote server. Next is configuring Gitlab for team management synchronization.