如何配置mysql主从复制(master 、salve)
目录
- 前言
- mysql主从复制原理
- 1、指定master中的server-id
- 2、开启master中的binlog功能
- 3、 建立一个mysql主从复制专用用户rep并授权
- 4、对主库进行锁表并导出数据(全备)
- 5、scp命令传输全备文件到slave服务器
- 6、设置slave服务器的server-id和关闭binlog参数
- 7、开始还原slave从库
- 8、登录slave从库并配置参数
- 9、启动slave从库开始主从复制
- 10、slave从库查看主从复制状态
- 11、教你一招无需熬夜,轻松部署mysql主从复制
- 12、查看master主库的I /O 线程状态
- 13、查看slave从库的I /O 线程、SQL线程状态
- 14、停止主从复制/停止从库复制
前言
环境:CentOS 7.9、mysql 5.7
准备两台mysql服务器,都安装相同的mysql5.7版本,以下操作假设master已经运行了很久,已经有很多数据,现在要做一台slave服务器,安装好slave服务器上的mysql版本,版本与master服务器中mysql版本一致。
mysql主从复制原理
master服务器接收来自客户端的数据更新,而slave接收来自master服务器的binlog日志内容,并解析出SQL语句后再重新在slave上重做一遍,从而使得主从数据一致。
1、应用在master主库上进行数据更改(DDL、DML、DCL)操作,并将更改记录记入Binary Log日志;
2、slave从库IO线程将master主库上的日志复制到自己的中继日志(Relay Log)中;
3、slave从库SQL线程读取中继日志(Relay Log)中的事件,将其重做到从库;
1、指定master中的server-id
vim /etc/my.cnf
[mysqld] #必须在mysqld下添加
server_id=1 #指定server_id,这id的值可以是整数,而且不能与其他实例相同,否则冲突
2、开启master中的binlog功能
vim /etc/my.cnf
[mysqld] #必须在mysqld下添加
server_id=1 #指定server_id,这id的值可以是整数,而且不能与其他实例相同,否则冲突
log_bin=/usr/local/mysql/binlog #开启binlog功能,binlog是文件的前缀,不是目录喔,生成的binlog日志都将是binlog.****开头的
注意:修改了mysql的配置文件,就必须重启mysql服务器:
service mysql.server restart
show variables like 'server_id'; #登录数据库命令查看server-id和binlog参数
show variables like 'log_bin';
3、 建立一个mysql主从复制专用用户rep并授权
root账号密码登录master服务器,然后建立一个mysql主从复制专用的用户rep:
grant replication slave on *.* to 'rep'@'192.168.1.%' identified by 'aabb001'; #授权,权限必须是replication slave
说明:
replication slave是主从复制专用权限, *.* 表示所有库所有表的含义,当然也可以具体指定库和表,如:DEP.emp、DEP.*,
'rep'@'192.168.1.%'表示用户名以及允许整个192.168.1.0网段使用rep该用户登录.
以上是mysql5.7版本中的直接建立用户并授权语句,如果是mysql8.0版本的话,还必须先创建用户再授权,因为mysql8.0版本用户和密码管理改变了,如下:
create user rep@'192.168.1.%' identified with mysql_native_password by 'aabb001';
grant replication slave on *.* to 'rep'@'192.168.1.%'
4、对主库进行锁表并导出数据(全备)
flush table with read lock; #可以查看锁表后的binlog日志文件和binlog日志偏移量
show master status; #后面从库导出全备后,正式开始主从复制就是从这个binlog文件和偏移量开始的
再打开一个ssh窗口进行全备份数据:
mysqldump -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock --events -A -B | gzip > /server/backup/mysql_bak.$(date +%F).sql.gz
返回上一个ssh窗口解锁表:
unlock tables;
(这一步等待验证,听说可以不用锁表也能全备)
5、scp命令传输全备文件到slave服务器
scp /server/backup/mysql_bak.2021-07-12.sql.gz root@192.168.1.2:/server/backup/
6、设置slave服务器的server-id和关闭binlog参数
vim /etc/my.cnf
[mysqld] #必须在mysqld下添加
server_id=2 #指定server_id,这id的值可以是整数,而且不能与其他实例相同,否则冲突
#log_bin=/usr/local/mysql/binlog #关闭binlog功能,删除或注释这一行即可
#如果slave库也是作为主库的就也需要开启binlog功能,如AB互为主从,ABC级联主从的情况.
修改了配置参数文件,需要重启mysql服务:
service mysql.server restart
show variables like 'server_id'; #登录数据库命令查看server_id和binlog参数
show variables like 'log_bin';
7、开始还原slave从库
gzip -d /server/backup/mysql_bak.2021-07-12.sql.gz #解压包,原包会被删除
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock </server/backup/mysql_bak.2021-07-12.sql
说明:因为master备份时使用-A参数全备份,所以这里从库还原之后root密码和master的root密码一致了,因为mysql数据库的user表都是一样的。
8、登录slave从库并配置参数
方法一、
登录slave从库,在命令行执行以下的命令参数:
change master to
master_host='192.168.1.1', #master主库IP
master_port=3306, #master主库端口
master_user='rep', #主从复制用户rep
master_password='aabb001', #主从复制用户rep的密码
master_log_file='mysql-bin.00008', #上面第4步在主库show master status时看到的binlog日志
master_log_pos=342; #上面第4步在主库show master status时看到的binlog日志偏移量
说明:命令等号两边不能有空格。
方法二、
不用登录从库,直接在Linux命令行配置:
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock <<EOF
change master to #change master 语句大小写均可
master_host='192.168.1.1',
master_port=3306,
master_user='rep',
master_password='aabb001',
master_log_file='mysql-bin.00008', #其实这一行可以省略,前提是master备份时指定--master-data=1参数
master_log_pos=342; #其实这一行可以省略,前提是master备份时指定--master-data=1参数
EOF
9、启动slave从库开始主从复制
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock #登录从库
start slave; #启动主从复制
10、slave从库查看主从复制状态
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock #登录slave
show slave status\G #查看从库状态(不用加;号)
重点关注以下3个参数:
slave_IO_Running、slave_SQL_Running、Seconds_Behind_Master
slave_IO_Running: YES 表示I/O线程工作正常
slave_SQL_Running: YES 表示I/O线程工作正常
Seconds_Behind_Master: 0 此参数表示主从复制延时秒数
11、教你一招无需熬夜,轻松部署mysql主从复制
master主库半夜执行定时计划任务,命令如下:
mysqldump -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock -A --events -B -x --master-data=1 | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
白天找机会把全备份文件导入从库,并配置从库,如下:
gzip -d /server/backup/mysql_bak.2021-07-12.sql.gz
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock <<EOF
change master to
master_host='192.168.1.1',
master_post=3306,
master_user='rep',
master_password='aabb001',
EOF
注意:以上的配置从库change master语句中并没有指定master_log_file和master_log_pos
,那么从库是怎么知道从哪个位置开始进行主从复制呢?答案是master全备时指定了--master-data=1
参数,实际上该参数在备份文件里添加了如下语句:
--Position to start replication or point-in-time recovery from CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.00008',MASTER_LOG_POS=342;
12、查看master主库的I /O 线程状态
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock #登录master主库
show processlist\G #查看master主库的I /O 线程状态
13、查看slave从库的I /O 线程、SQL线程状态
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock #登录slave从库
show processlist\G #查看slave从库的I /O 线程、SQL线程状态
14、停止主从复制/停止从库复制
mysql -uroot -p'aabb001' -S /usr/local/mysql/bin/mysql.sock #登录slave从库
stop slave;
相关文章
- mysql-介绍、MySQL部署、数据类型、存储引擎
- 磁盘爆满导致MySQL无法启动:Disk is full writing './mysql-bin.~rec~' (Errcode: 28). Waiting for someone to free space...
- 【MySQL】sync_binlog innodb_flush_log_at_trx_commit 浅析
- VS Code First使用Mysql数据库详解
- MySQL按照汉字的拼音排序,mysql汉字排序
- mysql_use_result & mysql_store_result & MYSQLI_ASYNC
- mysql innobackupex xtrabackup 大数据量 备份 还原(转)
- 阿里规范 - 五、MySQL 数据库 - (一)建表规约 - 2 - 数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑
- mysql 重新整理——配置文件[一]
- PHP连接MySQL数据库的三种方式(mysql、mysqli、pdo)
- MYSQL错误解决:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- mysql 主主复制
- MySQL高可用性之Keepalived+Mysql(双主热备)
- [转]分析MySQL数据类型的长度【mysql数据字段 中length和decimals的作用!熟悉mysql必看】
- 数据库基础之Mysql(2)主从库配置
- 重新整理 mysql 基础篇————— mysql 事务[三]
- MySQL主从同步(四)——M-M架构配置实战
- Atitit postgresql data type 数据类型与mysql对应表 数据库常用数据类型 PostgreMysql 整数intgreterInt 小数numericFL
- Starting MySQL ERROR! Couldn‘t find MySQL server (/usr/local/mysql/bin/mysqld_safe)
- 性能提升1400+倍,快来看MySQL Volcano模型迭代器的谓词位置优化详解
- MySQL Study之--Mysql无法启动“mysql.host”
- Mysql下Limit注入方法(此方法仅适用于5.0.0<mysql<5.6.6的版本)
- 【MySQL笔记】mysql来源安装/配置步骤和支持中国gbk/gb2312编码配置
- Mysql数据库的mysql Schema 究竟有哪些东西& 手工注入的基础要领
- 设计 MySQL 数据表的时候一般都有一列为自增 ID,这样设计原因是什么,有什么好处?
- Mysql之安全清理mysql-slow.log
- 【中间件】Redis与MySQL双写一致性如何保证?--缓存和数据库在双写场景下一致性是如何保证的
- Mycat启动正常但无法连接ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/lib/mysql
- Shell脚本一键安装-----MYSQL数据库