Eksportuj i importuj wszystkie bazy danych MySQL jednocześnie


353

Chcę zachować kopię zapasową wszystkich moich baz danych MySQL. Mam ponad 100 baz danych MySQL. Chcę wyeksportować je wszystkie jednocześnie i ponownie zaimportować je wszystkie na mój serwer MySQL jednocześnie. Jak mogę to zrobić?

Odpowiedzi:


774

Eksport:

mysqldump -u root -p --all-databases > alldb.sql

Przejrzyj dokumentację mysqldump . Możesz skorzystać z niektórych opcji wymienionych w komentarzach:

mysqldump -u root -p --opt --all-databases > alldb.sql
mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql

Import:

mysql -u root -p < alldb.sql

1
mysqldump -uroot -p --opt --all-databases> alldb.sql
koder

7
mysqldump -uroot -p - wszystkie bazy danych --skip-lock-tables> alldb.sql
świątynia

12
Dodaj opcje --verbose lub -v, aby zobaczyć, jak postępuje zrzut.
bagonyi

4
@ HalilÖzgür ze strony podręcznika mysqldump: „mysqldump domyślnie nie zrzuca bazy danych Information_SCHEMA lub performance_schema. Aby zrzucić dowolny z nich, nazwij go jawnie w wierszu poleceń, a także użyj opcji --skip-lock-tables.”
mmalone

2
OSTRZEŻENIE , import zastąpi wszystkich istniejących użytkowników MySQL.
RousseauAlexandre

218

Inne rozwiązanie:

Tworzy kopie zapasowe każdej bazy danych w innym pliku

#!/bin/bash

USER="zend"
PASSWORD=""
#OUTPUT="/Users/rabino/DBs"

#rm "$OUTPUTDIR/*gz" > /dev/null 2>&1

databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
       # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
    fi
done

3
Wolę takie podejście, ponieważ powoduje to, że zrzut wszystkich baz danych staje się innym plikiem.
Arda,

10
Możesz nieco uprościć / ulepszyć skrypt: zamień 9 linię [bazy danych = ...] na dwie linie: ExcludeDatabases="Database|information_schema|performance_schema|mysql"[następna linia] databases=`-u $USER -p$PASWORD -e "SHOW DATABASES;" | tr -d "| " | egrep -v $ExcludeDatabases`i usuń całkowicie linie ifi fi. W zmiennej ExcludeDatabasesprzechowujesz nazwy baz danych, których nie należy zrzucać [zwykle systemowe bazy danych].
Peter VARGA

2
@jruzafa Możesz użyć, -Bse "show databases"aby uniknąć dodatkowych wyników formatowania, a tym samym możesz usunąć | tr -d "| " | grep -v Database. W moim skrypcie eksportu wiersz ten brzmidatabases=`mysql -u $USER -p$PASSWORD -Bse "SHOW DATABASES;"
miquel

6
Wykonano skrypt odwrotny (import): gist.github.com/tenold/aa5e107d93c0f54436cb
Corey

1
OSTRZEŻENIE: Bardzo przydatny komentarz @AlBundy powyżej zawiera sekwencję znaków Unicode U+200C U+200Bmiędzy „c” i „h” słowa „schemat” . To łamie kopiowanie i wklejanie tego bitu. Więcej dyskusji na ten temat tutaj: meta.stackexchange.com/questions/170970/…
billynoah

29

Wszystkie odpowiedzi, które widzę na to pytanie, mogą mieć problemy z zestawami znaków w niektórych bazach danych z powodu problemu przekierowania wyjścia mysqldumpdo pliku w operatorze powłoki >.

Aby rozwiązać ten problem, wykonaj kopię zapasową za pomocą takiego polecenia

mysqldump -u root -p --opt --all-databases -r backup.sql

Aby wykonać dobre przywracanie BD bez problemów z zestawami znaków. Oczywiście możesz zmienić domyślny zestaw znaków, jak potrzebujesz.

mysql -uroot -p --default-character-set=utf8
mysql> SET names 'utf8';
mysql> SOURCE backup.sql;

Co robi -r?
Luca Reghellin,

3
-r ustawia nazwę pliku docelowego, w którym zapisywane będą dane wyjściowe.
NetVicious,

Czy lepiej byłoby użyć utf8mb4 zamiast utf8 dla zestawu znaków?
kojow7

1
To zależy od tego, co przechowujesz w swoich tabelach. Zestaw znaków utf8 ma maksymalnie 3 bajty na każdy znak. utf8mb4 ma maksymalnie 4 bajty na każdy znak. Oczywiście, jeśli twoja baza danych znajduje się na utf8mb4, powinieneś użyć tego zestawu znaków, aby go przywrócić.
NetVicious,

9

Na podstawie tych odpowiedzi stworzyłem skrypt, który tworzy kopie zapasowe wszystkich baz danych w osobne pliki, ale następnie kompresuję je w jednym archiwum z datą jako nazwą.

To nie poprosi o hasło, może być używane w cron. Aby zapisać hasło, .my.cnfsprawdź tę odpowiedź https://serverfault.com/a/143587/62749

Wykonane również z komentarzami dla tych, którzy nie są zbyt dobrze zaznajomieni ze skryptami bash.

#!/bin/bash

# This script will backup all mysql databases into 
# compressed file named after date, ie: /var/backup/mysql/2016-07-13.tar.bz2

# Setup variables used later

# Create date suffix with "F"ull date format
suffix=$(date +%F)
# Retrieve all database names except information schemas. Use sudo here to skip root password.
dbs=$(sudo mysql --defaults-extra-file=/root/.my.cnf --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema")
# Create temporary directory with "-d" option
tmp=$(mktemp -d)
# Set output dir here. /var/backups/ is used by system, 
# so intentionally used /var/backup/ for user backups.
outDir="/var/backup/mysql"
# Create output file name
out="$outDir/$suffix.tar.bz2"

# Actual script

# Check if output directory exists
if [ ! -d "$outDir" ];then
  # Create directory with parent ("-p" option) directories
  sudo mkdir -p "$outDir"
fi

# Loop through all databases
for db in $dbs; do
  # Dump database to temporary directory with file name same as database name + sql suffix
  sudo mysqldump --defaults-extra-file=/root/.my.cnf --databases "$db" > "$tmp/$db.sql"
done

# Go to tmp dir
cd $tmp

# Compress all dumps with bz2, discard any output to /dev/null
sudo tar -jcf "$out" * > "/dev/null"

# Cleanup
cd "/tmp/"
sudo rm -rf "$tmp"

1
Dobrze, że wróciłeś, aby dodać swoje dodatki. głos ode mnie
Fr0zenFyr

7

Po co parsować sformatowane dane wyjściowe, podczas gdy komenda mysql może robić bezpośrednio to, co chcesz?

databases=`mysql -u $USER -p$PASSWORD --batch --skip-column-names -e "SHOW DATABASES;" | grep -E -v "(information|performance)_schema"`

Wyświetla nazwy baz danych i tylko to.


3

Zachowaj ostrożność podczas eksportowania zi importowania do różnych wersji MySQL, ponieważ tabele mysql mogą mieć różne kolumny. Jeśli nie masz szczęścia, uprawnienia do grantu mogą nie działać. Stworzyłem ten skrypt (mysql_export_grants.sql), aby zrzucić granty na import do nowej bazy danych, na wszelki wypadek:

#!/bin/sh
stty -echo
printf 'Password: ' >&2
read PASSWORD
stty echo
printf "\n"
if [ -z "$PASSWORD" ]; then
        echo 'No password given!'
        exit 1
fi
MYSQL_CONN="-uroot -p$PASSWORD"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g'

3

Podczas zrzutu całej bazy danych. Oczywiście ma duże dane. Możesz więc wybrać poniżej dla lepszego:

Tworzenie kopii zapasowej:

mysqldump -u [user] -p[password]--single-transaction --quick --all-databases | gzip > alldb.sql.gz

Jeśli błąd

- Ostrzeżenie: Pomijanie danych tabeli mysql.event. Podaj jawnie opcję --events.

Posługiwać się:

mysqldump -u [user] -p --events --single-transaction --quick --all-databases | gzip > alldb.sql.gz

Przywracanie kopii zapasowej:

gunzip < alldb.sql.gz | mysql -u [user] -p[password]

Mam nadzieję, że to pomoże :)


3

Napisałem ten komentarz już ponad 4 lata temu i postanowiłem teraz udzielić odpowiedzi.

Skrypt z jruzafa może być nieco uproszczona:

#!/bin/bash

USER="zend"
PASSWORD=""
#OUTPUT="/Users/rabino/DBs"

#rm "$OUTPUTDIR/*gz" > /dev/null 2>&1

ExcludeDatabases="Database|information_schema|performance_schema|mysql"
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | egrep -v $ExcludeDatabases`

for db in $databases; do
    echo "Dumping database: $db"
    mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
    # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
done

Uwaga:

  1. Wykluczone bazy danych - głównie tabele systemowe - znajdują się w zmiennej ExcludeDatabases
  2. Należy pamiętać, że hasło jest podane w wierszu polecenia. Jest to uważane za niepewne. Przestudiuj to pytanie .

0

mysqldump -uroot -proot - wszystkie bazy danych> allDB.sql

Uwaga: -u „twoja nazwa użytkownika” -p „twoje hasło”


Twoja odpowiedź tak naprawdę nie rozwiązuje problemu, ponieważ prosi on o eksport wszystkich baz danych jednocześnie. Może możesz trochę poprawić: mysqldump -uroot -proot - wszystkie bazy danych> allDB.sql uwaga: -u "twoja nazwa użytkownika" -p "twoje hasło"
Marwan Salim

0

Wyeksportuj wszystkie bazy danych w Ubuntu

1 - mysqldump -u root -p --databases database1 database2 > ~/Desktop/databases_1_2.sql

LUB

2 - mysqldump -u root -p --all_databases > ~/Desktop/all_databases.sql

wprowadź opis zdjęcia tutaj

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.