Troubleshoot MySQL Performance

Login to MySQL

mysql -u root -p

Show Active processes

SHOW PROCESSLIST;

or to list all the processes use “FULL”

SHOW FULL PROCESSLIST;

Run MySQL optimizer

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl ./mysqltuner.pl

MySQL tuner should give some recommendations on settings to tweak.

Recovering LibreNMS from crashed XenServer VM

Had a LibreNMS instance crash, or the VM crashed, not bootable anymore.  Was able to boot it up on a CentOS iso with rescue mode, which gave me access to the files.  So the idea is to manually copy off the LibreNMS files and LibreNMS database and import them to a new LibreNMS instance

Issues

  • Need to access the the system files
  • MySQL doesn’t start in a chroot environment, so no way to do a mysqldump —  (Has to do with systemv or something)

Steps

  1. Create new LibreNMS VM with a new instance of LibreNMS installed
  2. Gain access to the crashed system
  3. Copy over LibreNMS MySQL databases to new LibreNMS instance
  4. Copy over LibreNMS files “/opt/librenms” to new LibreNMS instance
  5. Clean up.  Set users on directories, check SELinux etc.

1. Installing New LibreNMS VM

  • Guide for Ubuntu/Debian distro’s here
  • Guide for for Fedora/CentOS/RedHat here

2. Gaining access to crashed VM

There can be a couple of ways to gain access to a crashed XenServer VM.  One of the easiest ways is to boot up in recovery mode and go through the installers rescue mode.  Guide to boot up XenServer VM in recovery mode here.

The installer rescue mode should detect the OS and mount everything.  If not you should be able to mount the root partition manually.

Once booted up, you’ll need to enable network access if your going to use sftp or scp to copy files.  There are a few different ways to do this

  • Run “dhclient” to pull an address via DHCP
  • Set a static IP address
    • Guide for Ubuntu/Debian distro’s here
    • Guide for fedora/CentOS/RedHat here

3.Copy LibreNMS Mysql Database

Backup the LibreNMS MySQL database directory

tar czvf librenms_mysql.tgz /var/lib/mysql

Use scp or sftp to copy it to the new LibreNMS instance

scp librenms_mysql.tgz user@new_LibreNMS_ip

Now on the new LibreNMS instance we need to run the following few commands

systemctl stop mariadb
rm -rf /var/lib/mysql/*
tar xzvf librenms_mysql.tgz -C /
chown -R mysql:mysql /var/lib/mysql

4. Copy LibreNMS directory

Backup the LibreNMS directory

tar czvf librenms.tgz /opt/librenms

Use scp or sftp to copy it to the new LibreNMS instance

scp librenms.tgz user@new_LibreNMS_ip

Now on the new LibreNMS instance we need to run the following few commands

rm -rf /opt/librenms/*
tar xzvf librenms.tgz -C /
chown -R librenms:librenms /opt/librenms

5. Clean up

Disable SELinux if you have not already.  Guide here

Restart apache, and start mysql.  If your on Ubuntu, the services are named apache2 and mysql

systemctl restart httpd
systemctl restart mariadb

That should get it working, if not try a reboot.

Special notes

The whole MySQL directory needs to be copied, there are innodb files that will keep MySQL from starting if they are not copied.

There is some good info here
https://stackoverflow.com/questions/1795176/how-to-change-mysql-data-directory

Freeradius unauthorize clients in a group

First you’ll need a group that all your disabled clients are going to.

Next add the following lines to the user file “/etc/raddb/users”.  Change SQL-Group to Group if your groups are not in a SQL database.

DEFAULT SQL-Group == "disabled", Auth-Type := Reject
 Reply-Message = "Your account has been disabled."

Save, exit and test.

This should keep all clients in the disabled group from authorizing.

Some Basic MySQL Commands

Enter Mysql

mysql -u root -p

Create Database

create DATABASE testdb;

Select Database

use testdb;

Delete Database

drop DATABASE testdb;

Drop Table

drop table table_name;

Show Tables

show tables;

Show data in table

SELECT * FROM table_name;

Create Table

CREATE TABLE contacts_table (id INT, name VARCHAR(20), email VARCHAR(20));

Insert data into table

INSERT INTO contacts_table (id,name,email) VALUES(2,"John","John83@incredigeek.com");

List one row in table

SELECT * FROM table_name LIMIT 1;

Delete Row in Table

DELETE FROM table_name WHERE row_name=data_to_delete;

Add Auto Increment to Table

ALTER TABLE  `table_name` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Create table with Date and Time timestamp

CREATE TABLE table_name (id INT, timeStamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Show MySQL Users

select * from mysql.users;

Create MySQL User

GRANT ALL ON mysqldb.* TO username@localhost IDENTIFIED BY 'password';

Delete MySQL user

DROP USER 'username'@'localhost';

Add Column to the end of MySQL Table

ALTER TABLE mysqltable ADD email VARCHAR(60);

Add Column to the begging of MySQL Table

ALTER TABLE mysqltable ADD email VARCHAR(60) FIRST;

Insert Column after Specific Column in MySQL Table

ALTER TABLE mysqltable ADD email VARCHAR(60) AFTER columnname;