一、Mysql主从复制是什么?
MysSQL主从复制是一个异步的复制过程,底层是基于Mysql数据库自带的二进制日志功能。就是一台或多台AysQL数据库(slave,即从库)从另一台MysQL数据库(master,即主库)进行日志的复制然后再解析日志并应用到自身,最终实现从库的数据和主库的数据保持一致。MySQL主从复制是MysQL数据库自带功能,无需借助第三方工具。
MysQL复制过程分成三步:
- master将改变记录到二进制日志( binary log);
- slave将master的binary log拷贝到它的中继日志(relay log);
- slave重做中继日志中的事件,将改变应用到自己的数据库中;
首先做主从复制时,主从版本必须一致;
二、配置主库
1.修改主库配置文件
vim /etc/my.cnf
[mysqld]
#开启binlog
log-bin=mysql-bin
#不与其它从节点重复即可
server-id=101
#需要同步的数据,如果不配置则同步全部
binlog-do-db=test_db
#binlog日志保留的天数,清除超过10天的日志;防止日志文件过大,导致磁盘空间不足
expire-logs-days=10
#开启混合模式
binlog_format=mixed
innodb_file_per_table=ON
skip_name_resolve=ON
2.重启主库mysql
systemctl restart mysql
3.在主库执行
#创建一个wang用户 ip是从库的ip,192.168.2.100,密码是123456
create user wang@'192.168.2.100' identified by '123456';
#给本机用户wang分配可对所有数据库的所有表进行所有操作的权限
grant all privileges on *.* to 'wang'@'192.168.2.100' with grant option;
#刷新权限
FLUSH PRIVILEGES;
4.记录下结果中File和Position的值
show master status;
三、配置从库
1.修改从库配置文件
[mysqld]
#不与主节点以及其它从节点重复即可
server-id=102 #必须
relay-log=relay-log #必须 开启中继日志
relay-log-index=relay-log.index #必须
innodb_file_per_table=ON #非必须
skip_name_resolve=ON #非必须
2. 重启
3. 命令行输入
CHANGE MASTER TO
MASTER_HOST='192.168.245.131',//MASTER主节点IP
MASTER_USER='wang',//主机创建的用户
MASTER_PASSWORD='123456',//用户密码
MASTER_LOG_FILE='mysql-bin.000001',//MASTER主机binlog日志名称,必须是最新的一个
MASTER_LOG_POS=157,//binlog日志偏移量,对应master节点的postion
master_port=3306;/端口号
4.开启从库,查看mysql主从状态
#开启从库
start slave;
#查看mysql主从状态
show slave status\G;
两个都为YES就代表成功,一个为NO都不行;
四、排错
排错参考1
排错参考2
如果主从数据库同步不了,就从库执行下 show slave status\G;如果是Slave_IO_Running: No,那么,很可能是主从库UUID重复了,或者是防火墙端口没开通,(这些上面都有讲到);
如果是 Slave_SQL_Running: No,那么我们可以通过跳过错误和手动同步来解决。
Slave_SQL_Running: No 问题分析
1.程序可能在slave上进行了写操作
2.也可能是slave机器重启后,事务回滚造成的.
备机上每写一次,主机master的pos都会变一次,有时候同步没成功,就会导致主从机pos值不一样,这时候有两种解决方法,一种是跳过这个错误,一种是手动同步pos值
解决办法一:跳过这个错误,
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;就是跳过一次的意思
解决办法:
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;
解决办法二:手动同步pos值
进入master
到主服务器上查看主机状态:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 2844 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
然后进到slave
首先停掉Slave服务: stop slave;
然后到slave服务器上执行手动同步:
mysql>change master to
master_host='192.168.81.100',master_user='slave',master_password='135564',
master_log_file='mysql-bin.000003',master_log_pos=2844;
再打开Slave服务: start slave;
亲测下面可用:
Slave_SQL_Running: No 问题解决
- 查看master节点日志偏移量,同上
show master status\G
- 停掉从库的slave
stop slave;
- 清空从库的slave
reset slave;
-
在slave节点修改日志偏移量与master一致
CHANGE MASTER TO
MASTER_HOST=‘192.168.104.101’,
MASTER_USER=‘kangyuan’,
MASTER_PASSWORD=‘Root!123’,
MASTER_LOG_FILE=‘mysql-bin.000028’,
MASTER_LOG_POS=439908904; -
最后启动slave节点
start slave;
查看是否设置成功show slave status\G
,两个yes 就是成功,同上。