mysql 主从复制

1. 清理日志，阻断写操作。

mysql> reset master;

mysql> FLUSH TABLES WITH READ LOCK;

2.修改my.cnf 

server-id       = 1   修改ID

binlog-do-db=test22 添加要同步的数据库，多个用逗号隔开。

3.主添加同步帐号

mysql> grant replication slave   on *.*   to ‘mysqlbak‘@‘%‘ identified by ‘ts123456‘;

mysql> flush privileges; 

4.备份数据库，将数据库导入从库

主

mysqldump --default-character-set=utf8  test1 > test1.sql

从

mysql> source /data/test1.sql

5.登录主服务器的mysql，查询master的状态
   mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

6.配置从服务器状态

mysql>stop slave;

msyql> reset slave;

    CHANGE MASTER TO MASTER_HOST=‘192.168.1.245‘,

    MASTER_USER=‘mysqlbak‘,

    MASTER_PASSWORD=‘ts123456‘,

    MASTER_LOG_FILE=‘mysql-bin.000001‘,

    MASTER_LOG_POS=308;

msyql>start slave;

7查看从服务器状态

  mysql> show slave status\G

             Slave_IO_Running: Yes    //此状态必须YES
              Slave_SQL_Running: Yes     //此状态必须YES

保证这两行为yes则配置成功

8. 主解锁写权限

mysql> unlock tables;

mysql 主从复制

原文：http://5468755.blog.51cto.com/5458755/1735426