Submarine Database

Submarine needs to use the database to store information about the organization, user, projects, tasks, metastore and configuration of the system information, So consider using mysql to store this data.

• MySQL will be included in the mini-submarine docker image to allow users to quickly experience the submarine workbench.
• In a production environment, the submarine workbench can be connected to the official mysql database.

Prerequisite

Must:

• MySQL
• MyBatis

Run mysql on docker

By using the official docker image of submarine database, only one docker command is required to run submarine database

docker run -it -p 3306:3306 -d --name submarine-database -e MYSQL_ROOT_PASSWORD=password apache/submarine:database-<REPLACE_VERSION>

Initialize submarine database

It will create users and tables that submarine requires

sudo ./init-database

Manual operation of the submarine database

Modify character set (Optional)

If you need to store Chinese character data in mysql, you need to execute the following command to modify the mysql character set.

• Set database character set

  bash > mysql -uroot -ppassword
  
  mysql>SHOW VARIABLES LIKE 'character_set_%'; // View database character set
  mysql>SHOW VARIABLES LIKE 'collation_%';
  
  SET NAMES 'utf8';
  

• Configuration mysqld.cnf

  # install vim
  apt-get update
  apt-get install vim
  
  vi /etc/mysql/mysql.conf.d/mysqld.cnf
  
  [mysqld]
  character_set_server = utf8
  
  [mysql]
  default-character-set = utf8
  
  [mysql.server]
  default-character-set = utf8
  
  [mysqld_safe]
  default-character-set = utf8
  
  [client]
  default-character-set = utf8
  

Create Submarine Database

Create development database

Run mysql docker container

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag

Copy the files, submarine.sql, submarine-model.sql, submarine-data.sql and metastore.sql to the mysql docker.

docker cp ${SUBMARINE_HOME}/dev-support/database/submarine.sql ${DOCKER_ID}:/
docker cp ${SUBMARINE_HOME}/dev-support/database/submarine-model.sql ${DOCKER_ID}:/
docker cp ${SUBMARINE_HOME}/dev-support/database/submarine-data.sql ${DOCKER_ID}:/
docker cp ${SUBMARINE_HOME}/dev-support/database/metastore.sql ${DOCKER_ID}:/

Development database for development environment.

# in mysql container
bash > mysql -uroot -ppassword
mysql> CREATE USER IF NOT EXISTS 'submarine'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'submarine'@'%';
mysql> CREATE DATABASE IF NOT EXISTS submarine CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> use submarine;
mysql> source /submarine.sql;
mysql> source /submarine-model.sql;
mysql> source /submarine-data.sql;
mysql> CREATE USER IF NOT EXISTS 'metastore'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'metastore'@'%';
mysql> CREATE DATABASE IF NOT EXISTS metastore CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> use metastore;
mysql> source /metastore.sql;
mysql> quit

NOTE: submarine development database name is submarine and user name is submarine, password is password, metastore development database name is metastore and user name is metastore, password is password, This is the default value in the system's submarine-site.xml configuration file and is not recommended for modification.

Create test database

Test database for program unit testing and Travis test environment.

# in mysql container
bash > mysql -uroot -ppassword
mysql> CREATE USER IF NOT EXISTS 'submarine_test'@'%' IDENTIFIED BY 'password_test';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'submarine_test'@'%';
mysql> CREATE DATABASE IF NOT EXISTS `submarine_test` CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> use `submarine_test`;
mysql> source /submarine.sql;
mysql> CREATE USER IF NOT EXISTS 'metastore_test'@'%' IDENTIFIED BY 'password_test';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'metastore_test'@'%';
mysql> CREATE DATABASE IF NOT EXISTS `metastore_test` CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> use `metastore_test`;
mysql> source /metastore.sql;
mysql> quit

NOTE: submarine test database name is submarine_test and user name is submarine_test, password is password_test, metastore test database name is metastore_test and user name is metastore_test, password is password_test, Cannot be configured, values that cannot be modified.

mysqldump

mysqldump -uroot -ppassword --databases submarine > submarine.sql;
mysqldump -umetastore -ppassword metastore > metastore.sql;

Travis

1. In the submarine's Travis, the test database, database name, username and password will be automatically created based on the contents of this document.

   Therefore, do not modify the database's database name, username and password configuration to avoid introducing some problems.

2. In the mysql database in Travis, the submarine.sql are executed to create the submarine database table structure and test data.

3. The submarine database test case written in the workbench-server module will also be unit tested in the mysql database in travis.