This is one of those questions where there are so many options that it's hard to pick the one right answer without doing some serious "try it and see" work.
TL;DR
- Your solution is a solid option, that gives you control and will probably scale indefinitely, at the cost of some on-going management effort.
- I'd suggest (if you haven't already) you separate your database and PHP layers to see if simply adding load balanced PHP servers connecting to the single Database engine will solve your problems in the short term.
The first thing I'd do, and you may already have done this, is separate the PHP and the MariaDB, possibly only as a trial. This way, when you start hitting performance issues at around 1000 users, you can tell whether it's the database bit or the php bit that's having the issues.
If you do this, and the database server is unruffled, but the PHP server is sweating bricks, then you can use a simple load balancer, with sticky sessions. Sticky sessions means that all requests from the same browser would go to the same PHP server, meaning you can use sessions, and the caching works all good.
That's a great solution, because you can add more PHP servers to the load balancer until your database server starts to struggle. It also means you can use your load balancer to manage downtime/ upgrades etc.
If it's the database component that's struggling, the options get trickier.
With your suggestion, you're adding a chunk of code, and a chunk of management effort. But it will probably work, and if each server has it's own database, it will probably scale indefinitely.
It's also one of the more "available" solutions in that it doesn't need a great deal of specialist knowledge.
You'll need to have a way to keep track of which server the other player's profiles are on. Player A will have a cookie that says Player A's data is on Server 1, but it probably won't have a cookie that will tell it where Player Z's profile is. To do this you might have a dedicated "keep track of profiles" server, which all it does is answer queries about where to find a profile. This is another chunk of management effort.
Your suggestion is quite attractive because it eliminates the question of database replication lag (and expertise to set it up).
One of the major alternative strategies is duplicating the database load across several servers, either via replication or one of the NoSQL Database engines. This lets you spread the database load across several devices keep the whole data set available in one place. But it comes with considerable configuration complexity, and in some situations a degree of lag - it might take a second or two (or more) for updates on Server 1 to be copied to Server 2.
If you go to a NoSQL database, these are generally designed to be very highly scalable, at little effort. But it would probably mean rewriting your app, which sounds like regretful effort. And you also have the "eventually consistent" issue. Basically, the scaling out to multiple nodes is easy to do, but the data updates are not available between the servers immediately, and that could be an issue.
The replication option would work with MariaDB. This would allow you to have a copy of all of the data on each server, but add more nodes as load requires. Configuring replication like this is tricky.
Another option which you might want to consider is to stick all or part of it on the cloud. If your database can be separated from your app server, you could go with AWS's MySQL compatible RDS service. This will cost, but it also allows you to scale your database performance in ways that is hard to do with your own physical hardware. You'd want to look hard at the cost implications, but the AWS stuff is real clever, and real easy to work with (and the backups and failovers just work!!).
