MySQL Database How To Guide
From Swivel Knowledgebase Wiki
Contents |
MySQL Database How To Guide
Introduction
This document outlines the necessary steps to change from an internal PINsafe database on a Primary/Secondary Master or Standalone PINsafe server to a MySQL database on one or more servers.
Prerequisites
Fully configured PINsafe server (Ensure XML repositories and admin users are unique on multiple PINsafe servers)
MySQL server or PINsafe appliance with MySQL
Baseline
PINsafe 3.5
CMI 0.9w
MySQL Server 5.0.22
Webmin 1.430
Architecture
Information is taken from the repository data source and inserted into the MySQL database. Multiple PINsafe servers can use the MySQL database for user authentication. The MySQL database is replicated to other PINsafe servers and DR sites in real time. Issues may arise if the severs lose connectivity for long periods (several days or more).
Repository Synchronisation Considerations
Multiple PINsafe servers can connect to a database. It is essential that when multiple PINsafe servers are synchronising data from the same data source they do so at different times so as to avoid the same user being created at the same time on different cluster members. This can be done by setting only one active synchronisation, or setting different times for synchronisation.
Installation
Installing a JDBC driver
If the jdbc driver is not on the PINsafe server (the MySQL driver is supplied on PINsafe appliances), it will need to be downloaded see http://dev.mysql.com/downloads/connector/j/5.1.html
Copy the file to <path to apache Tomcat>/webapps/pinsafe/WEB-INF/lib
Ensure that the permissions, and any file or group ownership are correct, i.e. the same as the other files in the group.
Restart Tomcat
Configure the PINsafe Database
Note For their data sources Master servers should run in Synchronised mode and slave servers should run in slave mode.
The following parameters need to be set for the database:
Identifier: Database Name
Class: PINsafe Java class used for database
Driver: Software driver used for communication with database
URL: Location of the database
Username: Database username
Password: Database Password
Default MySQL 5 database config on a PINsafe appliance:
Identifier MySQL5
Class: com.swiveltechnologies.pinsafe.user.database.MySQL5Database
Driver: com.mysql.jdbc.Driver
URL: jdbc:mysql://localhost/pinsafe_rep
Username: pinsafe
Password: pinsafe
Note: At this stage DO NOT set the database to MySQL5 but keep it as Internal
Create Local database admins on each PINsafe server
Ensure that the name of each XML repository is unique to each PINsafe server. Ensure that each PINsafe server has a unique admin account name. See Repository_How_to_Guide
Migrate Data into MySQL database
Migrate the users from the Primary Master into the MySQL database. This is normally carried out on a single server unless differing repositories are used. See also Migrate How to guide.
From the PINsafe Administration console select Migration/Data then select the MySQL database and enter MIGRATE, click on apply. A message indicates the data has been successfully migrated.
The logs will indicate the following messages:
Database at jdbc:mysql://localhost/pinsafe_rep
Database com.mysql.jdbc.Driver loaded successfully.
The length of time for Migration will vary between install sites, but 500 users will approximately take 2 minutes.
Select the MySQL database
Select the MySQL database
From the PINsafe Administration console select Database/General then select the MySQL database and click on apply.
The logs will indicate the following messages:
Database at jdbc:mysql://localhost/pinsafe_rep opened successfully.
Database com.mysql.jdbc.Driver loaded successfully.
Verify that the status page lists an Active database of MYSQL
Active database MySQL 5
Verifying the Installation
Check status of servers, This can be done through the CMI
MySQL testing through the CMI
How Do I tell if the PINsafe appliances are in synchronisation?
PINsafe Appliance version 2.0.14 shows the status as Synchronised.
PINsafe Appliance version 2.0.13 see the below:
On the PINsafe appliances CMI select the MySQL status option. The Primary Master and Standby Slave positions should be the same, the Standby Master and Primary Slave positions should be the same. For a DR appliance, the Primary Master and DR Slave should be the same.
If the positions are not synchronised, verify the following:
- If the system has just been started allow time for the databases to synchronise
- Ensure all Slave IO Running are Yes
- Ensure all Slave SQL Running are Yes
In the example below the PINsafe appliances are in synchronisation, Note the Master/Master Slave/Slave positions do not need to be the same, only the Master/Slave and Master/Slave:
Primary Master Position : 98 matches the Standby slave Position : 98
Standby Master Position : 282991 matches the Standby slave Position 282991
MySQL testing through the PINsafe Administration Console
Create a user on the Primary Master and verify that the user can be seen on the Standby Master. Create a user on the Standby Master and verify that the user appears on the Primary Master. Verify that the user appears on any DR appliances.
Verify that the total numbers of users and locked accounts on the Primary Master and Standby Master are the same.
Note a user who becomes locked on a DR appliance will not be reflected on the Primary Master or Standby Master.
MySQL Command line Testing
Where possible the CMI should be used for testing. The information below is provided for those familiar with MySQL commands and their use.
MySQL commands for Master/Primary/DR are given below.
Use the status command to check the status of the MySQL servers. Each of the servers has a master database and a slave database to the other server.
mysql show master status; show slave status\G;
The Slave IO state should be ‘Waiting for master to send event’ when it is idle. The values for the Position on the Primary Master should be the same as the Secondary Master Slave
The master status values for Position on the Primary Master should match that of the slave status Read_Master_Log_Position on the Secondary Master.
The master status values for Position on the Secondary Master should match that of the slave status status Read_Master_Log_Position on the Primary Master.
- On the Primary Master
mysql> show master status; +------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------+----------+--------------+------------------+ | bin.000001 | 202 | | mysql,test | +------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
- On the Secondary Master
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.130.1
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000001
Read_Master_Log_Pos: 202
Relay_Log_File: relay.000002
Relay_Log_Pos: 229
Relay_Master_Log_File: bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: pinsafe_rep
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 202
Relay_Log_Space: 229
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
ERROR: No query specified
- On the secondary Master
mysql> show master status; +------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------+----------+--------------+------------------+ | bin.000001 | 202 | | mysql,test | +------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
- On The Primary Master
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.130.2
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000001
Read_Master_Log_Pos: 202
Relay_Log_File: relay.000002
Relay_Log_Pos: 229
Relay_Master_Log_File: bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: pinsafe_rep
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 202
Relay_Log_Space: 229
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
ERROR:
No query specified
Troubleshooting
Check the PINsafe, Apache Tomcat logs.
If the the PINsafe MySQL appliances are out of synchronisation, then the following solution can be used: MySQL Appliance Database Synchronisation
Bin files are used as a transaction buffer to track record changes which are synced to the alternate server. If the server has become busier and you are suffering from connectivity issues between the two servers it would store up transactions for the amount of days set.
To manage these bin files, from within the mysql shell you can run the following commands:
SET GLOBAL expire_logs_days = 10;
SET GLOBAL max_binlog_size = 256000000;
You can also ensure that the following is set within the /etc/my.cnf file:
max_binlog_size=256000000
Error Messages
Error Connecting to Master
mysql> load data from master;
ERROR 1218 (08S01): Error connecting to master: Access denied for user 'replication'@'192.168.0.129' (using password: YES)
Change the Master SQL Server Slave IP addresses permitted to authenticate. This is done in the webmin
User Permission errors
The following may be seen when attempting to MIGRATE the database to MYSQL;
192.168.0.1 admin:Unable to open the database: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: INDEX command denied to user 'pinsafe'@'localhost' for table 'PINSAFEM'
192.168.0.1 admin:PINsafe data migration failed! com.swiveltechnologies.pinsafe.user.database.DatabaseException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: INDEX command denied to user 'pinsafe'@'localhost' for table 'PINSAFEM'
To change the pinsafe user permissions see MySQL_permissions
PINsafe data migration failed! com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Incorrect database name 'pinsafe_rep '
Wrong database name has been specified. In this case the database should have been pinsafe
Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.user.database.DatabaseException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin' at line 1
There is an issue with PINsafe and MySQL 5.5. This will be resolved in a future release of PINsafe; meanwhile, we recommend using an earlier version of MySQL.
Known Issues and Limitations
Appliance build 2.0.9 has a requirement to set pinsafe user permissions, see troubleshooting above.
Additional Information
For assistance in the PINsafe installation and configuration please firstly contact your reseller and then email Swivel Secure support at support@swivelsecure.com
