As the second leg of the semi-final tie ticked into the 90th minute, City were 1-0 up on the night and in control after Riyad Mahrez had rifled an effort past Thibaut Courtois earlier in the second-half.
But Rodrygo struck an incredible brace in barely more than a minute to send the tie to extra-time, where Karim Benzema rolled home a penalty to put the Spanish side 3-1 up.
City could find no response as their Champions League dream died for another year, as Real ensured they will meet Liverpool in Paris at the end of the year in a repeat of the 2018 final.
City were boosted by the return of Kyle Walker to bring some pace to the right-side of defence, while Real had Casemiro back in midfield.
The Brazilian’s first involvement was to hack down Kevin De Bruyne with an ugly challenge, sparking a clash between both sets of players. Luka Modric and Aymeric Laporte each earned early yellow cards for their role in it.
Benzema had a couple of chances from inside the box, sending the ball over the bar on both occasions, before Vinicius Jr snatched at a presentable chance.
Bernardo Silva had the biggest chance of the first-half, as he was denied by Courtois from close range, and Gabriel Jesus then whipped an effort wide from the edge of the area.
The Real goalkeeper was called into action again to keep out Phil Foden’s volley, shortly after the Englishman had been hauled down by another cynicial foul from Casemiro who continued his miraculous ability to avoid a yellow card.
Second after the break, Real should have taken the lead on the night. Dani Carvajal fired a low cross into the box, and Vinicius sent his effort wide when completely free at the back post. The offside flag went up, but replays suggested it would have been very close.
With the hosts pushing for the goal they needed, the match became increasingly open. Vinicius cut the ball back to Luka Modric, but his touch let him down and the shot was eventually blocked.
Real still failed to register a shot on target though and with 73 minutes on the clock, their task became an almost impossible one. Silva had a huge amount of space to drive into, he then played it in to Mahrez who smashed it first-time past Courtois at the near post to make amends for his first-leg misses.
Jack Grealish came off the bench and very nearly capped the night in style, first having an effort cleared off the line and then seconds later seeing a shot turned inches wide by the Belgian goalkeeper.
Those misses proved to be more costly than anyone could possibly have predicted.
In the 90th minute, the ball was sent into the City box, Benzema at the back post played it across the face of goal and Rodrygo got across his man to flick it into the back of the net.
The home fans celebrated just as wildly when the board then went up to signal six minutes of added time, and seconds later they were sent delirious as Rodrygo levelled the tie up. Carvajal whipped the cross in, and the Brazilian got up brilliantly to power the header past a helpless Ederson.
Rodrygo nearly had the most incredible hat-trick in the space of three minutes, as he found himself through on goal straight from kick-off but this time the City goalkeeper was there to deny him.
The match went into extra-time, and five minutes later Real had the lead on aggregate for the first time in the tie. Benzema was brought down in the box by Ruben Dias and while there was no panenka this time, it was no less cool from the Frenchman as he rolled the ball into the bottom corner.
Real Madrid used every trick in the book to run down the clock in the closing stages and it did the job, as the most successful club in Champions League history sealed a comeback for the ages against a City side who have once again fallen short in the competition they crave more than any other.