恢复slave从机上的部分表

千家信息网最后更新 2025年01月23日恢复slave从机上的部分表以下案例演示重新初始化slave上的个别表，如test01和test02.
1、停止slave复制

点击(此处)折叠或打开

1. mysql> stop slave;
   
2. Query OK, 0 rows affected (0.02 sec)

2、在master导出这张表，并记录下同步的binlog和POS点
mysqldump -uroot -pmysql -q --single --master-data=2 test test01 test02 > test_01_test_02.sql
3、查看test_01_test_02.sql文件，找到记录的binlog和POS点
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=3407;
4、把test_01_test_02.sql复制到slave上，并做change master指向

点击(此处)折叠或打开

1. mysql> start slave until master_log_file='mysql-bin.000003', MASTER_LOG_POS=3407;
   
2. Query OK, 0 rows affected, 1 warning (0.01 sec)

直到sql_thread线程为NO，这期间的同步报错一律跳过即可，可用如下命令跳过：
stop slave;
set global sql_slave_skip_counter=1;
start slave;
5、在slave上导入

点击(此处)折叠或打开

1. mysql> source /mysql/dump/test_01_test_02.sql
   
2. Query OK, 0 rows affected (0.00 sec)
   
3. 
   
4. Query OK, 0 rows affected (0.00 sec)
   
5. 
   
6. Query OK, 0 rows affected (0.00 sec)
   
7. 
   
8. Query OK, 0 rows affected (0.00 sec)
   
9. 
   
10. Query OK, 0 rows affected (0.00 sec)
    
11. 
    
12. Query OK, 0 rows affected (0.00 sec)
    
13. 
    
14. Query OK, 0 rows affected (0.00 sec)
    
15. 
    
16. Query OK, 0 rows affected (0.00 sec)
    
17. 
    
18. Query OK, 0 rows affected (0.00 sec)
    
19. 
    
20. Query OK, 0 rows affected (0.00 sec)
    
21. 
    
22. Query OK, 0 rows affected (0.04 sec)
    
23. 
    
24. Query OK, 0 rows affected (0.00 sec)
    
25. 
    
26. Query OK, 0 rows affected (0.00 sec)
    
27. 
    
28. Query OK, 0 rows affected (0.04 sec)
    
29. 
    
30. Query OK, 0 rows affected (0.00 sec)
    
31. 
    
32. Query OK, 0 rows affected (0.00 sec)
    
33. 
    
34. Query OK, 0 rows affected (0.00 sec)
    
35. 
    
36. Query OK, 6 rows affected (0.01 sec)
    
37. Records: 6 Duplicates: 0 Warnings: 0
    
38. 
    
39. Query OK, 0 rows affected (0.01 sec)
    
40. 
    
41. Query OK, 0 rows affected (0.00 sec)
    
42. 
    
43. Query OK, 0 rows affected (0.06 sec)
    
44. 
    
45. Query OK, 0 rows affected (0.00 sec)
    
46. 
    
47. Query OK, 0 rows affected (0.00 sec)
    
48. 
    
49. Query OK, 0 rows affected (0.07 sec)
    
50. 
    
51. Query OK, 0 rows affected (0.00 sec)
    
52. 
    
53. Query OK, 0 rows affected (0.00 sec)
    
54. 
    
55. Query OK, 0 rows affected (0.00 sec)
    
56. 
    
57. Query OK, 6 rows affected (0.01 sec)
    
58. Records: 6 Duplicates: 0 Warnings: 0
    
59. 
    
60. Query OK, 0 rows affected (0.00 sec)
    
61. 
    
62. Query OK, 0 rows affected (0.00 sec)
    
63. 
    
64. Query OK, 0 rows affected (0.00 sec)
    
65. 
    
66. Query OK, 0 rows affected (0.00 sec)
    
67. 
    
68. Query OK, 0 rows affected (0.00 sec)
    
69. 
    
70. Query OK, 0 rows affected (0.00 sec)
    
71. 
    
72. Query OK, 0 rows affected (0.00 sec)
    
73. 
    
74. Query OK, 0 rows affected (0.00 sec)
    
75. 
    
76. Query OK, 0 rows affected (0.00 sec)
    
77. 
    
78. Query OK, 0 rows affected (0.00 sec)

6、导入完毕之后，开启同步即可。

点击(此处)折叠或打开

1. mysql> start slave;
   
2. Query OK, 0 rows affected (0.01 sec)