Differences

This shows you the differences between two versions of the page.

--- cloudappliance/database-troubleshooting [2019_10_01 16:24] – fix remount command after resize eric
+++ cloudappliance:database-troubleshooting [2024_02_28 01:03] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
 # Database Troubleshooting
-This document covers troubleshooting of the MariaDB database server included with the Enterprise File Fabric appliance.
+##### last updated on: Feb 7, 2023
+This document covers troubleshooting of the MariaDB database server included with the Access Anywhere appliance.
 There are two main deployment options:
- * Local database - database running on same virtual machine as the File Fabric Engine.
+ * Local database - database running on same virtual machine as Access Anywhere Engine.
- * Remote database - File Fabric Engine connecting to a database over a network.
+ * Remote database - Access Anywhere Engine connecting to a database over a network.
 A remote database could be:
- * An instance of the Enterprise File Fabric appliance configured to only run the bundled MariaDB database.
+ * An instance of the Access Anywhere appliance configured to only run the bundled MariaDB database.
  * An installation of MariaDB or compatible database
  * An external database-as-a-service compatible with MariaDB
@@ Line 23: / Line 25: @@
 ## Version Compatibility
-The Enterprise File Fabric appliance includes a MariaDB 5.5 database server. (The 5.5 version is included in the O/S upgrade channel).
+Access Anywhere appliance includes a MariaDB 10.4 database server.
-Supported databases (as of Enterprise File Fabric 1705.00 through at least 1803.03)
+Supported databases (as of Access Anywhere 1705.00 through at least 1803.03)
-  * MariaDB 5.5, 10.0, 10.1
+  * MariaDB 10.4
-  * MySQL 5.5, 5.6
 These compatible cloud database are supported. Please note versions.
-  * Amazon RDS for MySQL 5.5, 5.6
+  * Amazon RDS for MariaDB 10.4
-  * Google Cloud SQL for MySQL 5.5, 5.6
 These cloud database versions are NOT supported:
-  * MariaDB 10.2
   * MySQL 5.7
   * Amazon Aurora
@@ Line 54: / Line 53: @@
 ### Database Connection Settings
-To check what database an Enterprise File Fabric Engine is using look at the config.inc.php file.
+To check what database an Access Anywhere Engine is using look at the config.inc.php file.
     grep '$db' /var/www/smestorage/public_html/config.inc.php
@@ Line 85: / Line 84: @@
 ## Connecting across a Network
-For security the database included with Enterprise File Fabric cannot be connected to over a network. That is, it can only be accessed by processes running on the same machine.
+For security the database included with Access Anywhere cannot be connected to over a network. That is, it can only be accessed by processes running on the same machine.
 This section covers settings that need to be changed for the database to be connected to from another machine. Please consult your IT or network administrator for other connectivity issues.
@@ Line 91: / Line 90: @@
 ### Verify Access to Port
-If the database is remote (the Enterprise File Fabric connects to the database over a network) verify network access from the server(s) hosting the Enterprise File Fabric Engine(s):
+If the database is remote (Access Anywhere connects to the database over a network) verify network access from the server(s) hosting Access Anywhere Engine(s):
     curl hostname:3306
@@ Line 120: / Line 119: @@
 Comment out **bind-address 127.0.0.1** which was telling mysql to only accept local connections. Adding a line with **::** allows incoming IPv4 and IPv6 connections. A setting of **0.0.0.0** can be used instead, which is also the default. It will allow IPv4 but not IPv6 connections:
-    # SME managed file, do not edit directly!
+    # NAA managed file, do not edit directly!
     [mysqld]
     #bind-address = 127.0.0.1
@@ Line 161: / Line 160: @@
 We typically recommend tools from the hypervisor or third-parties to backup the appliance and database. In some cases it’s helpful to use native tools.
-The File Fabric database is named **smestorage** and stores data in of InnoDB and MyISAM tables.
+The Access Anywhere database is named **smestorage** and stores data in of InnoDB and MyISAM tables.
 For more information see https://mariadb.com/kb/en/library/backup-and-restore-overview/.
@@ Line 228: / Line 227: @@
     * Web File Manager (GUI) - Organization > Audit Logs > Clear Audit Logs (internally 500 at a time)
-    * MySQL (be sure to pause mariadb via systemctl)
+    * MySQL (be sure to pause mariadb via systemctl)- Preserve Old Logs
-      * RENAME TABLE us\_logs TO us\_logs\_date2018; CREATE TABLE us\_logs LIKE us\_logs\_date2018;
+      * RENAME TABLE or\_logs TO or\_logs\_date2021; CREATE TABLE or\_logs LIKE or\_logs\_date2021;
-   * MySQL
+   * MySQL - Remove Old Logs
-    * TRUNCATE us\_logs;
+    * TRUNCATE or\_logs;
   * **us\_logs** - Login audit log. Can be cleared:
-    * MySQL (be sure to pause mariadb via systemctl)
+    * MySQL (be sure to pause mariadb via systemctl))- Preserve Old Logs
-      * RENAME TABLE us\_logs TO us\_logs\_date2018;  CREATE TABLE us\_logs LIKE us\_logs\_date2018;
+      * RENAME TABLE us\_logs TO us\_logs\_date2021;  CREATE TABLE us\_logs LIKE us\_logs\_date2021;
-    * MySQL
+    * MySQL  - Remove Old Logs
       * TRUNCATE us\_logs;
@@ Line 264: / Line 263: @@
 ## Increasing Database Size
-Another way to deal with a full database is to allocate more space. The File Fabric database, by default, uses the local filesystem for storage. Allocating more space to the database involves increasing the space available on the filesystem. This is typically done by extending one of the disk partitions of the virtual machine.
+Another way to deal with a full database is to allocate more space. The Access Anywhere database, by default, uses the local filesystem for storage. Allocating more space to the database involves increasing the space available on the filesystem. This is typically done by extending one of the disk partitions of the virtual machine.
 <WRAP center round important 100%>
@@ Line 323: / Line 322: @@
     mysqlcheck smestorage
-## Repair Database
+## Repair Tables
-From the command line run from the data folder:
+From mysql shell run repair table on each corrupted table.
+    REPAIR TABLE  tbl_name [, tbl_name]
+You can also run for all MyISAM tables:
     cd /var/lib/mysql/smestorage
     myisamchk --force --fast --update-state --key_buffer_size=48G --read_buffer_size=128K --write_buffer_size=128K --sort_buffer_size=48G *.MYI
+See also: https://mariadb.com/kb/en/repair-table/
+## Convert to GTID for v2006+
+Access Anywhere HA/DR environments set up prior to v2006 did not leverage GTID for database replication.
+Starting with v2006 and greater, GTID is the recommended method for data migration.
+<WRAP center round important 100%>
+Warning: This work will bring down Access Anywhere. As such you should run this only during a planned maintenance window.
+</WRAP>
+### Enable GTID in the Access Anywhere appliances:
+As the root user run the following on every database node:
+<code>
+ echo "gtid_strict_mode=1" >> /etc/my.cnf.d/localhost.cnf
+ systemctl restart mariadb
+</code>
+### Change replication to GTID Based
+Please use this only if you currently have replication running and both nodes are currently in sync.
+On both nodes (secondary first than primary), in the 'mysql' console run the following:
+<code>
+STOP SLAVE;
+CHANGE MASTER TO MASTER_HOST=<ip of the other db node>, MASTER_USER="repl_user", MASTER_USE_GTID=slave_pos;
+START SLAVE;
+</code>
+### Reseed using GTID Based Replication
+Once you have made the configuration change above and restarted MariaDB, you can proceed with reseeding the databases and re-enabling replication.