Configuring MySQL

The following instructions are for PageSeeder v6 or higher,  MySQL 8.4 and Rocky 9 (or equivalent Red Hat family distributions).

Install MySQL 8.4

If this is a new installation of PageSeeder v6 or higher, it’s generally best to install the latest release of MySQL 8.4 from https://www.mysql.com . If you are using PageSeeder v5 or must install an earlier MySQL version see Configuring MySQL 5.7.

Install the repository RPM by entering:

# dnf install -y https://dev.mysql.com/get/mysql84-community-release-el9-1.noarch.rpm

Install MySQL:

# dnf install -y mysql-community-server

Start and enable the service:

# systemctl start mysqld
# systemctl enable mysqld

Secure MySQL

The default MySQL security settings are vulnerable to attacks, but you can improve security by running the mysql_secure_installation script, which modifies default options and strengthens the database.

MySQL 8.4 generates a temporary password which you can get with

# grep 'A temporary password is generated' /var/log/mysqld.log | tail -1

Run the following command in your shell to start the script:

# mysql_secure_installation

Then:

1. Enter the temporary root password generated by MySQL
2. Enter the new root password – the one you intend to use, and keep a copy in secret
3. Answer ‘N’ to changing the root password since you’ve just done it!
4. Answer ‘Y’ to all the other questions.

Changing where data is stored

On Linux, MySQL usually stores data under /var/lib/mysql, but if you want to use mounted storage for PageSeeder (for example storage mapped to the /opt folder), you can configure MySQL to store data there as well. To do this, follow these steps:

Stop MySQL by entering:

# systemctl stop mysqld

Move the existing MySQL data to the new location:

# mv /var/lib/mysql /opt/mysql

Edit the /etc/my.cnf file as follows:

[mysqld]
...
datadir=/opt/mysql
socket=/opt/mysql/mysql.sock
...
[client]
socket=/opt/mysql/mysql.sock

Start MySQL by entering:

# systemctl start mysqld

# semanage fcontext -a -t mysqld_db_t '/opt/mysql(/.*)?'
# restorecon -Rv /opt/mysql

Install the JDBC driver

The PageSeeder app requires a JDBC driver to communicate with MySQL. PageSeeder v6 or higher requires Connector/J v8.0.23 or higher. Download the latest version of MySQL Connector/J (JDBC Driver) for your MySQL and extract it on your server. 

• On Windows, it can be downloaded from https://www.mysql.com  . Subject to Oracle/MySQL website changes, Connector/J is under ‘MySQL Community Downloads’ and older versions are listed under ‘Archive’.
• On Linux, use the following commands (URL and version might have changed):

# wget https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-j-8.4.0.tar.gz
# tar xzvf mysql-connector-java-8.4.0.tar.gz

The PageSeeder installer asks for the location of the JDBC driver JAR file to be identified.

Tune MySQL

MySQL performance should be tuned on production servers. To do this increase the InnoDB buffer to 75% of total RAM, minus what is used by other processes (PageSeeder/java can use 20% more than its configured -Xmx heap size). For example, with 6GB total RAM and PageSeeder 2GB heap, the InnoDB buffer could be set to 2GB ( 6 x .75 - 2.4)  as follows:

Edit the /etc/my.cnf file and below [mysqld] add:

innodb-buffer-pool-size=2048M

or:

innodb-buffer-pool-size=2G

Restart MySQL by entering:

# systemctl restart mysqld

Backup and restore

To backup the PageSeeder database on Linux use a command like this:

# mysqldump --single-transaction -u root -p -r ps.sql pageseeder

Optionally compress the backup:

# gzip ps.sql

Restore with overwrite

To overwrite an existing database, first drop it, then recreate it using commands like this:

# mysql -u root -p
drop database pageseeder;
create database pageseeder character set utf8mb4;
grant all on pageseeder.* to pageseeder@localhost;
quit

To restore the PageSeeder database, use a command like this:

# mysql -u pageseeder -p pageseeder < ps.sql

or if the backup is compressed:

# gunzip -c ps.sql.gz | mysql -u pageseeder -p pageseeder

Restore with create

If no database or user exists, create them using commands like this:

# mysql -u root -p
create database pageseeder character set utf8mb4;
create user pageseeder@localhost identified by '<password>';
grant all on pageseeder.* to pageseeder@localhost;
quit

To restore the PageSeeder database use a command like this:

# mysql -u pageseeder -p pageseeder < ps.sql

or if the backup is compressed:

# gunzip -c ps.sql.gz | mysql -u pageseeder -p pageseeder