mysql master/slave复制设置

1.       在主库上创建replicate用户且赋予replication slave权限

mysql> GRANT REPLICATION SLAVE ON *.* TO repclub2 IDENTIFIED BY 'repclub2'

2.       enable binary logging

3.       设置server id ---在文件/etc/my.cnf 主库设为1 从库设为2

[mysqld]

log-bin=mysql-bin

server-id=1

4.       主库的其它设置

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file.

Ensure that the skip-networking option has not been enabled on your replication master. If networking has been disabled, then your slave will not able to communicate with the master and replication will fail.

 

5.       导出主库数据

mysql> FLUSH TABLES WITH READ LOCK;

Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.

select t.login_id,

       decode(m.company_type,'JapanSuppliers','正式日供','Free','免费','ImportJapanSuppliers',

              decode(m.from_site,'intl1','导入日供','intl2','白金会员'))

  from tbd_member_20080904 t, member m

 where t.login_id = m.login_id     

mysql > SHOW MASTER STATUS;

shell> mysqldump --all-databases --lock-all-tables >dbdump.db

When choosing databases to include in the dump, remember that you will need to filter out databases on each slave that you do not want to include in the replication process.

 

6.       将导出数据拷贝至slave库

scp dbdump.db root@172.20.40.45:/home/mysql/

 

7.       导入slave库

shell> mysql -uroot -p

8. 在slave上设置其master选项

CHANGE MASTER TO

    MASTER_HOST='jp_my3',

    MASTER_USER='repclub2',

    MASTER_PASSWORD='repclub2',

    MASTER_LOG_FILE='mysql-bin.000006',

    MASTER_LOG_POS=3132;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30821/viewspace-442280/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30821/viewspace-442280/