Stone Wiki - mysql
https://dwiki.heisl.org/
2026-04-19T13:30:31+00:00Stone Wiki
https://dwiki.heisl.org/
https://dwiki.heisl.org/lib/exe/fetch.php?media=wiki:dokuwiki.svgtext/html2020-02-10T16:06:57+00:00Anonymous (anonymous@undisclosed.example.com)datenbank_groesse
https://dwiki.heisl.org/doku.php?id=mysql:datenbank_groesse&rev=1581350817&do=diff
Datenbank Größen anzeigen lassen
SELECT
table_schema AS 'Datenbankname',
Round( SUM( data_length ) / 1024 / 1024, 3 ) AS 'Daten (MB)',
Round( SUM( index_length ) / 1024 / 1024, 3 ) AS 'Index (MB)',
Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Gesamt (MB)',
Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Freier Speicher (MB)'
FROM information_schema.tables
GROUP BY table_schema ;text/html2020-02-10T16:11:00+00:00Anonymous (anonymous@undisclosed.example.com)innodb_cache_hit_rate
https://dwiki.heisl.org/doku.php?id=mysql:innodb_cache_hit_rate&rev=1581351060&do=diff
InnoDB Cache Hit Rate
Zeigt über den gesamten MySQL die InnoDB Cache Hit Rate an:
SELECT round ((P1.variable_value / (P1.variable_value + P2.variable_value))*100,2) Hitratio
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';text/html2020-02-10T16:07:55+00:00Anonymous (anonymous@undisclosed.example.com)reparatur_offline
https://dwiki.heisl.org/doku.php?id=mysql:reparatur_offline&rev=1581350875&do=diff
DB Offline reparieren
MyIsam
Die MySQL DB muss dazu offline sein.
cd /var/lib/mysql/DB/
myisamchk --silent --force --fast --update-state *.MYI
Das Verzeichnis könnte eventuell abweichen.text/html2022-12-06T14:59:08+00:00Anonymous (anonymous@undisclosed.example.com)replication
https://dwiki.heisl.org/doku.php?id=mysql:replication&rev=1670338748&do=diff
MySQL Replication
Vorbereiten des Masters
Damit der Master Bin Logs schreibt müssen in der Config ein paar Sachen gemacht werden.
Änderungen in der my.cnf (bei Mariadb kann das etwas sein) - Service muss danach neugestartet werden.
server_id = 1
log_bin = /var/lib/mysql/mysql-bin.log
relay-log=/var/lib/mysql/mysql-relay-bin
sync_binlog = 1
expire_logs_days=7
max_binlog_size=500M
max_relay_log_size=500M
relay_log_space_limit=5G
relay_log_purge=1
replicate-ignore-db=performance_schema,inf…