How to implement scalability and fault tolerance of MySQL database?

Writing a project in PHP. As a DBMS selected MySQL. As many users, and high availability of service will not prevent want to ask you how to do DB failover and productive at the same time. There are 2 more or less powerful dedicated server. In this case, to arrange the most affordable and productive solution?
Need data relevance. That is almost instant duplication of the data on both servers.
April 3rd 20 at 17:44
3 answers
April 3rd 20 at 17:46
master-slave replication, what is there to think
2 slave

write to master, read from slave

scheme a hundred years in the lunch

separately daily dumps disk
Well, what replication then?
Isn't she slow?
Need data relevance.
For example. The user did the action and when you query the following data using 0.2-0.3 with the data should already be on the second server. - matilde_Blanda commented on April 3rd 20 at 17:49
@matilde_Blanda, so we need to design with the calculation of the replication delay. Ideally, this asynchronous system with idempotent operations - pearlie.Anderson commented on April 3rd 20 at 17:52
- performance - as I said, master-slave. Provided that a lot of reading, works perfectly. What about the speed of replication depends on the load on the record, there is semisynchronous replication

- failover - this is better. There are several solutions for clusters, the correct configuration is not trivial. If you have no experience, suggest simply configure the monitor and use manual failover if necessary. Or AWS RDS. - liza.Ward commented on April 3rd 20 at 17:55
April 3rd 20 at 17:48
Buddy, what are you asking of the Council on the toaster, it was good but not enough.
Then you will receive vague answers to your question.

It is best to know the good and useful books on your topic. To read and then you can ask a specific question if something is not clear.
1. look here https://ruhighload.com/
2. read the following books.
Ensuring high availability of systems based on MySQL Charles bell
MySQL performance Optimization Baron Schwartz
The maximum MySQL Baron Schwartz
Highly-loaded applications Martin Chapman

I think you will find more good answers and specific steps that need to be done.
April 3rd 20 at 17:50
If you are not satisfied with the speed of replication view in the direction of proxysql, and such things, it can be done to record mirrored on all servers and reading to balance in turn.
Yes, easier to just pick up and for every server to send the request)
But, thanks for the Vario, I will consider) - matilde_Blanda commented on April 3rd 20 at 17:53

Find more questions by tags MySQLHigh availability