Mysql Replication set up

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;
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值