|
|
发表于 2007-10-29 17:09:08
|
显示全部楼层
Step 1:
Install mysql on master 1 and slave 1. configure network services on both system, like
Master 1/Slave 2 ip: 192.168.1.112
Master 2/Slave 1 ip : 192.168.1.113
Step 2:
On Master 1, make changes in my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
log-bin
binlog-do-db=<database name> # input the database which should be replicated
binlog-ignore-db=mysql # input the database that should be ignored for replication
binlog-ignore-db=test
server-id=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Step 3:
On master 1, create a replication slave account in mysql.
mysql> grant replication slave on *.* to 'replication'@192.168.1.113 \ ###此IP为从服务器的IP;
identified by 'slave';
##以上是给从服务器用户授权,授与复制的权限.
##
and restart the mysql master1.
Step 4:
Now edit my.cnf on Slave1 or Master2 :
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1
server-id=2
master-host = 192.168.1.112
master-user = replication
master-password = slave
master-port = 3306
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Step 5:
Restart mysql slave 1 and at
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.4
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MASTERMYSQL01-bin.000009
Read_Master_Log_Pos: 4
Relay_Log_File: MASTERMYSQL02-relay-bin.000015
Relay_Log_Pos: 3630
Relay_Master_Log_File: MASTERMYSQL01-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 3630
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 1519187
1 row in set (0.00 sec)
Above highlighted rows must be indicate related log files and Slave_IO_Running and Slave_SQL_Running: must be to YES.
Step 6:
On master 1:
mysql> show master status;
+------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000008 | 410 | adam | |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.
Step 7:
On Master2/Slave 1, edit my.cnf and master entries into it:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2
master-host = 192.168.1.112
master-user = replication
master-password = slave
master-port = 3306
log-bin #information for becoming master added
binlog-do-db=adam
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Step 8:
Create a replication slave account on master2 for master1:
mysql> grant replication slave on *.* to 'replication'@192.168.1.40 identified by 'slave2'; ##在第二主上给第一主的用户授权
Step 9:
Edit my.cnf on master1 for information of its master.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test
server-id=1
#information for becoming slave.
master-host = 192.168.1.113
master-user = replication
master-password = slave2
master-port = 3306
[mysql.server]user=mysqlbasedir=/var/lib
Step 10:
Restart both mysql master1 and master2.
On mysql master1:
mysql> start slave;
On mysql master2:
mysql > show master status;
On mysql master 1:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.113
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Mysql1MYSQL02-bin.000008
Read_Master_Log_Pos: 410
Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
Relay_Log_Pos: 445
Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 410
Relay_Log_Space: 445
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 103799
1 row in set (0.00 sec)
ERROR:
No query specified
Auto increment
Submitted by da644 on Thu, 2007-06-14 15:39.
One thing that is missing from the above, is how to deal with auto-increments so that you do not get collisions if you add data to both masters at the same time. There is an easy way to deal with this if you are using MySQL 5.0.2 or above.
In the my.cnf file on server1 add:
auto_increment_increment=2
auto_increment_offset=1
In the my.cnf file on server2 add:
auto_increment_increment=2
auto_increment_offset=2
This will make the auto-increment on server1 go, 1,3,5,7,9,etc... and on server2 go, 2,4,6,8,etc... thereby preventing collisions.
Andrew |
|
IP卡
狗仔卡
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
显身卡