mysql的主从复制

/*
    @author:luowen
    @time:20113-07-24

        给从服务器设置权限该用户
        grant all  on *.* to user@192.168.16.88
        identified by 'passwd';
        grant replication slave on *.*
        user@192.168.16.87 identifiend by 'pass'


        在mysql 配置文件中打开bin-log日志选择项
        查看最后一个bin-log 日志 show master status
        清空所有的bin-log日志 reset master

        刷新日志文件 flush logs

        查看bin-log 文件

        mysqlbinog --no-defaults mysql-00001.bin;

        mysql bin-log 备份
        mysqldump -uroot -ppassword dbname -l 
        -F > 文件存放路径  (-l 枷锁, -F 刷新bin-log日志)


        更具position点恢复数据
        mysqlbinlog --no-defaults --start-postion="start" --end-positon="end" 'bin-log日志文件'



        主从服务器配置

        1.mysql 配置文件
            打开 log-bin = mysql-bin
            server-id = 1 (服务圈内唯一,不同)

        2.flush   tables with read lock(选做加锁解锁)
            可以 mysqldump -uroot -pluwoen test -l -F > d:/test.sql 代替


        从服务器配置
        1.mysql 配置文件
            server-id = 2
            master-host = 192.168.16.88
            master-user = user
            master-password = pass
            master-port = 3306
            log-bin = mysql-bin
        2.重启服务


        查看slave 命令 show slave status

        数据库命令
            1.start slave
            2.stop slave
            3.show slave status
            4.show master logs (查看所有的binlog日志)
            5.change master to
            6.show prosslist

        数据无法同步

        方法二:    
            1.stop slave
            2.set GLOBAL SQL_SLAVE_SKIP_COUNTER =1
            3.slave start
        方法一:
            change master to 
                master_host = "192.168.16.88",
                master_user = 'user',
                master_password = "pass",
                master_port = 3306,
                master_log_file = 'mysql-bin.000004',
                master_log_pos = 98;

            启动slave服务器
            Mysql> slave start;
            show slave status \G


change master to
     master_host='192.168.16.88',
     master_user='luowen',
     master_password='luowen',
     master_log_file='mysql-bin.000001',
     master_log_pos=107;

---------------------------mysql 5.5.1x以后配置变化----------------------------------------
    主配置不变，依旧是
    server-id=1
    log-bin=log
    binlog-do-db=database1          //需要同步的数据库
    binlog-do-db=database2
    binlog-ignore-db=mysql                     //被忽略的数据库

    从配置改为：
    server-id=2
    #master-host=192.168.16.88
    #master-user= luowen
    #master-password= luowen
    #master-port=3306
    #master-connect-retry=60
    replicate-do-db=database1     //同步的数据库
    replicate-do-db=database2
    replicate-ignore-db=mysql  //被忽略的数据库

    这也提示了我们需要使用change master to
    即：
    mysql>change master to
    >master_host='192.168.16.88',
    >master_user='luowen',
    >master_password='luowen',
    >master_log_file='bin-log.00001',
    >master_log_pos=107;

    然后start slave;