Настройка синхронизации пользователей в MySQL
Иногда возникает необходимость синхронизации пользователей баз данных в MySQL. Например существует несколько серверов MySQL и с них делаются дампы на резервный сервер, или нужно просто перенести рабочие базы данных с пользователями на другой сервер.
+----+ | db1|-------+ +----+ | +----+ | +----------+ | db2|-------+------| db backup| +----+ | +----------+ +----+ | | db3|-------+ +----+ |
Схема для примера, когда базы данных и пользователей копируются на резервный сервер.
С базами данных все просто, делается дамп c помощью команды mysqldump, он передается на резервный сервер и на нем уже импортируется. А вот с пользователями так не получиться, т.к. на целевом сервере уже существует своя таблица user в базе mysql и простым импортом здесь не обойтись.
В такой ситуации нам поможет следующий скрипт:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | #!/bin/sh #Задаем переменные для локального сервера _lusr="root" _lpass="local_root_password" _lhost="localhost" _tmp="/tmp/select_users.sql" #Исключаем не нужных пользователей _user="root" #Задаем переменные для резервного сервера _rusr="root" _rpass="remote_root_password" _rhost="localhost" #Задаем переменные для ssh резервного сервера _rsshusr="remote_ssh_user" _rsshhost="ip_address_remote_host" _keyfile="/path/to/key/file" mysql -u"$_lusr" -p"$_lpass" -h "$_lhost" -B -N -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM user" mysql | mysql -u "$_lusr" -p"$_lpass" -h "$_lhost" \ | grep -v "$_user" \ | sed 's/Grants for .*/#### &/' | sed 's/$/;/g' >> "$_tmp" #Пересылка sql-файла на резервный сервер echo "*** Send sql_file to ${rsshhost}..." scp -i $_keyfile "$_tmp" ${_rsshusr}@${_rsshhost}:$_tmp #Загружаем выбранных пользователей в базу ssh -i $_keyfile ${_rsshusr}@${_rsshhost} mysql -u"$_rusr" -p"$_rpass" -h "$_rhost" < "$_tmp" ssh -i $_keyfile ${_rsshusr}@${_rsshhost} rm "$_tmp" rm -f "$_tmp" |
Небольшие комментарии по работе скрипта:
- 22 — 24 строке идет выборка пользователей и формирование файла sql запроса. Файл будет выглядеть следующим образом
#### Grants for localuser1@localhost; GRANT USAGE ON *.* TO 'localuser1'@'localhost' IDENTIFIED BY PASSWORD 'crypt_password_for_localuser1'; GRANT ALL PRIVILEGES ON `db1`.* TO 'localuser1'@'localhost'; GRANT ALL PRIVILEGES ON `db2`.* TO 'localuser1'@'localhost'; GRANT ALL PRIVILEGES ON `db3`.* TO 'localuser1'@'localhost'; #### Grants for localuser2@localhost; GRANT USAGE ON *.* TO 'localuser2'@'localhost' IDENTIFIED BY PASSWORD 'crypt_password_for_localuser2'; GRANT ALL PRIVILEGES ON `db4`.* TO 'localuser1'@'localhost'; |
- 28 стока идет пересылка sql файла на резервный сервер. Хочу обратить внимание, что в данном примере показана авторизация ssh по ключу. Настройка данного механизма выходит за рамки этой статьи, поэтому подробно на нем останавливаться не буду.
- 31 строка выполнение sql файла на резервном сервере. Т.е. фактическое создание пользователей и назначение прав на базы данных.
- 32, 34 строки удаление временных файлов.