Prometheus and Grafana : Monitor MySQL/MariaDB

 1.Install MySQL Exporter
#curl -s https://api.github.com/repos/prometheus/mysqld_exporter/releases/latest   | grep browser_download_url   | grep linux-amd64 | cut -d '"' -f 4   | wget -qi -
#tar xvf mysqld_exporter*.tar.gz
#sudo mv  mysqld_exporter-*.linux-amd64/mysqld_exporter /usr/local/bin/
#sudo chmod +x /usr/local/bin/mysqld_exporter

2.Checking version of mysql exporter
#mysqld_exporter  --version

3.Create a user to collect the matrices of MySQL
#mysql -u root -p

CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'StrongPassword';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';
FLUSH PRIVILEGES;
EXIT

4.Create database credentials file
#sudo vi /etc/.mysqld_exporter.cnf

[client]
user=mysqld_exporter
password=StrongPassword

5.Enable ownership permission
#chown root:prometheus /etc/.mysqld_exporter.cnf

6.Create a service for mysql exporter
#vi /etc/systemd/system/mysql_exporter.servic

[Unit]
Description=Prometheus MySQL Exporter
After=network.target
User=prometheus
Group=prometheus

[Service]
Type=simple
Restart=always
ExecStart=/usr/local/bin/mysqld_exporter \
--config.my-cnf /etc/.mysqld_exporter.cnf \
--collect.global_status \
--collect.info_schema.innodb_metrics \
--collect.auto_increment.columns \
--collect.info_schema.processlist \
--collect.binlog_size \
--collect.info_schema.tablestats \
--collect.global_variables \
--collect.info_schema.query_response_time \
--collect.info_schema.userstats \
--collect.info_schema.tables \
--collect.perf_schema.tablelocks \
--collect.perf_schema.file_events \
--collect.perf_schema.eventswaits \
--collect.perf_schema.indexiowaits \
--collect.perf_schema.tableiowaits \
--collect.slave_status \
--web.listen-address=0.0.0.0:9104

[Install]
WantedBy=multi-user.target

7.Enable and start the mysql exporter service
#systemctl daemon-reload
#systemctl enable mysql_exporter
#systemctl start mysql_exporter
#systemctl status mysql_exporter

8.Configure the endpoint in “Prometheus.yaml” file

# mysql exporter
  - job_name: "mysqld"
    metrics_path: '/metrics'
    scheme: http
    static_configs:
      - targets: ["X.X.X.X:9104"]

9.Import dashboard from Grafana.com with ID:7362

source : https://shrihariharidas73.medium.com/unlocking-database-insights-monitoring-mysql-with-prometheus-and-grafana-ddd2c4f01929

Read More

MySQL : MySQL80 Service Started and Then Stopped on Windows Server

issue : “The MySQL80 service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.”

solutions: 

my.ini config file get HEX characters added to the beginning of the file. This causes the MySQL service to fail when it tries to start. The solution is to remove these HEX characters and then start the service again.

1. using Notepad++ (must install hex view plugin) open the my.ini file in HEX view

2. remove the HEX character ef bb bf then save the my.ini file

3. try restart the MYSQL80

source : https://madbray.com/mysql80-service-started-and-then-stopped-fixed/

Read More

MySQL : Master-Slave Replication

MySQL Server (Master) IP : 192.168.0.1
MySQL Server (Slave) IP : 192.168.0.2

1. Install MySQL in both server

2. Configure the MySQL Server (Master)

#vi /etc/my.cnf.d/mysql-server.cnf

- Add the following lines under the [mysqld] section.

server-id=1
log_bin=mysql-bin 

#sysemctl restart mysqld 

- Create a database USER that will be used to binf the master and slave.

#mysql -u root -p 

mysql> CREATE USER 'replica'@'192.168.0.2' IDENTIFIED BY 'XXXXXX';
mysql> GRANT REPLICATION SLAVE ON *.*TO 'replica'@'192.168.0.2'; 
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS\G
mysql> EXIT;

3. Configure the MySQL Server (Slave)

#vi /etc/my.cnf.d/mysql-server.cnf

- Add the following lines under the [mysqld] section.

server-id=2
log_bin=mysql-bin 
binlog_do_db = exampledb #for specifies the database to be replicated.

#sysemctl restart mysqld 

- Configures the Slave node to replicate from the Master node

#mysql -u root -p 
#mysql> STOP SLAVE;
#mysql> CHANGE MASTER TO
MASTER_HOST='192.168.0.1', 
MASTER_PORT=3306,
MASTER_USER='replica' ,
MASTER_PASSWORD='XXXXXX',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1232; 
#mysql> START SLAVE;

4. Testing MySQL Master-Slave replication

Master

#mysql -u root -p 
#mysql> CREATE DATABASE replication_db; 
#mysql> SHOW DATABASES;

Slave

#mysql -u root -p 
#mysql> SHOW DATABASES;

Source:

• http://www.tecmint.com/how-to-setup-mysql-master-slave-replication-in-rhel-centos-fedora/
• https://www.howtoforge.com/how-to-skip-certain-errors-in-mysql-replication
  

Read More

MySQL : Access denied for user 'root'@'localhost

Issue:

mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Solution: 

1.Edit /etc/my.cnf or /etc/mysql/my.cnf, depending on your distro.
2.Add skip-grant-tables under [mysqld]
3.Restart Mysql
4.You should be able to login to mysql now using the below command 

#mysql -u root -p

5.Flush privileges. 

#mysql> flush privileges;

6.Set new password by 

#mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';

7.Go back to /etc/my.cnf and remove/comment skip-grant-tables
8.Restart Mysql
9.Now you will be able to login with the new password #mysql -u root -p 

Source: https://stackoverflow.com/questions/41645309/mysql-error-access-denied-for-user-rootlocalhost

Read More

MySQL : Install MySQL 8.0 on CentOS 8/7

1. Adding the MySQL Yum Repository

Centos 8 - #wget https://repo.mysql.com/mysql80-community-release-el8-1.noarch.rpm
Centos 7 - #wget https://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm
Centos 6 - #wget https://dev.mysql.com/get/mysql80-community-release-el6-1.noarch.rpm

2. Installing Latest MySQL Version

#yum install mysql-community-server

3. Installing MySQL Release Series

#yum-config-manager --disable mysql57-community
#yum-config-manager --disable mysql56-community

4. Starting the MySQL Server

#service mysqld start
#service mysqld status
#mysql --version

5. Securing the MySQL Installation

#grep 'temporary password' /var/log/mysqld.log
#mysql_secure_installation

6. Connecting to MySQL Server

#mysql -u root -p

7. Updating MySQL with Yum

#yum update mysql-server 

Source: 

• https://www.tecmint.com/install-latest-mysql-on-rhel-centos-and-fedora/
  

Read More

MySQL : Reset root password

Solution:

1.Log in to MySQL console with root user  

#mysql -u root -p

2.Created a new user

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

3.Grant all privileges to the new user:

mysql> GRANT ALL PRIVILEGES ON *.* To 'user'@'localhost';

4.Change the Authentication Plugin with the password: 

mysql> ALTER USER user IDENTIFIED WITH mysql_native_password BY 'password';

Source:

• https://www.tecmint.com/reset-root-password-in-mysql-8/

Read More

MySQL : mysql_connect(): Too many connections

Issue:

mysql_connect(): Too many connections

Solutions:

1. directly connect to the mySQL server (via localhost), and perform the query: SET GLOBAL max_connections = 1024; to change the connection limit at runtime (no downtime).
2. make your change permanent, and edit the /etc/mysql/my.cnf (or similar) and add the line max_connections = 1024; line within the [mysqld] section; then restart if you couldn't do the live change.

Source: http://stackoverflow.com/questions/730953/how-to-fix-mysql-connect-too-many-connections

Read More

MYSQL : "Host is blocked because of many connection errors"

Error : "Host is blocked because of many connection errors"

Solutions : #mysql -u root -p -e 'flush hosts'


source : http://dev.mysql.com/doc/mysql/en/blocked-host.html

Read More

MYSQL " Table 'mysql.servers' doesn't exist at ../web-lib-funcs.pl

Problems

Table 'mysql.servers' doesn't exist at ../web-lib-funcs.pl

Solution

#mysql_upgrade -u root -p

OR

#mysql_upgrade -u root -h localhost -p --verbose --force

Then

Restart MySQL Server #/etc/init.d/mysqld

Source :

• http://tonkersten.com/2012/08/126-mysql-backup-error/

Read More

Cacti " ./syslog/syslog_incoming' is marked as crashed and should be repaired

Error:
[ERROR] /usr/libexec/mysqld: Table './syslog/syslog_incoming' is marked as crashed and should be repaired

Solution:

mysqlcheck --auto-repair --databases syslog
mysqlcheck --auto-repair --databases cacti

#mysqlcheck --auto-repair --databases syslog -u root -p

syslog.syslog
warning : 2 clients are using or haven't closed the table properly
status : OK
syslog.syslog_alert OK
syslog.syslog_incoming
warning : Table is marked as crashed
warning : 2 clients are using or haven't closed the table properly
error : Record at pos: 1062960 is not remove-marked
error : record delete-link-chain corrupted
error : Corrupt
syslog.syslog_remove OK

Repairing tables

source : 

http://forums.cacti.net/about26746.html
http://www.thegeekstuff.com/2011/12/mysqlcheck/

Read More

MySQL : Cannot load from mysql.proc. The table is probably corrupted

Error : Cannot load from mysql.proc. The table is probably corrupted

Solution :

#mysql_upgrade -p

Source :

• http://www.robsearles.com/2011/02/fix-cannot-load-from-mysql-proc-the-table-is-probably-corrupted/


• http://www.myguysolutions.com/2013/10/30/how-to-resolve-mysql-error-code-1548-cannot-load-from-mysql-proc-the-table-is-probably-corrupted/

Read More

MySQL error : Another MySQL daemon already running with same Unix socket

1. Stop mysql service

#service mysqld stop

2. Move

mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak

3. Start mysql service

#service mysqld start

Source :

• http://www.technouz.com/1951/fix-error-linux-another-mysql-daemon-already-running-with-the-same-unix-socket/

Read More

MySQL: Repair table database of Nagios

Problems

nagios error : ./nagios/nagios_servicechecks' is marked as crashed and should be repaired

Solutions:

#mysqlcheck -p --auto-repair nagios nagios_hoststatus;

Enter password:
nagios.nagios_hoststatus
warning : Table is marked as crashed
warning : 3 clients are using or haven't closed the table properly
error : Found 8 keys of 9
error : Corrupt

Repairing tables
nagios.nagios_hoststatus
warning : Duplicate key for record at 556 against record at 268
warning : Number of rows changed from 9 to 8
status : OK

source :http://alexmoralessatorres.com/2011/07/29/mysql-reparar-tabla-de-base-de-datos-de-nagios/

Read More

MYSQL : /usr/libexec/mysqld: Can’t create/write to file ‘/tmp/’ (Errcode: 13)

Problems :

121129 10:15:13 InnoDB: Using Linux native AIO
/usr/libexec/mysqld: Can't create/write to file '/tmp/ibTB92a6' (Errcode: 13)
121129 10:15:13 InnoDB: Error: unable to create temporary file; errno: 13
121129 10:15:13 [ERROR] Plugin 'InnoDB' init function returned error.
121129 10:15:13 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
121129 10:15:13 [ERROR] Unknown/unsupported storage engine: InnoDB
121129 10:15:13 [ERROR] Aborting

121129 10:15:13 [Note] /usr/libexec/mysqld: Shutdown complete

 

/etc/init.d/mysql start command returned following output:

[root@nms-sa ~]# /etc/init.d/mysqld start
MySQL Daemon failed to start.
Starting mysqld: [FAILED]

 

Solutions :

# chown root:root /tmp
# chmod 1777 /tmp
# /etc/init.d/mysqld start

 

Source : http://www.cyberciti.biz/faq/mysqld-innodb-error-unable-to-create-temporary-file/

Read More

MySQL : Remote Error

Resolved:
# edit /my.ini

Add:
[mysqld]
skip-name-resolve

OR

Disable:
# bind-address = 127.0.0.1

 

User Privilege:

add %

Read More

Quick PHP code

Quick PHP code to test ldap connection

$ldap = ldap_connect(“domain.com”);
$username=”username@domain.com”;
$password=”password”;

if($bind = ldap_bind($ldap, $username,$password ))
echo “logged in”;
else
echo “fail”;
echo “
done”;
?>

Quick PHP code to test mysql database connection

echo “trying
”;
$link = mysql_connect(’123.123.123.123′, ‘username’, ‘password’);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
else
{echo “connected”;}
?>

Read More