Troubleshoot MySQL

MySQL's role in FOG

MySQL is used to hold information and metadata about hosts, images, groups, snapins, various web-level fog settings, and other things. It does not house the actual image data. In newer Linux, it's being phased out in favor of MariaDB. All commands for MySQL will work for MariaDB.

Testing MySQL

On your FOG server, you can check passwords and the existence of the FOG database by issuing these commands:

No Password:

mysql
use fog
exit

Test with a username and password:

mysql -u UserNameHere -p PasswordHere
use fog
exit

MySQL Service

CentOS / RHEL / Fedora

Check status:

systemctl status mysql

Restart service:

systemctl restart mysql

Debian / Ubuntu

Check status:

Debian and Ubuntu status command here

Restart service:

service mysql restart

MySQL Config File

The location of this file varies based on the distribution of Linux you're using. Usually it's called my.cnf and it's probably best to just issue a search command for the file and then figure out which one of the results it is, generally you can pick it out quickly based on the path that the file is in. Here's how to search:

find / | grep /my.cnf

Here is a sample output from Fedora 23:

/etc/my.cnf
/etc/my.cnf.d
/etc/my.cnf.d/mysql-clients.cnf
/etc/my.cnf.d/client.cnf
/etc/my.cnf.d/mariadb-server.cnf
/etc/my.cnf.d/tokudb.cnf

In this case, the first result is the right file, /etc/my.cnf Looking into this particular file (on Fedora 23), there is a line that says !includedir /etc/my.cnf.d This means any files in that directory, /etc/my.cnf.d are included in MySQL's configuration.

Manually export / import Fog database

Export:

mysqldump -u USERNAME -p PASSWORD -h HOSTNAME fog > fogDB.sql

Import:

mysql -u USERNAME -p PASSWORD -h HOSTNAME fog < fogDB.sql

Example using root and 13375p3@k as the password on the local host.

#Exporting my DB locally on my FOG server...
mysql -u root -p 13375p3@k fog > fog_backup.sql
#
#Here is an import example...
mysql -u root -p 13375p3@k fog < fog_backup.sql
#
#Here is an example of exporting a remote DB to your current directory, where x.x.x.x would be replaced by the IP of the remote system...
mysql -u root -p 13375p3@k -h x.x.x.x fog > fog_backup.sql

Reset MySQL fog user and password

mysql
DROP USER 'fog'@'localhost';

#Create the user using a password:
CREATE USER 'fog'@'localhost' IDENTIFIED BY 'YourPasswordGoesHere';

#Create the user without a password:
CREATE USER 'fog'@'localhost';

GRANT ALL ON fog.* TO 'fog'@'localhost';
exit

Enable remote mysql access

Generally this isn't needed, as a "fogstorage" user is already setup with remote access for storage node purposes by the FOG Installer (In 1.3.0). However if you wanted ease of interaction to do testing or development on the FOG database via remote access with a third party tool (such as HeidiSQL), then you'd need to enable remote access on an account, or make a new account and enable remote access for it.

mysql
GRANT ALL PRIVILEGES ON fog.* TO 'MysqlUserNameGoesHere'@'%' IDENTIFIED BY 'PasswordHere' WITH GRANT OPTION;

If you want to restrict access to a specific IP, replace the percent symbol (%) with an IP.

Some systems have a bind address set. You can disable that in the my.cnf file. Search for that file with this:

find / | grep /my.cnf

Comment out these lines with a hash tag #

#skip-networking
#bind-address = 127.0.0.1

=Increase maximum simultaneous MySQL connections

If you're imaging 50 to 200 computers simultaneously, or simply have a very large amount of hosts, you'll find the below settings helpful.

mysql
SET GLOBAL max_connections = 200;
flush hosts;

Open the my.cnf file and make the below change.

Fedora 20,21,22:

/etc/my.cnf

Ubuntu 14:

/etc/mysql/my.cnf

max_connections = 200

then restart mysql

Repair broken database

If you've filled the partition that contains the MySQL Database file completely full, it's highly likely that your MySQL database is now corrupted. You can check for issues and attempt to repair them like this:

mysqlcheck -r fog

You really should try to avoid housing your images and snapins on an important partitions, and should instead place images and snapins on their own partition. Setting up a partition for images and snapins is most easily done during OS installation - but can be done afterwards albeit there are many more steps and many more chances to make mistakes.

Ubuntu 13.04 14.04 15.04 and higher with FOG 1.2.0

Ubuntu has issues with mysql. Particularly anything 13 and greater seems to be hugely a problem from my experience.

When this happens, fog will run normally after the server boots for 10 or 15 minutes and then mysql crashes, which forces a "Update the Database Schema" message when trying to use fog, and the schema updater does not fix the problem.

To test if this is the issue, simply try to restart MySQL (please note this is only temporary) and see if the problem goes away:

sudo service mysql restart

A fix has been reported with this issue. Here are the instructions to fix it:

Open this file:

sudo vi /var/www/fog/lib/fog/Config.class.php

Notate both the host, username & password and then close the above file. Open this file:

sudo vi /opt/fog/.fogsettings

Fill in the following portions:

• snmysqluser="{root}"

• snmysqlpass="{PasswordIfYouHaveOne}"

• snmysqlhost="{localhost}"

Reset the mySQL database password to be what is in the config files.

Then run this:

sudo dpkg-reconfigure mysql-server-5.5

Enter in the new password when prompted.

If you still have issues re-run the installer.

The DB and Multicast

For issues with Multicast, the DB tables associated with that could be dirty. You'll know this is the case if clients just sit at the partclone screen doing nothing.

TRUNCATE TABLE multicastSessions; 
TRUNCATE TABLE multicastSessionsAssoc; 
DELETE FROM tasks WHERE taskTypeId=8;

Via MySQL cli or phpmyadmin (easy to install)

Please also see: Troubleshoot Downloading - Multicast and Multicast