Differences

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

--- cloudappliance/database-troubleshooting [2018_07_11 17:09] – [Backups] added mention of InnoDB steven
+++ cloudappliance:database-troubleshooting [Unknown date] (current) – removed - external edit (Unknown date) 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.
-There are two main deployment options:
- * Local database - database running on same virtual machine as the File Fabric Engine.
- * Remote database - File Fabric 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 installation of MariaDB or compatible database
- * An external database-as-a-service compatible with MariaDB
-## Prerequisites
-Before you begin troubleshooting and checking the database server you should have the following information:
- * Machine passwords for smeconfiguser and root (will also allow access to a local database)
- * Remote database - credentials for database access
-## 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).
-Supported databases (as of Enterprise File Fabric 1705.00 through at least 1803.03)
-  * MariaDB 5.5, 10.0, 10.1
-  * MySQL 5.5, 5.6
-These compatible cloud database are supported. Please note versions.
-  * Amazon RDS for MySQL 5.5, 5.6
-  * Google Cloud SQL for MySQL 5.5, 5.6
-These cloud database versions are NOT supported:
-  * MariaDB 10.2
-  * MySQL 5.7
-  * Amazon Aurora
-  * Amazon RDS for MySQL 5.7
-  * Google Cloud SQL for MySQL 5.7
-### Checking Database Version
-To determine the version of the database start mysql and run the command:
-    select version();
-## Configuration
-The database uses the configuration file **/etc/my.cnf** which uses files from **/etc/my.cnf.d/**.
-The data files are located at **/var/lib/mysql/**.
-### Database Connection Settings
-To check what database an Enterprise File Fabric Engine is using look at the config.inc.php file.
-    grep '$db' /var/www/smestorage/public_html/config.inc.php
-This will return the following information:
-	var $dbuser='smestore';
-	var $dbpassword='bp85fyy';
-	var $dbname='smestorage';
-	var $dbhost='localhost';
-## Logs
-    /var/log/mariadb/mariadb.log
-The message “[ERROR] Invalid (old?) table or database name 'lost+found'” can be ignored.
-## Server Running
-Check the database is up by running this command on the machine hosting the database server:
-    systemctl status mariadb
-You should see a few lines of output including one that says, "Active: active (running) ". If you do not then the service is not running. This will prevent the Cloud File Manager from working.
-## Starting the Database
-Start the database on the machine hosting the database server:
-    systemctl start mariadb
-## 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.
-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.
-### 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):
-    curl hostname:3306
-If the port is open  success this should return something like:
-    curl: (56) Recv failure: Connection reset by peer
-.5.56-MariaDB?.......
-If the port is not accessible an error message like the following will be seen:
-    curl: (7) Failed to connect to 10.120.10.194 port 3306: Connection refused
-This could be because
- * Database is not listening on an external port
- * The server port is not open
-If the user does not have permission to connect from a host:
-    '172.16.79.1' is not allowed to connect to this MariaDB
-### Database Not Listening on Port
-Out of the box the database on the appliance is configured to only accept connections from local clients (on the same virtual machine). This can be changed with the bind-address setting. First, assuming the out of the box appliance settings haven’t been changed, edit localhost.cnf as root:
-    vi /etc/my.cnf.d/localhost.cnf
-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!
-    [mysqld]
-    #bind-address = 127.0.0.1
-    bind-address = ::
-Then restart the database:
-    systemctl restart mariadb
-### Database Server Port Not Open
-Out of the box iptables on the appliance is not configured to accept connections to port 3306.
-    vi /etc/sysconfig/iptables
-Then as root add the following line after line 21:
-    -A RH-Firewall-1-INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-Then restart iptables:
-    systemctl restart iptables
-### Logging In
-You can log into the local database through the command:
-    mysql
-### Create New User
-To create a user for external access:
-    CREATE USER 'workbench'@'%' IDENTIFIED BY 'password';;
-    GRANT ALL PRIVILEGES ON *.* TO 'workbench'@'%';
-    FLUSH PRIVILEGES;
-## Backups
-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.
-For more information see https://mariadb.com/kb/en/library/backup-and-restore-overview/.
-### Logical
-For smaller systems create a *logical* backup of the **smestorage** database using the command **mysqldump** run as root. It creates a set of SQL statements that can be executed to replicate the original database schema and data:
-    mysqldump smestorage > smestorage.sql
-Restoring or loading a database run mysql in script mode as root:
-    mysql smestorage < smestorage.sql
-### Physical
-For larger databases a physical backup is recommended. The backup file will be smaller and the database can be recovered more quickly than from SQL statements.
-Note: There are some innodb tables that may have problems participating in backup.
-[mysqlhotcopy](https://mariadb.com/kb/en/mysqlhotcopy/) performs a physical backup duplicating the on disk files. It can only be run on the same machine as the database and creates multiple files in the target folder:
-    mysqlhotcopy smestorage ~/backup
-## Health
-### Processes & Locks
-From the mysql program:
-    show full processlist;
-If there are processes waiting on locks these will be shown along with any active processes that we can presume are holding the locks.
-Review the Time field to confirm the longest running query and run the following command to kill it:
-    kill <thread_id>;
-### Number of Connections
-Execute mysqladmin from the command line as root:
-    $ mysqladmin extended-status | grep Threads
-    | Threads_connected                        | 3           |
-    | Threads_created                          | 3176        |
-    | Threads_running                          | 1           |
-##Database Full
-The database data is located at **/var/lib/mysql**. To check space on this volume (and others):
-    df -h
-This volume may run out of space for a number of reasons. To find the top 50 files above 10M:
-    find /var/lib/mysql -xdev -type f -size +10M -exec du -sh {} ';' | sort -rh | head -n50
-Either this space can be released or the partition needs to be extended.
-### Database Tables
-The following tables are most likely to become full due to regular usage or runaway processes:
-  * **or_logs** - Audit logs. Can be cleared:
-    * Web File Manager (GUI) - Organization > Audit Logs > Clear Audit Logs (internally 500 at a time)
-    * MySQL (be sure to pause mariadb via systemctl)
-      * RENAME TABLE us\_logs TO us\_logs\_date2018; CREATE TABLE us\_logs LIKE us\_logs\_date2018;
-   * MySQL
-    * TRUNCATE us\_logs;
-  * **us\_logs** - Login audit log. Can be cleared:
-    * MySQL (be sure to pause mariadb via systemctl)
-      * RENAME TABLE us\_logs TO us\_logs\_date2018;  CREATE TABLE us\_logs LIKE us\_logs\_date2018;
-    * MySQL
-      * TRUNCATE us\_logs;
-  * **fi\_files** - File metadata. Critical
-  * **io\_temp/io\_tempremovefile** - Used in provider sync.
-### Database Transaction Logs
-Though not enabled by default a customer may have turned on binary logs while setting up slave replications. The files will look something like:
-.1G    /var/lib/mysql/master-tx-log-bin.000011
-.1G    /var/lib/mysql/master-tx-log-bin.000010
-To purge these logs see
-    https://mariadb.com/kb/en/library/sql-commands-purge-logs/
-    https://dev.mysql.com/doc/refman/5.7/en/purge-binary-logs.html
-## 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.
-<WRAP center round important 100%>
-STOP: Is there a current backup of the database?
-</WRAP>
-Use the following command to determine the disk used for the mysql volume
-   $  blkid | grep sme-mysql
-   /dev/sdb1: LABEL="sme-mysql" UUID="af240e06-f1d2-4dd1-af74-daf6e7803413" TYPE=“ext4"
-In this case the Filesystem is <code>/dev/sdb1</code>
-First stop the database that is using the partition (run as root):
-    systemctl stop mariadb
-And unmount the partition:
-    umount /var/lib/mysql
-Next we will use the parted command to expand the filesystem to all available space on the disk
-    parted -s /dev/sdb resizepart 1 100%
-Next unmount:
-    umount /var/lib/mysql
-Then run a check:
-    e2fsck -f /dev/sdb1
-Extend the partition
-    resize2fs /dev/sdb1
-Run a check on the larger partition:
-    e2fsck -f /dev/sdb1
-Remount
-    mount var/lib/mysql
-Check new size
-    df -h
-Restart database:
-    systemctl start mariadb
-## Check Database
-From the command line:
-    mysqlcheck smestorage
-## Repair Database
-From the command line run from the data folder:
-    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