| Blog信息 |
|
blog名称:
日志总数:1304
评论数量:2242
留言数量:5
访问次数:7629211
建立时间:2006年5月29日 |
| |
|
 [MySQL]MySQL Master Master Replication（转）
软件技术
lhwork 发表于 2007/2/1 12:23:20 |
| 转至http://www.howtoforge.com/mysql_master_master_replicationThis tutorial describes how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). In my case I need two masters that are synchronized with each other so that if one of them drops down, other could take over and no data is lost. Similarly when the first one goes up again, it will still be used as slave for the live one.Here is a basic step by step tutorial, that will cover the mysql master and slave replication and also will describe the mysql master and master replication.Notions: we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.Step 1:Install mysql on master 1 and slave 1. configure network services on both system, like Master 1/Slave 2 ip: 192.168.16.4Master 2/Slave 1 ip : 192.168.16.5 Step 2: On Master 1, make changes in my.cnf:[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockold_passwords=1log-binbinlog-do-db= # input the database which should be replicatedbinlog-ignore-db=mysql # input the database that should be ignored for replicationbinlog-ignore-db=testserver-id=1[mysql.server]user=mysqlbasedir=/var/lib[mysqld_safe]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidStep 3:On master 1, create a replication slave account in mysql.mysql> grant replication slave on *.* to 'replication'@192.168.10.5 \identified by 'slave';and restart the mysql master1. Step 4: Now edit my.cnf on Slave1 or Master2 :[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockold_passwords=1server-id=2master-host = 192.168.16.4master-user = replicationmaster-password = slavemaster-port = 3306[mysql.server]user=mysqlbasedir=/var/lib[mysqld_safe]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidStep 5: Restart mysql slave 1 and atmysql> 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: 15191871 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/mysqlsocket=/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=1server-id=2master-host = 192.168.16.4master-user = replicationmaster-password = slavemaster-port = 3306log-bin #information for becoming master addedbinlog-do-db=adam [mysql.server]user=mysqlbasedir=/var/lib[mysqld_safe]err-log=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidStep 8:Create a replication slave account on master2 for master1:mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave2';Step 9:Edit my.cnf on master1 for information of its master.[mysqld]datadir=/var/lib/mysqlsocket=/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=1log-binbinlog-do-db=adambinlog-ignore-db=mysqlbinlog-ignore-db=testserver-id=1#information for becoming slave.master-host = 192.168.16.5master-user = replicationmaster-password = slave2master-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.16.5 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: 1037991 row in set (0.00 sec)ERROR:No query specifiedCheck for the hightlighted rows, make sure its running. Now you can create tables in the database and you will see changes in slave. Enjoy!! |
|
|