mysql设置slave复制_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-binserver-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 theFLUSH TABLESstatement 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_idmysql > 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库

7.导入slave库shell> mysql -uroot -p

8.在slave上设置其master选项CHANGE MASTER TOMASTER_HOST='jp_my3',MASTER_USER='repclub2',MASTER_PASSWORD='repclub2',MASTER_LOG_FILE='mysql-bin.000006',MASTER_LOG_POS=3132;