В процессе создания автоматизированного решения для резервного копирования и восстановления весь сервер MySQL, Я наткнулся на mysql база данных, которая, кажется, содержит учетные записи пользователей, разрешения, метаданные и тому подобное. Следует ли делать резервную копию этой базы данных? Не сломается ли резервное копирование и попытка восстановления?
Как вы можете себе представить, я чертовски потратил время на поиск в Google "резервное копирование базы данных mysql".
Вот кое-что интересное, что стоит подумать: Резервное копирование mysql база данных сильно ограничивает вас тем, что вы можете восстановить такую базу данных только в ту же версию mysql, из которой вы выполняли резервное копирование. Вот почему:
Вот mysql.user из MySQL 5.0.45
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | | |
| x509_issuer | blob | NO | | | |
| x509_subject | blob | NO | | | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.01 sec)
Вот mysql.user из MySQL 5.1.32
mysql> desc mysql.user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
39 rows in set (0.00 sec)
Вот mysql.user из MySQL 5.5.12
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)
Если вы попытаетесь восстановить mysql.user до версии MySQL, для которой он не предназначен, это вызовет проблемы с произвольным доступом. Способ резервного копирования разрешений пользователей mysql без учета версии - это выгрузка грантов пользователей в SQL. Таким образом, пользовательские гранты полностью переносимы. Этого можно добиться двумя способами:
ВАРИАНТ №1: Использование MAATKIT
мк-шоу-гранты сгенерирует SQL, необходимый для любого экземпляра mysql, к которому вы подключаетесь. (Имейте в виду, что MAATKIT переносится на Percona Toolkit. Этот инструмент, скорее всего, будет называться pt-show-grants).
ВАРИАНТ № 2: Сценарий сброса СУБД SQL GRANTS
Написал свой эмулятор mk-show-grants. Анонимных пользователей не будет. Выглядит это так:
mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql
Использование любого из этих вариантов позволит создать более стабильную резервную копию пользовательских грантов.
НА ОТДЕЛЬНОЙ ЗАПИСИ
Теперь, если вы используете это лог-вывод вариант
[mysqld]
log-output=TABLE
база данных mysql будет заполнять журнал медленных операций (если он включен) в виде таблицы mysql в схеме mysql, а не текстового файла. Таким образом, создание физических резервных копий будет включать такие журналы на основе таблиц mysql. Поверьте, дискового пространства не стоит, если общий журнал и журнал медленных запросов включены и накапливаются в схеме mysql. Просто придерживайтесь параметров дампа MySQL Grants.
ОБНОВЛЕНИЕ 2011-09-19 15:54 EDT
Есть один очень важный фактор в поддержании резервных копий разрешений MySQL с помощью SQL Grants:
Каждый пользователь сообщает свой пароль в модифицированном формате MD5. Для mysql 4.0 и более ранних версий это 16-символьная шестнадцатеричная строка. Для mysql 4.1+ это 41 символ (звездочка, за которой следует шестнадцатеричная строка из 40 символов).
Перед восстановлением дампа SQL Grants проверьте файл дампа SQL Grants на наличие 16-значных шестнадцатеричных паролей. Если вы видите хотя бы один, вы должны добавить следующее в /etc/my.cnf (или my.ini для Windows) на сервере mysql, на который вы будете выполнять восстановление:
[mysqld]
old_password=1
В Прежний пароль Директива разрешает пароли из 16 и 41 символов сосуществовать и правильно аутентифицироваться в одном запущенном экземпляре mysql. Любые пароли, созданные в будущем, будут состоять из 16 символов.
Перезапуск MySQL не требуется. Просто запустите это:
SET GLOBAL old_password = 1;
Да, вы определенно хотите сделать резервную копию mysql база данных - это неотъемлемая часть вашего сервиса. Пока ты жестяная банка реконструировать его содержимое из другой информации, это непомерно сложно, если вы пытаетесь быстро вернуться к работе.
Вы можете восстановить базу данных mysql между версиями, по крайней мере, относительно недавних версий. Есть инструмент под названием mysql_upgrade включены в новые версии MySQL, которые обновят за вас системные таблицы.
Пока вы выполняете восстановление до аналогичной версии mysql, вы можете восстановить базу данных mysql из резервной копии. Если вы сомневаетесь, просто сделайте различие между схемами базы данных mysql (одной из резервных копий и другой из нового сервера mysql).