mysql 主从复制

MySQL 主从复制原理

mysql 主从搭建（先有主运行一段时间后搭建了从）

主库上写一些数据以便测试

create database test;
use test;
create table t1(id int,name varchar(50));
insert into t1 values(1,'kobe');
insert into t1 values(2,'ivring');

主上配置

vi /etc/my.cnf 
    log-bin = /data/mysql/binlog/mysql-bin.log
    log_bin_index=/data/mysql/binlog/mysql-bin.index
    binlog-format=MIXED
    server-id=1

mkdir -p /data/mysql/binlog/
chown mysql.mysql /data/mysql/binlog/ -R

grant replication slave on *.* to rep@'192.168.231.%' identified by '1qaz@WSX';
flush privileges;
flush  tables  with read  lock;        #会话别退出
mysqldump -p'1qaz@WSX' --all-databases --single-transaction --master-data=2  --flush-logs > `date +%F`-all.sql    
unlock tables;

把备份出来的sql 语句文件复制到备机
备份完成后再增加新数据
insert into test.t1 values(3,'wade');
insert into test.t1 values(6,'james');

备机操作配置

vi /etc/my.cnf
    log-bin =/data/mysql/binlog/mysql-bin.log
    log_bin_index=/data/mysql/binlog/mysql-bin.index
    binlog-format=MIXED
    server-id=2

mkdir -p /data/mysql/binlog/
chown mysql.mysql /data/mysql/binlog/ -R
systemctl start mysqld

更改初始密码
mysql -h 192.168.231.100 -urep -p'1qaz@WSX'    #测试是否能够连接到主库
show grants;
mysql -p'asdfgfdf'  < /opt/2019-07-18-all.sql
mysql> change master to
    -> master_host='192.168.231.100',
    -> master_port=3306,
    -> master_user='rep',
    -> master_password='1qaz@WSX',
    -> master_log_file='mysql-bin.000002',        
#通过备份的sql文件查看得到,或者在master 执行命令show master status;查看
    -> master_log_pos=154;
    start slave;
    show slave status\G;     #io和sql 进程全部YES就可以了
   

master-data=1 时备机操作

#当在主机上备份库时mysqldump -p‘..’ --all-databases --single-transaction --master-data=1 时意味着在sql文件中记录了change master to ...的语句，且为非注释状态

前几步骤同上    #先不执行mysqldump 备份的语句
mysql> change master to
    -> master_host='192.168.231.100',
    -> master_port=3306,
    -> master_user='rep',
    -> master_password='1qaz@WSX',    #此处没有master_log_file master_log_pos 配置

source /opt/2019-07-18-all.sql    
#会话不要退出，执行source ，因为sql文件中记录了master_log_file master_log_pos ，所以此步骤就执行了change master to .. 的操作，一箭双雕，即导入了库，也配置了binlog 日志同步的位置。

start slave;

mysql 主从-GTID

5.6以后支持。主从复制无需配置change master to  logfile 和log position 直接自动协商

流程图

主上配置

vi /etc/my.cnf
    log-bin
    server-id=1
    gtid_mode=ON
    enforce_gtid_consistency=1

systemctl start mysqld
grep 'temporary password' /var/log/mysqld.log
mysqladmin -p'...' password "..."
创建一些数据

grant replication slave on *.* to rep@'192.168.231.%' identified by '1qaz@WSX';
flush privileges;
flush  tables  with read  lock;            #会话别退出
mysqldump -p'1qaz@WSX' --all-databases --single-transaction --master-data=2  --flush-logs --skip-gtids=true > `date +%F`-all.sql    
#如果开启了gtid 可以加上 --skip-gtid 参数
unlock tables;

从上配置

vi /etc/my.cnf
    log-bin
    server-id=2
    gtid_mode=ON
    enforce_gtid_consistency=1

systemctl restart mysqld

mysql> change master to
    -> master_host='192.168.231.102',
    -> master_port=3306,
    -> master_user='rep',
    -> master_password='1qaz@WSX',
    -> master_auto_position=1;            #binlog 日志位置采用自动协商方式

mysql>source /opt/2019-07-19-all.sql
mysql>show slave status\G

FAQ

show slave status\G
报错：
    Error 'Can't create database 'tem2'; database exists' on query. Default     
    database: 'tem2'. Query: 'create database tem2'

解决：
    mysql> set global sql_slave_skip_counter = 1;
    mysql>start slave;

    报错：
        ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the 
        server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each 
        transaction that you want to skip, generate an empty transaction with the 
        same GTID as the transaction
        原因也说的很清楚了,不支持GTID_MODE 模式运行的数据库
    
    解决：
        mysql> show slave statusG;    查看一下信息并记录下来
           Executed_Gtid_Set: 7f8d9eb8-a7fe-11e2-84fd-0015177c251e:1-260
        mysql> reset master;
        mysql> stop slave; 
        mysql> reset slave;
        重新设置GTID以跳过错误的信息，记得在第一步我们记录下来Executed_Gtid_set    
        吗? 没错执行它的时候粗错了,那么保守起见直接跳过这一条即可,在其ID上加1即可, 
        代码如下:
        mysql> set global gtid_purged=’7f8d9eb8-a7fe-11e2-84fd-5177c251e:1- 
                    261′;
        CHANGE MASTER TO MASTER_HOST=’192.168.1.136′,     
        MASTER_PORT=3306, 
        MASTER_USER=’dbadmin’,MASTER_PASSWORD=’123456′, 
        master_auto_position=1;
        
        start slave;
        show slave statusG;