Mysql бэкап базы данных windows — Ваш верный помощник с OS Windows

Задача, которая стояла передо мной:
Есть сервер с mysql под управлением windows server 2008 R2, на котором, в числе прочего, крутится mysql с несколькими десятками баз данных, число и состав которых периодически меняется. Нужно организовать ежедневный бекап этих баз без остановки mysql сервера, причем таким образом, чтобы каждая база попадала в отдельных архив. Эта, на первый взгляд простейшая задача (возможно, так оно и есть) для меня оказалась достаточно сложной.

Что нам говорит гугл?

О том, что есть mysqlhotcopy и mysqldump. Первый работает прямо с файлами баз данных, второй — делает дампы с помощью запросов.

Заставить работать mysqlhotcopy даже с простейшими параметрами я не смог, и погуглив, пришел к выводу (поправьте меня если ошибаюсь) что для windows данный скрипт не приспособлен.

Начинал я именно с mysqlhotcopy, потому что работать непосредственно с файлами в моем случае проще — просто подсунул файлы мускулю и работай. Дамп же, хоть и весит меньше за счет отсутствия индексов, еще нужно импортировать. Но, раз другого способа без остановки mysql я не нашел, будем делать дампы.

Здесь я нашел отличное руководство по ключам скрипта. Итак, он может сделать общий дамп всех баз в один файл (крайне неудобно), или сделать дампы перечисленных баз. Так как число и имена баз у нас меняются, то писать список не вариант — каждый раз править его замучаемся. Значит нужен скрипт, который будет подставлять в mysqldump каждую базу поочередно.

Реализовал я это так — батник смотрит какие папки лежат в директории данных mysql и в цикле подставляет имя каждой из них (которое и является именем базы данных) в строку параметров mysqldump.

SET SOURCEDIR=E:\xampp\mysql\data\

set hour=%TIME:~0,2%
set minute=%TIME:~3,2%
set second=%TIME:~6,2%
set HHMMSS=%hour%-%minute%

for /d %%i in (%SOURCEDIR%\*) do "E:\xampp\mysql\bin\mysqldump.exe" -uusername -hlocalhost -ppassword -c -n %%~ni | "c:\Program Files\7-Zip\7z.exe" a -tgzip -si"%%~ni_%DATE%_%HHMMSS%.sql" "D:\backups\data\%DATE%_%HHMMSS%\%%~ni.sql.gzip"

eachfile.exe -purge -r -w -e -d 13 -l 0 -dir D:\backups\data\

exit

Полученный дамп сразу же архивируется при помощи 7-zip в формат gzip (чтобы полученный файл можно было без распаковки скормить мускулю). Ну а утилитка eachfile удалит устаревшие бекапы.

В процессе гугления также наткнулся на программу MySQL Backup Tool тестировать которую, однако, не решился.

Источник

Summary: in this tutorial, you will learn how to make a backup of all the databases on a MySQL Server using the mysqldump program and restore them using the mysql program.

Creating sample databases

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Next, show all the databases:

show databases;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The command returns four system databases.

Then, create three databases called testdb1, testdb2, and testdb3:

CREATE DATABASE testdb1;
CREATE DATABASE testdb2;
CREATE DATABASE testdb3;Code language: SQL (Structured Query Language) (sql)

After that, show all the databases again:

show databases;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
| testdb2            |
| testdb3            |
+--------------------+
7 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Finally, exit the mysql program:

exitCode language: SQL (Structured Query Language) (sql)

Backing up all databases

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, use the following command to back up all databases on the MySQL server:

mysqldump -h localhost -u root -p --all-databases > D:\backup\all_databases.sqlCode language: SQL (Structured Query Language) (sql)

Let’s break down the command:

mysqldump: This is the command-line utility for MySQL that allows you to dump all databases into a file.
-h localhost: This option specifies the hostname where the MySQL server is running. In this case, it’s set to “localhost,” indicating that the MySQL server is on the same machine as the command is being executed.
-u root: This option specifies the MySQL user to be used for the connection. In this case, it’s set to “root,” which is a common default superuser account in MySQL.
-p: This option prompts the user for the MySQL password. After entering the command, you will be prompted to enter the password for the specified user (in this case, the root user).
–all-databases: This option tells mysqldump to dump all databases on the MySQL server, not just a specific one. It includes the structure and data for all databases including the system databases.
> D:\backup\all_databases.sql: This part of the command uses the output redirection symbol (>) to send the output of the mysqldump command to a file. In this case, the file is specified as D:\backup\all_databases.sql. The file will contain the SQL statements necessary to recreate all databases and their data.

Third, examine the all_database.sql file in the D:\backup directory.

Accidentally removing a database

First, open the Command Prompt on Windows or Terminal on Unix-like systems and connect to the MySQL server:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

Second, drop the testdb1 and testdb2 databases:

drop database testdb1;
drop database testdb2;Code language: SQL (Structured Query Language) (sql)

Third, show all the databases:

 show databases;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb3            |
+--------------------+
5 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Finally, exit the mysql program:

exitCode language: SQL (Structured Query Language) (sql)

Suppose you want to restore the testdb1 and testdb2 from the backup created by the mysqldump program.

Restoring all databases

First, open the Command Prompt on Windows or Terminal on Unix-like systems.

Second, use the mysql program to restore all databases from the backup created by the mysqldump program:

mysql -h localhost -u root -p < D:\backup\all_databases.sqlCode language: SQL (Structured Query Language) (sql)

Third, connect to the MySQL server:

mysql -h localhost -u root -pCode language: SQL (Structured Query Language) (sql)

Finally, show all the databases:

show databases;Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb1            |
| testdb2            |
| testdb3            |
+--------------------+
7 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output indicates that the testdb1 and testdb2 databases have been restored successfully.

Summary

Use the mysqldump with the --all-databases option to dump all databases on a MySQL server into a file.
Use the mysql program to restore all the databases from a backup file created by the mysqldump program.

Was this tutorial helpful?

Источник

Apr
21

Unlike on Linux, when MySQL is running on Windows, most of sys-admins including myself 🙂 found that backup MySQL Databases on Windows is little bit hard. When trying to automate it, then it would definitely become challenge . However there are lots of free and commercial tools are available to automate MySQL backup process on windows. Here we are going to discus how to achieve same using simple windows batch script. Later we discuss automate the batch script using Windows task scheduler

01) Create batch file

Open notepad and save following script as batch file (.bat) and make sure to change all SET parameters as you need. If you install XAMPP then mysqldump.exe location would be similar as below otherwise you need to change it.

@echo off

For /f «tokens=2-4 delims=/ « %%a in (‘date /t’) do (set mydate=%%c-%%a-%%b)

For /f «tokens=1-2 delims=/:» %%a in («%TIME%») do (set mytime=%%a%%b)

SET backupdir=C:\xampp\htdocs\backup

SET mysqluername=root

SET mysqlpassword=somepassword

SET database=dbname

C:\xampp\mysql\bin\mysqldump.exe —uroot —pPASSWORD %database% > %backupdir%\%database%_%mydate%_%mytime%_.sql

02) Automate the MySQL Backup process

i) Open task scheduler

Open a command prompt. To open a command prompt, click Start , click All Programs , click Accessories , and then click Command Prompt .
At the command prompt, type Taskschd.msc .

For alternative options refer this :- https://technet.microsoft.com/en-us/library/cc721931.aspx

ii) Create New Task

Click on “Create Task…” from right hand side. it will open up “create task” sub windows

please make sure to select “Run whether user is logged on or not” and tick “Run with highest privileges“. You may can change user but
recommend to select user with admin privileges , later you may need to provide credentials of that user.

iii) Schedule the time

From “Triggers” tab select how often back process should happen and it’s time

iv) Set backup process script

From the “Actions” tab select previously saved bat file.

v) Click “OK” and save the task

That’s it, 🙂 feel to comment here if you any doubts.

Источник

Резервную копию можно создать в графическом веб-интерфейсе в phpMyAdmin. Если вы хотите сделать бэкап сразу всех баз данных, то перейдите на главную страницу phpMyAdmin, оттуда перейдите во вкладку Экспорт. Далее всё достаточно просто.

Аналогично при бэкапе отдельных баз данных: перейдите на страницу интересующей БД, а далее во вкладку Экспорт.

phpMyAdmin — это прослойка на PHP между СУБД и пользователем, по этой причине скорость создания дампа очень большой базы займёт больше времени, чем если создавать его напрямую через специальную программу от MySQL или MariaDB.

Если вы решили сделать бэкап базы данных MySQL из командной строки в Windows, то для этого понадобиться утилита mysqldump.exe, она поставляется вместе с MySQL и расположена в каталоге с установленной MySQL/MariaDB в папке bin. Например, если СУБД установлена в C:\Server\bin\mysql-8.0, то программа mysqldump.exe будет находиться в папке C:\Server\bin\mysql-8.0\bin\.

Для её использования откройте командную строку и перетащите туда программу. Программу можно использовать с разнообразными опциями.

Внимание! Из-за особенностей PowerShell по работе с кодировками, в экспортируемых базах данных может быть испорчены все нелатинские символы. Подробности смотрите в статье «Решение проблем с кодировкой вывода в PowerShell и сторонних утилитах, запущенных в PowerShell».

Если вы хотите сделать резервную копию всех баз данных в один файл, то выполните:

mysqldump.exe -u root -p --all-databases --result-file=all-databases.sql

Кстати, файл нужно искать в той папке, которую вы видите в приглашении командной строки.

Для того, чтобы сделать резервную копию только одной базы данных (например, rsyslog):

mysqldump.exe -u root -p rsyslog --result-file=rsyslog.sql

Чтобы сделать резервную копию нескольких баз данных используйте опцию —databases, а после него через пробел перечислите желаемые для бэкапа базы данных:

mysqldump.exe -u root -p --databases rsyslog syslog --result-file=rsyslog_syslog.sql

Чтобы сделать резервную копию только одной таблицы (wp_posts) из базы данных (wordpress):

mysqldump.exe -u root -p wordpress wp_posts --result-file=wordpress_posts.sql

Для того, чтобы сделать резервную копию нескольких таблиц, перечислите их через пробел после названия БД:

mysqldump.exe -u root -p wordpress wp_posts wp_comments --result-file=wordpress_posts_comments.sql

Восстановление баз данных из резервной копии без phpMyAdmin в Windows

Для экспорта баз данных, phpMyAdmin работает довольно неплохо. Но с импортом возникло ряд проблем. Намного проще и в разы быстрее сделать импорт без phpMyAdmin и других помощников на PHP скриптах.

Для этого откройте командную строку Windows. Нажмите комбинацию WIN+x, затем выберите Windows PowerShell. Чтобы выйти из PowerShell и перейти в командную строку, выполните:

cmd

Затем смените текущую папку (укажите путь до подпапки bin внутри папки, куда установлена MySQL или MariaDB — у вас это может быть другой путь):

cd C:\Server\bin\mysql-8.0\bin\

И запустите команду вида:

mysql -uroot < C:\путь\до\файла\резервной_копии.sql

Например, моя резервная копия содержится в файле localhost.sql, который размещён в папке C:\Users\Alex\Downloads\, тогда команда выглядит так:

mysql -uroot < C:\Users\Alex\Downloads\localhost.sql

Связанные статьи:

mysqldump в PowerShell портит нелатинские символы при экспорте базы данных (РЕШЕНО) (97%)
Как в phpMyAdmin поменять настройки экспорта по умолчанию (58.8%)
MariaDB для Windows (55.8%)
Как обновить MySQL (55.8%)
Как обновить MariaDB в Windows (55.8%)
Как проверить конфигурационный файл Apache (RANDOM — 50%)

Источник