## Overview This is the procedure for setting up a MySQL database on a different server than where Pleasanter is deployed when using MySQL as the database for Pleasanter. For the background information on why this procedure is necessary, please refer to the explanation in "[FAQ: I want to separate the web server and DB server in MySQL](faq-mysql-multiple-server)". ## Limitations 1. When performing a new setup using the installer according to this procedure, please use installer version 1.1.0 or later. 2. This procedure is not required for SQL Server and PostgreSQL. 3. For procedures prior to Ver.1.4.17.1, please refer to "[Configure the Web Server and DB Server to Use MySQL Separately](mysql-create-user-by-sql)". ## Structure of This Procedure This procedure includes steps for both setting up Pleasanter in a new environment where Pleasanter is not currently in operation, and for separating the Pleasanter and database servers in an environment where Pleasanter is already in operation. 1. Setting up Pleasanter in a new environment where Pleasanter is not currently in operation Please refer to "1. New Environment Setup Procedure" in this procedure. 2. Separating the Pleasanter and database servers in an environment where Pleasanter is already in operation Please refer to "2. Existing Database Separation Procedure" in this procedure. ## 1. New Environment Setup Procedure ### New Environment Setup - Preparation 1. Please decide in advance the server settings (server names, IP addresses, etc.) for both the Pleasanter and MySQL servers. ### New Environment Setup - Procedure The procedure for adding a database user account using SQL when setting up a new Pleasanter environment is as follows: 1. Follow the new setup manual procedures from the beginning in order, and perform up to "MySQL Setup" on the database server. 1. [Setup procedure using the installer](https://pleasanter.org/ja/manual?category=0091) 1. [Manual setup procedure](https://pleasanter.org/ja/manual?category=0092) 2. To execute SQL, log in to MySQL on the MySQL server. 1. Command to log in to MySQL For Linux, use the following command to log in to MySQL. For Windows, use GUI tools such as Azure Data Studio to log in to MySQL. Also, if you have configured the PATH to execute the "mysql" command, you can log in to MySQL using the following command: ```text mysql -u root -p ``` 3. Check if the "MySQL administrative user account@&lt;connection source&gt;" account specified as the user in "SaConnectionString" in "Rds.json" exists on MySQL. If the confirmation command results show that an externally accessible account such as user: root, host: % exists, step 1.4 is not required, so proceed to step 1.5. The confirmation command is as follows: ```text SELECT user, host FROM mysql.user; ``` **About &lt;connection source&gt;** | Condition | Setting Value | |:--|:--| | When there are no particular requirements as described below | % | | When allowing MySQL access only from specified external hosts | Host name/IP address that allows connection. If multiple connection sources are permitted, multiple records will be retrieved. | 4. If the "MySQL administrative user account@&lt;connection source&gt;" account does not exist on MySQL, add the account. ```text create user 'root'@'<connection source>' identified by '<MySQL root account password string>'; grant all on *.* to 'root'@'<connection source>' with grant option; ``` **When specifying multiple hosts to allow connections** <details> <summary>(Click here to open/close details)</summary> Execute SQL statements for all connection sources to be permitted. The following are examples of SQL execution: ```text create user 'root'@'192.168.1.100' identified by 'password'; grant all on *.* to 'root'@'192.168.1.100' with grant option; create user 'root'@'192.168.1.200' identified by 'password'; grant all on *.* to 'root'@'192.168.1.200' with grant option; ``` </details> 5. Change the firewall settings of the database server to allow external connections to the MySQL service. 6. Continue with the setup according to the manual procedures until completion. For the three database connection strings in "[Rds.json](/en/manual/rds-json)" configured by the installer or manually ("SaConnectionString", "OwnerConnectionString", "UserConnectionString"), specify the database server in the Server parameter. 7. For "MySqlConnectingHost" in "[Rds.json](/en/manual/rds-json)" configured by the installer or manually, specify as follows: | Condition | Setting Value | |:--|:--| | When there are no particular requirements as described below (default value) | % | | When allowing MySQL access only from specified external hosts | Host name/IP address that allows connection. Multiple entries can be specified separated by commas (e.g., hostname01,hostname02) | 8. Confirm that Pleasanter can be used from a browser. ## 2. Existing Database Separation Procedure ### Existing Database Separation - Preparation 1. Please decide in advance the server settings (server names, IP addresses, etc.) for both the Pleasanter and MySQL servers. 2. Stop the Pleasanter service before executing the separation. 3. If moving MySQL to a different server than the one previously in operation, perform database backup and restore by referring to the following procedure: [FAQ: MySQL database backup and restore procedure](faq-mysql-backup-restore)\ (See section "Restoring to a Different Environment") If continuing to use the MySQL in operation on the same server, database backup and restore are not required. ### Existing Database Separation - Procedure The procedure for separating the Pleasanter and database servers in an environment where Pleasanter is already in operation is as follows: 1. Edit "[Rds.json](/en/manual/rds-json)" and specify the database server in the Server parameter for the three database connection strings ("SaConnectionString", "OwnerConnectionString", "UserConnectionString"). 2. Edit "[Rds.json](/en/manual/rds-json)" and specify "MySqlConnectingHost" as follows: | Condition | Setting Value | |:--|:--| | When there are no particular requirements as described below (default value) | "%" | | When allowing MySQL access only from specified external hosts | "Host name/IP address that allows connection". Multiple entries can be specified separated by commas (e.g., "hostname01,hostname02") | 3. To execute SQL, log in to MySQL on the MySQL server. 1. Command to log in to MySQL For Linux, use the following command to log in to MySQL. For Windows, use GUI tools such as Azure Data Studio to log in to MySQL. Also, if you have configured the PATH to execute the "mysql" command, you can log in to MySQL using the following command: ```text mysql -u root -p ``` 4. Check if the "MySQL administrative user account@&lt;connection source&gt;" account specified as the user in "SaConnectionString" in "[Rds.json](/en/manual/rds-json)" exists on MySQL. If the confirmation command results show that an externally accessible account such as user: root, host: % exists, step 2.5 is not required, so proceed to step 2.6. The confirmation command is as follows: ```text SELECT user, host FROM mysql.user; ``` **About &lt;connection source&gt;** | Condition | Setting Value | |:--|:--| | When there are no particular requirements as described below | % | | When allowing MySQL access only from specified external hosts | Host name/IP address that allows connection. If multiple connection sources are permitted, multiple records will be retrieved. | 5. If the "MySQL administrative user account@&lt;connection source&gt;" account does not exist on MySQL, add the account. ```text create user 'root'@'<connection source>' identified by '<MySQL root account password string>'; grant all on *.* to 'root'@'<connection source>' with grant option; ``` **When specifying multiple hosts to allow connections** <details> <summary>(Click here to open/close details)</summary> Execute SQL statements for all connection sources to be permitted. The following are examples of SQL execution: ```text create user 'root'@'192.168.1.100' identified by 'password'; grant all on *.* to 'root'@'192.168.1.100' with grant option; create user 'root'@'192.168.1.200' identified by 'password'; grant all on *.* to 'root'@'192.168.1.200' with grant option; ``` </details> 6. Change the firewall settings of the database server to allow external connections to the MySQL service. 7. Execute CodeDefiner in _rds mode. 1. Windows execution example ```text cd C:\web\pleasanter\Implem.CodeDefiner dotnet Implem.CodeDefiner.dll _rds ``` 2. Linux execution example ```text cd /web/pleasanter/Implem.CodeDefiner sudo -u <user that runs Pleasanter> /usr/local/bin/dotnet Implem.CodeDefiner.dll _rds ``` When "Type "y" (yes) if the license is correct, otherwise type "n" (no)." is displayed during the process, enter y. 8. Start the Pleasanter service. 9. Confirm that Pleasanter can be used from a browser. ## Supported Versions | Supported Version | Content | |:--|:--| | 1.4.18.0 and later | Manual published with the addition of functionality to specify connection source hosts other than localhost (same server as DB) for MySQL database connections |