Introduction to server management with provisioning tool fairy support run
table of contents
Sample introduction(database)

Prepare

Please download a sample from here
There are only simple things that don't need any explanation, but Some will be introduced
Go to the vagrant folder in the downloaded sample and enter vagrant up to start the virtual environment

Execute SQL

Add user

Create a new user called newuser in MySQL
Create sql folder
Create an input folder under the sql folder
Create a user folder under the input folder
Create 00001.sql in the user folder

00001.sql 
CREATE USER 'newuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newuser_password';
GRANT ALL ON *.* TO 'newuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- sql
     |   |   `-- input
     |   |       `-- user
     |   |           `-- 00001.sql
     |   |-- sql_exe
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

include.txt and get.txt under sql_exe folder are as follows

include.txt 
../common
../sql/input/${2}
get.txt 
output ../sql/output/${2}/${FILE}_${SERVER}/${DATE}_${HH}${MM}${SS}

Let's run
Run sql_exe
The argument mysql is the database name on MySQL that executes SQL
The argument user is the folder name under the sql>input folder

fairysupport_run.bat sql_exe mysql user
./fairysupport_run.sh sql_exe mysql user

output folder is created
Let's look at 00001.sql under the output>user>server_server1>YYYYMMDD_HHMMSS, output>user>server_server2>YYYYMMDD_HHMMSS folder

folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- sql
     |   |   |-- input
     |   |   |   `-- user
     |   |   |       `-- 00001.sql
     |   |   `-- output
     |   |       `-- user
     |   |           |-- server_server1
     |   |           |   `-- YYYYMMDD_HHMMSS
     |   |           |       `-- 00001.sql
     |   |           `-- server_server2
     |   |               `-- YYYYMMDD_HHMMSS
     |   |                   `-- 00001.sql
     |   |-- sql_exe
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

You can see that newuser has been created


Let's improve 00001.sql a little

00001.sql 
CREATE USER '${_ARG3}'@'localhost' IDENTIFIED WITH mysql_native_password BY '${_ARG4}';
GRANT ALL ON *.* TO '${_ARG3}'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Let's run
two arguments are increased 

fairysupport_run.bat sql_exe mysql user newuser2 newuser2_password
./fairysupport_run.sh sql_exe mysql user newuser2 newuser2_password

newuser2 is created

Create database

Create a new database on MySQL
Create db folder under sql>input folder
Create 00001.sql in the db folder

00001.sql 
CREATE DATABASE NEW_DB;
folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- sql
     |   |   `-- input
     |   |       `-- db
     |   |           `-- 00001.sql
     |   |-- sql_exe
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

Let's run 

fairysupport_run.bat sql_exe mysql db
./fairysupport_run.sh sql_exe mysql db

output>db folder is created

folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- sql
     |   |   |-- input
     |   |   |   `-- db
     |   |   |       `-- 00001.sql
     |   |   `-- output
     |   |       `-- db
     |   |           |-- server_server1
     |   |           |   `-- YYYYMMDD_HHMMSS
     |   |           |       `-- 00001.sql
     |   |           `-- server_server2
     |   |               `-- YYYYMMDD_HHMMSS
     |   |                   `-- 00001.sql
     |   |-- sql_exe
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

You can see that NEW_DB has been created

Create table

Creating the first table

Create a table for the first time on MySQL
create sql_version folder
Create an input folder under the sql_version folder
Create an table folder under the sql_version>input folder
Create 000000001 folder under sql_version>input>table folder
Create 00001_SAMPLE_1.sql in the 000000001 folder
Create 00002_SAMPLE_2.sql in the 000000001 folder

00001_SAMPLE_1.sql 
CREATE TABLE SAMPLE_1 (
  ID int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NAME varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
00002_SAMPLE_2.sql 
CREATE TABLE SAMPLE_2 (
  ID int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NAME varchar(255) NOT NULL,
  SAMPLE_1_ID int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- sql_version
     |   |   `-- input
     |   |       `-- table
     |   |           `-- 000000001
     |   |               |-- 00001_SAMPLE_1.sql
     |   |               `-- 00002_SAMPLE_2.sql
     |   |-- sql_version_exe
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

include.txt and get.txt under sql_version_exe folder are as follows

include.txt 
../common
../sql_version/input/${2}
get.txt 
output ../sql_version/output/${2}/${FILE}_${SERVER}/${DATE}_${HH}${MM}${SS}

Let's run
Run sql_version_exe
The argument NEW_DB is the name of the database on MySQL that executes SQL. This time we will run on the NEW_DB created earlier
The argument table is the folder name under the sql_version>input folder
Two 1 are given as arguments. It is the 000000001 folder under the sql_version>input>table folder
sql_version_exe can be executed by specifying a range such as executing files in folders from 000000001 to 000000003
This time, it will execute the file that is in the folder from 000000001 to 000000001 

fairysupport_run.bat sql_version_exe NEW_DB table 1 1
./fairysupport_run.sh sql_version_exe NEW_DB table 1 1

sql_version>output folder is made. Let's see

folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- sql_version
     |   |   |-- input
     |   |   |   `-- table
     |   |   |       `-- 000000001
     |   |   |           |-- 00001_SAMPLE_1.sql
     |   |   |           `-- 00002_SAMPLE_2.sql
     |   |   `-- output
     |   |       `-- table
     |   |           |-- server_server1
     |   |           |   `-- YYYYMMDD_HHMMSS
     |   |           |       `-- 000000001
     |   |           |           |-- 00001_SAMPLE_1.sql
     |   |           |           `-- 00002_SAMPLE_2.sql
     |   |           `-- server_server2
     |   |               `-- YYYYMMDD_HHMMSS
     |   |                   `-- 000000001
     |   |                       |-- 00001_SAMPLE_1.sql
     |   |                       `-- 00002_SAMPLE_2.sql
     |   |-- sql_version_exe
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

You can see that the table was created

Create and alter of a table at 2nd and subsequent

Assuming development was more progressed after the first time release, and that you needed a new table
Create a 000000002 folder under the sql_version>input>table folder
Create 00001_SAMPLE_3.sql under the 000000002 folder

00001_SAMPLE_3.sql 
CREATE TABLE SAMPLE_3 (
  ID int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  NAME varchar(255) NOT NULL,
  SAMPLE_1_ID int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Assuming development was more progressed after a new table is released, and that you need to add columns on the table
Create a 000000003 folder under the sql_version>input>table folder
Create 00001_SAMPLE_1.sql in the 000000003 folder

00001_SAMPLE_1.sql 
ALTER TABLE SAMPLE_1 ADD NEW_COL varchar(10);
folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- sql_version
     |   |   `-- input
     |   |       `-- table
     |   |           |-- 000000001
     |   |           |   |-- 00001_SAMPLE_1.sql
     |   |           |   `-- 00002_SAMPLE_2.sql
     |   |           |-- 000000002
     |   |           |   `-- 00001_SAMPLE_3.sql
     |   |           `-- 000000003
     |   |               `-- 00001_SAMPLE_1.sql
     |   |-- sql_version_exe
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

Set the environment in the first time release state to the version3 state
If you look under the sql_version>output folder, you can see up to which version
Let's run
Arguments 2 3 do not execute SQL under the 000000002 and 000000003 folders, but execute SQL under the from 000000002 to 000000003 folders
If you have created up to 000000004 folder, if you give 2 4 in the argument, SQL under 000000002, 000000003, 000000004 folder will be executed 

fairysupport_run.bat sql_version_exe NEW_DB table 2 3
./fairysupport_run.sh sql_version_exe NEW_DB table 2 3

SQL under the 000000002 and 000000003 folders was executed

Execute INSERT

Now that the table has been created, let's insert data using sql_exe
Create an insert folder under the sql>input folder
Create 00001.sql under the insert folder
Create 00002.sql under the insert folder

00001.sql 
INSERT INTO SAMPLE_1 (NAME, NEW_COL) VALUES ('sample1_name1', 'val1')
00002.sql 
INSERT INTO SAMPLE_2 (NAME, SAMPLE_1_ID) SELECT 'sample2_name1', ID FROM SAMPLE_1
folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- sql
     |   |   `-- input
     |   |       `-- insert
     |   |           |-- 00001.sql
     |   |           `-- 00002.sql
     |   |-- sql_exe
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

Let's run 

fairysupport_run.bat sql_exe NEW_DB insert
./fairysupport_run.sh sql_exe NEW_DB insert

output>insert folder is made

folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- sql
     |   |   |-- input
     |   |   |   `-- insert
     |   |   |       |-- 00001.sql
     |   |   |       `-- 00002.sql
     |   |   `-- output
     |   |       `-- insert
     |   |           |-- server_server1
     |   |           |   `-- YYYYMMDD_HHMMSS
     |   |           |       |-- 00001.sql
     |   |           |       `-- 00002.sql
     |   |           `-- server_server2
     |   |               `-- YYYYMMDD_HHMMSS
     |   |                   |-- 00001.sql
     |   |                   `-- 00002.sql
     |   |-- sql_exe
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

You can see that the INSERT statement was executed

export data

Let's export data

folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- dump_db_get
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

Let's run 

fairysupport_run.bat dump_db_get NEW_DB
./fairysupport_run.sh dump_db_get NEW_DB

dump_db folder is created

folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- dump_db
     |   |   |-- server_server1
     |   |   |   `-- NEW_DB
     |   |   |       `-- YYYYMMDD_HHMMSS
     |   |   |           `-- mysqldump.sql
     |   |   `-- server_server2
     |   |       `-- NEW_DB
     |   |           `-- YYYYMMDD_HHMMSS
     |   |               `-- mysqldump.sql
     |   |-- dump_db_get
     |   |   |-- get.txt
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

If you look at the mysqldump.sql file, you can see that the data is exported

Import data

Let's import data

folder tree 
     |-- fairysupport_run
     |   |-- common
     |   |   |-- common.sh
     |   |   `-- db_user.sh
     |   |-- dump_db
     |   |   |-- server_server1
     |   |   |   `-- NEW_DB
     |   |   |       `-- YYYYMMDD_HHMMSS
     |   |   |           `-- mysqldump.sql
     |   |   `-- server_server2
     |   |       `-- NEW_DB
     |   |           `-- YYYYMMDD_HHMMSS
     |   |               `-- mysqldump.sql
     |   |-- dump_db_set
     |   |   |-- include.txt
     |   |   `-- main.sh
     |   |-- com_fairysupport_run.jar
     |   |-- env.txt
     |   `-- server.properties.local
     |-- vagrant
     |   `-- Vagrantfile

Let's run
Change the YYYYMMDD_HHMMSS part of the argument to the actual folder name under dump_db>server_server1>NEW_DB 

fairysupport_run.bat dump_db_set NEW_DB YYYYMMDD_HHMMSS
./fairysupport_run.sh dump_db_set NEW_DB YYYYMMDD_HHMMSS

mysqldump was executed


That's all for the sample introduction


table of contents