mactricks - MySQL Kurztipps und Einzeiler

Inhaltsverzeichnis

↓ Sichern von MySQL Dumps in GZIP/BZIP2 Archive in einem Arbeitsgang
↓ Zurückspielen von GZIP/BZIP2 komprimierten MySQL Dumps in einem Arbeitsgang
↓ MySQL shell ohne User/Passwort
↓ Search and Replace in MySQL Tabelle
↓ Backup einer Tabelle einer MySQL Datenbank
↓ Anzeigen / Löschen von Einträgen älter als 30 Tage
↓ Backup aller MySQL Datenbanken (ohne die mysql Tabelle)
↓ Untersuchen und Reparieren einer MySQL Datenbank
↓ Wie man in MySQL Variablen definiert und später weiter nutzt
↓ Wie man in MySQL in einer Abfrage einen Regex benutzt
↓ Abfrage und zählen von bestimmten Stichwörter mit einem Regex
↓ Information über MySQL Server + Encoding anzeigen
↓ MYSQL Server komplett auf UTF-8 umstellen
↓ Collation einer Tabelle/Datenbank ändern nach UTF8:
↓ Script zum kopieren einer DB/Tabelle von einem Server zum anderen

↑ Sichern von MySQL Dumps in GZIP/BZIP2 Archive in einem Arbeitsgang #

Bash

1	mysqldump -uMyUserName --default-character-set="UTF8" -pMyPassWord MyTableInDB \| gzip > outputfile.sql.gz

Bash

1	mysqldump -uMyUserName --default-character-set="UTF8" -pMyPassWord MyTableInDB \| bzip2 > outputfile.sql.bz2

↑ Zurückspielen von GZIP/BZIP2 komprimierten MySQL Dumps in einem Arbeitsgang #

Bash

1	gunzip < MySQLdump.sql.gz \| mysql --default-character-set="UTF8" -uMyUserName -pMyPassWord MyTableInDB

Bash

1	bunzip2 < MySQLdump.sql.bz2 \| mysql --default-character-set="UTF8" -uMyUserName -pMyPassWord MyTableInDB

↑ MySQL shell ohne User/Passwort #

Unter debian wird in der Datei /etc/mysql/debian.cnf Zugangsdaten für Wartungs-Aufgaben hinterlegt. Diese kann man nutzten (als root) um eine SQL Shell zu erhalten (z.B. um ein Password eines Users zu ändern):

Bash

#!/bin/bash

if [ $(whoami) != 'root' ]; then
    echo "Error: You must start this script with sudo!"
    exit
fi

set -x
USERNAME=`grep user /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`
PASSWORD=`grep password /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`
mysql -u${USERNAME} -p${PASSWORD}

↑ Search and Replace in MySQL Tabelle #

Ersetzt in der Spalte "introtext"
"/images/alt" nach "/images/neu"

SQL

1	update jos_content set `introtext` = replace(`introtext`,'/images/alt/','images/neu/');

↑ Backup einer Tabelle einer MySQL Datenbank #

Bash

mysqldump -uUsername \
-pPasswort \
 -hlocalhost \ 
Tabellenname \
--compatible=mysql40 \
--default-character-set="UTF8" \
--lock-tables=false \
--compact > /root/flashchat.sql

↑ Anzeigen / Löschen von Einträgen älter als 30 Tage #

Anzeigen:

SQL

1 2	SELECT * FROM database.table WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) >= `dbfield`

Löschen:

SQL

1 2	DELETE FROM database.table WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) >= `dbfield`

↑ Backup aller MySQL Datenbanken (ohne die mysql Tabelle) #

SQL

mysqldump -uUsername -pPasswort -hlocalhost --all-databases \
--compatible=mysql40 \
--default-character-set=utf8 \
--lock-tables=false \
--compact \
--ignore-table=mysql.columns_priv \
--ignore-table=mysql.db \
--ignore-table=mysql.event \
--ignore-table=mysql.func \
--ignore-table=mysql.general_log \
--ignore-table=mysql.help_category \
--ignore-table=mysql.help_keyword \
--ignore-table=mysql.help_relation \
--ignore-table=mysql.help_topic \
--ignore-table=mysql.host \
--ignore-table=mysql.ndb_binlog_index \
--ignore-table=mysql.plugin \
--ignore-table=mysql.proc \
--ignore-table=mysql.procs_priv \
--ignore-table=mysql.servers \
--ignore-table=mysql.slow_log \
--ignore-table=mysql.tables_priv \
--ignore-table=mysql.time_zone \
--ignore-table=mysql.time_zone_leap_second \
--ignore-table=mysql.time_zone_name \
--ignore-table=mysql.time_zone_transition \
--ignore-table=mysql.time_zone_transition_type \
--ignore-table=mysql.user \
> dump_all.sql

↑ Untersuchen und Reparieren einer MySQL Datenbank #

Beispiel zeigt die Reparatur einer Plesk "psa" Datenbank.

anzeigen:

Bash

1	mysqlcheck -uadmin -p`cat /etc/psa/.psa.shadow ` psa

reparieren:

Bash

1	mysqlcheck --auto-repair -uadmin -p`cat /etc/psa/.psa.shadow ` psa

↑ Wie man in MySQL Variablen definiert und später weiter nutzt #

Eine Variable in einer weiteren späteren Abfrage weiter zu nutzen. In diesem Fall wird dann anschliessend die abgefragte ID in einer weiteren Abfrage dann gelöscht.

SQL

select @ID:=id, ip
from datenbank.tabelle
where ip = '80.141.217.138';

delete from datenbank.tabelle where id = @ID;

↑ Wie man in MySQL in einer Abfrage einen Regex benutzt #

In diesem Fall werden alle Einträge gesucht wo wie Begriffe "google" und "robots" auftauchen

SQL

select id, ip, agent
from datenbank.tabelle
where agent 
rlike '(google|robots)'

↑ Abfrage und zählen von bestimmten Stichwörter mit einem Regex #

In diesem Fall werden alle Einträge aufgelistet wo der Begriff "google" oder "spbot" oder "java" auftaucht und zählt deren Vorkommnisse.
Im DB Feld "agent" sind Browser und Bot Agent Strings gespeichert

SQL

select id, ip, agent, count(agent) AS entrycounts
from datenbank.tabelle
where agent rlike '(google|spbot|java)'
group by agent 
ORDER BY entrycounts DESC

↑ Information über MySQL Server + Encoding anzeigen #

Dieses skript muß man per sudo starten, weil die Zugangsdaten zum MySQL Server aus der Datei /etc/mysql/debian.cnf ausgelesen wird. Man kann allerdings auch USERNAME und PASSWORD eines normalen MySQL Benutzers eintragen.

Bash

#!/bin/bash

if [ $(whoami) != 'root' ]; then
    echo "Error: You must start this script with sudo!"
    exit
fi

USERNAME=`grep user /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`
PASSWORD=`grep password /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`

function mysql_query {
    echo "____________________________________________________________________"
    (
        set -x
        mysql -u${USERNAME} -p${PASSWORD} --execute="${1}"
    )
}

mysql_query "SELECT VERSION();"
mysql_query "SHOW GLOBAL VARIABLES LIKE 'character%';"
mysql_query "SHOW GLOBAL VARIABLES LIKE 'collation%';"

↑ MYSQL Server komplett auf UTF-8 umstellen #

in der Datei "/etc/mysql/my.cnf" folgende Einträge zufügen bzw. ändern.

Unbekannter Typ

[client]
...
default-character-set=utf8
...

[mysqld]
...# Character Settings
default-character-set=utf8
collation_server=utf8_unicode_ci
character_set_server=utf8
skip-character-set-client-handshake
...

↑ Collation einer Tabelle/Datenbank ändern nach UTF8: #

SQL

1
2
3

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER DATABASE tabelle DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
SELECT CONVERT(_latin1'Müller' USING utf8);

↑ Script zum kopieren einer DB/Tabelle von einem Server zum anderen #

Scenario: Ich möchte ohne grosse Umwege ganze Datenbanken oder einzelne Tabellen von einem MySQL Server direkt auf einen anderen kopieren. Dabei werden im Beispiel die Datenbank/Tabelle im SQL Dump Skript gelöscht (DROP Befehle) und dann neu eingespielt.

Das ganze fragt bei der Aktion ein Kennwort ab. Dies läßt sich für eine höhere Automatisierung auf Zertifikatsbasis umstellen.

Bash

#!/bin/bash

LOCAL_DB_USER="root"
LOCAL_DB_PASS="******"
LOCAL_DB_DATABASE="MyDatabase"
LOCAL_DB_TABLE="MyTablename"   # if set only the table will be copied / if not than database

REMOTE_USER="root"
REMOTE_HOST="myDestinationServer.de"
REMOTE_DB_USER="admin"
REMOTE_DB_PASS="******"


# based on following work task:
#   mysqldump myDatabase [myTable] | ssh root@myDestinationServer.de mysql [myDatabase] 
#  this works directly through Linux pipe
#
mysqldump -u${LOCAL_DB_USER} -p${LOCAL_DB_PASS} \
  --compatible=mysql4 \
  --default-character-set=latin1 \
  --lock-tables=false \
  ${LOCAL_DB_DATABASE} ${LOCAL_DB_TABLE} \
| ssh -C -o CompressionLevel=9 ${REMOTE_USER}@${REMOTE_HOST} \
  mysql -u${REMOTE_DB_USER} -p${REMOTE_DB_PASS} ${LOCAL_DB_DATABASE}

0 Kommentare für 'MySQL Kurztipps':

Es existiert kein Kommentar für 'MySQL Kurztipps' Kommentar hinterlassen

permalink

Inhaltsverzeichnis

laden...