Hi, StarBright. I was one of the readers of your old journal, and I'm somewhat of a trailing spouse myself, in that I have an ambitious husband whose career ties us to NYC, and, until now, he's always been somewhat underpaid for his skill set and the amount that he works. (A few differences in my case: I'm not the primary breadwinner; I make roughly 50% of the money in our household, and I get to pursue a passion project of my own.)
If I were in your shoes, I wouldn't move to Texas. From what you've shared here, it sounds like DH would get to work at a somewhat more prestigious institution and get a bit more money, but you'd still be the main breadwinner. From what I remember, your DH isn't in an academic field where he needs a whole lot of specialized equipment or on-the-ground technical support for his own research, so the differences in resources between the two institutions don't matter as much. And he can lean into his work as much as he wants to at his current institution, as you say. In our interconnected world, he can still pow-wow or collaborate with anyone in the world, and for everything else, there's inter-library loan and research trips.
I would keep your options open and gather as much info as possible, as lhamo suggests, but unless you just fall in love with Waco, if it were me, I'd say no. If possible, it would also be good for your DH to get something in writing from his current institution before he replies to the other offer.
Also, I wouldn't necessarily discourage DH's ambition if his work is fulfilling to him, as long as it doesn't make you unhappy in terms of taking him away from family life. If he does write a NYT bestseller, you'll never have to work again, and you'd have all kinds of options. And if he becomes a super-star in his field (not just the star that he currently is), he will get an offer from an institution where you no longer have to be the breadwinner, and that could very well be worth moving for. Right now, the benefits for him don't seem to be enough to uproot your family, imho.