mysql top slowest queries by permormance schema

SELECT 

    schema_name, 

    digest_text AS query, 

    COUNT_STAR AS executions, 

    SUM_TIMER_WAIT / 1000000000000 AS total_seconds,

    AVG_TIMER_WAIT / 1000000000000 AS average_seconds,

    MAX_TIMER_WAIT / 1000000000000 AS max_seconds

FROM 

    performance_schema.events_statements_summary_by_digest

WHERE 

    schema_name IS NOT NULL

ORDER BY 

    total_seconds DESC

LIMIT 10;

mysql memory usage analysis

 SELECT SUBSTRING_INDEX(event_name,'/',2) AS

       code_area, FORMAT_BYTES(SUM(current_alloc))
       AS current_alloc
       FROM sys.x$memory_global_by_current_bytes
       GROUP BY SUBSTRING_INDEX(event_name,'/',2)
       ORDER BY SUM(current_alloc) DESC

code_area	current_alloc
memory/innodb	4.65 GiB
memory/performance_schema	232.48 MiB
memory/sql	80.08 MiB
memory/mysys	33.07 MiB
memory/temptable	2.00 MiB
memory/myisam	1.41 MiB
memory/mysqld_openssl	152.04 KiB
memory/csv	49.84 KiB
memory/blackhole	88 bytes
memory/vio	8 bytes

SELECT CONCAT(FORMAT(A.num * 100.0 / B.num,2),'%') `BufferPool %` FROM
    (SELECT variable_value num FROM performance_schema.global_status
    WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
    (SELECT variable_value num FROM performance_schema.global_status
    WHERE variable_name = 'Innodb_buffer_pool_pages_total') B

BufferPool %
91.72%

select format_bytes(sum(current_alloc)) from sys.x$memory_global_by_current_bytes;

mysql block from remote except localhost

sudo iptables -A INPUT -i lo -p tcp --dport 3306 -j ACCEPT

sudo iptables -A INPUT -p tcp --dport 3306 -j DROP 

komanda, matyti iš kurių IP buvo prisijungta prie ssh

 grep "sshd.*Accepted" /var/log/auth.log | awk '{print $11}' | sort | uniq

 grep "sshd.*Accepted" /var/log/secure | awk '{print $11}' | sort | uniq

php kodas, tikrinantis įmonės registracijos numerį

function validateCompanyRegistrationNumber($number) { if (strlen($number) != 9) { return false; } $weights = [1, 2, 3, 4, 5, 6, 7, 8]; $sum = 0; for ($i = 0; $i < 8; $i++) { $sum += $weights[$i] * (int) $number[$i]; } $checkDigit = $sum % 11; if ($checkDigit == 10) { $checkDigit = 0; } return $checkDigit == (int) $number[8]; } $number = "123456789"; if (validateCompanyRegistrationNumber($number)) { echo "$number is valid."; } else { echo "$number is not valid."; 

} 

xtrabackup - mysql file level backup

 https://www.digitalocean.com/community/tutorials/how-to-create-hot-backups-of-mysql-databases-with-percona-xtrabackup-on-centos-7 

https://www.percona.com/blog/2020/04/10/percona-xtrabackup-backup-and-restore-of-a-single-table-or-database/

large mysql db load

 zr more