本文共 4080 字，大约阅读时间需要 13 分钟。
主从复制主要用途：
1.灾备切换2.读写分离3.备份，避免影响业务4.高可用和故障切换5.mysql测试升级首先，异步主从复制主要步骤：
1.主库中开启log_bin2.全备份主库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.cnfmysql 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=6000root 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.sqlEnter password: [root@VM_85_42_centos downloads]# lsall_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: 0Master_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: 01 row in set (0.00 sec)上面各类参数都是可以配置的，需要查看文档
转载地址：http://msvia.baihongyu.com/