The new chief executive of Southampton City Council has been appointed with the unanimous backing of councillors.