Настройка репликации в MySQL + создание бекапов у нагруженного сервера MySQL с помощью репликации — различия между версиями

Материал из Домашняя записная книжка (c) Artful
Перейти к: навигация, поиск
(8. Создание бекапа с помощью сервера репликации)
 
(не показано 26 промежуточных версии этого же участника)
Строка 1: Строка 1:
 
Мы имеем два физических сервера, неважно какая ОС установлена, FreeBSD / Linux. На обоих серверах стоит Mysql (я лично тестировал на связках Ubuntu 9.04/9.10 FreeBSD 8.0 и Mysql5.1)
 
Мы имеем два физических сервера, неважно какая ОС установлена, FreeBSD / Linux. На обоих серверах стоит Mysql (я лично тестировал на связках Ubuntu 9.04/9.10 FreeBSD 8.0 и Mysql5.1)
 
На одном сервере стоит рабочий MySQL сервер (IP = 10.0.0.1). На втором свежеустановленный MySQL (IP = 10.0.0.2).
 
На одном сервере стоит рабочий MySQL сервер (IP = 10.0.0.1). На втором свежеустановленный MySQL (IP = 10.0.0.2).
 
  
 
===1.Открываем конфигурационный файл mysql на основном сервере===
 
===1.Открываем конфигурационный файл mysql на основном сервере===
Для Linux: /etc/mysql
+
Для Linux: /etc/mysql<br>
 
+
Для FreeBSD: /var/db/mysql<br>
Для FreeBSD: /var/db/mysql
+
 
+
Так же просим учесть, что у ОС FreeBSD его по умолчанию нету, взять дефолтный конфигурационный файл можно в папке /usr/local/share/mysql
+
 
+
<tt>(my-small.cnf, my-medium.cnf, my-large.cnf или my-huge.cnf - в зависимости от конфигурации сервера и нагрузки на него.
+
  
 +
Так же просим учесть, что у ОС FreeBSD его по умолчанию нету, взять дефолтный конфигурационный файл можно в папке /usr/local/share/mysql<br>
 +
<tt>(my-small.cnf, my-medium.cnf, my-large.cnf или my-huge.cnf - в зависимости от конфигурации сервера и нагрузки на него.<br>
 
Не забудьте что при копировании дефолтного конфигурационного файла в папку /var/db/mysql ему необходимо задать имя my.cnf).</tt>
 
Не забудьте что при копировании дефолтного конфигурационного файла в папку /var/db/mysql ему необходимо задать имя my.cnf).</tt>
  
и редактируем следующие поля:
+
и редактируем следующие поля:<br>
 
+
для Linux:
;для Linux:
+
<syntaxhighlight lang="xml">
: server-id              = 1
+
server-id              = 1
: log_bin                = /var/log/mysql/mysql-bin.log
+
log_bin                = /var/log/mysql/mysql-bin.log
: expire_logs_days        = 10
+
expire_logs_days        = 10
: max_binlog_size        = 1024M
+
max_binlog_size        = 1024M
;для FreeBSD:
+
</syntaxhighlight>
: server-id=1
+
для FreeBSD:
: log-bin=mysql-bin
+
<syntaxhighlight lang="xml">
: expire_logs_days= 10
+
server-id=1
: max_binlog_size=1024M
+
log-bin=mysql-bin
 
+
expire_logs_days= 10
Параметры означают следующее:
+
max_binlog_size=1024M
server-id - Уникальный server_id
+
</syntaxhighlight>
log-bin - указывает путь для ведения логов и формат имени файлов для Linux, или формат файла для FreeBSD
+
expire_logs_days - сколько по времени днях хранить логи, учтите что на нагруженном сервере за 10 дней объем может достигать 50-100Gb
+
max_binlog_size - какой размер файла логов делать
+
  
И теперь самый неприятный момент - Нужно перезапустить основной сервер MySQL
+
Параметры означают следующее:<br>
Для Linux
+
server-id - Уникальный server_id<br>
/etc/init.d/mysql restart
+
log-bin - указывает путь для ведения логов и формат имени файлов для Linux, или формат файла для FreeBSD<br>
Для FreeBSD
+
expire_logs_days - сколько по времени днях хранить логи, учтите что на нагруженном сервере за 10 дней объем может достигать 50-100Gb<br>
/usr/local/etc/rc.d/mysql-server restart
+
max_binlog_size - какой размер файла логов делать<br>
 +
<br>
 +
'''И теперь самый неприятный момент - Нужно перезапустить основной сервер MySQL'''<br>
 +
Для Linux<br>
 +
<tt>/etc/init.d/mysql restart</tt><br>
 +
Для FreeBSD<br>
 +
<tt>/usr/local/etc/rc.d/mysql-server restart</tt><br>
  
 
===2. Заходим в консоль MySQL сервер. (на основном сервере)===
 
===2. Заходим в консоль MySQL сервер. (на основном сервере)===
Если у пользователя root нету пароля - то просто вводим mysql [Enter]
+
Если у пользователя root нету пароля - то просто вводим mysql [Enter]<br>
Если пароль есть - то вводим mysql -u root -p [Enter] и вводим пароль от пользователя root [Enter]
+
Если пароль есть - то вводим mysql -u root -p [Enter] и вводим пароль от пользователя root [Enter]<br>
  
 +
<syntaxhighlight lang="xml">
 
root@mysql:~# mysql
 
root@mysql:~# mysql
 
Welcome to the MySQL monitor.  Commands end with; or \g.
 
Welcome to the MySQL monitor.  Commands end with; or \g.
Строка 51: Строка 51:
  
 
mysql>
 
mysql>
 +
</syntaxhighlight>
  
  
3. Вводим одну команду
+
===3. Вводим одну команду===
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username'@'10.0.0.2' IDENTIFIED BY 'password';
+
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username'@'10.0.0.2' IDENTIFIED BY 'password';<br>
где
+
где<br>
username - имя пользователя
+
username - имя пользователя<br>
password - пароль
+
password - пароль<br>
10.0.0.2 - ip сервера репликации (поменяйте на свой IP)
+
10.0.0.2 - ip сервера репликации (поменяйте на свой IP)<br>
  
4. Открываем конфигурационный файл MySQL на резервном сервере.
+
===4. Открываем конфигурационный файл MySQL на резервном сервере.===
 
и редактируем следующие параметры
 
и редактируем следующие параметры
 +
<syntaxhighlight lang="xml">
 
server-id = 2
 
server-id = 2
 
master-host = 10.0.0.1
 
master-host = 10.0.0.1
Строка 67: Строка 69:
 
master-user = username
 
master-user = username
 
master-password = password
 
master-password = password
 +
</syntaxhighlight>
  
Параметры означают следующее:
+
Параметры означают следующее:<br>
server-id - уникальный id сервера, значение должно отличаться от значения первого сервера
+
server-id - уникальный id сервера, значение должно отличаться от значения первого сервера<br>
master-host = IP адрес на котором работает мастер-сервер
+
master-host = IP адрес на котором работает мастер-сервер<br>
master-port = Порт на котором работает мастер-сервер
+
master-port = Порт на котором работает мастер-сервер<br>
master-user = имя пользователя, которое мы задали в пункте 3
+
master-user = имя пользователя, которое мы задали в пункте 3<br>
master-password = пароль, который мы задали в пункте 3
+
master-password = пароль, который мы задали в пункте 3<br>
  
5. Перезапускаем MySQL на резервном сервере, ОБЯЗАТЕЛЬНО
+
===5. Перезапускаем MySQL на резервном сервере, ОБЯЗАТЕЛЬНО===
Для Linux
+
Для Linux<br>
/etc/init.d/mysql restart
+
/etc/init.d/mysql restart<br>
Для FreeBSD
+
Для FreeBSD<br>
/usr/local/etc/rc.d/mysql-server restart
+
/usr/local/etc/rc.d/mysql-server restart<br>
Заходим в консоль mysql
+
Заходим в консоль mysql<br>
Если у пользователя root нету пароля - то просто вводим mysql [Enter]
+
Если у пользователя root нету пароля - то просто вводим mysql [Enter]<br>
Если пароль есть - то вводим mysql -u root -p [Enter] и вводим пароль от пользователя root [Enter]
+
Если пароль есть - то вводим mysql -u root -p [Enter] и вводим пароль от пользователя root [Enter]<br>
и после вводим 1 команду: STOP SLAVE; [Enter]
+
и после вводим 1 команду: STOP SLAVE; [Enter]<br>
 +
<syntaxhighlight lang="xml">
 
root@rep:~# mysql
 
root@rep:~# mysql
 
Welcome to the MySQL monitor.  Commands end with; or \g.
 
Welcome to the MySQL monitor.  Commands end with; or \g.
Строка 93: Строка 97:
 
mysql> STOP SLAVE;
 
mysql> STOP SLAVE;
 
Query OK, 0 rows affected (0,02 sec)
 
Query OK, 0 rows affected (0,02 sec)
 +
</syntaxhighlight>
  
 
+
===6. Переходим к созданию бекапа с рабочего сервера===
6. Переходим к созданию бекапа с рабочего сервера
+
 
На основном сервере вводим команду:
 
На основном сервере вводим команду:
 +
<syntaxhighlight lang="xml">
 
mysqldump --user=root --password=root_password --extended-insert --all-databases --master-data --lock-tables > /tmp/backup.sql
 
mysqldump --user=root --password=root_password --extended-insert --all-databases --master-data --lock-tables > /tmp/backup.sql
 +
</syntaxhighlight>
  
Параметры означают следующее:
+
Параметры означают следующее:<br>
--user=root - имя пользователя, от чьего имени будем делать бекап
+
--user=root - имя пользователя, от чьего имени будем делать бекап<br>
--password=root_password - пароль пользователя root
+
--password=root_password - пароль пользователя root<br>
--extended-insert - Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).
+
--extended-insert - Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).<br>
--all-databases - Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.
+
--all-databases - Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.<br>
--lock-tables - Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.
+
--lock-tables - Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.<br>
 
Внимание, бекап лучше всего делать в самое не загруженное время работы MySQL (например в 6 утра)
 
Внимание, бекап лучше всего делать в самое не загруженное время работы MySQL (например в 6 утра)
  
7. Установка бекапа на сервер репликации
+
===7. Установка бекапа на сервер репликации===
 
Далее, копируем файл /tmp/backup.sql на резервный севрер и загружаем его командой:
 
Далее, копируем файл /tmp/backup.sql на резервный севрер и загружаем его командой:
 +
<syntaxhighlight lang="xml">
 
mysql --user=root --password=root_password < /tmp/backup.sql
 
mysql --user=root --password=root_password < /tmp/backup.sql
 +
</syntaxhighlight>
  
После чего заходим в консоль mysql и вводим команду START SLAVE; [Enter]
+
После чего заходим в консоль mysql и вводим команду START SLAVE; [Enter]<br>
 
Проверить работоспособность сервера репликаций можно с помощью команды SHOW SLAVE STATUS; в консоле управления mysql
 
Проверить работоспособность сервера репликаций можно с помощью команды SHOW SLAVE STATUS; в консоле управления mysql
  
8. Создание бекапа с помощью сервера репликации
+
===8. Создание бекапа с помощью сервера репликации===
В ручном режиме:
+
В ручном режиме:<br>
Сначала заходим в консоль MySQL сервера: mysql [Enter] и вводим команду STOP SLAVE;
+
Сначала заходим в консоль MySQL сервера: mysql [Enter] и вводим команду STOP SLAVE;<br>
Выходим из консоли mysql и запускаем mysqldump:
+
Выходим из консоли mysql и запускаем mysqldump:<br>
 +
<syntaxhighlight lang="xml">
 
mysqldump --user=root --password=root_password --all-databases --lock-all-tables > /tmp/backup.sql
 
mysqldump --user=root --password=root_password --all-databases --lock-all-tables > /tmp/backup.sql
 +
</syntaxhighlight>
 +
 +
Параметры означают следующее:<br>
 +
--user=root - имя пользователя, от чьего имени будем делать бекап<br>
 +
--password=root_password - пароль пользователя root<br>
 +
--all-databases - Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.<br>
 +
--lock-all-tables - Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.<br>
 +
После чего не забываем запустить репликацию снова, для этого заходим в консоль MySQL: mysql [Enter] и вводим команду START SLAVE;<br>
  
Параметры означают следующее:
+
В автоматическом режиме:<br>
--user=root - имя пользователя, от чьего имени будем делать бекап
+
<syntaxhighlight lang="xml">
--password=root_password - пароль пользователя root
+
--all-databases - Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.
+
--lock-all-tables - Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.
+
После чего не забываем запустить репликацию снова, для этого заходим в консоль MySQL: mysql [Enter] и вводим команду START SLAVE;
+
В автоматическом режиме:
+
 
#!/bin/sh
 
#!/bin/sh
  
Строка 138: Строка 150:
 
tar jcvf /backups/mysql/backup-${DATE}.tar.bz2 /backups/mysql/backup-${DATE}.sql
 
tar jcvf /backups/mysql/backup-${DATE}.tar.bz2 /backups/mysql/backup-${DATE}.sql
 
rm /backups/mysql/backup-${DATE}.sql
 
rm /backups/mysql/backup-${DATE}.sql
 +
</syntaxhighlight>
  
Если вы захотите добавить этот скрипт в crontab - то добавте в скрипте полные пути до всех запускаемых файлов
+
Если вы захотите добавить этот скрипт в crontab - то добавте в скрипте полные пути до всех запускаемых файлов<br>
 
Найти полный путь до запускаемого файла можно с помощью:
 
Найти полный путь до запускаемого файла можно с помощью:
 +
<syntaxhighlight lang="xml">
 
root@rep:~# whereis mysqladmin
 
root@rep:~# whereis mysqladmin
 
mysqladmin: /usr/bin/mysqladmin /usr/share/man/man1/mysqladmin.1.gz
 
mysqladmin: /usr/bin/mysqladmin /usr/share/man/man1/mysqladmin.1.gz
 +
</syntaxhighlight>
  
 
У FreeBSD другие пути, например:
 
У FreeBSD другие пути, например:
 +
<syntaxhighlight lang="xml">
 
www# whereis mysqladmin
 
www# whereis mysqladmin
 
mysqladmin: /usr/local/bin/mysqladmin /usr/local/man/man1/mysqladmin.1.gz
 
mysqladmin: /usr/local/bin/mysqladmin /usr/local/man/man1/mysqladmin.1.gz
 +
</syntaxhighlight>

Текущая версия на 20:17, 6 августа 2014

Мы имеем два физических сервера, неважно какая ОС установлена, FreeBSD / Linux. На обоих серверах стоит Mysql (я лично тестировал на связках Ubuntu 9.04/9.10 FreeBSD 8.0 и Mysql5.1) На одном сервере стоит рабочий MySQL сервер (IP = 10.0.0.1). На втором свежеустановленный MySQL (IP = 10.0.0.2).

1.Открываем конфигурационный файл mysql на основном сервере

Для Linux: /etc/mysql
Для FreeBSD: /var/db/mysql

Так же просим учесть, что у ОС FreeBSD его по умолчанию нету, взять дефолтный конфигурационный файл можно в папке /usr/local/share/mysql
(my-small.cnf, my-medium.cnf, my-large.cnf или my-huge.cnf - в зависимости от конфигурации сервера и нагрузки на него.
Не забудьте что при копировании дефолтного конфигурационного файла в папку /var/db/mysql ему необходимо задать имя my.cnf).

и редактируем следующие поля:
для Linux:

 server-id               = 1
 log_bin                 = /var/log/mysql/mysql-bin.log
 expire_logs_days        = 10
 max_binlog_size         = 1024M

для FreeBSD:

 server-id=1
 log-bin=mysql-bin
 expire_logs_days= 10
 max_binlog_size=1024M

Параметры означают следующее:
server-id - Уникальный server_id
log-bin - указывает путь для ведения логов и формат имени файлов для Linux, или формат файла для FreeBSD
expire_logs_days - сколько по времени днях хранить логи, учтите что на нагруженном сервере за 10 дней объем может достигать 50-100Gb
max_binlog_size - какой размер файла логов делать

И теперь самый неприятный момент - Нужно перезапустить основной сервер MySQL
Для Linux
/etc/init.d/mysql restart
Для FreeBSD
/usr/local/etc/rc.d/mysql-server restart

2. Заходим в консоль MySQL сервер. (на основном сервере)

Если у пользователя root нету пароля - то просто вводим mysql [Enter]
Если пароль есть - то вводим mysql -u root -p [Enter] и вводим пароль от пользователя root [Enter]

root@mysql:~# mysql
Welcome to the MySQL monitor.  Commands end with; or \g.
Your MySQL connection id is 4057381
Server version: 5.1.37-1ubuntu5.1-log (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>


3. Вводим одну команду

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username'@'10.0.0.2' IDENTIFIED BY 'password';
где
username - имя пользователя
password - пароль
10.0.0.2 - ip сервера репликации (поменяйте на свой IP)

4. Открываем конфигурационный файл MySQL на резервном сервере.

и редактируем следующие параметры

server-id = 2
master-host = 10.0.0.1
master-port = 3306
master-user = username
master-password = password

Параметры означают следующее:
server-id - уникальный id сервера, значение должно отличаться от значения первого сервера
master-host = IP адрес на котором работает мастер-сервер
master-port = Порт на котором работает мастер-сервер
master-user = имя пользователя, которое мы задали в пункте 3
master-password = пароль, который мы задали в пункте 3

5. Перезапускаем MySQL на резервном сервере, ОБЯЗАТЕЛЬНО

Для Linux
/etc/init.d/mysql restart
Для FreeBSD
/usr/local/etc/rc.d/mysql-server restart
Заходим в консоль mysql
Если у пользователя root нету пароля - то просто вводим mysql [Enter]
Если пароль есть - то вводим mysql -u root -p [Enter] и вводим пароль от пользователя root [Enter]
и после вводим 1 команду: STOP SLAVE; [Enter]

root@rep:~# mysql
Welcome to the MySQL monitor.  Commands end with; or \g.
Your MySQL connection id is 229333
Server version: 5.1.37-1ubuntu5.1 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> STOP SLAVE;
Query OK, 0 rows affected (0,02 sec)

6. Переходим к созданию бекапа с рабочего сервера

На основном сервере вводим команду:

mysqldump --user=root --password=root_password --extended-insert --all-databases --master-data --lock-tables > /tmp/backup.sql

Параметры означают следующее:
--user=root - имя пользователя, от чьего имени будем делать бекап
--password=root_password - пароль пользователя root
--extended-insert - Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).
--all-databases - Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.
--lock-tables - Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.
Внимание, бекап лучше всего делать в самое не загруженное время работы MySQL (например в 6 утра)

7. Установка бекапа на сервер репликации

Далее, копируем файл /tmp/backup.sql на резервный севрер и загружаем его командой:

mysql --user=root --password=root_password < /tmp/backup.sql

После чего заходим в консоль mysql и вводим команду START SLAVE; [Enter]
Проверить работоспособность сервера репликаций можно с помощью команды SHOW SLAVE STATUS; в консоле управления mysql

8. Создание бекапа с помощью сервера репликации

В ручном режиме:
Сначала заходим в консоль MySQL сервера: mysql [Enter] и вводим команду STOP SLAVE;
Выходим из консоли mysql и запускаем mysqldump:

mysqldump --user=root --password=root_password --all-databases --lock-all-tables > /tmp/backup.sql

Параметры означают следующее:
--user=root - имя пользователя, от чьего имени будем делать бекап
--password=root_password - пароль пользователя root
--all-databases - Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.
--lock-all-tables - Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.
После чего не забываем запустить репликацию снова, для этого заходим в консоль MySQL: mysql [Enter] и вводим команду START SLAVE;

В автоматическом режиме:

#!/bin/sh
 
DATE=`date +%Y-%m-%d_%H-%M`
USER=root
PASS=root_password
 
mysqladmin --user=${USER} --password=${PASS} stop-slave
mysqldump --user=${USER} --password=${PASS} --all-databases --lock-all-tables > /backups/mysql/backup-${DATE}.sql
mysqladmin --user=${USER} --password=${PASS} start-slave
tar jcvf /backups/mysql/backup-${DATE}.tar.bz2 /backups/mysql/backup-${DATE}.sql
rm /backups/mysql/backup-${DATE}.sql

Если вы захотите добавить этот скрипт в crontab - то добавте в скрипте полные пути до всех запускаемых файлов
Найти полный путь до запускаемого файла можно с помощью:

root@rep:~# whereis mysqladmin
mysqladmin: /usr/bin/mysqladmin /usr/share/man/man1/mysqladmin.1.gz

У FreeBSD другие пути, например:

www# whereis mysqladmin
mysqladmin: /usr/local/bin/mysqladmin /usr/local/man/man1/mysqladmin.1.gz