MYSQL 主从热备方式配置

    MySQL数据库没有增量备份的机制，当数据量太大的时候备份是一个很大的问题。还好MySQL数据库提供了一种主从备份的机制，其实就是把主数据库的所有的数据同时写到备份数据库中。实现MySQL数据库的热备份。
    要想实现双机的热备首先要了解主从数据库服务器的版本的需求。要实现热备MySQL的版本都要高于3.2，还有一个基本的原则就是作为从数据库的数据库版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本。
    MySQL的双机热备份是基于MySQL内部复制功能，建立在两台或者多台以上的服务器之间，通过它们之间的主从关系，是插入主数据库的数据同时也插入到从数据库上，这实现了动态备份当前数据库的功能。下面细说一下主从热备份的步骤：
    假设主服务器A(master)、从服务器为B(slave)
    
    官方建议版本最好一致，如果不一致，做备份的mysql版本一定要高于主mysql版本


    A：172.16.0.183 主
    B：172.16.0.251 备


    [root@localhost ~]# mysql -V
        mysql  Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (x86_64) using readline 5.1




1、主服务器创建用户授权
    授权副服务器可以连接主服务器并可以进行更新。这是在主服务器上进行的，创建一个
    username和password供副服务器访问时使用。
    在MySQL命令行下输入
    mysql> insert into user(user,host,password) values('root','172.16.0.251',password("caink303"));
    Query OK, 1 row affected, 3 warnings (0.00 sec)
    
    mysql>grant replication slave on *.* to root@'172.16.9.251';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    这命令将自动创建了一个帐号root用于slave访问master来更新slave数据库。
    当然也可以跳过这步直接使用网站本身的root用户和密码来访问master，在这里以root用户作为例子来介绍
     


2、数据复制--（在数据复制阶段，向办法让其他访问的人不能插入数据，或者说屏蔽mysql端口不让别人访问）
    将master上已有的数据复制到slave上，以便主从数据库建立的时候两个数据库的数据保持一致。
    在master上导出数据
    把缓冲数据写入表，只能读，不能写数据了。
    mysql> flush tables with read lock;
    D:/Develop/MySQL/bin> mysqldump –u root -p --default-character=utf8  -B test_ms > d:/test_ms.sql
    mysql>unlock tables
    复制完了解锁。
    在slave上导入数据
    mysql> set names utf8;
    mysql>use test_ms
    mysql>source d:/test_ms.sql
    另外也可以使用LOAD DATA FROM MASTER语句将主服务器的数据传输到副服务器，但使用上有些限制。注意：在执行数据复制的过程中，要确保不能对主服务器执行更新操作。不推荐使用！


3、配置主服务器
    修改master上mysql的根目录下的my.ini配置文件
    在选项配置文件中赋予主服务器一个server-id，该id必须是1到2^23-1范围
    内的唯一值。主服务器和副服务器的server-id不能相同。另外，还需要配置主服务器，使之启用二进制日志，
    即在选项配置文件中添加log-bin启动选项。


    [mysqld]
        #唯一值，并不能与副服务器相同
        server-id=1
        #日志文件以binary_log为前缀，如果不给log-bin赋值，日志文件将以#master-server-hostname为前缀
        log-bin = bin_log
        sync_binlog=1
        innodb_flush_log_at_trx_commit = 2   
        innodb_support_xa=1
        
        注意：如果主服务器的二进制日志已经启用，关闭并重新启动之前应该对以前的二
        进制日志进行备份。重新启动后，应使用RESET MASTER语句清空以前的日志。
        原因：master上对数据库test_ms的一切操作都记录在日志文件中，然后会把日志发给slave，
        slave接收到master传来的日志文件之后就会执行相应的操作，使slave中的数据库做和master
        数据库相同的操作。所以为了保持数据的一致性，必须保证日志文件没有脏数据。
     
4、重启master
    配置好以上选项后，重启MySQL服务，新选项将生效。现在，所有对数据库中信息的
    更新操作将被写进日志中。
    


5、配置slave
    在副服务器上的MySQL选项配置文件中添加以下参数。


    [mysqld]
        server-id=2    #唯一，并与主服务器上的server-id不同。
        replicate-ignore-db='mysql' #忽略不复制的数据库
        replicate-ignore-db='test'
        replicate-ignore-db='information_schema'
        master_host='172.16.0.183'  #主服务器的主机名或者ip地址
        master_port='3306'   #如果主服务器没有在默认的端口上监听，则需确定master-port选项
        master_user='root' 　#主服务器用户复制的用户名
        master_password='caink303'  #主服务器用户复制用户的密码
        relay-log=relay-bin   
        relay-log-index=relay-bin.index
        slave-skip-errors=all    #当发现错误的时候忽略，如果不加这个参数，复制出错误之后，讲会在/var/log/mysqld.log 下出现错误，复制也停止
        master-retry-count = 999
        master-connect-retry = 60
        skip_slave_start  
        read_only
        


6、登入主服务器查看当前主服务器的bin-log信息
    [root@localhost tmp]# mysql -uroot -pmysql#303 -h 172.16.0.183
    mysql> show master status;
    +----------------+----------+--------------+------------------+
    | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +----------------+----------+--------------+------------------+
    | bin_log.000001 |   116560 |              |                  |
    +----------------+----------+--------------+------------------+
    1 row in set (0.00 sec)
    
        
    然后登入slave服务器设置相关复制的启动参数
    
    执行：
        mysql> change master to 
                master_log_file='mysql-bin.000001',
                master_log_pos=116560;
            Query OK, 0 rows affected (0.02 sec)




7、重启slave
    副服务器上MySQL服务重启后，还在数据目录中创建一个master.info文件，其中包含
    所有有关复制过程的信息(连接主服务器的相关信息及与主服务器交换数据的相关信息)。在初次启动以后，副服务器将检查这个master.info文件，以得到相关信息。
    如果想修改复制选项，删除master.info并重启MySQL服务，在启动过程中使用选项配置文件中的新选项进行重新创建了master.info文件。
    将主服务器上备份好的数据库脚本文件(test_ms.sql)导入到副服务器数据库中，以便保
    证主-副服务器上进行复制操作的起点一样。


8、查看master 的信息
    a、查看状态：show master status    或者  show master status\G
    b、刷新未写入的数据到bin-log： flush master;


9、查看slave
    一般情况下重启了slave之后，自动就会开启复制功能，第一次的时候可以在skip-slave-start设置不开启，
    可以通过下面的语句查看
    登入slave 172.16.0.251
    a、查看状态：show slave status 或者  show slave status\G
    如果显示waiting for master to send event 的话就表示已经启动了，反之就运行
    mysql>start slave
    来启动slave
    在SHOW SLAVE STATUS\G的命令输出后，应该包含
    Slave_IO_Running对应的值为YES，
    Slave_SQL_Running对应的值为YES，如下图所示，只有这样才保证主从机能正常备份。
    暂时停止主从热备份
    mysql>stop slave
    注：以上的配置方式只能实现A->B，即数据由A(master)转移到B(slave)，不能由B转移到A，这样的话对B做的任何操作就不会被同步到数据库A中。当然也可以通过把A设置成slave和master，把B设置成slave和master从而实现对A或者B的任何改动都会影响到另外一方。配置同上，在此不在论述。
    b、刷新slave 数据到日志： flush slave;
    


注意事项：
    配置主-从前先要同步数据
    修改了复制的参数需要删除以下文件
    /var/lib/mysql/master.info
    /var/lib/mysql/relay-log.info
    
10、如果是通过拷贝mysql所有的数据文件到另外一台
    1、大版本必须一致
    2、拷贝完文件之后，文件的权限要设置为mysql.mysql
    3、/etc/my.cnf 也要拷贝过去
    


11、从数据库同步错误终止，重传
    a、查看错误日志
        tail /data/mysql/mysql-error.log
        111010 17:35:49 [ERROR] Error reading packet from server: Client requested master
         to start replication from impossible position ( server_errno=1236)
        111010 17:35:49 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data
        from binary log: 'Client requested master to start replication from impossible
        position', Error_code: 1236
        111010 17:35:49 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000288',
        position 627806304
        
    b、按照习惯, 先尝试必改position位置.
        mysql> stop slave;
        mysql> change master to master_log_file='mysql-bin.000288',master_log_pos=627625751;
        mysql> start slave;
    
    c、错误依旧 则查看看
        mysqlbinlog --start-position=627806304 /data/mysql/binlog/mysql-bin.000288
        /*!40019 SET @@session.max_insert_delayed_threads=0*/;
        /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
        DELIMITER /*!*/;
        # at 4
        #111010 13:31:19 server id 4 end_log_pos 106 Start: binlog v 4, server v 5.1.45-log
        created 111010 13:31:19
        # Warning: this binlog is either in use or was not closed properly.
        BINLOG '
        F1aTTg8EAAAAZgAAAGoAAAABAAQANS4xLjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
        '/*!*/;
        DELIMITER ;
        # End of log file
        ROLLBACK /* added by mysqlbinlog */;
        /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
        
        没有看到这个位置.
        
    d、mysqlbinlog /data/mysql/binlog/mysql-bin.000288 > test.txt
        less text.txt
        找到最接近错误标记627655136的一个position是627806304.
    e、修改位置
        再回到slave机器上change master, 将postion指向这个位置.
        
        mysql> stop slave;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> change master to master_log_file='mysql-bin.000288',master_log_pos=627625631;
        Query OK, 0 rows affected (0.06 sec)
        
        mysql> start slave;
        Query OK, 0 rows affected (0.00 sec)
        


12、MSYQL 启动不了：


    MySQL Plugin 'InnoDB' init function returned error.
    . . 在MySQL的配置文件中，设定default-table-type=InnoDB，发现MySQL无法正常的启动，错误日志中给出了如下的信息： 


    引用
    170207 09:34:27 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
    170207 09:36:44 mysqld_safe Starting mysqld daemon with databases from /home/mysqldata
    170207  9:36:44 [Warning] 'for replication startup options' is deprecated and will be removed in a future release. Please use ''CHANGE MASTER'' instead.
    170207  9:36:44  InnoDB: Initializing buffer pool, size = 24.0G
    170207  9:36:46  InnoDB: Completed initialization of buffer pool
    InnoDB: Error: log file ./ib_logfile0 is of different size 0 104857600 bytes
    InnoDB: than specified in the .cnf file 0 524288000 bytes!
    170207  9:36:47 [ERROR] Plugin 'InnoDB' init function returned error.
    170207  9:36:47 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    170207  9:36:47 [ERROR] Unknown/unsupported table type: InnoDB
    170207  9:36:47 [ERROR] Aborting
    
    170207  9:36:47 [Note] /usr/libexec/mysqld: Shutdown complete
    
    170207 09:36:47 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
    


    调查得知，只要删除MySQL目录下的ib_logfile0和ib_logfile1两个文件（注意：ib_data文件不能删除）就可以解决问题了。




13、重置主与从，再切换从数据库作为主数据库，或者切换主数据库为从数据库
    stop master;
    reset master;
    stop slave;
    reset slave;