In large cluster environment, it is always challenge of manage hundreds MySQL databases.<\/p>\n
Ideally, these all handled by proper development process and you have enough software engineers to support.
\nBut in real world, it is more urgent of solving problems.<\/p>\n
Here is the 9 best practices to operate thousands of app servers and databases and we don\u2019t have any “user impact\u201d because of database maintenance.<\/p>\n
We design our database schema like a one-node-cluster, we can run all the service in one box or 1000 boxes.
\nIt is transparent to software engineers.<\/p>\n
Use only basic MySQL features: table, primary key, index, replication.<\/p>\n
App servers are scalable, but MySQL is a bottle neck, Use as much as application CPU, which means:
\nDo not query without index
\nDo not sort using database
\nDo not use any query causes temporary tables<\/p>\n
We use DBPool for a long time<\/p>\n
When you want to move a few tables to a different master.
\na. Setup new master (B) as the slave of the old master (A)
\nb. Change the DBPool configuration, point master to B.
\nc. In the middle of the time, (B) will also receive some update requests from old client.
\nd. Make sure it is no any queries to tables on B
\ne. Stop the replication and optionally drop old tables on A.<\/p>\n
When you want to distribute data of one table into more physic servers.
\na. Estimate how many is needed, find a proper shading key. You should visit only one instance after shading.
\nb. A good shading number may be 10 or 100. It is human friendly when debug.
\nc. You don\u2019t need to have 100 physic servers to deploy all tables, DBPool have the ability of route.
\nd. Use the same method to move tables to new master as showed in (5)<\/p>\n
a. We do add column only, no drop column.
\nb. Application level compatible is required. Make sure new code is working with both old and new data. (if impossible, see (8))
\nc. Make the changes
\nd. Update the application to use new column for new feature.<\/p>\n
This situation always involves a big change to the logic, you need to redesign the structure
\na. Create a new master (B) of tables using (5)
\nb. Create new data structures on (B).
\nc. Create a trigger to update new structure when old data changed on (B).
\nd. Migrate your old data into the new structure, pay attention to (c) have already moved some recent data.
\ne. Create a new abstract instance in DBPool, for new structures.
\nf. Update the application use the new structure for reading.
\ng. In the same time, old client and new client have the same data for we have (c).
\nh. Update the application use the new structure for writing.
\ni. Stop the replication (a), trigger (c) and drop the old tables<\/p>\n
a. DBPool is enough to move MySQL slave servers.
\nb. Use (5) to make a new master or promote one slave to master.<\/p>\n","protected":false},"excerpt":{"rendered":"
\u7ed9\u516c\u53f8\u5199\u7684\u6587\u6863\u8349\u7a3f\uff0c\u7a0d\u540e\u4f1a\u5199\u6210\u4e2d\u6587\u7684\u3002 Summary In large cluster environment, it is always challenge of manage hundreds MySQL databases. Planed down time of database server maintenance Scalability Change of data structures Ideally, these all handled by proper development process and you have enough software engineers to support. But in real world, it is more urgent of solving problems. Here […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"","footnotes":""},"categories":[5],"tags":[],"class_list":["post-187","post","type-post","status-publish","format-standard","hentry","category-tech"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/posts\/187","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/comments?post=187"}],"version-history":[{"count":1,"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/posts\/187\/revisions"}],"predecessor-version":[{"id":188,"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/posts\/187\/revisions\/188"}],"wp:attachment":[{"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/media?parent=187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/categories?post=187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sinofool.net\/blog\/wp-json\/wp\/v2\/tags?post=187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}