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:
- Enter the temporary root password generated by MySQL
- Enter the new root password – the one you intend to use, and keep a copy in secret
- Answer ‘N’ to changing the root password since you’ve just done it!
- Answer ‘Y’ to all the other questions.
Later, in the PageSeeder setup screen, you must enter these details under Database administrator: login name and password or as parameter for the setup.sh
shell script.
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
If MySQL doesn’t start after changing the data location, SELinux might need to be configured to allow access to the new location by entering the following:
# 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
To increase the number of Database connections, see Database Properties.
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
If PageSeeder is running on a different server to MySQL, replace @localhost
with @"%"
On Linux, if you get an error ERROR 2006 (HY000) at line 207: MySQL server has gone away
while restoring, add max_allowed_packet=32M
under [mysqld]
in the /etc/my.cnf
file and restart MySQL before trying again.