A mayor has insisted he will not resign after being caught on camera snorting a white substance inside a toilet cubicle.