Eine einfacherere Alternative wäre das Tool `pt-show-grants` aus dem Percona-Toolkit.
Bei der Einrichtung einer frischen Replikation kann es wichtig sein, dass vorhandene User nicht überschrieben werden. Mit dem folgenden Snippet lassen sich die Benutzer einer MySQL-DB als einfache GRANT-Statements inkl. gesonderten Rechten auf Datenbanken ausgeben und für den späteren Import passend bearbeiten. ```shell mygrants() { mysql -B -N $@ -e "SELECT DISTINCT CONCAT( 'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';' ) AS query FROM mysql.user" | \ mysql $@ | \ sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' } ``` Ein Aufruf könnte dann so aussehen: ```shell mygrants --host=prod-db1 --user=admin --password=secret ``` # Datenbank und Benutzer löschen Einen Benutzer inkl. Datenbank löschen (ab MySQL 5.0.2): ``` DROP USER user1; DROP DATABASE userdb1; ``` Einen Benutzer inkl. Datenbank löschen (pre MySQL 4.1.1): ``` REVOKE ALL PRIVILEGES ON 'userdb1'.* FROM 'user1'@'123.123.123.123'; # und evtl. REVOKE GRANT OPTION ON 'userdb1'.* FROM 'user1'@'123.123.123.123'; DELETE FROM mysql.user WHERE Host='123.123.123.123' AND User='root'; FLUSH PRIVILEGES; DROP DATABASE userdb1; ``` # InnoDB - ideale Logfile Größe herausfinden von [code.openark.com](http://code.openark.org/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap "http://code.openark.org/blog/mysql/how-to-calculate-a-good-innodb-log-file-size-recap") und [MySQL Performance Blog](http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ "http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/") Um den idealen Wert für innodb\_log\_file\_size für seine Datenbank herauszufinden, läßt sich sehr einfach dieser Code verwenden. Die Query erfasst über den Zeitraum von 60 Sekunden alle Schreiboperationen auf das InnoDB-Logfile. Idealer Zeitpunkt zur Ausführung ist während einer durchschittlichen Zugriffsmenge, nicht zur Peak-Zeit. ```SQL SELECT innodb_os_log_written_per_minute*60 AS estimated_innodb_os_log_written_per_hour, CONCAT(ROUND(innodb_os_log_written_per_minute*60/1024/1024, 1), 'MB') AS estimated_innodb_os_log_written_per_hour_mb FROM (SELECT SUM(value) AS innodb_os_log_written_per_minute FROM ( SELECT -VARIABLE_VALUE AS value FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'innodb_os_log_written' UNION ALL SELECT SLEEP(60) FROM DUAL UNION ALL SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'innodb_os_log_written' ) s1 ) s2 ; ``` Das sieht dann z.B. so aus: ``` +------------------------------------------+---------------------------------------------+ | estimated_innodb_os_log_written_per_hour | estimated_innodb_os_log_written_per_hour_mb | +------------------------------------------+---------------------------------------------+ | 58920960 | 56.2MB | +------------------------------------------+---------------------------------------------+ ``` Also sind es aufgerunden ca. 60 MB und da es standardmäßig 2 Logfiles gibt, lautet der ideale Wert: ``` innodb_log_file_size = 30M ``` # Größe einer DB, Tabelle, aller DBs herausfinden Ersetze 'YOUR\_DATABASE\_NAME' durch die gewünschte Datenbank. Größe aller Tabellen einer Datenbank: ```sql SELECT TABLE_SCHEMA AS 'Database_name', TABLE_NAME AS 'Table_Name',CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," Mb") AS Size FROM INFORMATION_SCHEMA.TABLES; ``` Größe bestimmter Tabellen: ```sql SELECT TABLE_SCHEMA AS 'Database_name', TABLE_NAME AS 'Table_Name',CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2)," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'YOUR_DATABASE_NAME'; ``` Größe einer bestimmten Datenbank: ```sql SELECT CONCAT(sum(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2))," MB") AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'YOUR_DATABASE_NAME'; ``` Größe aller Datenbanken: ```sql SELECT table_schema AS "Database", sum( data_length + index_length ) / 1024 / 1024 AS "Size in MB" FROM information_schema.TABLES GROUP BY table_schema; ``` Größe aller Datenbanken und wieviel Platz durch ein "OPTIMIZE TABLE" gewonnen werden kann: ```sql SELECT table_schema "database name", round( sum( data_length + index_length ) / 1024 / 1024) "database size in MB", round( sum( data_free )/ 1024 / 1024) "free space in MB" FROM information_schema.TABLES GROUP BY table_schema; ``` Die 10 größten Tabellen anzeigen und wie viel Platz gewonnen werden könnte: ```sql SELECT table_schema AS database_name, table_name, round( (data_length + index_length) / 1024 / 1024, 2) AS total_size, round( (data_length) / 1024 / 1024, 2) AS data_size, round( (index_length) / 1024 / 1024, 2) AS index_size, round( (data_free) / 1024 / 1024) AS free_space FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema' ,'sys') AND table_type = 'BASE TABLE' -- AND table_schema = '$i' ORDER BY total_size DESC LIMIT 10; ``` wo kann am meisten Platz gewonnen werden (sortiert nach free\_space): ```mysql SELECT table_schema AS database_name, table_name, round( (data_length + index_length) / 1024 / 1024, 2) AS total_size, round( (data_length) / 1024 / 1024, 2) AS data_size, round( (index_length) / 1024 / 1024, 2) AS index_size, round( (data_free) / 1024 / 1024) AS free_space FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema' ,'sys') AND table_type = 'BASE TABLE' -- AND table_schema = '$i' ORDER BY free_space DESC LIMIT 10; ``` # Passwort setzen oder ändern mit „mysqladmin“ ein Passwort setzen oder ändern: ``` mysqladmin -h 'hostname' -u root password 'neues-passwort' für localhost: mysqladmin -u root password 'neues-passwort' ``` mittels SQL ein neues Passwort vergeben: ``` mysql -u root mysql> GRANT ALL PRIVILEGES ON userdb1.* TO 'user1'@'123.123.123.123' IDENTIFIED BY 'neues-passwort'; ``` # Passwort vergessen Wenn das Kennwort für 'root' verloren ging / vergessen wurde und sonst auch keine Adminuser existieren, lassen sich die Kennwörter noch über den folgenden Weg zurücksetzen (getestet unter Debian). Ist euer MySQL-Server direkt und öffentlich über das Internet erreichbar solltet ihr vorher den Zugriff darauf per iptables-Regel einschränken, da sich anschließend alle User ohne Kennwort einloggen können. 1\. MySQL Server stoppen: /etc/init.d/mysql stop 2\. in /etc/mysql/my.cnf wird diese Zeile hinzugefügt, damit werden beim Start die Berechtigungstabellen ignoriert und jeder(!) User kann sich ohne Kennwort einloggen. ``` --skip-grant-tables ``` 3\. MySQL Server wieder starten: /etc/init.d/mysql start 4\. MySQL Client starten und Kennwörter neu setzen ``` # mysql -u root mysql mysql> UPDATE user SET Password=PASSWORD('newrootpassword') WHERE User='root'; exit ``` 5\. MySQL-Server wieder stoppen 6\. „–skip-grant-tables“ wieder aus der my.cnf löschen 7\. MySQL-Server wieder starten. Ihr solltet Euch nun mit dem neuen Kennwort einloggen können. # MySQL-Datenbank umbenennen Der ganz sichere Weg ist, die DB per mysqldump zu exportieren und wieder in die neue DB zu importieren: ```shell mysqldump alteDb | mysql neueDB ``` Für kurze Zeit gab es das MySQL-Kommando „RENAME DATABASE“, das aber wegen verschiedenen Problemen schnell wieder entfernt wurde. Per „RENAME TABLE“ lassen sich Tabellen umbenennen und dabei auch in ein anderes Schema verschieben. Da das für jede Tabelle einzeln gemacht werden muss, kann das über dieses Script auch automatisiert werden: ```shell #!/bin/bash # # rename a mysql database - this works both for MyISAM and InnoDB # # usage: rename-mysql-db.sh old new # dbname as in "SHOW DATABASES" # # mysql command and authorization info if needed mysql="mysql" #mysql="mysql -uroot -pblabla" #mysql="mysql -uroot -pblabla -S /var/lib/mysql/mysql.sock -h localhost" olddb=${1} newdb=${2} ${mysql} -e "CREATE DATABASE ${newdb}" tables=$(${mysql} -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema='${olddb}'") for name in ${tables}; do ${mysql} -e "RENAME TABLE $olddb.$name to $newdb.$name"; done; ${mysql} -e "DROP DATABASE $olddb" ``` Oder einfach die notwendigen Kommandos für die manuelle Ausführung ausgeben lassen: ```shell ALTE_DB="nextcloud_test" NEUE_DB="bla_test" for table in `mysql -s -N -e "USE $ALTE_DB; SHOW TABLES;"`; do echo mysql -s -N -e "USE $ALTE_DB; RENAME TABLE $ALTE_DB.$table to $NEUE_DB.$table;"; done; ``` # mysqltuner - MySQL Performance Tuning Check-Script MySQLTuner is ein Perlscript, das versucht die MySQL Konfiguration zu prüfen und Vorschläge zum Tuning auf Basis der laufenden Installation zu machen. Installation: ``` wget -O mysqltuner.pl http://mysqltuner.pl, chmod u+x mysqltuner.pl ``` Ausführung: ``` ./mysqltuner.pl Di 27. Jul 2021 14:48:07 >> MySQLTuner 1.8.1 - Major HaydenFalls man ein Replication-Setup hat muss man natürlich darauf achten, dass der Slave auf aktuellem Stand ist und man nicht zuviele Logs löscht!
# Ungenutzte Datenbanken finden Dieses Snippet listet die neueste Datei in jedem Datenbankverzeichnis unterhalb von /var/lib/mysql auf: ```bash cd /var/lib/mysql for i in `mysql --skip-column-names -B -e "show databases" | grep -v -E "mysql|information_schema|sys|performance_schema|percona_schema"`; do echo -ne "\n--- $i ---\nSize: "; du -hs $i; find $i -type f -print0 | xargs -0 stat --format '%Y :%y %n' | sort -nr | cut -d: -f2- | head -n1; done ``` Beispiel: ``` --- database1 --- Size: 121M database1 2024-12-16 21:12:42.437939063 +0100 database1/Portal_PortalRequestLog.ibd --- database2 --- Size: 17G database2 2024-12-10 06:24:49.902646396 +0100 database2/app_session.ibd --- database3 --- Size: 152M database3 2024-10-10 14:20:10.301089103 +0200 database3/layout_file.frm ``` # Fehlerbehandlung # Replikationsfehler Errno: 1236 Replikationsfehler Errno: 1236: start replication from impossible position ``` Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event mysql-bin.010346 at 5134523, the last event read from /var/lib/mysql/mysql-bin.000346 at 4, the last byte read from /var/lib/mysql/mysql-bin.010346 at 4., Error_code: 1236 ``` Dieser Fehler kann mehrere Ursachen haben. Die häufigste Ursache bei meinen Server war ein harter Crash des Master-Servers. Dabei wurden die letzten Binlog-Einträge nicht auf die Disk geschrieben oder es landete Müll darin. Das kann man herausfinden, wenn man sich das letzte Binlog (auf dem Master) ansieht. Sollte das Log hier zu Ende sein, muss man dem Slave das darauf folgende Logfile und die neue Logposition mitteilen. ``` $ mysqlbinlog --no-defaults --base64-output=decode-rows --verbose --verbose --start-position=5134523 mysql-bin.010346 ``` Das nächste Logfile wäre in diesem Fall /var/lib/mysql/mysql-bin.000347, bei Position 4. ``` mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000347', MASTER_LOG_POS=4; mysql> START SLAVE; ``` Die Replikation sollte nun wieder normal weiterlaufen. Um dem Fehler entgegen zui wirken, kann man „sync\_binlog = 1“ in der my.cnf im \[mysqld\]-Bereich setzen. Damit führt MySQL nach jedem commit einen fsync auf der Disk aus. Bei entsprechend schnellen Platten bedeutet diese Option nur einen minimalen Performanceverlust. # Replikationsfehler Errno: 1580 ALTER Logtable Nach einem Update von MySQL bei einer Master-Slave-Replikation konnte die Replikation auf einem Slave-Server kürzlich nicht mehr gestartet werden. ``` Last_Errno: 1580 Last_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log ``` Offenbar hat der Slave Probleme, das „`ALTER TABLE`“ anzuwenden, sofern das Logging noch aktiv ist (in diesem Fall das Slow-Query Log). Der Fehler läßt sich relativ einfach beheben (über die MySQL CLI): ``` STOP SLAVE; SET GLOBAL slow_query_log = 'OFF'; START SLAVE; SHOW SLAVE STATUS\G ``` Der Fehler sollte nun verschwunden sein, der Slave konnte die Query also auf seine DB anwenden. Nun kann auch das Slow-Query Log wieder aktiviert werden: ``` mysql> SET GLOBAL slow_query_log = 'ON'; ``` # Replikationsfehler Errno: 1594 Relay log read failure Wenn ein Server, auf dem ein MySQL-Replication-Slave läuft abstürzt, kann es passieren, dass die Master-Slave-Replikation nicht mehr funktioniert. Beim Absturz landen korrupte Daten in den Relay-Logs, die von MySQL nach dem Neustart nicht verarbeitet werden können. Im SLAVE STATUS steht dann folgendes: Last\_Errno: 1594 Last\_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. In diesem Fall reicht es häufig die Relay-Logs vom Slave zu löschen und die Replikation neu zu starten. Ein erneuter Dump der Datenbank ist dabei nicht notwendig. Wir sagen dem Server einfach, wo er unterbrochen wurde und setzen die Replikation an dieser Stelle neu auf. 1. Slave-Prozesse stoppen: `STOP SLAVE;` 2. Aus `SLAVE STATUS` die folgenden Werte merken: „Relay\_Master\_Log\_File“ und „Exec\_Master\_Log\_Pos“ 3. Slave zurücksetzen, dabei werden die korrupten Logs gelöscht: `RESET SLAVE;` 4. jetzt sagen wir dem Slave an welcher Stelle die Replikation unterbrochen wurde mit den Werten aus dem zweiten Schritt. Master-Host, Username usw. müssen wir dabei nicht nochmal eingeben: `CHANGE MASTER TO MASTER_LOG_FILE='Relay_Master_Log_File', MASTER_LOG_POS=Exec_Master_Log_Pos;` 5. nun kann der Slave wieder gestartet werden und sollte an der richtigen Stelle weitermachen. Das zuvor korrupte Binlog wird erneut vom Master übertragen: `START SLAVE;` # Fehler bei ALTER TABLE Errno 150: Foreign key constraint is incorrectly formed Dieser Fehler trat bei einem „`ALTER TABLE `table` ENGINE = InnoDB`“ auf (die Tabelle sollte von MyISAM auf InnoDB umgestellt werden). Eine Spalte war von einer anderen Tabelle (bereits InnoDB) referenziert. Nach dem der Contraint von der anderen Tabelle entfernt worden war, ließ sich die Tabelle konvertieren. Anschließend kann der Constraint wieder hinzugefügt werden. Mit diesem Query lassen sich Referenzen von externen Tabellen auf die zu konvertierende Tabelle anzeigen: ``` SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '