文章目录
环境介绍
提示:为方便新手与演示,本人使用宝塔面板进行环境快速搭建(与使用命令行搭建无异,仅文件位置可能稍有不用,不影响开发)
主库开发环境:Linux+ContOS7+MySql8+php7(主)
从库A开发环境:Linux+Ubuntu 20.04.1 +MySql8+php7(从A)
从库B开发环境:Linux+Ubuntu 20.04.1 +MySql8+php7(从B)
服务器数量3台(服务器数量至少>=2台)
原理
官方解释
1)主库master将数据的改变记录到binlog二进制日志中,
2)从库slave会在一定时间间隔内对主库master中的二进制文件进行检测是否发生改变,
如果发现主库master二进制文件有改变则从库slave会开始I/OThread线程请求主库master二进制事件
3)同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存在从节点本地的中继日志中,从节点将启动SQL线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致,最后I/OThread和SQLThread将进入睡眠状态,等待下一次被唤醒。
白话版:
- 主库master有个binlog二进制日志用来记录主库master上的数据改变。
- 从库slave有2线程,一个是I/O线程,一个是SQL线程。
- 从库的I/O线程会在一定时间间隔内主动请求主库的binlog
- 主库被请求后生成一个log dump线程用来给从库的I/O线程传binlog
- 从库将得到的binlog存在自己本地的relay-log(中继日志)文件中
- 从库SQL线程会读取relay-log(中继日志)文件,并将内容解析成sql语句在从库上重新执行一遍
- 这就完成了主从复制的过程
- End
实现
情景:搭建一主多从
主库公网ip:39.103.299.01
从库公网ip:115.28.137.02
一、创建数据库
- 在主服务器上创建数据库,将创建好的数据库文件备份。
2.在从库上创建数据库,并将主库的数据被备份在从库上导入。(目的:保证主从2个数据库是完全一致)
不导入也可以,不过有时会因为在主库新建表格,从库并没有自动创建,此时插入数据从库数据库日志会报错,导致主从复制关系失效!
一、主库操作
-
使用root权限登录主库mysql
mysql -u root -p
-
主库创建mysql用户,可以将用户设置为所有人都能访问,也可以设置成指定从库ip访问。
2.1 创建所有人都能访问mysql用户并设置密码create user '用户名'@'%';
#创建用户rootslave并设置为设备都可登录 create user 'rootslave'@'%';
2.2为新用户设置密码
set password for '同户名'@'host'='新的密码';
这里的host只有3种分别为 %(所有ip)、localhost(本服务器)、指定的服务器ip(如158.652.30.145)
#将用户rootslave的密码设为qwe123 set password for 'rootslave'@'%'='qwe123';
2.3位新创建的用户设置权限
grant replication slave on *.* to '用户名'@'从服务ip(host)';
注意:我们不在这里设置同步的具体数据库名和数据库表,而是在mysql配置文件中进行设置,所以照这个命令敲即可!#给rootslave用户分配‘replication slave’权限并让他可以操作所有数据库中的所有表 #如果是2台从服务器,只需要将这条命令运行2次(注意后面的从服务器ip地址) #我这里为了延时,直接从服务器的地址设为了% grant replication slave on *.* to 'rootslave'@'%';
这里建议给rootslave账户的权限高一些,不然后面主库新建表时可能会因为账号权限不足导致主从SQL线程因权限问题报错。
2.4刷新权限
注意:只要执行了权限操作指令就要用这个命令来刷新flush privileges;
2.5查看命令是否分配成功
show grants for 'rootslave'@'%';
3.配置主服务器my.cnf文件(文件路径 /etc/my.cnf)
这个可以在服务器中使用vim /etc/my.cnf
进行修改。也可以在宝塔中进行配置
3.1
server-id=66 #服务器id 此id在主从复制服务器集群中要保持唯一(每台服务器的这值都是唯一的)
log-bin=mysql-bin #二进制文件存放路径,非必须,mysql8后默认存放/var/lib/mysql这里
binlog-do-db=sync_database #想要同步的数据库,如果有多个以空格隔开db1 db2 db3 ....
binlog-ignore-db=mysql #不同步的数据(可以不配置) 如果有多个以空格隔开db1 db2 db3 ....
3.2修改了mysql配置项需要重载配置重启mysql
3.2.1重启命令:
service mysqld restart;
3.2.2 或者在宝塔中重启mysql,两种方式二选一
4.检查binlog日志是否开启
show variables like 'log_bin';
状态为ON表示已经开启
5.查看主库状态,获取从库必要的配置参数
show master status;
记住这几个参数,配置从库的时候要用到;
从库配置
- 配置从服务器my.cnf文件(文件路径 /etc/my.cnf),将server-id=2 #这里的id在mysql集群中必须是唯一的不可重复的(数字自定义就可以)
这个可以在服务器中使用vim /etc/my.cnf
进行修改。也可以在宝塔中进行配置与主服务器的配置方法一样
-
修改了mysql配置项,需要重启mysql
service mysqld restart
-
启动主从同步功能
3.1 登录从服务器mysqlmysql -u root -p 密码
3.2 设置同步信息
change master to master_host='主服务器的IP',master_user='主服务的slave账号',master_password='主服务器的密码',master_log_file='日志的名称',master_log_pos=同步位置;
change master to master_host='39.103.233.01',master_user='rootslave',master_password='qwe123',master_log_file='mysql-bin.000013',master_log_pos=156;
3.2.1查看配置信息(这个表在主服务器的第5步)
3.2.2 在从服务器mysql中设置主从配置
4.启动同步start slave;
5.查看服务启动状态show slave status\G
6. 测试效果(左主库,右从库)
常见错误处理
从库启动主从查询状态出现异常
情况1:(从库查看运行状态出现Slave_IO_Running: No)
情况2:(从库查看运行状态出现Slave_SQL_Running: No)
1.主库使用命令:show master status;
来查看主库配置信息
2.从库使用命令:show slave status\G
命令查看从库主从配置信息。
开始解决问题(从库操作)
stop slave;//停止主从同步
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=0; //当Slave_IO_Running:No使用本行命令
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; //当 Slave_SQL_Running: No使用本行命令
start slave;//启动主从服务器
show slave status\G //查看主从同步信息
关于其他错误
是什么原因导致主从复制失效的?最简单的途径就是看sql的错误日志!
更具具体的错误日志去解决问题
例如:主库建表,从库并没跟着新建表,而且主从也失效了,通过查看主库的mysql日志发现,是因为我的主库rootslave主从同步账号权限给的不够导致的同步建表失败!
MySql常见错误码
MySQL常见错误类型
1005:创建表失败
1006:创建数据库失败
1007:数据库已存在,创建数据库失败
1008:数据库不存在,删除数据库失败
1009:不能删除数据库文件导致删除数据库失败
1010:不能删除数据目录导致删除数据库失败
1011:删除数据库文件失败
1012:不能读取系统表中的记录
1020:记录已被其他用户修改
1021:硬盘剩余空间不足,请加大硬盘可用空间
1022:关键字重复,更改记录失败
1023:关闭时发生错误
1024:读文件错误
1025:更改名字时发生错误
1026:写文件错误
1032:记录不存在
1036:数据表是只读的,不能对它进行修改
1037:系统内存不足,请重启数据库或重启服务器
1038:用于排序的内存不足,请增大排序缓冲区
1040:已到达数据库的最大连接数,请加大数据库可用连接数
1041:系统内存不足
1042:无效的主机名
1043:无效连接
1044:当前用户没有访问数据库的权限
1045:不能连接数据库,用户名或密码错误
1048:字段不能为空
1049:数据库不存在
1050:数据表已存在
1051:数据表不存在
1054:字段不存在
1065:无效的SQL语句,SQL语句为空
1081:不能建立Socket连接
1114:数据表已满,不能容纳任何记录
1116:打开的数据表太多
1129:数据库出现异常,请重启数据库
1130:连接数据库失败,没有连接数据库的权限
1133:数据库用户不存在
1141:当前用户无权访问数据库
1142:当前用户无权访问数据表
1143:当前用户无权访问数据表中的字段
1146:数据表不存在
1147:未定义用户对数据表的访问权限
1149:SQL语句语法错误
1158:网络错误,出现读错误,请检查网络连接状况
1159:网络错误,读超时,请检查网络连接状况
1160:网络错误,出现写错误,请检查网络连接状况
1161:网络错误,写超时,请检查网络连接状况
1062:字段值重复,入库失败
1169:字段值重复,更新记录失败
1177:打开数据表失败
1180:提交事务失败
1181:回滚事务失败
1203:当前用户和数据库建立的连接已到达数据库的最大连接数,请增大可用的数据库连接数或重启数据库
1205:加锁超时
1211:当前用户没有创建用户的权限
1216:外键约束检查失败,更新子表记录失败
1217:外键约束检查失败,删除或修改主表记录失败
1226:当前用户使用的资源已超过所允许的资源,请重启数据库或重启服务器
1227:权限不足,您无权进行此操作
1235:MySQL版本过低,不具有本功能
mysql主从复制常用命令
创建用户相关
-
创建新用户
host: '%'=所有人 'localhost'=本服务器才能访问 '指定ip'=指定ip才能访问
格式:
create user '新用户的名字'@'host地址' identified by '新用户的登录密码';
#创建用户并设置用户密码 create user 'rootslave'@'%' identified by 'qweabc123'; #指定多个ip能访问 并设置密码(多个ip使用英文逗号分割) create user 'rootslave'@'115.28.137.30','rootslave'@'8.140.159.122' identified by 'qweabc123'; #或者 仅创建用户 create user 'rootslave'@'%';
-
修改mysql用户密码
格式:set password '用户名'@'登录地址' = '新密码';
#设置用户名为rooter且host地址为%的用户密码为123qwe set password for 'rooter'@'%' = '123qwe';
-
查看用户名和host
格式:select 字段A,字段B from 数据库名.用户表;
#查询数据库名为mysql中的user数据表,获取所有user与host列 select user,host from mysql.user;
-
删除用户
格式:DROP USER '用户名'@'host地址';
#删除用户 rootslave drop user 'rootslave'@'localhost';
权限相关
-
给新用户设置权限
格式:grant 权限 on 数据库.数据表 to '要被授权的用户名'@'用户host';
#设置最高权限 grant all privileges on *.* to 'rootslave'@'%';
-
刷新权限
设置完权限后要执行此命令flush privileges;
-
查看用户权限
格式:show grants for '用户名'@'用户的host';
#查看rootslave用户拥有的权限 show grants for 'rootslave'@'%'; #或 show grants for 'rootslave';#如果这个用户的host有%、localhost、指定ip可能会出现多个结果
-
删除用户权限
格式:revoke 权限 on 数据对象 from 用户;
#删除用户rootslave在所有数据库中所有数据表的全部权限 revoke all privileges on *.* from 'rootslave'@'%';
-
显示mysql进程
show processlist;