MySQL多IDC部署注意事项
目前暂时还没有哪款数据库产品是专门针对跨IDC进行的优化,在理论上被业界认为最优的方案是采用基于Paxos协议(暂时只有google做
目前暂时还没有哪款数据库产品是专门针对跨IDC进行的优化,在理论上被业界认为最优的方案是采用基于Paxos协议(暂时只有google做出了实现,google f1),今天只讨论MySQL在这方面的注意事项:
对于跨IDC的情况基本都会设计到以下问题:
1、MySQL多IDC的数据同步,数据一致性
2、多IDC之间的高可用
3、多IDC的多点写入问题
4、运维监控
对于MySQL多IDC数据之间的同步问题:
1、MySQL的复制是异步的(对于5.5的半同步来说,还是属于“异步的”),MySQL同步依赖的因素很多,同步的网络环境,硬件配置,SQL语句是否高效,以及数据量的大小。
在数据量较大的情况下在master和slave端启用slave-compressed-protocol = 1压缩模式,网络环境采用专线。可以利用blackhole存储引擎的MySQL作为relay server实现级联复制,这个最好控制在三层内,减少同步的延迟,减少机房之间传输的数据量。关于SQL语句,索引不要太多,影响插入速度,注意更新和删除的问题,如果采用row模式进行复制,,数据量是个问题,建议采用mixed模式进行复制。对于实时性要求较高的应用可以允许第一次读的时候从主库上读取。或者在应用端给出等待时间来缓解同步延迟对用户的影响。
2、对于多IDC中的高可用问题。
对于采用relay server的方式,可以采用两台server,每隔relay下面都是一个slave集群。当某个出现问题的时候,可以只访问另一个slave集群。
对于采用双主模式的情况下,这个容灾方式比较明显,每个master下都接一个slave集群。但对于多IDC部署的话缺点也很明显,只能使用两个IDC。
3、多点写入问题
双主复制,环状复制,通过federated引擎都可以实现多点写入。这个时候对于多点写入需要考虑自增问题,可通过auto_increment_increment=N(增量值)和auto_increment_offset=N (初始值)来解决。更新丢失问题。每个master只写本地数据,slave拥有全部数据,这个时候数据是以一定次序出现的。利用消息队列,数据写入本地数据库时也写入消息队列中(本人为涉及到)
4、运维监控问题
监控系统对整个维护过程中起着很重要的作用。为防止在多IDC之间网络环境的影响,最好采用分布式监控,并且每个IDC的监控系统都能独立工作,可以参考zabbix,ganglia(两个开源企业级监控系统)

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



Go language is an efficient, concise and easy-to-learn programming language. It is favored by developers because of its advantages in concurrent programming and network programming. In actual development, database operations are an indispensable part. This article will introduce how to use Go language to implement database addition, deletion, modification and query operations. In Go language, we usually use third-party libraries to operate databases, such as commonly used sql packages, gorm, etc. Here we take the sql package as an example to introduce how to implement the addition, deletion, modification and query operations of the database. Assume we are using a MySQL database.

Apple's latest releases of iOS18, iPadOS18 and macOS Sequoia systems have added an important feature to the Photos application, designed to help users easily recover photos and videos lost or damaged due to various reasons. The new feature introduces an album called "Recovered" in the Tools section of the Photos app that will automatically appear when a user has pictures or videos on their device that are not part of their photo library. The emergence of the "Recovered" album provides a solution for photos and videos lost due to database corruption, the camera application not saving to the photo library correctly, or a third-party application managing the photo library. Users only need a few simple steps

Hibernate polymorphic mapping can map inherited classes to the database and provides the following mapping types: joined-subclass: Create a separate table for the subclass, including all columns of the parent class. table-per-class: Create a separate table for subclasses, containing only subclass-specific columns. union-subclass: similar to joined-subclass, but the parent class table unions all subclass columns.

How to use MySQLi to establish a database connection in PHP: Include MySQLi extension (require_once) Create connection function (functionconnect_to_db) Call connection function ($conn=connect_to_db()) Execute query ($result=$conn->query()) Close connection ( $conn->close())

To handle database connection errors in PHP, you can use the following steps: Use mysqli_connect_errno() to obtain the error code. Use mysqli_connect_error() to get the error message. By capturing and logging these error messages, database connection issues can be easily identified and resolved, ensuring the smooth running of your application.

HTML cannot read the database directly, but it can be achieved through JavaScript and AJAX. The steps include establishing a database connection, sending a query, processing the response, and updating the page. This article provides a practical example of using JavaScript, AJAX and PHP to read data from a MySQL database, showing how to dynamically display query results in an HTML page. This example uses XMLHttpRequest to establish a database connection, send a query and process the response, thereby filling data into page elements and realizing the function of HTML reading the database.

PHP is a back-end programming language widely used in website development. It has powerful database operation functions and is often used to interact with databases such as MySQL. However, due to the complexity of Chinese character encoding, problems often arise when dealing with Chinese garbled characters in the database. This article will introduce the skills and practices of PHP in handling Chinese garbled characters in databases, including common causes of garbled characters, solutions and specific code examples. Common reasons for garbled characters are incorrect database character set settings: the correct character set needs to be selected when creating the database, such as utf8 or u

Through the Go standard library database/sql package, you can connect to remote databases such as MySQL, PostgreSQL or SQLite: create a connection string containing database connection information. Use the sql.Open() function to open a database connection. Perform database operations such as SQL queries and insert operations. Use defer to close the database connection to release resources.
