Rodrygo gave the visitors an early lead before Kevin De Bruyne hit back for the holders to leave the match level at 1-1 on the night and 4-4 on aggregate.
However, City's defence of the competition came to an end after Bernardo Silva and Mateo Kovacic saw spot-kicks saved by Madrid's unlikely hero Andriy Lunin.
The Ukrainian, who has deputised this season for the injured Thibaut Courtois, had been at fault for City's early opener in a thrilling first leg but more than made amends.
Madrid will face Bayern Munich in the last four after the German giants beat Arsenal 3-2 on aggregate.
City had not lost at home in the Champions League since 2018 but the holders were held at bay by a heroic defensive effort by the 14-time European champions despite having 34 attempts on goal.
"It's so difficult. They are continuously probing with the ball and move you around. Most teams would fall apart when City get on top of you but we stood up really well," said Madrid midfielder Jude Bellingham.
"Today it came down to mentality."
Madrid were blown away by an early City blitz in a 4-0 defeat at the Etihad 11 months ago as Pep Guardiola's men went on to win the competition for the first time.
City also edged a semi-final first leg thriller 4-3 on home soil two years ago only to be stunned by a late Madrid fightback in the return leg.
"In other sports for the stats we would win but football is like this and it's marvellous," said Guardiola.
"Congratulations to Madrid because their capacity to resist and defend till the end, they did so fantastically. We didn't manage to score in the final pass or final shot.
"I don't have any regrets. Always we try to create more and concede less (than the opponent) and we did everything."
Carlo Ancelotti's men flipped the script on Wednesday with an assured start that was rewarded in the opening goal.
Bellingham plucked the ball out of the sky with an immaculate touch to set Real roaming down the right.
Vinicius Junior picked out the unmarked Rodrygo and he tapped in the rebound after his first effort was parried by Brazilian international teammate Ederson.
If City were slow to warm to their task they quickly had Madrid penned against the ropes but failed to land a telling blow for 76 minutes.
Erling Haaland failed to score for a fourth consecutive clash between the sides but has rarely come up closer than when his looping header came back off the crossbar and left Silva no time to adjust to turn in the rebound.
Lunin was forced to turn behind De Bruyne's shot from outside the box and the tenacious Antonio Rudiger deflected Jack Grealish's effort into the side-netting.
The Madrid goalkeeper saved two more efforts from Grealish at the start of the second half before Guardiola sacrificed the England international for the extra pace of Jeremy Doku.
That proved an inspired change as Rudiger failed to deal with a Doku cross and presented the ball perfectly for De Bruyne to smash into the roof of the net 14 minutes from time.
De Bruyne should have turned the tie around single-handedly as he then blazed a glorious chance over.
However, City's threat was blunted during extra-time as both Haaland and De Bruyne had to be replaced.
It was Real who had the best chance of the extra 30 minutes when Rudiger sliced over with a clear sight of goal after staying forward from a rare Madrid corner.
But the German defender was still to have the decisive say as he slotted in the final penalty of the shootout.
Ederson's save from Luka Modric had given City an early lead.
But Lunin calmly collected Silva's attempted chip down the middle and then denied Kovacic low to his right.
Bellingham, Lucas Vazquez and Nacho put Madrid on the brink before Rudiger landed the fatal blow to City's hopes of a second consecutive treble.
For an optimal experience, please
