MySQL5.7.19 - 主从复制 - 日志点
来源:SegmentFault
时间:2023-01-13 08:09:05 158浏览 收藏
对于一个数据库开发者来说,牢固扎实的基础是十分重要的,golang学习网就来带大家一点点的掌握基础知识点。今天本篇文章带大家了解《MySQL5.7.19 - 主从复制 - 日志点》,主要介绍了MySQL、主从复制,希望对大家的知识积累有所帮助,快点收藏起来吧,否则需要时就找不到了!
【第一部分】 Master - Lebron - 192.168.1.122
1. 开放3306端口
[root@lebron sysconfig]# vim /etc/sysconfig/iptables # Firewall configuration written by system-config-firewall # Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT #mysql port -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT
2. 重启网络
[root@lebron sysconfig]# service iptables restart iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ] iptables: Applying firewall rules: [ OK ]
3. 开启binlog
[root@lebron log]# vim /etc/my.cnf [mysqld] log-bin=mysql-bin server-id=1 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
4. 重启mysqld
service mysqld restart
5. 登录mysql,创建复制用户
create user 'lxl'@'192.168.%' identified by '123456';
6. 赋权
grant replication slave on *.* to lxl@'192.168.%';
7. 创建测试数据库
create database lixinlei;
8. 创建测试表
use lixinlei; create table t(id int, c1 varchar(10), primary key(id));
9. 插入测试数据
insert into t values(1,'aa'),(2,'bb'),(3,'cc');
10. 退出mysql
quit
11. 备份数据库
[root@lebron tmp]# cd /tmp [root@lebron tmp]# mysqldump --single-transaction --master-data=2 --triggers --routines --all-databases -uroot -p > all.sql
12. 拷贝备份文件到Slave - Kyrie
[root@lebron tmp]# scp all.sql root@192.168.1.123:/tmp
【第二部分】 Slave - Kyrie - 192.168.1.123
1. 开放3306端口
[root@lebron sysconfig]# vim /etc/sysconfig/iptables # Firewall configuration written by system-config-firewall # Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] -A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A INPUT -p icmp -j ACCEPT -A INPUT -i lo -j ACCEPT -A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT #mysql port -A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT -A INPUT -j REJECT --reject-with icmp-host-prohibited -A FORWARD -j REJECT --reject-with icmp-host-prohibited COMMIT
2. 重启网络
[root@lebron sysconfig]# service iptables restart iptables: Setting chains to policy ACCEPT: filter [ OK ] iptables: Flushing firewall rules: [ OK ] iptables: Unloading modules: [ OK ] iptables: Applying firewall rules: [ OK ]
3. 开启binlog
[root@lebron mysql]# vim /etc/my.cnf [mysqld] log-bin=mysql-slave server-id=2 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
4. 重启mysqld
service mysqld restart
5. 检查备份文件是否拷贝成功
[root@kyrie ~]# cd /tmp [root@kyrie tmp]# ls all.sql yum.log
6. 恢复
[root@kyrie tmp]# mysql -uroot -p
7. 记录Master的日志文件&日志点
[root@kyrie tmp]# more all.sql MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
8. 登录mysql,检查恢复是否成功
[root@lebron tmp]# mysql -uroot -p'y;WQx*H>?78K' mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lixinlei | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) mysql> use lixinlei; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------------+ | Tables_in_lixinlei | +--------------------+ | t | +--------------------+ 1 row in set (0.00 sec) mysql> select * from t; +----+------+ | id | c1 | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | +----+------+ 3 rows in set (0.00 sec)
9. 配置复制链路
mysql> change master to master_host='192.168.1.122', -> master_user='lxl', -> master_password='123456', -> master_log_file='mysql-bin.000001', -> master_log_pos=154;
10. 启动Slave
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
11. 查看Slave状态
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.122 Master_User: lxl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: lebron-relay-bin.000005 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 528 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: a41e6957-dc02-11e7-a9aa-0800271c6804 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
【第三部分】 验证
1. Mater插入数据
[root@lebron sysconfig]# mysql -uroot -p'y;WQx*H>?78K' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.7.19-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use lixinlei; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------------------+ | Tables_in_lixinlei | +--------------------+ | t | +--------------------+ 1 row in set (0.00 sec) mysql> select * from t; +----+------+ | id | c1 | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | +----+------+ 3 rows in set (0.00 sec) mysql> insert into t values(4,'dd'); Query OK, 1 row affected (0.02 sec)
2. Slave验证是否同步成功
[root@lebron mysql]# mysql -uroot -p'y;WQx*H>?78K' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.19-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use lixinlei; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * from t; +----+------+ | id | c1 | +----+------+ | 1 | aa | | 2 | bb | | 3 | cc | | 4 | dd | +----+------+ 4 rows in set (0.00 sec)
终于介绍完啦!小伙伴们,这篇关于《MySQL5.7.19 - 主从复制 - 日志点》的介绍应该让你收获多多了吧!欢迎大家收藏或分享给更多需要学习的朋友吧~golang学习网公众号也会发布数据库相关知识,快来关注吧!
声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
-
499 收藏
-
244 收藏
-
235 收藏
-
157 收藏
-
101 收藏
最新阅读
更多>
-
321 收藏
-
260 收藏
-
229 收藏
-
478 收藏
-
427 收藏
-
259 收藏
课程推荐
更多>
-
- 前端进阶之JavaScript设计模式
- 设计模式是开发人员在软件开发过程中面临一般问题时的解决方案,代表了最佳的实践。本课程的主打内容包括JS常见设计模式以及具体应用场景,打造一站式知识长龙服务,适合有JS基础的同学学习。
- 立即学习 542次学习
-
- GO语言核心编程课程
- 本课程采用真实案例,全面具体可落地,从理论到实践,一步一步将GO核心编程技术、编程思想、底层实现融会贯通,使学习者贴近时代脉搏,做IT互联网时代的弄潮儿。
- 立即学习 507次学习
-
- 简单聊聊mysql8与网络通信
- 如有问题加微信:Le-studyg;在课程中,我们将首先介绍MySQL8的新特性,包括性能优化、安全增强、新数据类型等,帮助学生快速熟悉MySQL8的最新功能。接着,我们将深入解析MySQL的网络通信机制,包括协议、连接管理、数据传输等,让
- 立即学习 497次学习
-
- JavaScript正则表达式基础与实战
- 在任何一门编程语言中,正则表达式,都是一项重要的知识,它提供了高效的字符串匹配与捕获机制,可以极大的简化程序设计。
- 立即学习 487次学习
-
- 从零制作响应式网站—Grid布局
- 本系列教程将展示从零制作一个假想的网络科技公司官网,分为导航,轮播,关于我们,成功案例,服务流程,团队介绍,数据部分,公司动态,底部信息等内容区块。网站整体采用CSSGrid布局,支持响应式,有流畅过渡和展现动画。
- 立即学习 484次学习
评论列表
-
- 干净的悟空
- 这篇文章真是及时雨啊,好细啊,很好,码起来,关注老哥了!希望老哥能多写数据库相关的文章。
- 2023-04-12 16:05:04
-
- 怕孤单的墨镜
- 很棒,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,看完之后很有帮助,总算是懂了,感谢up主分享技术文章!
- 2023-04-05 11:07:34
-
- 文艺的盼望
- 真优秀,一直没懂这个问题,但其实工作中常常有遇到...不过今天到这,帮助很大,总算是懂了,感谢博主分享技术文章!
- 2023-03-27 01:31:59
-
- 朴素的小懒猪
- 这篇文章真是及时雨啊,好细啊,感谢大佬分享,码起来,关注大佬了!希望大佬能多写数据库相关的文章。
- 2023-03-25 21:47:40
-
- 安静的歌曲
- 太全面了,mark,感谢大佬的这篇文章内容,我会继续支持!
- 2023-03-20 20:45:52
-
- 微笑的长颈鹿
- 这篇技术文章出现的刚刚好,好细啊,受益颇多,收藏了,关注老哥了!希望老哥能多写数据库相关的文章。
- 2023-01-31 14:49:35