## Overview This is the procedure for using MySQL for the database of Pleasanter, when building the database on a server different from the one where Pleasanter is deployed. For the background of the need for this procedure, 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 carrying out this procedure, the version of Pleasanter must be 1.4.10.0 onward. Pleasanter has supported MySQL since version 1.4.9.0, but this procedure can only be carried out on versions 1.4.10.0 onward. 2. This procedure is not necessary for SQL Server and PostgreSQL. ## Configuration of This Procedure This procedure describes the steps for setting up Pleasanter in an environment where it is not currently being used, and for separating the Pleasanter and DB servers in an environment where Pleasanter is already being used. 1. When setting up Pleasanter in an environment where it is not currently being used See "1. Procedure for building a new environment" in this procedure. 2. When separating the Pleasanter and DB servers in an environment where Pleasanter is already being used See "2. Procedure for separating an existing DB" in this procedure. The contents of the SQL to be executed are the same. See "[Common Procedure] SQL syntax for adding a user account for external connections" in this procedure. ## 1. Procedure for Building a New Environment ### Build a new environment - Preparation 1. Please perform the setup up to "Run CodeDefiner" in advance as described in the manual below. <div id="ManualList"><ul><li><a href="/en/manual/getting-started-pleasanter-azure">Install Pleasanter on Azure AppService with serverless configuration</a><span>05.26.2025 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-windows-server2022">Install Pleasanter on Windows Server 2022</a><span>10.10.2024 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-windows-server2016">Install Pleasanter on Windows Server 2016</a><span>08.13.2024 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-windows">Install Pleasanter on Windows</a><span>10.01.2024 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-windows-server2019">Install Pleasanter on Windows Server 2019</a><span>10.10.2024 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-windows10">Install Pleasanter on Windows 10/11</a><span>08.13.2024 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-ubuntu">Install Pleasanter on Ubuntu</a><span>11.19.2024 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-centos">Install Pleasanter on CentOS</a><span>10.01.2024 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-rhel-8">Install Pleasanter on Red Hat Enterprise Linux 8</a><span>10.01.2024 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-rhel">Install Pleasanter on Red Hat Enterprise Linux 9</a><span>10.01.2024 up</span></li> <li><a href="/en/manual/getting-started-pleasanter-almalinux">プリザンターを AlmaLinux にインストールする</a><span>01.16.2024 up</span></li> <li><a href="/en/manual/getting-preparation-pleasanter-windows">Preparations before installing Pleasanter on Windows</a><span>06.26.2025 up</span></li></ul></article></div><input id="SearchTextHidden" type="hidden" value="" /> 2. Please decide the settings for Pleasanter and MySQL servers (server name, IP address, etc.) in advance. 3. Please set "[Service.json](/en/manual/service-json)" and "[Rds.json](/en/manual/rds-json)" in advance. [Set Parameter: Service.json](service-json) [Set Parameter: Rds.json](rds-json) ### Create a new environment - Procedure When creating a new Pleasanter environment, the procedure for adding a DB user account in SQL is as follows. 1. Edit "[Rds.json](/en/manual/rds-json)" and specify the database server in Server for the three database connection strings ("SaConnectionString", "OwnerConnectionString", "UserConnectionString"). 2. To execute SQL, log in to MySQL on the MySQL server. ##### Command to log in to MySQL *For Linux, log in to MySQL with the following command. *For Windows, log in to MySQL using a GUI tool such as Azure Data Studio. Also, if you have set it so that the "mysql" command can be executed through Path, you can also log in to MySQL with the following command. ``` mysql -u root -p<Write the MySQL root account password without a space between -p> ``` 3. Refer to "[Common Procedure] SQL syntax for adding a user account for external connections" below and create 3 new MySQL user accounts. 4. If necessary, use a database connection client on the server where Pleasanter is deployed to check communication with the MySQL user accounts created in step 3. If communication is not possible, please review the settings as there may be connection restrictions imposed by the firewall settings on each server or the contents of the MySQL configuration file. 5. Carry out the steps from "Running CodeDefiner" onwards in the initial Pleasanter setup. ## 2. Procedure for separating an existing DB ### Separate an existing DB - Preparation 1. Decide the server settings for Pleasanter and MySQL (server name, IP address, etc.) in advance. 2. Set "[Service.json](/en/manual/service-json)" and "[Rds.json](/en/manual/rds-json)" in advance. [Set Parameter: Service.json](service-json) [Set Parameter: Rds.json](rds-json) 3. Stop the Pleasanter service before executing SQL. 4. If you are transferring MySQL to a server different from the one you are using previously, please refer to the following procedure to back up and restore the DB. [FAQ: MySQL database backup and restore procedure](faq-mysql-backup-restore) └ When restoring to a different environment If you continue to use the currently operating MySQL on the same server, there is no need to back up and restore the DB. ### Separate an existing DB - Procedure The procedure for separating the Pleasanter and DB 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 Server for the three database connection strings ("SaConnectionString", "OwnerConnectionString", "UserConnectionString"). 2. To execute SQL, log in to MySQL on the MySQL server. If you backed up and restored the DB during preparation, log in to the MySQL where you restored it. ##### Command to log in to MySQL *For Linux, log in to MySQL with the following command. *For Windows, log in to MySQL using a GUI tool such as Azure Data Studio. Also, if you have set it so that you can run the "mysql" command through Path, you can log in to MySQL with the following command. ``` mysql -u root -p<Password for the MySQL root account is entered without a space between -p> ``` 3. Refer to "Common Procedure: SQL syntax for adding user accounts for external connections" below and create a total of 3 new MySQL user accounts. 4. If necessary, use a database connection client on the server where Pleasanter is deployed to check communication with the MySQL user account created in step 3. If communication is not possible, please review the settings as there may be connection restrictions imposed by the firewall settings on each server or the contents of the MySQL configuration file. 5. Start the Pleasanter service. 6. Check that you can use Pleasanter from a browser. ## [Common Procedure] SQL syntax for adding a user account for external connections Execute the SQL to create three new MySQL accounts that can connect from external servers. 1. MySQL root account 2. MySQL user account with authority to create/modify tables (hereafter referred to as "Owner account") 3. MySQL user account with authority to view tables (hereafter referred to as "User account") Each SQL consists of two lines, a create user statement and a grant statement, and is used to set the same password and privileges as when using a user account with the same name for a localhost connection. ### SQL syntax to allow external connections from the MySQL root account ``` create user 'root'@'<Connection source>' identified by '<MySQL root account password string>'; grant all on *.* to 'root'@'<Connection source>'; ``` #### Description of variable parts <details> <summary> (Click here to open/close details) </summary> Enter the password you set in the initial setup procedure for MySQL and the contents that match the "SaConnectionString" contents in "Rds.json". |Variable part|e.g.|Description| |:--|:--|:--| |Password string for MySQL root account|password|String that matches the PWD of "SaConnectionString" in "[Rds.json](/en/manual/rds-json)"| |Connection source|192.168.1.100|Connection source for MySQL. For details, see "Rules for specifying connection source" below. If multiple connection sources are specified, execute the SQL in lines 1 and 2 for each connection source. | 〈Example of "SaConnectionString" in "Rds.json"〉 ``` "SaConnectionString": "Server=db.hostname;Port=3306;Database=mysql;UID=root;PWD=password", ``` 〈SQL example〉 ``` create user 'root'@'192.168.1.100' identified by 'password'; grant all on *.* to 'root'@'192.168.1.100'; ``` </details> ### SQL syntax to allow external connection by Owner account ``` create user '<Owner name>'@'<Connection source>' identified by '<Owner password string>'; grant all on `<database name>`.* to '<Owner name>'@'<Connection source>' with grant option; ``` #### Description of variable part <details> <summary> (Click here to open/close details) </summary> Enter the content that matches the "OwnerConnectionString" content in "[Rds.json](/en/manual/rds-json)". |Variable part|e.g.|Description| |:--|:--|:--| |Database name|Implem.Pleasanter|String that matches "Name" in "[Service.json](/en/manual/service-json)"| |Owner name|Implem.Pleasanter_Owner|String that matches the UID of "OwnerConnectionString" in "[Rds.json](/en/manual/rds-json)". Replace the #ServiceName#part with the "database name" above| |Owner password string|SetAdminsPWD|String that matches the PWD of "OwnerConnectionString" in "[Rds.json](/en/manual/rds-json)"| |Connection source|%|Connection source for MySQL. For details, see "Rules for specifying connection source" below. If multiple connection sources are specified, execute the SQL in lines 1 and 2 for each connection source. | 〈Example of "OwnerConnectionString" in "[Rds.json](/en/manual/rds-json)"〉 ``` "OwnerConnectionString": "Server=db.hostname;Port=3306;Database=#ServiceName#;UID=#ServiceName#_Owner;PWD=SetAdminsPWD", ``` 〈SQL example〉 ``` create user 'Implem.Pleasanter_Owner'@'%' identified by 'SetAdminsPWD'; grant all on `Implem.Pleasanter`.* to 'Implem.Pleasanter_Owner'@'%' with grant option; ``` </details> ### SQL syntax to allow external connections by user account ``` create user '<User name>'@'<Connection source>' identified by '<User password string>'; grant select, insert, update, delete, create routine, alter routine on `<database name>`.* to '<User name>'@'<connection source>'; ``` #### Description of variable part <details> <summary> (click here to open/close details) </summary> Enter the contents that match the "UserConnectionString" contents written in "[Rds.json](/en/manual/rds-json)". |Variable part|e.g.|Description| |:--|:--|:--| |Database name|Implem.Pleasanter|String that matches "Name" in "[Service.json](/en/manual/service-json)"| |User name|Implem.Pleasanter_User|String that matches the UID in "UserConnectionString" in "[Rds.json](/en/manual/rds-json)". Replace the #ServiceName# part with the "database name" above| |User password string|SetUsersPWD|String that matches the PWD in "UserConnectionString" in "[Rds.json](/en/manual/rds-json)"| |Connection source|%|Connection source for MySQL. For details, see "Rules for specifying the connection source" below. If you specify multiple connection sources, execute the SQL in lines 1 and 2 for each connection source.| 〈Example of "UserConnectionString" in "[Rds.json](/en/manual/rds-json)"〉 ``` "UserConnectionString": "Server=db.hostname;Port=3306;Database=#ServiceName#;UID=#ServiceName#_User;PWD=SetUsersPWD", ``` 〈SQL example〉 ``` create user 'Implem.Pleasanter_User'@'%' identified by 'SetUsersPWD'; grant select, insert, update, delete, create routine, alter routine on `Implem.Pleasanter`.* to 'Implem.Pleasanter_User'@'%'; ``` </details> ## Check MySQL user accounts and permitted connection sources Executing the following SQL will display a list of user accounts that exist on MySQL and permitted connection sources. ``` select user, host from mysql.user; ``` 〈Example of execution result〉 ``` +-------------------------+---------------+ | user | host | +-------------------------+---------------+ | Implem.Pleasanter_Owner | % | | Implem.Pleasanter_User | % | | root | 192.168.1.100 | | Implem.Pleasanter_Owner | localhost | | Implem.Pleasanter_User | localhost | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +-------------------------+---------------+ ``` ## Connection source specification rules If you specify an IP address as the connection source, connections will be allowed from that IP address. Please specify the IP address of the server where Pleasanter is deployed. ``` +-------------------------+---------------+ | user | host | +-------------------------+---------------+ | root | 192.168.10.12 | ``` If you specify the wildcard symbol "%" as the source of the connection, connections will be permitted from all sources. **For the root account, do not specify "%" as the source of the connection for security reasons.** ``` +-------------------------+---------------+ | user | host | +-------------------------+---------------+ | Implem.Pleasanter_Owner | % | ``` Since localhost connections are permitted by operations other than this procedure, there is no need to implement it in "[Common Procedure] SQL syntax for adding a user account for external connections." ``` +-------------------------+---------------+ | user | host | +-------------------------+---------------+ | root | localhost | ``` *If you use a root account, it will be automatically set when installing MySQL. *If you are setting up Pleasanter for the first time, the Owner and User accounts will be automatically set by running CodeDefiner. If you are transferring a Pleasanter database that is already in operation to another environment, there is a procedure to add the Owner and User accounts when restoring the database. [FAQ: MySQL database backup and restore procedure](faq-mysql-backup-restore) └When restoring to a different environment ## Supported Versions |Supported versions|Body| |:--|:--| |1.4.10.0 onward|Manual released to resolve an issue where Owner and User connections were rejected due to MySQL's access control function<br>*Pleasanter has been compatible with MySQL since version 1.4.9.0, but this procedure can only be performed with version 1.4.10.0 onward. |