Есть два сервера:
192.168.0.111 — master
192.168.0.222 — slave
Настройка master сервера 192.168.0.111
В /etc/mysql/…
1 2 3 4 5 6 | [mysqld] server-id=1 log_bin=mysql-bin log_error=mysql-bin.err binlog_do_db=db_test sync_binlog=0 |
Далее:
1 | systemctl restart mysql |
Далее:
1 2 3 | FLUSH PRIVILEGES; |
Далее:
1 | mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 db_test > /home/username/master.sql |
Тут готово.
Настройка slave сервера 192.168.0.222
В /etc/mysql/…
1 2 3 | [mysqld] server-id=2 replicate-do-db=db_test |
Далее:
1 2 3 4 | systemctl restart mysql scp -P 22 username@192.168.0.111:/home/username/master.sql ./ mysql db_test < master.sql head -n80 master.sql | grep MASTER_LOG |
Далее:
1 2 3 | CHANGE MASTER TO MASTER_HOST='192.168.0.111', MASTER_USER='database_user', MASTER_PASSWORD='user_password', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=366; START SLAVE; SHOW SLAVE STATUS \G |
Отправляем 77% нагрузки на реплику. Если любой из серверов недоступен — сразу подменяем.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | <?php $slave_load_percent = 77; if ($_SERVER['SCRIPT_NAME'] == '/index.php' && $slave_load_percent && $slave_load_percent >= (substr(number_format(microtime(true), 1, '.', ''), -3) * 10)) { $mysqli = new mysqli('192.168.0.222', 'user', 'pass', 'db'); if ($mysqli->connect_error) { $mysqli = new mysqli('192.168.0.111', 'user', 'pass', 'db'); } } else { $mysqli = new mysqli('192.168.0.111', 'user', 'pass', 'db'); if ($mysqli->connect_error) { $mysqli = new mysqli('192.168.0.222', 'user', 'pass', 'db'); } } if ($mysqli->connect_error) { die('Error connection (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); } $mysqli->set_charset("utf8"); |