基于MySQL Master Slave同步配置的操作详解

环境：
pc：ubuntu 10.10  192.168.1.112（master） 192.168.10.245（slave）
mysql : 5.1.49-1ubuntu8.1-log
在master中已经存在数据库test
首先修改mysql配置文件：/etc/mysql/my.cnf
[master]
#author:zhxia

 #master 同步设置
 server-id               = 1
 log_bin                 = /var/log/mysql/mysql-test-bin.log
 expire_logs_days        = 10
 max_binlog_size         = 100m
 binlog_format           =mixed

[slave]
#author:zhxia

server-id               = 2
 replicate-do-db=test
 replicate-do-db=blog
 log_bin                 = /var/log/mysql/mysql-bin.log
 relay_log               =/var/log/mysql/mysql-relay-bin.log
 expire_logs_days        = 10
 max_binlog_size         = 100m
 #binlog_do_db           = test
 #binlog_ignore_db       = include_database_name
 binlog_format           = mixed
 slave-net-timeout=6012 master-connect-retry=10

接着在master上创建备份帐号

grant replication slave,replication client on *.* to 'slave'@'192.168.10.245' identified by '123456';

将master中的数据库 导入到slave中，
先锁表，禁止写入操作
flush tables with read lock;
先从master导出：mysqldump -uroot -p test > /tmp/test.sql
再导入到slave： mysql -uroot -p test < /tmp/test.sql ,记得需要先建库test
进入master上的mysql,查看master状态

#author:zhxia

mysql> show master status;
 +-----------------------+----------+--------------+------------------+
 | file                  | position | binlog_do_db | binlog_ignore_db |
 +-----------------------+----------+--------------+------------------+
 | mysql-test-bin.000022 |      624 |              |                  |
 +-----------------------+----------+--------------+------------------+
 1 row in set (0.02 sec)

进入slave上的mysql
#author:zhxia

change master to  master_host='192.168.1.112', master_user='slave', master_password='123456', master_log_file='mysql-test-bin.000022', master_log_pos=106;

然后启动salve，并查看状态：  
#author:zhxia

start slave;

 mysql> show slave status\g;
 *************************** 1. row ***************************
                slave_io_state: waiting for master to send event
                   master_host: 192.168.1.112
                   master_user: slave
                   master_port: 3306
                 connect_retry: 60
               master_log_file: mysql-test-bin.000022
           read_master_log_pos: 624
                relay_log_file: mysql-relay-bin.000005
                 relay_log_pos: 533
         relay_master_log_file: mysql-test-bin.000022
              slave_io_running: yes
             slave_sql_running: yes
               replicate_do_db: test,blog
           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: 624
               relay_log_space: 688
               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: 0
 master_ssl_verify_server_cert: no
                 last_io_errno: 0
                 last_io_error:
                last_sql_errno: 0
                last_sql_error:
 1 row in set (0.00 sec)

 error:
 no query specified

最后将master上的表解锁
 unlock tables;