锐客网

  1. 首页 > 生活百科 > it技术 > >

第十二周学习作业

IT知识作业学习作业

追风赶月莫停留，平芜尽处是春山。这篇文章主要讲述第十二周学习作业相关的知识，希望能为你提供帮助。
1、主从复制及主主复制的实现
主从复制实现mysql的主从复制,读写分离,每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
主从复制相关线程
主节点：
dump Thread：为每个Slave的I/O Thread启动一个dump线程，用于向其发送binary log events
从节点：
【第十二周学习作业】I/O Thread：向Master请求二进制日志事件，并保存于中继日志中
SQL Thread：从中继日志中读取日志事件，在本地完成重放
主节点配置:

#修改主节点配置文件,重新启动服务
[root@master ~]# echo -e"server-id=128 \\nlog-bin" > > /etc/my.cnf.d/mysql-server.cnf
[root@master ~]# systemctl restart mysqld.service
#查看二进制文件和位置
mysql> show master logs;
+-------------------+-----------+-----------+
| Log_name| File_size | Encrypted |
+-------------------+-----------+-----------+
| master-bin.000001 |179 | No|
| master-bin.000002 |204 | No|
| master-bin.000003 |156 | No|
+-------------------+-----------+-----------+
3 row in set (0.00 sec)
#备份数据库
[root@master ~]# mysqldump -A -F --master-data=https://www.songbingjia.com/android/1 --single-transaction > /data/all.sql

#创建复制账户
mysql> create user repluser@172.16.128.% identified by 123456;
Query OK, 0 rows affected (0.03 sec)

mysql> grant replication slave on *.* to repluser@172.16.128.%;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#备份数据拷贝到slave 节点
[root@master ~]# scp -r /data 172.16.128.129:/
root@172.16.128.129s password:
all.sql100% 1208KB41.8MB/s00:00


从节点配置:
#修改配置文件,重新启动服务
[root@slave ~]# echo -e "server-id=129 \\nlog-bin \\nread-only" > > /etc/my.cnf.d/mysql-server.cnf
[root@slave ~]# systemctl restart mysqld.service
#slave 节点修改备份文件,
#增加
22 -- Position to start replication or point-in-time recovery from
23 --
24
25 CHANGE MASTER TO MASTER_HOST=172.16.128.128,MASTER_USER=repluser,MASTER_PASSWORD=123456,MASTER_PORT=3306, MASTER_LOG_FILE=master-bin.000003, MASTER_LOG_POS=156;
26
27 --
28 -- Current Database: `mysql`

#登陆数据临时关闭二进制log,还原数据,再启动二进制log
[root@slave ~]# mysql
Welcome to the MySQL monitor.Commands end with ; or \\g.
Your MySQL connection id is 13
Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>
mysql> set sql_log_bin=0;
mysql> source /data/all.sql;
mysql> set sql_log_bin=1;

#启动复制
mysql> start slave;
mysql> show slave status\\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.16.128.128
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 689
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 858
Relay_Master_Log_File: master-bin.000003
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: 689
Relay_Log_Space: 1067
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: 128
Master_UUID: 44e3b8c3-9954-11ec-83e8-000c29688702
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)

检查主从进程
##主节点:
mysql> show processlist;
+----+-----------------+----------------------+------+-------------+------+-----------------------------------------------------------------+------------------+
| Id | User| Host| db| Command| Time | State| Info|
+----+-----------------+----------------------+------+-------------+------+--------------------

    推荐阅读


    上一篇：游戏开发新手入门教程5:所有看到的听到的都是资源

    下一篇：5个远程开机的方案