Mysql: Handy commands when looking for any issues (mysqlbinlog)/diag report/mysql setting.

 

1) 

mysqlbinlog commands:

--- to start from specific time:

mysqlbinlog --start-datetime="2023-07-28 12:00:00" --base64-output=AUTO --verbose ctdayasd001-bin.000005 >/home/oracle/siva/asd_bin005.log

mysqlbinlog -d database_name --base64-output=AUTO --verbose mysqld-bin.000001 >/home/oracle/siva/test.log

--debug-check option will check for open file and memory use after it finish processing the given binary log file

mysqlbinlog -d database_name --debug-check mysqld-bin.000001

--debug-info option will display additional debugging option after it finish processing the given binary log file

mysqlbinlog -d database_name --debug-info mysqld-bin.000001

~

2) to run diag collect: like AWR for current time.

runs for 300 seconds(5 mins) for every 30 seconds to collect the details and report it.

mysql -u root -p -H -e"CALL sys.diagnostics(300, 30, 'current');" > /opt/mysql/current_Host_instance_report.html

3)

--- HTML report to check the mysql settings:

vi collect-mysql-det.sql

SELECT VERSION(), NOW(), @@time_zone, @@system_time_zone\G

/*!50503 SELECT ENGINE, COUNT(*), SUM(DATA_LENGTH) AS DATA, SUM(INDEX_LENGTH) AS IDXS, ((SUM(DATA_LENGTH)+SUM(INDEX_LENGTH))/(1024*1024)) AS TOTALMB

FROM information_schema.TABLES GROUP BY ENGINE*/;

select benchmark(50000000,(1234*5678/37485-1298+8596^2)); #should take less than 20 seconds

SHOW GLOBAL VARIABLES;

SELECT * FROM INFORMATION_SCHEMA.PLUGINS;

/*!80001 SELECT * FROM mysql.component*/;

-- part of an IC?

/*!50706 SELECT * FROM `performance_schema`.`replication_group_members`*/;

/*!50604 select processlist_state,count(*) from performance_schema.threads group by processlist_state*/;

SHOW GLOBAL STATUS;

SELECT SLEEP(180);

/*!50604 select processlist_state,count(*) from performance_schema.threads group by processlist_state*/;

SHOW GLOBAL STATUS;

STATUS;

MySQL MEB FULL Backup and Restore Testing (incremental)

 

MySQL MEB FULL Backup and Restore Testing

Here for this, we are testing the scenarios on Test server (TestServer). The backup and restore is little different from Oracle point of view. So we need to make sure we are testing MySQL as is with available functionality.  Do follow below steps to test the scenario to make sure we can put “Incremental Backups” in line.

1. Restore the database from Prod backup on to Test Server (TestServer). Once restored, Make sure can login into MySQL database and query tables and rows. Edit the shell with backup location.

Use this script: /opt/mysql/mysql-meb-restore_auto.sh

2. Once confirmed, take MEB backup for Test Server (TestServer).

Use this script: 

/opt/mysql/scripts/mysql-meb-backup.sh FULL 14 

3. Once backed up, make sure you can login into MySQL database and query tables and rows and truncate below tables.

select table_schema,table_name,table_rows from information_schema.tables where

table_schema='scott' and table_rows < 20000 order by 3 desc;

select count(*) from scott.Table1;

select count(*) from scott.Table2;

select count(*) from scott.Table3;

select count(*) from scott.Table4;

example: 

truncate scott.Table1;

4. We now take the incremental backup on the Test Server (TestServer).

Use this script: 

/opt/mysql/scripts/ mysql-meb-incr-backup.sh INCR 14

5. Now, we try to restore MySQL as full backup and test it. edit the shell with backup location.

Use this script: 

/opt/mysql/ mysql-meb-restore_auto_host.sh

6. Once restored, login and see the table rows of the above truncated tables. You should see all rows.

7. Now, do the incremental restore to get to the current state. Edit the shell with backup location.

Use script:

/opt/mysql/mysql-meb-restore_auto_incremental.sh

8. Once restore, login to MySQL database and check the truncate table row counts.

Point-in-time recovery:

Point-in-time is manual process in MySQL not like that we do in Oracle.

MySQL doesn’t recommend using timestamp to do point-in-time recovery. Instead we need to restore the database to a logfile before the event logfile happened and then apply the changes of the event logfile using start lsn and end lsn. Below link you can take a look for Point-in-time recovery.

• Using the --start-datetime or --stop-datetime option to specify the range of binary log segment to replay is not recommended: there is a higher risk of missing binary log events when using the option. Use --start-position and --stop-position instead.

Links for More Information: 

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/mysqlbackup.incremental.html

https://dev.mysql.com/doc/mysql-enterprise-backup/8.4/en/backup-incremental-options.html

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/restore.incremental.html

https://dev.mysql.com/doc/refman/8.4/en/analyze-table.html

https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/advanced.point.html

PL/sql code to delete rows from table.

 -- DDL TO CREATE DEBUGGING TABLE -----> Monitor First time

-- create TABLE TRACK_TAB(ROWS_DELETED NUMBER,TOTAL NUMBER,START_TIME TIMESTAMP,END_TIME TIMESTAMP,DESC VARCHAR2(60))

-- create index TEST_TAB_INDX on TEST_TAB( CREATION_DATE)

DECLARE

nCount NUMBER:=0;

iCount NUMBER:=0;

 batchSize NUMBER:=5000;

daysOld NUMBER:=30;

 sql1 VARCHAR2(2000);

 starttime timestamp;

BEGIN

sql1 := 'DELETE FROM TEST_TAB WHERE CREATION_DATE < sysdate - ' || daysOld ||' and rownum <= ' || batchSize;

LOOP

starttime:=sysdate;

EXECUTE IMMEDIATE sql1;

nCount := sql%rowcount;

iCount:=iCount+nCount;

insert into TRACK_TAB values (batchSize,iCount,starttime, sysdate,'DELETE old TEST_TAB records ');

commit;

 EXIT WHEN nCount = 0;

END LOOP;

end;

Mysql: find tables with no primary key

 SELECT t.table_schema,
       t.table_name,
       k.constraint_name,
       k.column_name
FROM information_schema.tables t
     LEFT JOIN information_schema.key_column_usage k
          ON t.table_schema = k.table_schema
             AND t.table_name = k.table_name
             AND k.constraint_name = 'PRIMARY'
 WHERE t.table_schema NOT IN ( 'mysql', 'information_schema',
                                       'performance_schema' )
       AND k.constraint_name IS NULL
       AND t.table_type = 'BASE TABLE'
       order by table_schema desc;

how to skip commented lines in linux in loop for shell script.

How to skip commented input lines from a input file in loop in Linux script:

 

below is the simple code with small example:

#!/bin/ksh
# Script to check required ORACLE - RHEL RPMs:
#
while read rpm_c
  do
case "$rpm_c" in \#*) continue ;; esac
rpm_check=`rpm -qa $rpm_c | wc -l`
if [[ $rpm_check != 0 ]]; then
rpm_check=YES
echo $'\e[1;36m' rpm: $'\e[1;34m' $rpm_c   $'\e[1;32m' $rpm_check $'\e[0m'
else
rpm_check=NO
echo $'\e[1;36m' rpm: $'\e[1;34m' $rpm_c  $'\e[1;31m'  $rpm_check $'\e[0m'
fi
done<RHEL-7_ORACLE19C_RPMS.log

input in the logfile:

libgcc
libstdc++
libstdc++-devel

libxcb
make
smartmontools
sysstat
#

#Optional Packages for Red Hat Enterprise Linux 7:
#

#ipmiutil (for Intelligent Platform Management Interface)
#net-tools (for Oracle RAC and Oracle Clusterware)

#nfs-utils (for Oracle ACFS)
#python (for Oracle ACFS Remote)

Mysql: Kill a session

 How to find and kill a session in Mysql.

select * from information_schema. processlist;

if we want to generate kill statement for 'IDs' the use below sql statement.

 SELECT GROUP_CONCAT(CONCAT('KILL ',id ,'  ' ,HOST , '   ',command,';')) FROM information_schema. processlist WHERE user <> 'system user'

 group by ID; 

Mysql: Kill a session

 In Mysql, If we want to kill the multiple sessions quickly. Use below sql statement to generate the "KILL statements for the ID's and decide based on the once to kill.

 SELECT GROUP_CONCAT(CONCAT('KILL ',id ,'  ' ,HOST ,';')) FROM information_schema. processlist WHERE user <> 'system user'

 group by ID; 

 

example:

KILL 1  localhost;

KILL 24905  localhost2:1234;