mysql主从备份，读写分离

1.安装MySQL
   1.1下载安装包
    wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-server-5.6.21-1.rhel5.x86_64.rpm
    wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-devel-5.6.21-1.rhel5.x86_64.rpm 
    wget http://dev.mysql.com/Downloads/MySQL-5.6/MySQL-client-5.6.21-1.rhel5.x86_64.rpm
    1.2卸载系统预安装的mysql-libs
    yum remove mysql-libs
    1.3安装依赖包
    yum install libaio
    1.4安装MySQL
    rpm -ivh MySQL-server-5.6.21-1.rhel5.x86_64.rpm
    rpm -ivh MySQL-devel-5.6.21-1.rhel5.x86_64.rpm 
    rpm -ivh MySQL-client-5.6.21-1.rhel5.x86_64.rpm  
    1.5修改配置文件位置
    cp /usr/share/mysql/my-default.cnf /etc/my.cnf
    1.6初始化数据库
    /usr/bin/mysql_install_db
    1.7启动数据库
        1.7.1设置开启启动
        chkconfig mysql on
        1.7.2启动数据库
        service mysql restart
    1.8更改mysql初始化密码
        1.8.1查看数据库初始化密码
        cat /root/.mysql_secret
        1.8.2更改密码
        set password=password('1234');
    1.9主从复制配置（Master：主，Slave：从）
        1.9.1准备两台主机（在同一网络中）
            主机A：Master
            主机B：Slave
        1.9.2设置防火墙对3306端口开启
            最简单的方式: service iptables stop
        1.9.3Master配置
            1.9.3.1更改配置文件/etc/my.cnf
        log-bin=mysql-bin
        server-id=2
        binlog-ignore-db=information_schema 
        binlog-ignore-db=cluster
        binlog-ignore-db=cluster
        binlog-ignore-db=mysql
        binlog-do-db=nxb #要备份的数据库
            1.9.3.1重启mysql数据库
                service mysql restart
            1.9.3.2进入数据库，赋予从库权限，允许从库在主库上读取日志。
                1）赋予Slave机器File权限
            GRANT FILE ON *.* TO 'root'@'192.168.40.148' IDENTIFIED BY '1234';
                2）赋予Slave机器REPLICATION SLAVE权限
            GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.40.148' IDENTIFIED BY '1234';
            FLUSH PRIVILEGES;
            1.9.3.3重新启动mysql，登录mysql，查看主机信息
                show master status;
            +------------------+----------+--------------+----------------------------------

+-------------------+
            | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                 | 

Executed_Gtid_Set |
            +------------------+----------+--------------+----------------------------------

+-------------------+
            | mysql-bin.000002 |      560 | test         | information_schema,cluster,mysql |       

            |
            +------------------+----------+--------------+----------------------------------

+-------------------+
        这里的 File 、Position 是在配置Salve的时候要使用到的，Binlog_Do_DB表示要同步的数据库，

Binlog_Ignore_DB 表示Ignore的数据库，这些都是在配置的时候进行指定的。
            如果执行这个步骤始终为Empty set(0.00 sec)，那说明前面的my.cnf没配置对。
        1.9.4Slave配置
            1.9.4.1修改从库配置文件：/etc/my.cnf
        log-bin=mysql-bin
        server-id=3
        binlog-ignore-db=information_schema
        binlog-ignore-db=cluster
        binlog-ignore-db=mysql
        replicate-do-db=nxb  #要备份的数据库
        replicate-ignore-db=mysql
        log-slave-updates
        slave-skip-errors=all
        slave-net-timeout=60
        1.9.4.2注意, 不需要配置，配置之后会出错
        master-host=192.168.1.1#Master的主机IP
        master-user=root
        master-password=mysql password#Master的MySQL密码
        不需要配置，配置之-后会出错
            1.9.4.3配置文件修改完成后重启mysql，进入Slave mysql控制台，执行：
        stop slave;#关闭Slave
          change master to master_host='192.168.40.147',master_user='root',master_password='1234',master_log_file='mysql-bin.000002',master_log_pos=120;
        start slave;#开启Slave

        # master_host: master主机IP地址
        # master_user: master用户名
        # master_password: masger用户使用的密码
        # master_log_file: 对应master的File选项
        # master_log_pas: 对应master的Position选项
            1.9.4.4查看slave配置信息
                show slave status\G;
        *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.40.146
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 560
               Relay_Log_File: localdomain02-relay-bin.000002
                Relay_Log_Pos: 723
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           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: 560
              Relay_Log_Space: 904
              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: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: b00baea9-204e-11e7-aadc-000c29379aff
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to 

update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)