The results are a significant blow for Recep Tayyip Erdogan, who had hoped to regain control of the cities less than a year after he claimed a third term as president.
He led the campaign to win in Istanbul, where he grew up and became mayor.
But Ekrem Imamoglu, who first won the city in 2019, scored a second victory for the secular opposition CHP.
Mr Erdogan had vowed a new era in Turkey's megacity of almost 16 million people, but the incumbent mayor of Istanbul was on course to win more than 50% of the vote, over 10 points ahead of the president's AK Party candidate.
This was also the first time since Mr Erdogan came to power 21 years ago that his party was defeated across the country at the ballot box.
In the capital Ankara, opposition mayor Mansur Yavas was so far ahead of his rival on 59% that he declared victory when less than half the votes were in. Supporters blocked all the main roads in the city, waving flags and sounding their car horns.
Significantly, the CHP was also on course to win in many of Turkey's other big cities, including Izmir and Bursa, Adana and the resort of Antalya.
President Erdogan, 70, acknowledged the election had not gone as he had hoped, but he told supporters in Ankara it would mark "not an end for us but rather a turning point".
President Erdogan promised supporters his party would learn its lessons from the defeat
He has always relied on the "people's will" for his authority and he told supporters he would respect the electorate now too.
During the election campaign, Mr Erdogan said this would be his last, because his presidential term ends in 2028.
But critics believed that victory would have encouraged him to revise the constitution so he could stand again. After such a dramatic defeat that is looking very unlikely.
Ekrem Imamoglu won Istanbul for the opposition in 2019
Crowds in Istanbul gathered outside the town hall in Sarachane, one of Istanbul's oldest districts.
They waved Turkish flags and banners showing Ekrem Imamoglu's picture alongside Turkey's founding father Kemal Ataturk, whose poster was draped down the walls of the local authority building.
"I can say that our citizens' trust and faith in us has been rewarded," said Mr Imamoglu.
Both he and Mansur Yavas are seen as potential candidates to run for the presidency in 2028.
"Everything will be fine," Imamoglu supporters chanted as they danced to drums and clarinets in Sarachane, one of Istanbul's oldest districts.
Istanbul's incumbent mayor had first used the slogan when he won the city from Mr Erdogan's party five years ago. Some of the banners in Sarachane used his current slogan, "Full speed ahead".
"They're only local elections but the opposition's victory in big cities is a significant show of force against the ruling party," Imamoglu supporter Yesim Albayrak, 25, told the BBC.
I am now hoping the country will become a more secular country, respecting human rights, women's rights and childrens rights
Yesim Albayrak
Nurse
Mehmet Bankaci, 27, told the BBC there was a need for change in Turkey: "If Imamoglu or Mansur Yavas had been the CHP candidate in last year's presidential election, they definitely would have won."
Mehmet Bankaci, 27, told the BBC there was a need for change in Turkey: "If Imamoglu or Mansur Yavas had been the CHP candidate in last year's presidential election, they definitely would have won."
Mr Erdogan's AK Party continues to dominate central Turkey and had more success in areas of the south-east devastated by the February 2023 double earthquake, including the cities of Kahramanmaras and Gaziantep.
Speaking from the balcony of his party headquarters in Ankara, he promised to use the four years before the next presidential election to "renew ourselves and compensate for our mistakes".
His supporters chanted back: "Stand still, this nation is with you."
About 61 million Turks were eligible to take part in Sunday's election and more than a million young voters were casting their ballots for the first time. Turnout was estimated at more than 77% across the country's 81 provinces.
BBC