New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Single mysql db accessed by multiple servers
For a new project initially I will have 1db server and 1 application server. However I will need to add more application servers which all need access to the db and doing reads and writes. Is there a correct way of doing this to avoid corruption etc ie queuing of some sort.
Or is there a better way to approach this?
Comments
You won't get corruption from multiple servers accessing one DB server. MySQL will lock resources to prevent it. You might experience performance issues depending on volume of queries and the fact the locks will make queries wait but it's no different to the same number of queries coming from one application server.
If you're doing this for redundancy and your application relies on the DB then your setup still won't be truly redundant as the MySQL server will be the single point of failure. If you want to move to a truly redundant setup then you'll need to use MySQL replication which is where it gets more complex and you risk corruption if it's not set up correctly.
Primary key with sequantial id is the you need to worry, if 2 apps write same id how your db will handle it
Thanks for the info looks like I should be ok. Yeah I had thought about the db being the single point of failure and was hoping to find a host that has some sort of mysql high availability feature where they handle replication etc.
See,
Simply if you are just reading the data just cache it and use it. On some intervals update cached data.
You won't corrupt any data from accessing from multiple servers. I suggest use pure SSD servers for better performance.
Alternatively you can use MongoDB.
just open the port 3366
Just enable remote mysql and you're good to go. and if security is concern you should consider encryption as it's not encrypted by default.
In general you might not have issues but depends on the web app and how your using, accessing and saving the data
Example for mysql is isolation levels https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html