This procedure targets the database of Pleasanter (Implem.Pleasanter) set up using the installation procedure in the user manual. ## Restoring to the Same Environment To restore the database to the same environment where the backup was taken, follow the steps below. ### Backup 1. Obtain the backup file. In this procedure, the backup will be stored under the /backup directory. ``` sudo mkdir /backup sudo chown -R <the username of the user who will execute mysqldump> /backup mysqldump -u root -p<Write the MySQL root account password without a space between -p and the password.> Implem.Pleasanter > /backup/Implem.Pleasanter.dump ``` ### Restore 1. After obtaining the backup using the same command as the backup procedure above, log in to the database using the MySQL root account. ``` mysqldump -u root -p<Write the MySQL root account password without a space between -p and the password > Implem.Pleasanter > <The output path/filename of the backup file to be obtained before restoration> mysql -u root -p<Write the MySQL root account password without a space between -p and the password.> ``` 2. In SQL, delete and recreate the database (Implem.Pleasanter), then log out from the database. ``` drop database `Implem.Pleasanter`; create database `Implem.Pleasanter` collate utf8mb4_general_ci; quit; ``` 3. Execute the restore command. In this step, the database will be restored using the backup file located under the /backup directory. ``` mysql -u root -p<Write the MySQL root account password without a space between -p and the password.> Implem.Pleasanter < /backup/Implem.Pleasanter.dump ``` ## Restoring to a Different Environment When restoring to a different environment (destination) from the environment where the backup was taken (source), follow these steps. ### Backup 1. Obtain the backup file from the source environment. This step will save the backup under the /backup directory in the source environment. ``` sudo mkdir /backup sudo chown -R <the username of the user who will execute mysqldump> /backup mysqldump -u root -p<Write the MySQL root account password without a space between -p and the password.> Implem.Pleasanter > /backup/Implem.Pleasanter.dump ``` ### Restore The database is migrated using the same steps as in "Restoring to the same environment" mentioned above. The MySQL user account used by the application to access the relevant database will be created via SQL. #### Database Migration 1. Log in to MySQL on the destination environment. ``` mysql -u root -p<The MySQL root account password should be written without a space between -p and the password. > ``` 2. Here are the steps to create the (Implem.Pleasanter) database using SQL and then log out ``` create database `Implem.Pleasanter` collate utf8mb4_general_ci; quit; ``` 3. Execute the restore command on the destination system. This will restore the database from the backup file located in the /migrate directory. ``` mysql -u root -p<The MySQL root account password should be written without a space between -p and the password. > Implem.Pleasanter < /migrate/Implem.Pleasanter.dump ``` #### Add MySQL user account 1. Log in to MySQL on the destination serve ``` mysql -u root -p<The MySQL root account password should be written without a space between -p and the password.> ``` 2. Add two MySQL user accounts using SQL and log out from the database. *This SQL creates the same MySQL user accounts on the destination database as those created in the installation instructions of the user manual ``` create user '<Owner username>'@'localhost' identified by '<Owner password string>'; grant all on `<Database name>`.* to '<Owner username>'@'localhost' with grant option; create user '<User's username>'@'localhost' identified by '<User password string>'; grant select, insert, update, delete, create routine, alter routine on `<Database name>`.* to '<User's username>'@'localhost'; quit; ``` The configurable values in the above SQL are as follows. |Configurable Part|e.g.|Description| |:--|:--|:--| |Database name|Implem.Pleasanter|Database name added in "Migrating databases" above| |Owner's user 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's password string|SetAdminsPWD|String that matches the PWD of "OwnerConnectionString" in "[Rds.json](/en/manual/rds-json)"| |User's user name|Implem.Pleasanter_User|String that matches the UID of "UserConnectionString" in "[Rds.json](/en/manual/rds-json)". Replace the #ServiceName# part with the "Database name" above| |User's password string|SetUsersPWD|String that matches the PWD of "UserConnectionString" in "[Rds.json](/en/manual/rds-json)"| ##### Example of the SQL to be created Example: If the connection information listed in "[Rds.json](/en/manual/rds-json)" is set as follows. ``` "OwnerConnectionString": "Server=localhost;Port=3306;Database=#ServiceName#;UID=#ServiceName#_Owner;PWD=SetAdminsPWD", "UserConnectionString": "Server=localhost;Port=3306;Database=#ServiceName#;UID=#ServiceName#_User;PWD=SetUsersPWD", ``` Example of MySQL user account creation SQL based on the above "[Rds.json](/en/manual/rds-json)" settings: ``` create user 'Implem.Pleasanter_Owner'@'localhost' identified by 'SetAdminsPWD'; grant all on `Implem.Pleasanter`.* to 'Implem.Pleasanter_Owner'@'localhost' with grant option; create user 'Implem.Pleasanter_User'@'localhost' identified by 'SetUsersPWD'; grant select, insert, update, delete, create routine, alter routine on `Implem.Pleasanter`.* to 'Implem.Pleasanter_User'@'localhost'; ``` #### Adding a MySQL user account to connect to MySQL from external sources If you configure a setup where the web server and database server are separated, refer to the following steps to add a MySQL user account for connecting to MySQL from external sources. 〈Additional Steps〉 [Configure the Web Server and DB Server to Use MySQL Separately](mysql-create-user-by-sql) └【Common Steps】SQL syntax for adding a user account for external connections. Note「[Configure the Web Server and DB Server to Use MySQL Separately](mysql-create-user-by-sql)」The target version of Pleasanter is ver1.4.10.0 onward. In ver1.4.9.x, Pleasanter cannot be operated on a host different from the MySQL installation location, so please be aware of this. ## Regular Backups If you want to perform regular backups and delete backup files, you can set up a cron job as described below. With the following cron settings, files older than one week will be deleted at 1:00 AM daily, and a backup will be taken at 2:00 AM. ``` sudo crontab -u <The Linux username that will execute the backu※> -e ``` *It is fine to use the same Linux user that runs Pleasanter. ### Take a database backup every day at 2:00 AM. ``` 00 02 * * * /bin/mysqldump -u root -p<The MySQL root account password should be written without a space between -p and the password. > Implem.Pleasanter > /backup/`date "+\%Y\%m\%d"`_Implem.Pleasanter.dump ``` ### Delete files older than one week every day at 1:00 AM. ``` 00 01 * * * /bin/find /backup -maxdepth 1 -mtime +7 -type f -delete ``` ## Supported Version |Supported Version|Body| |:--|:--| |1.4.9.0 onward|Created a new FAQ due to MySQL support| |1.4.10.0 onward|Resolved the issue where Owner and User connections could be rejected due to MySQL access control features<br>*Additionally, added the "Adding MySQL User Accounts for External Connections" procedure in "Restoring to a Different Environment"|