Настройка синхронизации пользователей в 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 строки удаление временных файлов.

Комментарии запрещены.