MySQL Failover Circular Replication
===================================
Assume we have 2 servers: Server1 and Server2.
Server1 Settings
================
1. Put the option file my.cnf to Server1 path /etc with these settings:
[mysqld]
log-bin=mysql-bin
server-id = 1
auto_increment_increment = 10
auto_increment_offset = 1
2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf
3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start
4. Configure the server:
# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root
create user replicant@'%' identified by 'password';
# Grant access rights:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
Flush Privileges;
# Specify the info for the serve2:
CHANGE MASTER TO
MASTER_HOST='ip_of_server2',
MASTER_USER='replication_user_name_on_server2',
MASTER_PASSWORD='replication_password_on_server2';
# Start the listerner:
Start slave;
# Verify whether the replication is working:
show slave status\G
Server2 Settings
================
1. Put the option file my.cnf on to Server2 path /etc
with these settings:
[mysqld]
log-bin=mysql-bin
server-id = 2
auto_increment_increment = 10
auto_increment_offset = 2
2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf
3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start
4. Configure the server:
# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root
create user replicant@'%' identified by 'password';
# Grant access rights:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
Flush Privileges;
# Specify the info for the serve1:
CHANGE MASTER TO
MASTER_HOST='ip_of_server1',
MASTER_USER='replication_user_name_on_server1',
MASTER_PASSWORD='replication_password_on_server1';
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1',
# MASTER_USER='replicant', MASTER_PASSWORD='password';
# Load data from Server1:
Load Data from Master;
# Start the listerner:
Start slave;
分享到:
相关推荐
Perform backup tasks, recover data and set up various replication topologies for your database Maximize performance by using new features of MySQL 8 like descending indexes, controlling query ...
Set up, manage, and configure the new InnoDB Cluster feature in MySQL from Oracle. If you are growing your MySQL installation and want to explore making your servers highly available, this book ...
Chapter 9, Replication, explains how to set up various replication topologies. The recipes on switching a slave from master-slave to chain replication and switching a slave from chain replication to ...
The book shows you how to set up and configure the Trove DBaaS framework, use prepackaged or custom database implementations, and provision and operate a variety of databases - including MySQL, ...
### MySQL Master-Master Replication Manager (MMM) 架构配置详解 #### 一、概述 MySQL Master-Master Replication Manager (MMM) 是一套强大的脚本集合,主要用于监控和故障转移,以及管理 MySQL 的 Master-...
The book shows you how to set up and configure the Trove DBaaS framework, use prepackaged or custom database implementations, and provision and operate a variety of databases—including MySQL, ...
# an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # You can copy this file to # /etc/my.cnf to set global options, # mysql-data...
- **主库配置**:首先,通过`mysql`客户端连接到主库(192.168.1.111),创建一个用于复制的用户`mylink`,赋予`REPLICATION SLAVE`权限。接着,锁定所有表以便备份,通过`show master status;`获取当前binlog...
Then you find yourself setting up replication so you can scale out reads and deal with potential failures. And, before too long, you’ve added a caching layer, tuned all the queries, and thrown even ...