I think your question(s) have four answers in total:
1) Will dropping non-significant predictors increase the root-mean-square error? Yes, virtually always, in the same way and for the same reason that it will always increase the R-squared: a model will only ever use a predictor to improve its predictions (or, rather, its retrodictions, which I'll return to shortly). If the predictor's regression coefficient with the dependent variable is exactly zero, to infinite decimal places, then including it had no effect on the errors, and dropping it won't either, but that's about as realistic a scenario as flipping a coin and having it land on its edge. So generally speaking, the error will always increase when you drop a predictor.
2) Can it increase to some substantively meaningful degree even if the predictor you drop is insignificant? Yes, though the drop will always be less than if you dropped a significant predictor. By way of illustration/proof, here's some R code that will (somewhat) quickly produce variables where one predictor is significant while the other is not, using the same dependent variable, and yet the RMSE for the insignificant variable is only worse than the insignificant one by an arguably trivial degree (less than half a percent increase).
# Package that has the rmse function
require(hydroGOF)
# Predefine some placeholders
pvalx1 <- 0
rmsex1 <- 0
pvalx2 <- 0
rmsex2 <- 1
# Redraw these three variables (x1, x2, and y) until x1 is significant as a predictor of y
#and x2 is not, but x2's RMSE is less than 0.5% higher
while(pvalx1 > 0.05 | pvalx2 < 0.05 | rmsex2/rmsex1 > 1.005) {
y <<- runif(100, 0, 100)
x1 <<- y + rnorm(100, sd=300)
x2 <<- y + rnorm(100, sd=500)
pvalx1 <- summary(lm(y ~ x1))$coefficients[2,4] # P-value for x1
pvalx2 <- summary(lm(y ~ x2))$coefficients[2,4] # P-value for x2
rmsex1 <<- rmse(predict(lm(y ~ x1)), y)
rmsex2 <<- rmse(predict(lm(y ~ x2)), y)
}
# Output the results
summary(lm(y ~ x1))
summary(lm(y ~ x2))
print(rmsex1, digits=10); print(rmsex2, digits=10)
You can change the 1.005 to a 1.001 and eventually produce an example where the RMSE is less than a tenth of a percent higher for the non-significant predictor. Of course, this is mostly due to the fact that "significance" is defined using some arbitrary P-value cutpoint, so the difference in RMSE is tiny usually because the two variables are almost identical and just barely on different sides of the 0.05 significance threshold.
This leads me to an important point about the relationship between multicollinearity and the effect that dropping predictors has on overall prediction error/model quality: the relationship is inverse, not direct as you implied. That is to say, when there is high multicollinearity, dropping any variable will lave less of an effect on prediction error, because the other predictor(s), which were highly correlated with the dropped one, will pick up the slack, as it were, and happily take credit for the extra predictive power they now have, whether they are causal factors of the DV or just functioning as measurements for the actual causal factors which are not being measured and/or included. The error will still increase, but if the dropped predictor was strongly correlated with one or more of the remaining predictors, then much, or even most, of the increase in error that would otherwise occur will be prevented due to the increase in predictive power that one or more of the remaining predictors will now exhibit. This all is made clearest, I think, by an introduction to multivariate that includes ballantine graphs (basically Venn diagrams), such as the one in McClendon's fantastic book: https://books.google.com/books/about/Multiple_Regression_and_Causal_Analysis.html?id=kSgFAAAACAAJ
3) Does any of this matter if we only care about prediction and not causal inference? Yes, if only because it is always perfectly possible - especially if you have a lot of time on your hands - to build a model that retrodicts amazingly and yet predicts no better than chance. Consider one of the popular spurious correlations we all like to talk about: 
Sure, you can hand-wave to some degree when it comes to causal inference, and say that you don't care why you can predict heat-related murders using just Miss America's age, so long as you can - but the thing is, you can't, can you? You can only retrodict it, i.e. accurately guess what the rate of heat-related murders was in a given past year based on Miss America's age that year. Unless there is some unfathomable causal chain that produced this correlation and that will continue to drive it in the future, then this robust observed correlation is useless to you, "even" if you "only" care about prediction. So even if your RMSE (or other goodness-of-fit measure) is excellent and/or made better by some predictor, you need, at a minimum, the general causal inference theory that there is some persistent process driving the observed correlation into the future as well as throughout the observed past.
4) Can dropping a non-significant predictor lead to false causal inferences and/or false inferences about what is driving a successful forecasting model? Yes, absolutely - in fact, the significance level of a predictor's coefficient in a multivariate model tells you nothing at all about what dropping that predictor will do to the coefficients and significance levels of other predictors. Whether or not a given predictor is significant, dropping it from a multivariate regression may, or may not, make any other predictors significant that weren't before, or insignificant when they were significant before. Here's an R example of a randomly-generated situation where one variable (x1) is a significant predictor of the DV (y) but this can only be seen when we include x2 in our model, even though x2 is not significant as an independent predictor of y.
# Predefine placeholders
brpvalx1 <- 0 # This will be the p-value for x1 in a bivariate regression of y
mrpvalx1 <- 0 # This will be the p-value for x1 in a multivariate regression
# of y alongside x2
mrpvalx2 <- 0 # This will be the x2's p-value in the multivariate model
# Redraw all the variables until x1 does correlate with y, and this can
# only be seen when we control for x2,
# even though x2 is not significant in the multivariate model
while(brpvalx1 < 0.05 | mrpvalx1 > 0.05 | mrpvalx2 < 0.05) {
x1 <- runif(1000, 0, 100)
y <- x1 + rnorm(1000, sd=500)
x2 <- x1 + rnorm(1000, sd=500)
brpvalx1 <- summary(lm(y ~ x1))$coefficients[2,4]
mrpvalx1 <- summary(lm(y ~ x1 + x2))$coefficients[2,4]
mrpvalx2 <- summary(lm(y ~ x1 + x2))$coefficients[3,4]
}
# Output the results
summary(lm(y ~ x1 + x2))
summary(lm(y ~ x1))
The significance level on any coefficient, including the predictor you're considering dropping, in a multivariate model tells you about that variable's correlation not with the DV but with what's left of the DV - or, rather, of its variance - after all the other predictors are given their shot at explaining the DV and its variance. A variable x2 can easily have no independent correlation with the DV in this sense, when other, better predictors are present, and yet have a very strong bivariate correlation with the DV and with the other predictors, in which case x2's inclusion in the model can drastically change the correlation that the other predictors appear to have with what's left of the DV and its variance after x2 has explained what it can as if in a bivariate regression. In terms of a ballantine graph, x2 can have large overlap with y but most or all of this overlap can be within the overlap of x1 and y, while much of the other overlap between x1 and y remains outside x2's overlap. That verbal description may not be clear, but I can't find online the kind of really appropriate graph that McClendon has.
I think the tricky thing here is that it is the case that, in order for the inclusion of some additional predictor to change the results for the other predictors' coefficients and significance levels, it is necessary that the new predictor be correlated with both the dependent variable and the predictor it's affecting. But those are both bivariate relationships with everything else left to vary, which a single multivariate model won't tell you anything about unless you include interaction terms. Again, though, all that refers to the causal-inference dynamic of appraising individual coefficients and testing their non-zero-ness - if you just care about the overall goodness of fit, then the story is relatively simple in that the exclusion of a given variable will lower the goodness of fit, but the decrease will be large if and only if the variable was not strongly correlated with any of the other predictors, and was correlated both consistently (low p-value) and substantially (large coefficient) with the dependent variable. This does not mean, though, that dropping a significant predictor will always have a much larger increase in error than dropping an insignificant one - a barely significant variable, especially one with a small coefficient, might not matter much either.
