主从复制第一篇之异步复制

主从复制主要用途：
1.灾备切换
2.读写分离
3.备份，避免影响业务
4.高可用和故障切换
5.mysql测试升级

首先，异步主从复制主要步骤：
1.主库中开启log_bin
2.全备份主库
3.授权（grant replication slave on . ）
4.配置复制，并启动（change master to）
5.查看主从复制信息

异步主从复制存在的主要问题：
1.主机宕机后，数据可能丢失
2.从库只有一个sql thread，当主库有大量写入时候，从库容易丢失数据

异步复制的演示：
第一步：主库中开启log_bin=1,在my.cnf文件中设置，这里就不详细介绍了。

第二步：用mysqldump进行主库全备份

[root@VM_85_42_centos downloads]# ps -ef |grep mysqld #查看配置位置
root      5307     1  0 Apr03 ?        00:00:00 /bin/sh /usr/local/mysql56/bin/mysqld_safe --defaults-file=/mysqldata/my.cnf
mysql     5539  5307  0 Apr03 ?        00:00:50 /usr/local/mysql56/bin/mysqld --defaults-file=/mysqldata/my.cnf --basedir=/usr/local/mysql56 --datadir=/mysqldata/node1 --plugin-dir=/usr/local/mysql56/lib/plugin --user=mysql --log-error=/mysqldata/node1/VM_85_42_centos.err --pid-file=/mysqldata/node1/VM_85_42_centos.pid --socket=/tmp/mysql.sock --port=6000
root     13332 20569  0 23:19 pts/0    00:00:00 grep --color=auto mysqld

[root@VM_85_42_centos downloads]#  mysqldump -uroot -p --socket=/tmp/mysql.sock --single-transaction -A --master-data=1 > all_data.sql
[root@VM_85_42_centos downloads]# mysqldump -uroot -p --socket=/tmp/mysql.sock --single-transaction -A --master-data=1 > all_data.sql
Enter password: 
[root@VM_85_42_centos downloads]# ls
all_data.sql

使用远程登入从库myqsl

mysql -utest -ptest -h(从库IP) -P（端口号）

登入从库后用

mysql>source all_data.sql; # 导入主库

第三步：主库授权用户

grant replication slave on *.* to replication@'(从库IP)' identified by 'replication';

从库配置复制

CHANGE MASTER TO
  MASTER_HOST='主库IP',
  MASTER_USER='replication',
  MASTER_PASSWORD='replication',
  MASTER_PORT=（根据my.cnf配置端口）,
  MASTER_LOG_FILE='master2-bin.XXX',
  MASTER_LOG_POS=X,
  MASTER_CONNECT_RETRY=X; #等一系列参数

我的实例配置

mysql> change master to  
MASTER_HOST='119.29.215.56',
MASTER_USER='replication',
MASTER_PASSWORD='replication',
MASTER_PORT=6000,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=630；
#MASTER_LOG_FILE，MASTER_LOG_POS参数记录在刚才的 all_data.sql，可以用less命令查看。

配置完成以后启动：

mysql> start stave;

mysql> show slave status \G #是否启动看running是yes
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 119.29.215.56
                  Master_User: repl
                  Master_Port: 6000
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 952
               Relay_Log_File: VM_91_3_centos-relay-bin.000002
                Relay_Log_Pos: 605
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 952
              Relay_Log_Space: 787
              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: 6000
                  Master_UUID: cf441ef0-181b-11e7-b566-525400ef16de
             Master_Info_File: /mysqldata/node1/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)

上面各类参数都是可以配置的，需要查看文档