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_01_12 18:16] – steven
@@ Line 1: / Line 1: @@
 # Database Troubleshooting
+##### last updated on: Feb 7, 2023
 This document covers troubleshooting of the MariaDB database server included with the Enterprise File Fabric appliance.
@@ 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).
+The Enterprise File Fabric appliance includes a MariaDB 10.4 database server.
 Supported databases (as of Enterprise File Fabric 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 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 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+
+File Fabric 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 the File Fabric. As such you should run this only during a planned maintenance window.
+</WRAP>
+### Enable GTID in the File Fabric 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.