Mysql主从复制、常见报错以及解决方法

目录

一、搭建主从复制

二、主从复制常见报错

1、1062报错—主键冲突

2、1236报错----1）binlog日志没有开启，2）binlog日志的pos不一致

3、1593报错----主从库的server-id冲突

4、1032报错----从库误删了记录

5、2003报错----主库的mysql服务停止

一、搭建主从复制

1、主从库手动同步

1）导出主库所有数据到all.sql中

mysqldump --single-transaction -uroot -p123123 -A --master-data=2 > all.sql

–single-transaction和master-data=2参数可以保证数据的完整性

2）将数据拷贝到从库

scp -r all.sql root@106.75.209.58:/tmp/all.sql

3）从库同步数据

mysql -uroot -p < /tmp/all.sql

2、配置主从关系

• 主库操作：

1)修改配置文件，vim /etc/my.conf

[mysqld]

log-bin = mysql-bin

server-id = 1

2)为从库授权

>grant replication slave on *.* to 'test'@'106.75.209.58' identified by 'test';  //指定从库，并创建用来同步的账号密码
>flush privileges;

3）获取Binlog的日志名和偏移量

show master status;

• 从库操作：

1)修改配置文件，vim /etc/my.conf
[mysqld]
log-bin = mysql-bin
server-id = 2
2）配置主从信息

> change master to

    -> master_host='192.168.142.176',        #主库服务器的IP

    -> master_port=3306,                  #主库端口

    -> master_user='test',                 #主库用于复制的用户

    -> master_password='123',              #用户密码

    -> master_log_file='mysql-bin.000002',     #主库的Binlog日志名

    -> master_log_pos=570;               #Binlog日志的偏移量（从什么位置开始复制`

3）启动slave线程

>start slave;

3、测试主从同步

• 主库：

       create database copytest;
       use copytest;
       create table test1(id int,name varchar(11));
       insert into test1 valuse(1,'wyq');
       select * from test1;

• 从库：

show salve status;

select * from copytest.test1;

可以发现，已经同步过来了

二、主从复制常见报错

1、1062报错—主键冲突

• 报错模拟

主库：

>create table test2(id int not null auto_increment primary key,name varchar(11))auto_increment=1;
>insert into copytest.test2(name) values('wyq','glt');

从库：

select * form copytest.test2;

发现同步成功

从库插入数据：

insert into copytest.test2(name) values('hjc');
select * form copytest.test2;

主库：

select * form copytest.test2;

这个时候主库是没有新插入的数据的，因为配置的主从复制是单向的。我们在主库中来插入同样的数据，看会发生什么

insert into copytest.test2(name) values('hjc');
select * form copytest.test2

没有疑问，数据插入是没有问题的，但我们来看一下，主库的这个插入操作，有没有同步成功呢

从库：

show slave status\G

出现了1062错误码，因为：当从库同步新数据（执行SQL进程时）的时候，主键字段id在主库之前的数据上加一的，但是从库中已经存在了这个加一后的值，所以发生了主键冲突，导致SQL线程发生错误。报错1062

• 解决方法：

跳过或者删除从库自己插入的那条数据

从库：

stop slave;
set global sql_slave_skip_counter=1;
start slave;
show slave status\G

它的原理是：先停止slave线程，用set跳过这条事务，再打开slave线程即可。也可以在停掉salve线程后直接删除从库新插入的那条数据，开启slave线程后，自动使其同步即可。

2、1236报错----1）binlog日志没有开启，2）binlog日志的pos不一致

• 模拟报错1

主库:

#vim /etc/my.cnf
# log-bin = mysql-bin   //将这一行注释，关闭binlog
x  保存退出
#systemctl restart mysql
#mysql -uroot -p

从库：

>show slave status；

• 解决办法

还用说吗，编辑/etc/my.cnf，打开二进制日志开关，重启数据库并连接即可

• 模拟报错2

这个报错想了半天怎么搞出来呢，最后只好手动改binlog的pos了。。。改大改小都报错1236

主库：

>show master status; 

从库：

>stop slave;
> change master to master_log_file='mysql-bin.000003',master_log_pos=1900;
> start slave;
> show salve status;

• 解决办法

查看主库上对应的二进制日志的pos，在从库上修改pos，使其一致即可。

这个时候假装我们之前没改过（嘿嘿），还原一下真实的1236报错场景，好了，开始解决：
主库：shows master status; //记住二进制文件名和偏移量 pos
从库：

>stop slave;
> change master to master_log_file='mysql-bin.000003',master_log_pos=1899;
> start slave;
> show salve status;

3、1593报错----主从库的server-id冲突

• 报错模拟

将主从库的server-id改成一样的值，重启mariadb服务进程
从库：

>show slave status;

可以看到，提示主从库有了相同的server-id，因为这个server-id是mysql服务器的唯一表标识，一个server-id代表一台mysql服务器，所以不允许冲突。
解决方法： 将sevrer-id改为不一致的值即可，记得重启服务进程

4、1032报错----从库误删了记录

• 报错模拟

从库：

>select * from test2;

>delete from test2 where id =3;

• 主库

>update test2 set age=22 where id=3;

从库：

>show slave status;

看到1032报错，提示找不到记录，查看主库的二进制日志mysql-bin.000007 pos=1832

• 解决办法

在主库的binlog中找出发生错误时的那条语句，然后在从库中手动添加即可

主库：

# mysqlbinlog  -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000007 |grep -A '10' 1832

看到是更新了一条id=3,name=‘hjc’,age=22的记录，而从库是没有这条数据的，所以更新失败，SQL线程出错。那我们按照主库更新后的这条数据把他加在从库中即可
从库：

>stop slave;
>insert into test2 values(3,'hjc',22);
>show slave status;
>select * from test2;

5、2003报错----主库的mysql服务停止

• 报错模拟

主库：

#systemctl stop mariadb

从库：

>show slave status;

报错2003，IO线程显示正在连接中。提示重连主库出错

• 解决方法

恢复主库的mysql服务进程
主库：

#systemctl start mysql
#mysql -uroot -p

从库：

>show slave status;

在主从复制中还有很多种报错，在出现报错时，可以根据报错下面的提示来分析出错的原因。