I am looking for a smooth closed 4-manifold $M$ with a distinguished point $x\in M$, endowed with an $\mathbb{S}^1$ action such that the stabilizer of $p\in M\setminus\{x\}$ is trivial and $x$ is fixed.
A naive attempt:
If we consider the action given by $\mathbb{S}^1$ on $\mathbb{S}^3\subset \mathbb{C}^2$ that gives the Hopf fibration we can extend this action to the 4-ball (it is an unitary action), and it will have the origin as single fixed point. Unfortunately this manifold is not closed.
Such a closed $4$-manifold does not exist, and this follows from:
Church, P., & Lamotke, K. (1974). Almost free actions on manifolds. Bulletin of the Australian Mathematical Society, 10(2), 177-196
Let me present the argument anyway. The answer breaks down into a local and global part. The local question is to understand what happens near the single fixed point of the $S^1$-action. The global question is about whether we can find, for each local model near the fixed point, a $4$-manifold which closes off the boundary of the model.
Claim 1: The only local model near the fixed point is the "naive attempt" from your question statement, i.e. the standard $S^1$-action on $\mathbb{C}^2$.
Proof of Claim 1: Up to coordinate change, we may assume the $S^1$-action acts by orthogonal transformations on $\mathbb{R}^4$. In particular, it preserves and is free on $S^3$, and the only free $S^1$-action on $S^3$ is the Hopf (or anti-Hopf if we invert one coordinate) fibration. So your naive attempt really is the only local model.
Claim 2: There is no closed $4$-manifold with the property you ask for.
Proof of Claim 2: Suppose there were such a closed $4$-manifold. Removing a ball around $p$ corresponding to the local model, $\widetilde{M} := M \setminus B_p$. Then we obtain a fibration
$$S^1 \rightarrow \widetilde{M} \rightarrow X,$$
such that on the boundary the fibration is just the Hopf fibration $\partial\widetilde{M} = S^3$ over $\partial{X} = S^2$. The fibration over $X$ comes with its classifying map $X \rightarrow BS^1$, and the composition
$$S^2 = \partial X \hookrightarrow X \rightarrow BS^1$$
classifies the Hopf fibration. But the image of $S^2$ under the classifying map for the Hopf fibration $S^2 \rightarrow BS^1$ (which in more down-to-earth language is just $\mathbb{C}\mathbb{P}^1 \hookrightarrow \mathbb{C}{P}^{\infty}$) represents a nontrivial homology class, while $X \rightarrow BS^1$ is a nullhomology of this class. So we arrive at a contradiction.
If you prefer characteristic classes, it suffices to consider the first Chern class in this argument. In addition, we see that if we have a $4$-manifold with an "almost free" $S^1$-action, then the number of fixed points is even, since each fixed point either adds or subtracts $1$ from the first Chern class, depending upon orientations. Conversely, this argument can be boosted to prove you can find a closed $4$-manifold with any even number of fixed points.
EDIT (responding to comment)
You can ask this question for other group actions, and again, there’s the local and global parts to consider. Let me do this for the case of $\mathbb{Z}_p$ (the cyclic group, not the $p$-adics for anybody who might be confused) which was asked in a comment. We claim that in this case, again, there is no $4$-manifold $M$ with a $\mathbb{Z}_p$ action which is free except for a single fixed point. (Hopefully I haven't made a mistake, which is entirely possible, so feel free to be skeptical. I'm sure an algebraic topologist on this site has a better argument for the last part.)
Again, we start with the local models, which arise from the representation theory of $\mathbb{Z}_p$. For any finite cyclic group, the irreducible real representations are either 1-dimensional (act by the +1 or -1, the latter if p is even) or 2-dimensional (a rotation of order $p$). Since you want every point to be free except the fixed point, you can't use the 1-dimensional representations, since they have order 1 and 2 respectively, so you have a direct sum of two rotations by some 2$\pi k_1/p$ and $2\pi k_2/p$ where $k_1$ and $k_2$ are coprime to $p$. Up to $\mathrm{Aut}(\mathbb{Z}_p)$, we may assume $k_1 = 1$, and we will simply write $k_2 = k$. At the boundary $S^3$ of this local model, we obtain the fibration
$$\mathbb{Z}_p \rightarrow S^3 \rightarrow L(p;k)$$
where $L(p;k)$ is a Lens space. Now for the global part. Assume there exists such a $4$-manifold $M$. Then we have the classifying map for the bundle $S^3 = \partial \widetilde{M} \rightarrow \partial X = L(p;k)$ factors through the classifying map for $X$ (which is now itself a $4$-manifold):
$$\phi \colon L(p;k) = \partial X \hookrightarrow X \rightarrow B\mathbb{Z}_p = K(\mathbb{Z}_p,1).$$
The cohomology ring $H^*(K(\mathbb{Z}_p,1);\mathbb{Z}_p)$ can be completely understood in terms of Steenrod squares and the Bockstein homomorphism, and in particular, we find that for $u \in H^1(K(\mathbb{Z}_p,1);\mathbb{Z}_p)$ the fundamental class, the class
$$v:= u \smile \beta(u) \in H^3(K(\mathbb{Z}_p;1)\mathbb{Z}_p)$$
is a nontrivial element (in fact a generator). Then one can check (e.g. from the cohomology ring structure of $L(p;k)$) that $\phi^*v \neq 0$ as well. Dually, the pushforward of the mod $p$ fundamental class represents a nontrivial element
$$\phi_*[L(p;k)] \neq 0 \in H_3(K(\mathbb{Z}_p,1);\mathbb{Z}_p),$$
and so $\phi$ cannot factor through a $4$-manifold $X$. So no such $4$-manifold $M$ exists.
