Configuring MySQL

Install MySQL 5.7

PageSeeder supports MySQL 5.6.4 or higher and if this is a new installation, it’s generally best to install the latest release of 5.7 from https://www.mysql.com .

• On Linux use commands (CentOS 6) like:

$ rpm -Uvh https://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm

• Or (CentOS 7)

$ rpm -Uvh https://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm

$ yum install mysql-server

• Or (CentOS 8) disable the MySQL 8 repository and enable MySQL 5.7 using steps similar to these .

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:

$ service mysqld stop

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:

$ service mysqld start

Upgrading 5.6 to 5.7

Upgrading from MySQL 5.6 to 5.7 requires adding (or modify) the following line in my.ini or my.cnf:

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Also ensure that mysql-connector-java-8.0.23.jar or newer (mysql-connector-java-5.1.39.jar or newer for PageSeeder v5) is in pageseeder/webapp/WEB-INF/lib and drivers folders.

Only if using mysql-connector-java-5.x.x.jar edit the following file:

pageseeder/webapp/WEB-INF/config/database.properties

To the end of the DBURL add:

?useSSL=false

For example:

DBURL=jdbc:mysql://localhost/pageseeder?useSSL=false

Restart MySQL and then PageSeeder after making these changes.

Install the JDBC driver

The PageSeeder app requires a JDBC driver to communicate with MySQL. 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://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.29.tar.gz
$ tar xzvf mysql-connector-java-8.0.29.tar.gz

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

Use UTF-8mb4 character set

To correctly process special characters—MySQL must be configured for UTF-8mb4. To do this, edit the following:

• On Windows (to see the ProgramData folder might require showing “hidden” items):

\ProgramData\MySQL\MySQL Server 5.7\my.ini

• On Linux:

/etc/my.cnf

After [mysqld] (add it if it doesn’t exist), add or modify the following line:

character-set-server=utf8mb4

Change any other character-set properties to utf8mb4.

Add (or modify) the following line as shown:

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

If spare RAM is available increase the InnoDB buffer to 80% of available RAM (minus what is used by  other processes). For example, with 6GB total RAM and PageSeeder using 1GB, the InnoDB buffer could be set to 4GB by adding:

innodb-buffer-pool-size=4G

On Windows restart MySQL using the Services Control Panel, on Linux type:

$ service mysqld restart

Or

$ service mysql restart

character-set-server=utf8mb4

Backup and restore

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

$ mysqldump --single-transaction -u root -p --default-character-set=utf8mb4 -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 utf8;
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 utf8;
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