Есть два сервера:
192.168.0.111 — master
192.168.0.222 — slave
Настройка master сервера 192.168.0.111
В /etc/mysql/…
[mysqld] server-id=1 log_bin=mysql-bin log_error=mysql-bin.err binlog_do_db=db_test sync_binlog=0
Далее:
systemctl restart mysql
Далее:
CREATE USER [email protected]; GRANT REPLICATION SLAVE ON *.* TO [email protected] IDENTIFIED BY 'user_password'; FLUSH PRIVILEGES;
Далее:
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/…
[mysqld] server-id=2 replicate-do-db=db_test
Далее:
systemctl restart mysql scp -P 22 [email protected]:/home/username/master.sql ./ mysql db_test < master.sql head -n80 master.sql | grep MASTER_LOG
Далее:
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% нагрузки на реплику. Если любой из серверов недоступен — сразу подменяем.
<?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");