Open a Command Prompt and run the following command to see the current configuration file path. This will display the paths where MySQL looks for its configuration file.
mysql --help | findstr "my.ini"
I have tried to document step by step how to setup Percona Monitoring and Management for MySQL on Rocky Linux. I hope it will be useful for those who need it!
1-Check Environment
2-Install PMM Server
2.1-Disable Firewall & Selinux
2.2-Pull Latest PMM Docker Image
2.3-Create Docker Container to Keep Data
2.4-Check PMM Server IP
2.5-Go to PMM Login Page and Change Default Password
3-Install PMM Client on Target DB Server
3.1-Disable Firewall & Selinux
3.2-Download and Install Percona Repo Package
3.3-Install Percona Monitoring and Management Client
3.4-Verify the Installation
4-Register PMM Client with PMM Server
5-Create PMM User for Monitoring on Target Database
6-Register the Server on Target Server to Enable Monitoring
6.1-Check Available Options
6.2-Add MySQL to Monitoring
6.3-Check pmm-admin list
7-Check PMM Portal for Newly Added Database
8-PMM Server Shutdown/Startup Commands
| ROLE | HOSTNAME | IP | OS |
| PMM Server | rocky95-pmm | 192.168.1.160 | Rocky Linux 9.5 |
| MySQL Server | rocky95-mysql8 | 192.168.1.161 | Rocky Linux 9.5 |
# Disable & Check Firewall
sudo systemctl status firewalld
sudo systemctl stop firewalld
sudo systemctl disable firewalld
sudo systemctl status firewalld
# Disable & Check Selinux
vi /etc/selinux/config
SELINUX=disabled
reboot
sestatusyum install podman-docker
docker pull percona/pmm-server:2
docker images
I will keep PMM Data in /data/pmm directory.
mkdir -p /data/pmm
docker create --volume /data/pmm --name pmm-data percona/pmm-server:2 /bin/true
docker volume ls
docker run --detach --restart always --publish 443:443 --volumes-from pmm-data --name pmm-server percona/pmm-server:2
docker ps
docker inspect pmm-data | egrep "Source|Destination"
ip addr
https://192.168.1.160/graph/login
# Disable & Check Firewall
sudo systemctl status firewalld
sudo systemctl stop firewalld
sudo systemctl disable firewalld
sudo systemctl status firewalld
# Disable & Check Selinux
vi /etc/selinux/config
SELINUX=disabled
reboot
sestatussudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpmsudo yum install pmm2-clientsudo pmm-admin -v
## Syntax
sudo pmm-admin config --server-insecure-tls --server-url=https://admin:<password>@pmm.example.com
## Run Below Command
## My PMM Server Ip is 192.168.1.160
## Use Own Admin Password
sudo pmm-admin config --server-insecure-tls --server-url=https://admin:admin2025@192.168.1.160:443
##### For MySQL 8.0 #####
# 192.168.1.161 is my target MySQL database to be monitored
mysql -u root -p
CREATE USER 'pmm'@'192.168.1.161' IDENTIFIED BY 'Welc0me1!' WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD, BACKUP_ADMIN ON *.* TO 'pmm'@'192.168.1.161';
FLUSH PRIVILEGES;
pmm-admin add --help
# Use Your Own Host IP Address
sudo pmm-admin add mysql --username=pmm --password=Welc0me1! --host=192.168.1.161 --port=3306 --query-source=perfschema
pmm-admin list
https://192.168.1.160/graph/login
# Shutdown PMM Server
docker ps -a
docker stop pmm-server
docker ps -a
# Startup PMM Server
docker ps -a
docker start pmm-server
docker ps -a
# Check PMM Home Page
https://192.168.1.160/graph/login
I have tried to document step by step how to enable and check slow query logging in MySQL. I hope it will be useful for those who need it!
1-Check the Threshold for Slow Queries
2-Check Slow Query Logging Parameter
3-Enable Slow Query Logging
4-Restart the MySQL Server to Apply the Changes
5-Ensure slow_query_log is Enabled
6-Run Sample SQL to Test the Slow Query Log
7-Check the Log File to Confirm that the Query is Logged
Note: long_query_time variable value is seconds.
mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
-- Change the Threshold for Slow Queries (If Needed)
SET GLOBAL long_query_time = 1.0; --> set as 1 second
SET GLOBAL long_query_time = 0.5; --> set as 500msmysql> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)Open the MySQL configuration file and add the following lines under the [mysqld] section.
# For DEBIAN Based Linux
vi /etc/mysql/mysql.conf.d/mysqld.cnf
# For REDHAT Based Linux
vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10# For DEBIAN Based Linux
sudo systemctl restart mysql
sudo systemctl status mysql
# For REDHAT Based Linux
sudo systemctl restart mysqld
sudo systemctl status mysqldmysql> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.01 sec)mysql> SELECT SLEEP(11); -- Example query that takes 11 seconds
+-----------+
| SLEEP(11) |
+-----------+
| 0 |
+-----------+
1 row in set (11.00 sec)root@ubuntu2404-mysql-master:~# more /var/log/mysql/mysql-slow.log
/usr/sbin/mysqld, Version: 8.0.40-0ubuntu0.24.04.1 ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 2025-01-09T10:12:28.408559Z
# User@Host: root[root] @ localhost [] Id: 8
# Query_time: 11.001798 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
SET timestamp=1736417537;
SELECT SLEEP(11);
root@ubuntu2404-mysql-master:~#I have tried to document step by step how to install MySQL for Redhat-Based Linux (Rocky Linux). I hope it will be useful for those who need it!
sudo yum update -ysudo yum install mysql-serverrpm -qa | grep mysql
sudo systemctl status mysql.service
sudo systemctl enable mysqld.service
sudo systemctl start mysqld.service
sudo systemctl status mysqld.service
sudo mysql
sudo mysql_secure_installationsudo mysql -u root -p
In order to perform some tests in your MySQL cluster environment, you can create a new clone server by shutting down the services in the slave database, then promote it to the master role and perform your tests by following the steps below.
1. Stop Replica & MySQL Service on Slave Server
2. Shutdown VM (Optional) & Clone Slave Server
3. Start Slave Server & Check the Replica Status
4. Start Newly Cloned Slave Server & Check the Replica Status
5. Stop Replication on the Newly Cloned Slave Server
6. Reset the Replica Configuration on Newly Cloned Slave Server
7. Check Write Operations on Newly Cloned Slave Server
8. Configure IP Address on Newly Cloned Slave Server
9. Check IP Address and Port on Newly Cloned Slave Server
10. Update Application Connection Strings & Test It
# sudo mysql -u root -p
mysql> STOP REPLICA;
mysql> exit
# systemctl stop mysql.service
# systemctl status mysql.service
Depending on the virtualization environment technology, MySQL Slave Server VM can be cloned either while running or closed.
Run the following command on the replica to ensure it is up and running.
# sudo mysql -u root -p
mysql> SHOW REPLICA STATUS\G
Run the following command on the replica to ensure it is up and running.
# sudo mysql -u root -p
mysql> SHOW REPLICA STATUS\GStop the replica threads to ensure no further replication happens.
mysql> STOP REPLICA;
Remove the replication configuration from the replica. This clears the replication settings, ensuring the replica no longer attempts to connect to the old master.
mysql> RESET SLAVE ALL;
mysql> show databases;
mysql> create database testdb;
mysql> show databases;
mysql> use testdb;
mysql> CREATE TABLE test_table (test varchar(50));
mysql> show tables;
mysql> INSERT INTO test_table VALUES
('Test01 was inserted by Master-192.168.1.162'),('Test02 was inserted by Master-192.168.1.162'),
('Test03 was inserted by Master-192.168.1.162'),('Test04 was inserted by Master-192.168.1.162'),
('Test05 was inserted by Master-192.168.1.162'),('Test06 was inserted by Master-192.168.1.162');
mysql> SELECT * FROM test_table;
# Backup current configuration file
cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.backup
# Edit configuration file
vi /etc/mysql/mysql.conf.d/mysqld.cnf
# Type your master database ip address
bind-address = 192.168.1.162
# Save and Exit
# Restart the MySQL server service to apply the changes
sudo systemctl restart mysql# mysql -u root -p
mysql> SHOW VARIABLES LIKE 'bind_address';
mysql> SHOW VARIABLES LIKE 'port';
Redirect your applications to the new master.
I have tried to document step by step how to install MySQL for Debian-Based Linux (Ubuntu). I hope it will be useful for those who need it!
sudo apt update
sudo apt upgradesudo apt install mysql-server# dpkg --list | grep mysql
# apt list --installed | grep mysql
sudo systemctl status mysql.service
sudo mysql
sudo mysql_secure_installationsudo mysql
alter user 'root'@'localhost' identified with mysql_native_password by 'Deuce4040';
sudo mysql -u root -p
I have tried to document step by step how to delete Oracle log files with ADRCI as automatically. I hope it will be useful for those who need it!
1-Run ADRCI
2-Check All ADR Homes
3-Set ADR Home
4-Check the Oldest Files for the Directory
5-Change Purging Policy As Your Need
6-Check LAST_AUTOPRG_TIME to Ensure Automatic Deletion is Run
7-Check the Directory to Ensure Log Files are Deleted as Requested
8-Is it Possible to Change ADR Automatic Purge Scheduled Time?
[oracle@racprimary01 ~]$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Tue Dec 31 18:18:10 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>
adrci> show homes
ADR Homes:
diag/rdbms/test/TEST1
diag/asm/+asm/+ASM1
diag/asm/user_oracle/host_1107208194_110
diag/crs/racprimary01/crs
diag/clients/user_oracle/host_1107208194_110
diag/clients/user_root/host_1107208194_110
diag/tnslsnr/racprimary01/asmnet1lsnr_asm
diag/tnslsnr/racprimary01/listener_scan1
diag/tnslsnr/racprimary01/listener_scan2
diag/tnslsnr/racprimary01/listener_scan3
diag/tnslsnr/racprimary01/listener
diag/asmcmd/user_oracle/racprimary01.localdomain
diag/kfod/racprimary01/kfod
adrci>
I will run my tests on diag/crs/racprimary01/crs home directory.
adrci> set home diag/crs/racprimary01/crs
adrci>
adrci> show home
ADR Homes:
diag/crs/racprimary01/crs
adrci>
[oracle@racprimary01 trace]$ cd /u01/app/oracle/diag/crs/racprimary01/crs/trace
[oracle@racprimary01 trace]$
[oracle@racprimary01 trace]$ pwd
/u01/app/oracle/diag/crs/racprimary01/crs/trace
[oracle@racprimary01 trace]$
[oracle@racprimary01 trace]$ ls -lrt | head -10
For testing purposes, I will configure it to delete files older than 8 days (192 hours). My test date is January 1st 🙂
adrci> set control (SHORTP_POLICY=192) -- Default is 720 hours (30 days)
adrci> set control (LONGP_POLICY=192) -- Default is 8760 hours (365 days)
adrci> select SHORTP_POLICY,LONGP_POLICY from ADR_CONTROL;
An empty LAST_AUTOPRG_TIME field in the output indicates that no deletions have been made automatically.
adrci> select SHORTP_POLICY,LONGP_POLICY,LAST_AUTOPRG_TIME from ADR_CONTROL;
ADR Home = /u01/app/oracle/diag/crs/racprimary01/crs:
*************************************************************************
SHORTP_POLICY LONGP_POLICY LAST_AUTOPRG_TIME
-------------------- -------------------- ----------------------------------------
192 192 2025-01-01 00:56:14.124793 +03:00 
[oracle@racprimary01 trace]$ cd /u01/app/oracle/diag/crs/racprimary01/crs/trace
[oracle@racprimary01 trace]$
[oracle@racprimary01 trace]$ pwd
/u01/app/oracle/diag/crs/racprimary01/crs/trace
[oracle@racprimary01 trace]$
[oracle@racprimary01 trace]$ ls -lrt | head -10
There is no control on the purge frequency, it is fixed to be 48 hours after startup and then every 7 days. Manual purge is the only way to control purge actions in between.
Reference: Is there a way to control ADRCI Auto_Purge Frequency done by the MMON ? (Doc ID 1446242.1)
# Create & Go to the Script Directory
cd /home/oracle/scripts
# Create SH File & Add Files to be Deleted
vi remove_old_logs.sh
find /u01/app/oracle/diag/rdbms/test/test/trace -type f -name "*.trc" -mtime +7 -exec rm {} \;
find /u01/app/oracle/diag/rdbms/test/test/trace -type f -name "*.trm" -mtime +7 -exec rm {} \;
find /u01/app/oracle/diag/rdbms/test/test/alert -type f -name "*.xml" -mtime +7 -exec rm {} \;
find /u01/app/oracle/diag/tnslsnr/racprimary01/listener/alert -type f -name "*.xml" -mtime +7 -exec rm {} \;
find /u01/app/oracle/diag/tnslsnr/racprimary01/listener1/alert -type f -name "*.xml" -mtime +7 -exec rm {} \;
find /u01/app/oracle/diag/tnslsnr/racprimary01/listener2/alert -type f -name "*.xml" -mtime +7 -exec rm {} \;
find /u01/app/oracle/diag/tnslsnr/racprimary01/listener3/alert -type f -name "*.xml" -mtime +7 -exec rm {} \;
find /u01/app/oracle/admin/test/adump -type f -name "*.aud" -mtime +7 -exec rm {} \;
# Try it Manually Before Schedule
./remove_old_logs.sh
# Add to Crontab
# Delete Old Log Files
00 7 * * * /home/oracle/scripts/remove_old_logs.sh[oracle@racprimary01 ~]$ adrci
ADRCI: Release 19.0.0.0.0 - Production on Tue Dec 31 18:18:10 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>adrci> show base
ADR base is "/u01/app/oracle"
adrci>adrci> show homes
ADR Homes:
diag/rdbms/test/TEST1
diag/asm/+asm/+ASM1
diag/asm/user_oracle/host_1107208194_110
diag/crs/racprimary01/crs
diag/clients/user_oracle/host_1107208194_110
diag/clients/user_root/host_1107208194_110
diag/tnslsnr/racprimary01/asmnet1lsnr_asm
diag/tnslsnr/racprimary01/listener_scan1
diag/tnslsnr/racprimary01/listener_scan2
diag/tnslsnr/racprimary01/listener_scan3
diag/tnslsnr/racprimary01/listener
diag/asmcmd/user_oracle/racprimary01.localdomain
diag/kfod/racprimary01/kfod
adrci>adrci> set home diag/rdbms/test/TEST1
adrci>
adrci> show home
ADR Homes:
diag/rdbms/test/TEST1
adrci>adrci> show alertadrci> show problem
adrci> show incidentadrci> show tracefile
adrci> show tracefile -rt# CHECK COMMAND
adrci> show control
# CHANGE COMMAND
adrci> set control (SHORTP_POLICY=120)
adrci> set control (LONGP_POLICY=720)SHORTP_POLICY
LONGP_POLICY
#!/bin/sh
for ADRHOME in `adrci exec="show home"`
do
if [ $ADRHOME = "ADR" -o $ADRHOME = "Homes:" ]
then
continue;
fi
echo $ADRHOME
adrci <<EOF
set home $ADRHOME
set control (SHORTP_POLICY = 192)
set control (LONGP_POLICY = 192)
purge
exit
EOF
done# Purge Specific File Types
adrci> purge -age 2880 -type trace (Deletes older than 2 days trace files. [2880 minutes=2 days])
adrci> purge -age 2880 -type alert (Deletes older than 2 days alert files. [2880 minutes=2 days])
# It is possible to delete the following file types with the same command:
ALERT – files in ./alert directory
INCIDENT — files in ./incident/incdir_<incid> directory
TRACE — files in ./trace directory
CDUMP — files in ./cdump directory
UTSCDMP — ./trace/cdmp_<timestamp> directories
STAGE — files in ./stage directory
SWEEP — files in ./sweep directory
HM — files in the ./hm directory and metadata information in the HM schema
IPS — files in the ./incpkg directory and the metadata information in the IPS schema
# Purge All Files
adrci> purge -age 2880 (Deletes older than 2 days all files. [2880 minutes=2 days])adrci> help
HELP [topic]
Available Topics:
CREATE REPORT
ECHO
ESTIMATE
EXIT
HELP
HOST
IPS
PURGE
RUN
SELECT
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW LOG
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list
adrci>
SERHAT CELIK DATABASE BLOG 