Бекап и восстановление баз данных MySQL

Есть масса информации о процессе бекапов баз данных MySQL, но в большинстве случаев, повествуется о простом запуске mysqldump. На практиче же, зачастую нужно иметь хоть какую информацию о процессе бекпа, его целостности и т.д.

Перед созданием бекапа, было бы полезно узнать размер баз данных. Сделать это можно SQL запросом:

SELECT table_schema "Data Base Name", sum( data_length + index_length )/1024/1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;
SELECT table_schema "Data Base Name", sum( data_length + index_length )/1024/1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema;

результом будет следующая таблица:

+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| db_1               |         106.53884888 |
| db_10              |          30.42764664 |
| db_2               |        3608.50179482 |
| db_20              |          75.88463402 |
| db_3               |        2505.82299519 |
| db_4               |         914.69011402 |
| db_7               |        1871.93762398 |
| information_schema |           0.00878906 |
| mysql              |           0.66736794 |
| performance_schema |           0.00000000 |
| roundcube          |           0.42187500 |
+--------------------+----------------------+
+--------------------+----------------------+
| Data Base Name     | Data Base Size in MB |
+--------------------+----------------------+
| db_1               |         106.53884888 |
| db_10              |          30.42764664 |
| db_2               |        3608.50179482 |
| db_20              |          75.88463402 |
| db_3               |        2505.82299519 |
| db_4               |         914.69011402 |
| db_7               |        1871.93762398 |
| information_schema |           0.00878906 |
| mysql              |           0.66736794 |
| performance_schema |           0.00000000 |
| roundcube          |           0.42187500 |
+--------------------+----------------------+

При создани бекапа, размер файла не будет совпадать с указанным в таблице. Но позволит хоть примерно опеределить занимаемое место на диске. Аналогичным способом можно подсчитать размер таблиц в базе данных. Меняем в запросе databasename на имя вашей базы данных и получаем:

SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "databasename";
SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "databasename";
+-----------------------------------+------------+-------------+--------------+------------+
| TABLE_NAME                        | table_rows | data_length | index_length | Size in MB |
+-----------------------------------+------------+-------------+--------------+------------+
| wp_commentmeta                    |       1003 |     3319680 |        84992 |       3.25 |
| wp_comments                       |        934 |      746276 |        93184 |       0.80 |
| wp_gdsr_data_article              |         39 |        3240 |         2048 |       0.01 |
| wp_gdsr_data_category             |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_data_comment              |        497 |       22204 |         7168 |       0.03 |
| wp_gdsr_ips                       |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_moderate                  |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_multis                    |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_multis_data               |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_multis_trend              |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_multis_values             |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_templates                 |         48 |       19224 |         3072 |       0.02 |
| wp_gdsr_votes_log                 |        478 |       24356 |        34816 |       0.06 |
| wp_gdsr_votes_trend               |        464 |       16708 |        14336 |       0.03 |
| wp_links                          |          0 |           0 |         1024 |       0.00 |
| wp_options                        |        349 |      895084 |        25600 |       0.88 |
| wp_postmeta                       |        662 |      135948 |        43008 |       0.17 |
| wp_posts                          |        471 |     1946908 |        55296 |       1.91 |
| wp_subscribe_reloaded_subscribers |        134 |        9488 |        11264 |       0.02 |
| wp_term_relationships             |        177 |        3717 |        11264 |       0.01 |
| wp_term_taxonomy                  |        119 |        4648 |         9216 |       0.01 |
| wp_terms                          |        119 |        3492 |        15360 |       0.02 |
| wp_usermeta                       |         34 |       46644 |        10240 |       0.05 |
| wp_users                          |          1 |         132 |         6144 |       0.01 |
+-----------------------------------+------------+-------------+--------------+------------+
24 rows in set (0.20 sec)
+-----------------------------------+------------+-------------+--------------+------------+
| TABLE_NAME                        | table_rows | data_length | index_length | Size in MB |
+-----------------------------------+------------+-------------+--------------+------------+
| wp_commentmeta                    |       1003 |     3319680 |        84992 |       3.25 |
| wp_comments                       |        934 |      746276 |        93184 |       0.80 |
| wp_gdsr_data_article              |         39 |        3240 |         2048 |       0.01 |
| wp_gdsr_data_category             |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_data_comment              |        497 |       22204 |         7168 |       0.03 |
| wp_gdsr_ips                       |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_moderate                  |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_multis                    |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_multis_data               |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_multis_trend              |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_multis_values             |          0 |           0 |         1024 |       0.00 |
| wp_gdsr_templates                 |         48 |       19224 |         3072 |       0.02 |
| wp_gdsr_votes_log                 |        478 |       24356 |        34816 |       0.06 |
| wp_gdsr_votes_trend               |        464 |       16708 |        14336 |       0.03 |
| wp_links                          |          0 |           0 |         1024 |       0.00 |
| wp_options                        |        349 |      895084 |        25600 |       0.88 |
| wp_postmeta                       |        662 |      135948 |        43008 |       0.17 |
| wp_posts                          |        471 |     1946908 |        55296 |       1.91 |
| wp_subscribe_reloaded_subscribers |        134 |        9488 |        11264 |       0.02 |
| wp_term_relationships             |        177 |        3717 |        11264 |       0.01 |
| wp_term_taxonomy                  |        119 |        4648 |         9216 |       0.01 |
| wp_terms                          |        119 |        3492 |        15360 |       0.02 |
| wp_usermeta                       |         34 |       46644 |        10240 |       0.05 |
| wp_users                          |          1 |         132 |         6144 |       0.01 |
+-----------------------------------+------------+-------------+--------------+------------+
24 rows in set (0.20 sec)

Подобная информация пригодится вам, если база очень большая и есть смысл делать бекап не всей базы сразу, а по отдельности каждую таблицу.

Самым простой и известный способ сделать бекап одной командой:

mysqldump -hlocalhost -uusername -p database_name > database_name.sql
mysqldump -hlocalhost -uusername -p database_name | gzip -9 > database_name.sql.gz
mysqldump -hlocalhost -uusername -p database_name > database_name.sql
mysqldump -hlocalhost -uusername -p database_name | gzip -9 > database_name.sql.gz

При выполнении второй команды, файл базы данных автоматически будет сжиматься в архив Gzip, без создания промежуточного файла, который занимает место на диске. Особо полезно на виртуальном хостинге с ограниченным дисковым местом.

Восстанавливаем базу данных database_name из файл дампа database_name.sql или её архива:

mysql -hlocalhost -uusername -p database_name < database_name.sql
 
unzip -p database_name.sql.zip | mysql -uusername -p database_name
gunzip < database_name.sql.gz | mysql -uusername -p database_name
mysql -hlocalhost -uusername -p database_name < database_name.sql

unzip -p database_name.sql.zip | mysql -uusername -p database_name
gunzip < database_name.sql.gz | mysql -uusername -p database_name

Опции команд mysql и mysqldump очень просты в данном случае. Прописывать опцию и её значение можно как слитно, так и раздельно (кроме опции указания пароля "-p"). Часто используемые опции и некоторые полезные в особых случаях:

  • -h - адрес сервера mysql
  • -u - имя пользователя для авторизации
  • -p - запрос пароля, пароль можно указать в команде и писать нужно слитно с командой
  • -P - задать нестандартный порт подключения. Порт по умолчанию - 3306
  • --hex-blob - опция необходима для правильного бекапа данных хранящихся в бинарных форматах: BINARY, VARBINARY, BLOB и BIT
  • --no-data - бекап структуры базы, без её данных
  • --all-databases - бекап всех баз данных на сервере
  • --lock-tables - полезно для MyISAM и позволяет блокировать таблицу с помощью "READ LOCAL", давая производить INSERT во время бекапа
  • --single-transaction - для баз в формате InnoDB, позволяет делать бекап без блокировки таблиц
  • --quick - ускоряет процесс бекапа, записывая данные сразу на диск без формирования вывода в stdout

  • Не возникает каких-либо лишних вопросов, пока размер баз данных мал. С большими базами, может показаться, что процесс бекапа завис. В подобном случае, к нам на помощь придет утилита PV. С её помощью можно видеть процесс в удобом виде. Пример с восстановленим базы и pv:

    pv database_name.sql | mysql database_name
    96.8MB 0:00:17 [5.51MB/s] [==>                             ] 11% ETA 0:02:10
     
    unzip -p database_name.sql.zip | pv | mysql -uusername -p database_name
    gunzip < database_name.sql.gz | pv | mysql -uusername -p database_name
    pv database_name.sql | mysql database_name
    96.8MB 0:00:17 [5.51MB/s] [==>                             ] 11% ETA 0:02:10
    
    unzip -p database_name.sql.zip | pv | mysql -uusername -p database_name
    gunzip < database_name.sql.gz | pv | mysql -uusername -p database_name

    Аналогично с созданием дампа, PV и сжатием. Менее информативно, но все же лучше, чем ничего не видеть о процессе:

    mysqldump database_name | pv > database_name.sql.gz
     239MB 0:00:03 [ 111MB/s] [       <=>                                       ]
     
    mysqldump database_name | pv | gzip -9 -c > database_name.sql.gz
    mysqldump database_name | pv > database_name.sql.gz
     239MB 0:00:03 [ 111MB/s] [       <=>                                       ]
    
    mysqldump database_name | pv | gzip -9 -c > database_name.sql.gz


    Обязательно проверяйте целостность созданого дампа на тестовой базе данных! Если Вы преносите базу данных с одного сервера на другой, где различаются настройки кодировки соединения с базой данных, то бекап может получиться с "кривыми" данными в виде невнятных символов. В таких случая, при создании дампа, нужно указывать опции default-character-set и skip-set-charset. Пример:

    mysqldump -udatabase_name -p --default-character-set=latin1 --skip-set-charset database_name > database_name.sql
    mysqldump -udatabase_name -p --default-character-set=latin1 --skip-set-charset database_name > database_name.sql

    где latin1 - кодировка установленная на сервере MySQL.


    При создании дампа базы, процесс может завершить с некоторыми ошибками:

    ERROR 2013 (HY000) at line 222: Lost connection to MySQL server during query
     
    # или
    ERROR 2006 (HY000) at line 450: MySQL server has gone away
     
    # или такие
    MySQL server has gone away (error 2006)
    ERROR 2013 (HY000) at line 222: Lost connection to MySQL server during query
    
    # или
    ERROR 2006 (HY000) at line 450: MySQL server has gone away
    
    # или такие
    MySQL server has gone away (error 2006)

    Избавить от них, можно увеличением значений переменных wait_timeout и max_allowed_packet в my.cnf и последующим перезапуском MySQL сервера:

    wait_timeout = 28800
    max_allowed_packet = 64M
    wait_timeout = 28800
    max_allowed_packet = 64M
  • wait_timeout - задает время ожидания завершения запроса в секундах (28800 = 8 часов)
  • max_allowed_packet - размер пакета данных при вставке в таблицу

  • К сожалению, имея дело с очень большими базами данных в гигабайты и десятки гигабайт, подобный процесс может длиться большое количество времени и не факт, что успешно. Здесь стоит использовать специальные утилиты mysqlhotcopy для MyISAM, xtrabackup для InnoDB от создателей Percona MySQL Server. А лучше всего иметь MySQL Slave сервер реплики и производит все бекапы на нем.

    Более полный список опции для mysqldump, можно узнать из справки man или, к примеру, тут http://www.mysql.ru/docs/man/mysqldump.html
    Так же отличный ресурс на русском http://adw0rd.com/2009/6/7/mysqldump-and-cheat-sheet/

    VN:F [1.9.22_1171]
    Рейтинг: 0.0/10 (0 голоса(ов))

    Добавить комментарий

    Ваш e-mail не будет опубликован. Обязательные поля помечены *