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 .
MySQL 8 is not yet supported by PageSeeder.
- 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
If MySQL doesn’t start after changing the data location, SELinux might need to be set to permissive
, see Troubleshooting.
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 PageSeeder v6 or higher do not use Connector/J v5 or due to a security issue. MySQL v8 is not supported but use Connector/J v8.0.23 or higher. On PageSeeder v5 Connector/J v5 must be used.
- 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
When saving edits, do NOT change the file encoding from ascii
. Doing so might prevent MySQL from starting.
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
On Amazon RDS, the only value that MUST be set using DB parameter groups is:
character-set-server=utf8mb4
To increase the number of Database connections, see Database Properties.
Backup and restore
When manually creating a PageSeeder database, DO NOT use uft8mb4
, DO use utf8
as shown in the following command.
However, to backup use --default-character-set=utf8mb4
and -r
flag.
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
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.