Inhalt:

• MySQL Dumps
• User anlegen
• Nützliches
• Platzbedarf von Datenbanken
• InnoDB Tablespace – Information
• Größe der innoDB-Logfiles berechnen
• Größe der innoDB-Logfiles sicher ändern

MySQL Dump

nur die Datenbank-Struktur sichern:

Dump einer/mehrerer Datenbank(en):

Dump aller Datenbanken:

Dump bestimmter Tabellen:

Dump einlesen:

Dump von bestimmten Spalten einer Tabelle:

Am Beispiel: Es soll eine Teil-Sicherung der Tabelle c_data gemacht werden. Es sollen nicht alle Spalten aus c_data, sondern nur ecu_gw, dbs_extern, dbs_extern_ip gesichert werden. Das lässt sich mit einem CONCAT-Statement realisieren. Es ist zu beachten, dass Mysql in das angegebene Verzeichnis für die Output-Datei schreiben darf. Dieses Quasi-Dump kann ganz normal eingespielt werden.

 
Skript für Dump aller Datenbanken, aber es wird jede Datenbank in eine eigene Datei geschrieben mysqldump_each_single_file.sh:

#!/bin/bash
# Dump mysql databases via mysqldump and write
# each database in a single file

# Exclude the following databases
edb="information_schema performance_schema"

# Use this prefix for backup file names
prefix="$(date +'%Y-%m-%d')_$(hostname)_"

# Use this suffix for backup file names
suffix=".sql"

# Compress backup files
pack=$(which bzip2)

dump=$(which mysqldump)
if [ ! -x $dump ]; then
echo "$dump not executable"
exit 1
fi

mysql=$(which mysql)
if [ ! -x $mysql ]; then
echo "$mysql not executable"
exit 1
fi

# Ask for password without prompt
stty -echo
read -p "Enter MySQL password for root: " pass; echo
stty echo

if [ "" = "$pass" ]; then
echo "Login without password"
dblogin='-u root'
else
dblogin="-u root -p$pass"
fi

######################
# Execute backup #
######################
out="$($mysql $dblogin -Bse 'show databases')"
# List for compressing files
fc=

# Loop over all databases
for db in $out
do
# Don't skip any database as default
skipdb=0
# If excludable databases are defined
if [ "$edb" != "" ]; then
# Loop over excludable databases
for n in $edb; do
if [ "$db" = "$n" ]; then
skipdb=1
break;
fi
done
fi

if [ "$skipdb" = "1" ] ; then
echo "Skip database $db"
continue
fi

fn="./$prefix$db$suffix"
echo "Dump database $db to $fn"
$dump $dblogin --databases $db --result-file=$fn
fc="$fc $fn"
done

######################
# Compress backups #
######################
if [ $pack ] && [ -x $pack ] && [ "$fc" != "" ]; then
echo "Compress $fc"
$pack $fc
fi

Skript mit Dialog-Oberfläche zum auswählen von Datenbanken für Dump in eine Datei mysqldump_select.sh:

#!/bin/bash
# Dump selected mysql databases via mysqldump and write
# the complete dump in a single file including master-data

# Compress backup files
#pack=$(which bzip2)
pack=''

# mysqldump binary
dump=$(which mysqldump)
if [ ! -x $dump ]; then
        echo "$dump not executable"
        exit 1
fi

# mysql binary
mysql=$(which mysql)
if [ ! -x $mysql ]; then
        echo "$mysql not executable"
        exit 1
fi

# file for temporary data
data=$(mktemp /tmp/$(basename $0).XXXXXX) || exit 1
# trap it
trap "rm -f $data" 0 1 2 5 15

# Read password and concatenate login string
dialog --title "Mysql user root" --clear --insecure --passwordbox "Enter password" 10 30 2> $data
pw=$(cat $data)
if [ "" = "$pw" ]; then
        dblogin="-u root"
else
        dblogin="-u root -p$pw"
fi

# Fetch databases
alldbs="$($mysql $dblogin -Bse 'show databases')"
# initialize database counter and checkbox options
dbc=0
opt=""
for db in $alldbs; do
 opt="$opt $db $dbc off"
 dbc=$(($dbc+1))
done

# Define height more flexible
height=$((5+$dbc))
if [ $height -gt 25 ]; then
        height=25
fi

dialog --checklist "Choose databases for dump:"  $height 40 30  $opt 2>$data
mydb=$(cat "${data}")

# Concatenate database string
databases="";
for db in $mydb; do
        d=$(echo $db | sed -e "s/^.//" | sed -e "s/.$//")
        databases="$databases $d"
done

# Master data
dialog --yesno "Write master informations (--master-data)" 0 0
if [ 1 = $? ]; then
        mopt=''
else
        mopt="--master-data"
fi

dialog --inputbox "Enter dump file name" 0 0 2> $data
fn=$(cat "${data}")

$dump $mopt $dblogin --databases $databases --result-file=$fn

dialog --msgbox "Done! Databases $databases has been saved." 0 0
dialog --clear

Skript zum Erstellen eines Dumps mit allen Usern und deren Passwörtern sowie Rechten mysql_user_dump.sh:

#!/bin/bash

DBUSR="fb"
DBPW="geheim"
DB="mysql"

(
 mysql --batch --skip-column-names -h localhost -u "$DBUSR" --password="$DBPW" "$DB" -e "SELECT user, host FROM user"
) | while read user host
do
  echo "# $user @ $host"
  mysql --batch --skip-column-names -h localhost -u "$DBUSR" --password="$DBPW" "$DB" -e "SHOW GRANTS FOR '$user'@'$host'" >> /home/fb/tools/mysql_user.sql
done
sed -i 's/.*/&\;/' /home/fb/tools/mysql_user.sql

User anlegen

Der Host ist auch eine Art Sicherheitsmaßnahme, denn der User hat nur Zugriff vom angegebenen Host. Host kann „localhost“, „%“ oder eine IP-Adresse sein. „%“ bedeutet Zugriff von überall her.

Nützliches

Überprüfen, welche Engine eine Tabelle benutzt. Alle Tabellen einer Datenbank:

Eine spezielle Tabelle:

Doppelte Einträge in Tabelle finden (am Beispiel doppelter dbname in c_data):

Skript zum Löschen aller Datenbanken (erstellt Drop-Statements):
Achtung! Vorsicht!

drop_all_db.sh

#!/bin/bash

echo "***************************"
echo "DANGER DANGER DANGER DANGER"
echo "script drops all databases "
echo "***************************"

mysql -uroot -p  -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema |gawk '{print "drop database " $1}' > drop.sql

sed -i 's/.*/&\;/' drop.sql

Platzbedarf von Datenbanken

Platzbedarf aller Datenbanken:

Ausgabe Platzbedarf je Datenbank:

Mit dieser Abfrage erhält man einen Überblick über den Speicherverbrauch pro Datenbank:

Mit dieser Abfrage erhält man einen Überblick über den Speicherverbrauch pro Tabelle einer bestimmten Datenbank (b2bbp):

InnoDB Tablespace – Information

–       Durch (ungeordnetes) Schreiben und Löschen in die Datenbank kann es dazu kommen, dass der Tablespace mehr Speicherplatz im Filesystem nutzt als die Daten in der Datenbank eigentlich groß sind.

–       Der Platz wird innerhalb des Tablesspaces wieder freigegeben, aber nicht im Filesystem. D.h. auf absehbare Zeit wird der Tablespace auch beim Hinzufügen von Daten erst einmal nicht weiter wachsen.

–       Eine Möglichkeit bei InnoDB den Tablespace zu defragmentieren führt über Dump, Löschen und Import

–       Es gibt die Möglichkeit Tabellen von vornherein in einen eigenen Tablespace pro Tabelle anzulegen

–       Infos zur Realisierung: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html

–       Mit OPTIMIZE TABLE kann nicht verwendeten Speicher in der Tabelle freigeben und die Datendatei defragmentiert werden.

–       Bei InnoDB wird OPTIMIZE TABLE auf ALTER TABLE umgesetzt, wodurch die Tabelle neu erstellt wird, um Indexstatistiken zu aktualisieren und ungenutzten Speicher im Cluster-Index freizugeben. Man muss beachten, dass MySQL die Tabelle während der Laufzeit von OPTIMIZE TABLE sperrt und eine hohe Auslastung verursachen kann.

Info über Löschen von großen Datenmengen in MySQL: http://mysql.rjweb.org/doc.php/deletebig

Größe der innoDB-Logfiles berechnen

Im Grunde muss die Größe der Logfiles groß genug sein, damit innoDB sein I/O optimieren kann, aber auch nicht zu groß, weil sonst ein Reparatur sehr lagne dauert. Aber wie soll diese Größe definiert werden? Es gibt eine gute Daumenregel.

Wir müssen uns auf dem MySQL-Server einloggen und die Logsequenz-Nummern mit einem Zeitabstand ausgeben lassen. Hier wähle ich eine viertel Stunde.

Wir notieren uns die Logsequenz-Nummern. Das ist die Gesamtzahl von Bytes, welche in das Transaktionslog geschrieben werden. Wir können jetzt also berechnen wieviel Megabyte in einer viertel Stunde geschrieben wurden.

Die Daumenregel besagt, dass man die Logs so groß machen kann, dass sie eine Stunde halten können. Das ist in der Regel völlig ausreichend. Hochgerechnet auf eine Stunde ergibt das:

Diese Zahl wird noch aufgerundet auf das Dualsystem für eine gute Maßeinheit, also 128 MB. Weil es standardmäßig zwei Logfiles gibt wird die Zahl durch 2 geteilt und wir können 64 MB als Größe festlegen.

Quelle: http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Größe der innoDB-Logfiles sicher ändern

Es ist nicht möglich die Größe einfach in der my.cnf zu ändern und dann den MySQL-Server neuzustarten.

Der MySQL-Server muss normal und sauber gestoppt werden. Dies kann mit dem Error-Log oder im Syslog überprüft werden. Wenn alles ok ist, werden die /var/lib/mysql/ib_logfile* nach /tmp/ verschoben, nicht löschen!

Jetzt kann der MySQL-Server wieder gestartet werden. Im Log sollten Meldung erscheinen wie:

An diesem Punkt kann man sagen, dass innoDB ordnungsgemäß funktioniert und die alten Logfiles unter /tmp/ können gelöscht werden.

Eine typische Fehlermeldung, dass innoDB bezüglich der Logfiles nicht startet, lautet: