Live2D

主从复制&&延时复制 

  1 # 主从搭建
  2 mysql> create user replcation@'10.0.0.%' identified with mysql_native_password by '123';
  3 mysql> grant replication slave on *.* to 'replcation'@'10.0.0.%';
  4 mysql> select user,host from mysql.user;
  5 
  6  
  7 
  8 [root@MySQL ~]# mysqldump -A --master-data=2 > /tmp/all.sql
  9 mysql> source /tmp/all.sql;
 10 
 11  
 12 
 13 mysql> CHANGE MASTER TO
 14 MASTER_HOST='10.0.0.20',
 15 MASTER_USER='replcation',
 16 MASTER_PASSWORD='123',
 17 MASTER_PORT=3306,
 18 MASTER_LOG_FILE=' binlog.000014',
 19 MASTER_LOG_POS=156,
 20 MASTER_CONNECT_RETRY=10;
 21 
 22 
 23 mysql> start slave;
 24 
 25  
 26 
 27 
 28 # file info
 29 mysql> SELECT * FROM mysql.slave_master_info\G
 30 mysql> select * from mysql.slave_relay_log_info\G
 31 
 32  
 33 
 34  
 35 
 36  
 37 
 38  
 39 
 40  
 41 
 42 #主从搭建之延时
 43 mysql> stop slave;
 44 mysql> change master to master_delay=300;
 45 mysql> start slave;
 46 mysql> show slave status\G
 47 Slave_IO_Running: Yes
 48 Slave_SQL_Running: Yes
 49 SQL_Delay: 300
 50 
 51 
 52 #模拟恶意删库
 53 mysql> create table t1(id int);
 54 Query OK, 0 rows affected (0.02 sec)
 55 
 56 mysql>
 57 mysql> insert into t1 values(1);
 58 Query OK, 1 row affected (0.01 sec)
 59 
 60 mysql> insert into t1 values(1);
 61 Query OK, 1 row affected (0.01 sec)
 62 
 63 mysql> insert into t1 values(1);
 64 Query OK, 1 row affected (0.01 sec)
 65 
 66 mysql> insert into t1 values(1);
 67 Query OK, 1 row affected (0.01 sec)
 68 
 69 mysql> insert into t1 values(1);
 70 Query OK, 1 row affected (0.00 sec)
 71 
 72 mysql> insert into t1 values(1);
 73 Query OK, 1 row affected (0.01 sec)
 74 
 75 mysql> commit;
 76 Query OK, 0 rows affected (0.00 sec)
 77 
 78 mysql> drop database a1;
 79 Query OK, 1 row affected (0.01 sec)
 80 
 81 
 82 # 从库停止sql线程 是其失去回放功能防止回访恶意sql
 83 mysql> stop slave sql_thread;
 84 mysql> show slave status\G
 85 Slave_IO_Running: Yes
 86 Slave_SQL_Running: No
 87 
 88 # 读取relaylog log日志确认回放 position
 89 mysql> show RelayLog events in 'MySQL-relay-bin.000002';
 90 
 91 
 92 # 回放数据
 93 change master to master_delay=0;
 94 start slave until relay_log_file="MySQL-relay-bin.000002", relay_log_pos=2298;
 95 
 96 # 验证 重构主从关系
 97 
 98  
 99 
100  
101 
102  
103 
104  
105 
106 # 问题一：
107 Last_IO_Errno: 2061
108 Last_IO_Error: error connecting to master 'replication@10.0.0.20:3306' - retry-time: 10 retries: 6 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
109 解决方法：统一加密插件
110 
111  
112 
113 # 问题二：
114 Last_IO_Errno: 13114
115 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binarylog index file'
116 
117 解决方法：
118 主
119 flush logs
120 
121 从
122 change master to master_log_file='binlog.000015', master_log_pos=156;
123 start salve
124 
125  
126 
127 # 问题三：连接数上限
128 Last_IO_Errno: 1040
129 Last_IO_Error: error connecting to master 'replcation@10.0.0.20:3306' - retry-time: 10 retries: 1 message: Too many connections
130 
131 
132 # 问题四：change master 信息有误
133 Last_IO_Errno: 1045
134 Last_IO_Error: error connecting to master 'replcation@10.0.0.20:3306' - retry-time: 10 retries: 1 message: Access denied for user 'replcation'@'MySQL' (using password: YES)
135 
136

 

posted @ 2025-03-20 17:15  乔尼娜蓖炀  阅读(9)  评论(0)    收藏  举报
Live2D