The song is about escaping to the city of Acapulco, Mexico. There's a woman there named Rose Marie who speaks honestly to Dylan and gives "it" to him for a song. We never find out what "it" is, but it's safe to assume that it's sex. She does also serve food, though ("I just make it down to Rose Marie's to get something quick to eat"), so it's not really clear.
Dylan also mentions someone named "soft gut" ("going down to see soft gut"), who apparently is a person he can have some fun with.
The whole song is about weariness with the world and a desire to simply get away and have some fun. The lyrics at some points seem somewhat trivial, sort of playfully morose, but Dylan sings them with real pain in The Basement Tapes.
A couple verses in particular indicate the possible source of Dylan's world weariness.
It's a wicked life but what the hell
But what that hell, oh everybody's got to eat
And I'm just the same as anyone else
When it comes to scratchin' for my meals
It's not a bad way to make a living
And I ain't complainin' none.
But I can blow my plump, and break my rump
And then go home and have my fun
When Dylan made The Basement Tapes, he was recovering from a motorcycle wreak. Beyond that, he had also just grown tired of his own fame and career. Many (including Dylan himself) have even suggested that he exaggerated the damage done in that motorcycle accident as cover to get out of his old life. In his autobiography Chronicles, he states flat-out that he'd gotten sick of the rat race and wanted out of it.
So, it would seem that "Goin' to Acapulco" captures that yearning for escape.
In 1963, Elvis Presley starred in a film titled Fun In Acapulco. The chorus of "Goin' to Acapulco" ends with, "Yeah - goin' to have some fun." It seems highly likely there's a connection here, as Dylan was a big Elvis fan.
Jim James and Calexico covered this song on the soundtrack to the 2007 Todd Haynes film I'm Not There. In the film, they perform the song during an emotionally poignant scene when Billy the Kid (a version of Dylan played by Richard Gere) wrestles with his decision to abandon the town of Riddle to scrupulous money interests. It represents an emotional peak in the film.
As with all of The Basement Tapes, The Band plays with Dylan on this song.