mysql的主从复制详解数据库

/* 
    @author:itxm 
    @time:20113-07-24 
 
        给从服务器设置权限该用户 
        grant all  on *.* to [email protected] 
        identified by 'passwd'; 
        grant replication slave on *.* 
        [email protected] 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;