CN102937955A - Main memory database achieving method based on My structured query language (SQL) double storage engines - Google Patents
Main memory database achieving method based on My structured query language (SQL) double storage engines Download PDFInfo
- Publication number
- CN102937955A CN102937955A CN2011103873735A CN201110387373A CN102937955A CN 102937955 A CN102937955 A CN 102937955A CN 2011103873735 A CN2011103873735 A CN 2011103873735A CN 201110387373 A CN201110387373 A CN 201110387373A CN 102937955 A CN102937955 A CN 102937955A
- Authority
- CN
- China
- Prior art keywords
- mysql
- memory
- storage engines
- record
- server
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a main memory database achieving method based on My structured query language (SQL) double storage engines. The method aims at solving the technical problems in the prior art that the existing My SQL products can not meet requirements for high reliability, redundancy backups, persistent storage, high throughput, instant response and the like simultaneously. The method can meet requirements for high reliability, redundancy backups, persistent storage and the like simultaneously and is high in throughput and fast in responding and convenient to use. The method includes the following steps: selecting two computers and installing My SQL servers respectively; starting identical storage engines on the two My SQL servers and building the same data base so as to achieve a persistent storage function of the main memory database; using a copying function of My SQL, and enabling the two storage engines to be each other's backup; and configuring memory tables and the like.
Description
Technical field
The present invention relates to database technology, relate in particular to a kind of memory database implementation method based on the two storage engines of MySQL.
Background technology
At present MySQL is widely used in the middle-size and small-size website on the Internet.Because its volume is little, speed is fast, the total cost of ownership is low, these characteristics of open source code especially, many middle-size and small-size websites have selected MySQL as site databases in order to reduce the website total cost of ownership.Even in telecommunications industry, because the superior performance of MySQL, stability, perfect function also more and more are used in the telecommunication product.For example open day is on 04 09th, 2008, publication number is that the patent documentation of CN 101158958A has adopted such technical scheme, a kind of Online Video control system and control method, it is set up the MySQL database by the conventional usage of MySQL and comes user profile, video information and film comment information are controlled, make the user realize operating purpose by the operation interface input operation order of system, finally finish the various operations to described video, although there is response speed in this scheme, the not high deficiency of reliability, but still can realize smoothly the various operations control of video.In fact, no matter be internet product or telecommunication product, to the high reliability of product and response speed fast, more and more higher requirement is arranged.
In order to satisfy high reliability, concentrating best selection at the MySQL product is MySQL Cluster, and it is the database storage engines of a highly redundant, but facts have proved, the insertion of MySQL Cluster, inquiry velocity and unhappy, and expensive, and management function is limited.The user needs a lot of secondary development, such as, the node in the cluster does not have the function of autoboot, needs user's self-developing, and MySQL Cluster is as a new product, and is also stable not.The Memory engine of MySQL as its name suggests, is stored data in the internal memory, very at a high speed, all data disappear behind the MySQL server closing, do not have the ability of lasting storage, and it uses SQL statement API, although convenient, but do not make query performance improve maximization.Moreover, can solve the problems such as backup, redundancy, load balancing about the copy function of MySQL, but final queried access still drops on access disk table on the MySQL server but not memory table, for the fast speed inquiry, or not as direct access memory fast.For product MySQL Proxy, be that main copy function by MySQL realizes the problem that exists MySQL to copy.In addition, although MySQL provides Query Cache mechanism, it is not suitable for the situation that query statement often changes, and if tables of data be modified, relevant with this tables of data so whole Cache can be invalid, and deleted.Certainly, caching of page mechanism is arranged generally in the various storage engines of MySQL, but this mechanism is complicated, because caching page may be released, may switch, and causes too much consumption, affects performance between disk and caching page.Access in addition the MySQL database, also need to resolve the compiling SQL statement, this all is no small consumption.
From the above, in the MySQL product family, do not have a database engine that can satisfy simultaneously the requirements such as interface of high reliability, redundancy backup, lasting storage, high-throughput, summary responses, direct access list internal memory.
And the free memory database outside the MySQL all is the database of standalone version such as MonetDB, FastDB, can not solve the redundancy backup problem.
More well-known commercial memory database has the TimesTen of Oracle at present, its performance is very powerful, but price is very high, owing to be relevant database, satisfy strict ACID(atomicity, consistance, isolation, persistence) requirement, this also also has large consumption to read or write speed, and inquiry velocity may not maximize.In fact, in order to improve the query performance of database, a lot of companies all are that oneself product development is based on the memory database on the Oracle.
Memcached also is the memory object caching system of commonly using, but its based on data database table structure not, can not directly be used as the buffer memory of table data, needs some many secondary development on it.
In actual applications, a lot of products do not have strict coherence request, when in the new data write into Databasce, even application program can not be read this new data immediately in fact, the also not operation of movie affair, such as, the IPTV system, the user has bought the right of watching of some channel, because order record also is not synchronized in the server of user equipment access (may be in the main memory database table) yet, may can't see program in several seconds, see that again this can tolerate after having spent several seconds.Moreover many application are the intensive application of inquiry, and a small amount of write operation is so as long as can satisfy the inquiry that height is handled up.
Summary of the invention
The present invention solves the existing MySQL product of prior art can not satisfy simultaneously the technical matterss such as high reliability, redundancy backup, lasting storage, high-throughput, summary responses, a kind of memory database implementation method based on the two storage engines of MySQL is provided, it can satisfy the requirements such as high reliability, redundancy backup, storage are lasting in to the application that coherence request is strict, inquiry is intensive, write operation is few simultaneously at some, and handling capacity is large, fast response time, uses very convenient.
The present invention is directed to the prior art problem and mainly solved by following technical proposals, a kind of memory database implementation method based on the two storage engines of MySQL may further comprise the steps:
(A) select two computing machines, and the MySQL server is installed respectively;
(B) start storage engines at described two MySQL servers respectively, described storage engines is the storage engines with permanent storage function, sets up the database that is equal to, to realize the lasting memory function of memory database;
(C) utilize the copy function of MySQL, described two storage engines are backuped each other;
(D) caching engine of deployment memory table and independent operating in application program.
The present invention has selected two MySQL storage engines, and these two storage engines operate in respectively two and are installed within two MySQL servers on the computing machine.The present invention has selected to have the MySQL storage engines of lasting memory function, such as MyISAM storage engines or Innodb storage engines, to realize the lasting storage of memory database.That utilizes again MySQL copies (replication) function, two storage engines are backuped each other, satisfy the requirement of redundancy backup, and when a MySQL storage engines can not be worked, another one can continue to provide service, add the memory table that high concurrent fast query function can be provided, thus framework high reliability, high performance memory database platform.
As preferably, memory table is deployed within the process of each application program, all move a caching engine that works alone in the different application programs, application program selects different physical database tables to carry out buffer memory according to the needs of oneself, also selects as required different records to carry out buffer memory from the physical database table.The self-dependent caching engine of each application program is from physical database table synchrodata to memory table, and the fault of a caching engine does not affect the normal operation of other caching engines.
Two MySQL storage engines back up mutually, and the write operation of his-and-hers watches can carry out simultaneously from arbitrary engine or two engines.
As preferably, the physical database table is the database table on the MySQL server disk.Physical database is the database on the MySQL server disk namely.
As preferably, memory table and program process take same memory headroom, two kinds of interfaces of direct rdma read table record are provided, two kinds of interfaces all are the tight coupling interfaces, a kind of interface be with the memory table record copies in the memory block of application process appointment, another kind of interface is that the memory address that memory table records is returned to application process in order to directly access wherein data, and two kinds of interfaces all adopt the capable read lock of record.Memory table is implemented in the inside of application process, take same memory headroom with program process, two kinds of interfaces of direct rdma read table record are provided, two kinds all is tightly coupled interface, a kind of interface be with the memory table record copies in the memory block of application process appointment, another kind of interface is that the memory address with the memory table record returns to application process with direct access data wherein, and two kinds of interfaces all adopt the capable read lock of record.This implementation has guaranteed inquiring about intensive quick summary responses.
Use memory database implementation method of the present invention, can dispose as required a plurality of caching engines, to realize a kind of pattern of cluster.
As preferably, utilize the trigger mechanism of MySQL, follow the tracks of more in real time the modification action of physical database table, in time modification, increase, the deletion action with the physical database table record is synchronized in the memory table, the final consistency that keeps internal memory and disk namely keeps the consistance of memory database and physical database.
As preferably, caching engine operates in the application program, and when a MySQL storage engines can not be worked, caching engine automatically switched to another MySQL storage engines, continues Simultaneous Physical database table data.It is transparent all that these operational correspondences are used.Handoff procedure and switch after, the data in the memory table are not lost, even and two storage engines when breaking down simultaneously because memory table still exists and is available, thus application program also responsible memory table continue the service that provides certain.
The beneficial effect that the present invention brings is, a kind of MySQL memory database implementation method that can satisfy simultaneously high reliability, redundancy backup, lasting storage, high-throughput, summary responses, direct access list internal memory is provided, coherence request applied environment strict, that inquiry is intensive, write operation is few is had good result of use.
Description of drawings
Fig. 1 is a kind of one-piece construction block diagram of the present invention;
Fig. 2 is a kind of structure and the workflow diagram of caching engine of the present invention;
Fig. 3 is a kind of process flow diagram of caching engine Simultaneous Physical record of the present invention;
Fig. 4 is trigger of the present invention and a kind of graph of a relation that copies;
Fig. 5 is that caching engine of the present invention is processed MySQL Server switching synoptic diagram;
Fig. 6 is a kind of buffer memory event table structure of the present invention;
Fig. 7 is Aided Physical list structure of the present invention.
Embodiment
Below by embodiment, and by reference to the accompanying drawings, technical scheme of the present invention is further described in detail.
Embodiment: as shown in Figure 1, the present invention is a kind of memory database implementation method based on the two storage engines of MySQL, and critical piece comprises two the MySQL storage engines (MySQL server) in the dotted ellipse frame and interior memory table and the caching engine of broken circle frame in the structural drawing.Two MySQL storage engines are distributed on the different machines with caching engine, and caching engine and the memory table of oneself are on same machine.Be installed in respectively two MySQL servers on two machines, copy function according to MySQL, they are configured to one copy ring, it is bidirectional replication, can both copy to an other station server from the modification of any station server his-and-hers watches, every station server be the other side's master server (master) be again the other side from server (slave).
MySQL 1 and MySQL 2 are installed in respectively two MySQL servers on the machine, data can write also and can write from MySQL2 from MySQL 1, by the replicanism of MySQL product itself, the data in the database table of final MySQL1 server and MySQL2 server will be consistent.Which MySQL server read-write is application program select determined by application program oneself, as long as select one of them the MySQL server that can work as (active) that activate, selected the MySQL1 server such as application program among the figure 3, and initiate write operation by MySQL Client interface to physical database, these write operations finally can copy on the MySQL2 server.There is caching engine and memory table separately application program 1 and 2 inside, the record on their synchronous MySQL2 servers, and the change of these records may be from the change of showing on 3 pairs of MySQL1 servers of application program.
Fig. 1 has showed a kind of architecture of the present invention, but not as restriction type of service of the present invention.Such as, although only have an application program of writing physical database among the figure, a plurality of such application programs can be arranged in actual applications.An application program can be write also readable memory table of physical database.
Generally speaking, administration configuration of the present invention is few, caching engine only need know that the address of a MySQL server gets final product, which record determines by using in which table of buffer memory and the table, main configuration is on two MySQL servers, the installation of two MySQL servers is deployed with special feature, to reach requirement.With reference to Fig. 1, two MySQL servers are installed in respectively on two machines, simultaneously on each machine a process that is called DBGate are arranged, and each DBGate is responsible for separately the restarting of local MySQL server, detects anyway, various abnormality detection etc.When a MySQL server had catastrophic failure, DBGate can cut off it, and sends alarm to system network management.Two MySQL servers do not have public IP address, and namely so-called virtual ip address only has private IP address separately, mutual without any other alternately except MySQL copy function intermodule between them.
The IP address of supposing MySQL1 is 10.50.49.12, and the IP address of MySQL2 is 10.50.49.76, and to set up the identical account that copies use for them be george, and password is rss123.Their physical database has identical name, i.e. lsdb, and be to use the identical engine with lasting memory function to set up.So, their MySQL configuration is as follows respectively,
The configuration of MySQL1 server:
server-id = 1
log-bin=mysql-bin
binlog-do-db=lsdb
binlog-ignore-db=mysql
binlog-format = STATEMENT
replicate-ignore-table = lsdb.cache_events
auto-increment-increment = 2
auto-increment-offset = 2
master-host = 10.50.49.76
master-user = george
master-password = rss123
The configuration of MySQL2 server:
server-id = 2
log-bin=mysql-bin
binlog-do-db=lsdb
binlog-ignore-db=mysql
binlog-format = STATEMENT
replicate-ignore-table = lsdb.cache_events
auto-increment-increment = 2
auto-increment-offset = 1
master-host = 10.50.49.12
master-user = george
master-password = rss123
From top configuration information, the configuration item of visible two MySQL servers is basic identical.Be not both server_id, auto-increment-offset, master-host, wherein auto-increment-offset be one especially but very important configuration item in the content of back, describe.
The present invention allows application program to write simultaneously data toward two MySQL servers.But can not guarantee can correctly copy in the following state data:
From MySQL1 data inserting record (k1, value1, wherein k1 is unique index key assignments), from MySQL2 data inserting record (k1, value2, k1 are unique index key assignments), suppose that the record that MySQL1 inserts has been synchronized to MySQL2, so this moment, MySQL2 can not insert record (k1, value2).Suppose that perhaps the record that MySQL1 inserts also is not synchronized to MySQL2, so this moment, MySQL2 can insert record (k1, value2), but MySQL2 is at the record (k1 that copies from MySQL1 subsequently, value1) can failure the time, be present in MySQL2 and gone up because key assignments is the record (k1, value2) of k1.
Above-mentioned situation is that the asynchronous replication characteristic of MySQL itself causes, but can be avoided voluntarily by application program, is responsible for writing different tables such as different application programs, perhaps only has an application program to be responsible for writing data in the whole system.
In actual database, some tables may cause copying makeing mistakes owing to used the data type of self-propagation (auto_incretement).Once inserted a record by MySQL1 to certain table such as application program, MySQL1 is 100 to the row assignment of this record self-propagation type, suppose that this record also is not synchronized to before the MySQL2, application discovery MySQL1 is out of order, and switch to MySQL2 and continue in identical table, to insert another record, MySQL2 also is 100 to the row assignment of this record self-propagation type, and insert successfully, but MySQL1 recovers normal subsequently, it is 100 record (this is recorded on the MySQL1) that MySQL2 begins to copy the key assignments that does not also copy, copy and can make mistakes this moment, because the existing key assignments of MySQL2 is 100 record, the key assignments conflict occurs.
The present invention is by arranging the variable auto_increment_increment(i.v. of MySQL server) and the auto_increment_offset(side-play amount) table record that two MySQL server replicates have the AUTO_INCREMENT row can be coordinated.Be these variable set ups (auto_increment_increment and auto_increment_offset) value of non-conflict, when same table inserts newline, the value of the self-propagation of server-assignment just can not clash, so just can solve above-mentioned this problem, such as take the two- server 1 and 2 here as example:
In the configuration of MySQL1, include:
auto-increment-increment = 2
auto-increment-offset = 1
In the configuration of MySQL2, include:
auto-increment-increment = 2
auto-increment-offset = 2
Like this, when two-server has data to be inserted into the table of self-propagation field simultaneously, just can not clash, simple example be exactly the self-propagation field value that inserts under the MySQL1 be 1,3,5,7 ... by that analogy, the self-propagation field value that inserts under the MySQL2 just is 2,4,6,8 ..., by that analogy.
More than be the present invention to the configuration of storage engines and management to realize lasting redundant storage.The below introduces the design of caching engine of the present invention.
Although relate to two MySQL servers among the present invention, application program only need to know that the address of one of them MySQL server gets final product, and need not to know two, and the discovery of another one server is gone to find by caching engine fully.This uses memory database of the present invention very convenient to existing application program instead, such as, need not change the configuration of the address of originally only having a MySQL server.The below is that caching engine is found another one MySQL server and the process of selecting one as Active:
1. after caching engine starts, use the MySQL server ip address of configuration, attempt connecting this server;
2. if successful connection, then this MySQL server is as active, and ' show slave ' obtains the IP address of another MySQL server, and is kept in the local file to use the order of MySQL;
3. if connect unsuccessfully, the IP address of reading another MySQL server from above-mentioned local file is if can connect, then with its server as active;
4. if the connection of above-mentioned steps is unsuccessful, can not from file, read again the IP(of another MySQL server such as without file), the never correct initialization that copies that then shows two MySQL servers is installed or configuration, or show between two MySQL servers never normal communication, need the installation allocation problem of Test database.
Through above-mentioned steps, generally can both determine the MySQL server of active, in the subsequently access to physical database, if finding the MySQL server of active, caching engine can not provide service, and other one passable, then switch in addition that MySQL server request service, with it as active.
The selected MySQL server of caching engine in each application program is determined alone by above-mentioned steps and principle, mutually without any negotiation, so each free different MySQL server that has them as active may, but because two MySQL servers are to copy mutually, back up mutually, so this does not affect the use that the memory table data reach MySQL synchronously, do not affect the correctness of memory table content.When a MySQL server had catastrophic failure, DBGate can cut off it, and like this, all application programs all can be used same MySQL server.
Fig. 2 is a kind of structure and the workflow diagram of caching engine.Caching engine of the present invention is the trigger mechanism of utilizing MySQL, and modification, increase, the deletion action of table record is stored in the special table, and this table is called buffer memory event table cache_events.Caching engine has a special thread (synchronizing thread DB-Sync) to be responsible for reading record in the cache_events table, this table record be event_id event from small to large, be equivalent in chronological sequence order, during the new record of in reading event table one, if daily record of revising or increasing, so from physical database, unique identifier according to the table record in the event reads corresponding table record, and it is synchronized in the memory table (if certainly in the physical database table without this record, then from memory table, delete it, to keep the physics table consistent with memory table).If the deletion record event, and really recorded without this in the physical database, then from memory table, delete it.Quick-reading flow sheets is as follows:
1.. application call MySQL API revises table (certainly, data modification also can be the retouching operation that has copied on the MySQL server of opposite end) in the physical database by the MySQL client to the MySQL server request;
2. the .MySQL client sends the order of insertion, modification or deletion record to the MySQL server;
3. .MySQL server modifications physical database table, and carry out corresponding trigger program;
4.. the trigger program is inserted a logout in the cache_events table;
5.. synchronizing thread DB-Sync this new events record in reading sometime the cache_events table;
6.. if revise or insertion table record event, synchronizing thread can read corresponding record according to table name and Record ID from the physical database table;
7.. if revise or insertion table record event, new data is synchronized in the memory table, if deletion action, then deletion record from memory table.
The cache_events table is the pass key table that needs in the caching engine work, its structure such as Fig. 6; Aided Physical table cache_info, its structure such as Fig. 7.To each table, the three kinds of Action Events (insert, revise, delete) that all need to be respectively it are set up the trigger program, and the trigger program is set up by caching engine.
Suppose to have in the physical database table, table name is vod_content, is chosen as the recordid_field by name of row of the line item identifier field of table,
To set up the SQL statement of three trigger programs as follows for it so:
1.. insert record trigger program
CREATE TRIGGER trigger_vod_content_insert AFTER INSERT on vod_content FOR EACH ROW
BEGIN
insert into cache_events (record_id, ‘vod_content’, event_type, timestamp)
values(NEW.recordid_field, 1, 'I', UNIX_TIMESTAMP(sysdate()));
END
2.. amendment record trigger program
CREATE TRIGGER trigger_vod_content_update AFTER UPDATE on vod_content FOR EACH ROW
BEGIN
insert into cache_events (record_id, ‘vod_content’, event_type, timestamp)
values(NEW.recordid_field, 1, 'U', UNIX_TIMESTAMP(sysdate()));
END
3.. deletion record trigger program
CREATE TRIGGER trigger_vod_content_delete AFTER DELETE on vod_content FOR EACH ROW
BEGIN
insert into cache_events (record_id, ‘vod_content’, event_type, timestamp)
values(OLD.recordid_field, 1, 'D', UNIX_TIMESTAMP(sysdate()));
END
The initialization procedure that caching engine starts:
1. set up cache_info table (if exist then do not set up);
2. read the data in the cache_info table, and keep;
3. if without the trigger program, according to the data in the cach_info table, set up it;
4. read cache_events, obtain the event of event_id maximum, be i.e. last event, and reservation event data;
5. obtain the table name inventory (calling SQL statement show tables obtains) in the physical database;
6. need the table of buffer memory to set up memory table for using, and select record condition according to using to establish, deposit memory table in from crawl record from the physics table afterwards;
7. start synchronizing thread DB Sync.
The step of setting up the cache_info table is as follows:
1. connection physical database;
2. look into and have or not cache_info table, if nothing continues the following step, otherwise, withdraw from;
2. set up the cache_info table;
3. obtain the table name inventory (calling SQL statement show tables) in the physical database;
4. to each the table＜table_name in the inventory 〉, inquire about its structure;
5. determine can be used as row field name＜id_col_name of line item sign ID 〉;
6. with table name and row field name＜id_col_name〉insert cache_info as a record;
Attention: cache_info can be copied to another MySQL server.
Set up the concrete section step of memory table:
1. with SQL statement desc＜tablename〉obtain the structure of physics table, set up consistent memory table structure (can be according to using needs, selected some row, and record condition is selected in setting);
2. with SQL statement show index from＜tablename〉obtain the index structure of physics table, set up consistent memory table index;
After having finished above-mentioned initialization flow process, caching engine just can have been started working.
Fig. 3 is caching engine Simultaneous Physical record process flow diagram.The idiographic flow of the synchronous updating memory table of caching engine is:
1. wait for 5 seconds;
2. after surpassing in 5 seconds, check among the cache_events to have or not new events, if nothing got back to for the 1st step, otherwise, continue the following step;
3. read from small to large new events by event_id, deposit a result set in;
4. read article one logout from result set, from result set, delete it after reading;
5. according to a record of record_id in the event and table_id reading matter reason database;
6. judge and to obtain this physical record, if no record was jumped to for the 8th step and carried out, if having, carry out the following step;
7. with the data of physics table record, insert or covering memory table corresponding record, jumped to for the 9th step and carry out;
From memory table according to record_id and table_id deletion record;
9. the event_id of reservation process event is last_event_id, and this is to set up synchronous point;
10. whether the result set of judging the generation of the 3rd step is sky, if empty, jump to for the 1st step and carries out, not empty, then jumps to the 4th and goes on foot execution.
When database server switched, the processing of caching engine: caching engine of the present invention was synchronized to each retouching operation to the physical database table record (insert, delete, revise) in the corresponding memory table by table cache_events.In design of the present invention, this table does not copy, and like this, the cache_events table just is equivalent to an event log (LOG) of minute book ground physics database table operation, and each event according to it sequencing occurs and arranges from small to large by event_id.Notice that cache_events is exactly a local daily record (local log).
Fig. 4 is trigger and a kind of graph of a relation that copies.In the drawings, if application program is connected to first the MySQL1 server, and successful execution a write operation, the trigger program of MySQL1 server will be inserted simultaneously a logout (event_id=99) in cache_events so, but it can not be copied in the cache_events table of MySQL2 server of opposite end.Only have when this write operation copies to the MySQL2 server, the trigger on it just can insert a logout (event_id=102) this moment in the cache_events of this locality, and event_id 102 is self-propagation values that MySQL2 server oneself distributes.As seen, the generation of the logout of MySQL2 server is not the record that has copied among the cache_events of opposite end, but local trigger generation, their event_id is not identical (one is the odd number sequence, and another is the even number sequence, and this is to be determined by the configuration of server).Otherwise, if as the same from MySQL2 server write data.
The present invention does not copy cache_events, and its reason is may cause following problem if copy cache_events:
Suppose that an application program writes data and produces a logout (event_id=7) in cache_events at the MySQL1 server, this moment, application program switched to the MySQL2 server, suppose to copy between two MySQL servers this moment and do not work or not prompt enough, cause this logout (event_id=7) and corresponding table record data all not to copy on the MySQL2 server, also be not synchronized in the memory table.After the switching, application program continues to write data at MySQL2, and produces logout 12(event_id=12), the synchronous point of caching engine has pointed to logout 12.After a period of time, copy function is recovered, logout on the MySQL1 server (event_id=7) and table record data have all copied on the MySQL2 server, but because logout (event_id=7)＜logout (event_id=12), the caching engine module can only be pounced on and catch event_id greater than 12 log events, can not know generation even existence less than 12 event, so its can not synchronous event record (event_id=7) corresponding write operation is in memory table.Like this, memory table just produces inconsistent with physical database.
In design of the present invention, the cache_events table does not copy, like this when application program is switched the MySQL server, because the event_id (int value) of the cache_events list event of two MySQL servers record may be fully inconsistent, even differing very large, the content that perhaps records among the cache_events is fully inconsistent.In a word, switching the problem of drawing is exactly: after the switching, need to find at new cache_events suitable starting point event, namely new synchronous point can continue correct synchrodata at new MySQL server to guarantee caching engine.
Fig. 5 is that caching engine is processed MySQL Server switching synoptic diagram.Application program is switched before the physical database, and it is connected on the MySQL1 server, and synchronous last event is 98 (event_id=98), and event 99,100 is not able to do in time just to lose synchronously and being connected of MySQL1 server.And we can also see, the event of MySQL2 server, from event_id just see with the MySQL1 server on differ greatly, the former is from 40 to 202, the latter is from 0 to 100, the difference of holding within wherein still more.
Suppose that the timestamp (timestamp) in event 98 record of MySQL1 is T
98, and the system time on the system time of application program and the MySQL1 server differs and is T
d, i.e. T
d=APP time-DB time, according to the Time Calculation of application program, the time that event 98 occurs is T
98=T
98+ T
dIf the system time of the system time of application program and MySQL2 server differs and is T
D2, according to the Time Calculation of MySQL2 server, the time that event 98 occurs is T so
98=T
98-T
D2So far, we have estimated the time on corresponding MySQL2 when event 98 occurs, certainly, because MySQL1 and copying of MySQL2 are asynchronous, Possible event 98 representatives the operation of physical data baserecord is not also occured at MySQL2 also is very likely, but it has been synchronized to memory table and has suffered at least.T has been arranged
98After, caching engine begins to search for the cache_events on the MySQL2 server.Write on the principle of afterbody according to up-to-date event, look for from back to front (namely since 202, then 201 ...), look at that the timestamp of which logout is near T
98, what suppose that we finally find is that event 44 (is its time stamp T
44~=T
98), the new event synchronization point after the switching that Here it is so.
Owing to look for by the time not being very accurate, so we suitably will new event synchronization o'clock skip several events forward from 44, reaching the some seconds (such as 3 seconds) that to shrink back lock in time, as jump to 42.Since then the caching engine in the application program just the event 42 from the MySQL2 begin synchronous, because some event of possibility is synchronous (such as the event 96 on the corresponding MySQL1 server of 44 possibilities), but the event that re-executes is on the impact of memory table nothing itself, because caching engine is when inserting the record operation, if existing record in the discovery memory table is then with the record in the record covering memory table in the physics table; If deletion event, and have record in the physics table, then caching engine is converted into the insertion event handling with this event.
Features such as so the present invention has high reliability, redundancy backup, lasting storage, high-throughput, summary responses, direct access list internal memory, and result of use is good.
Claims (6)
1. the memory database implementation method based on the two storage engines of MySQL is characterized in that, may further comprise the steps:
(A) select two computing machines, and the MySQL server is installed respectively;
(B) start storage engines at described two MySQL servers respectively, described storage engines is the storage engines with permanent storage function;
(C) utilize the copy function of MySQL, described two storage engines are backuped each other;
(D) caching engine of deployment memory table and independent operating in application program.
2. described a kind of memory database implementation method based on the two storage engines of MySQL according to claim 1, it is characterized in that: the memory table in the described step (D) is deployed within the process of each application program, all move a caching engine that works alone in the different application programs, application program selects different physical database tables to carry out buffer memory according to the needs of oneself, also selects as required different records to carry out buffer memory from the physical database table.
3. described a kind of memory database implementation method based on the two storage engines of MySQL according to claim 2, it is characterized in that: described physical database table is the database table on the MySQL server disk.
4. described a kind of memory database implementation method based on the two storage engines of MySQL according to claim 1 and 2, it is characterized in that: described memory table and program process take same memory headroom, two kinds of interfaces of direct rdma read table record are provided, two kinds of interfaces all are the tight coupling interfaces, a kind of interface be with the memory table record copies in the memory block of application process appointment, another kind of interface is that the memory address that memory table records is returned to application process in order to directly access wherein data, and two kinds of interfaces all adopt the capable read lock of record.
5. described a kind of memory database implementation method based on the two storage engines of MySQL according to claim 1, it is characterized in that: the trigger mechanism of utilizing MySQL, follow the tracks of in real time the modification action of physical database table, in time modification, increase, the deletion action with the physical database table record is synchronized in the memory table, keeps the final consistency of internal memory and disk.
6. described a kind of memory database implementation method based on the two storage engines of MySQL according to claim 2, it is characterized in that: described caching engine operates in the application program, when a MySQL storage engines can not be worked, caching engine automatically switches to another MySQL storage engines, continues Simultaneous Physical database table data.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2011103873735A CN102937955A (en) | 2011-11-29 | 2011-11-29 | Main memory database achieving method based on My structured query language (SQL) double storage engines |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2011103873735A CN102937955A (en) | 2011-11-29 | 2011-11-29 | Main memory database achieving method based on My structured query language (SQL) double storage engines |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN102937955A true CN102937955A (en) | 2013-02-20 |
Family
ID=47696853
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2011103873735A Pending CN102937955A (en) | 2011-11-29 | 2011-11-29 | Main memory database achieving method based on My structured query language (SQL) double storage engines |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN102937955A (en) |
Cited By (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2014135011A1 (en) * | 2013-08-29 | 2014-09-12 | 中兴通讯股份有限公司 | Database system and data synchronization method |
| CN104598508A (en) * | 2013-09-18 | 2015-05-06 | Ims保健公司 | System and method for fast query response |
| CN105373623A (en) * | 2015-12-08 | 2016-03-02 | 国云科技股份有限公司 | Method for quickly generating big data in custom table |
| CN105740104A (en) * | 2016-02-03 | 2016-07-06 | 网易（杭州）网络有限公司 | Method and device for backup of data in database as well as game system |
| CN105989124A (en) * | 2015-02-13 | 2016-10-05 | 深圳万兴信息科技股份有限公司 | Method and system for recovering self-increased primary key values of Sqlite file |
| CN107004010A (en) * | 2014-12-08 | 2017-08-01 | 国际商业机器公司 | Control multidatabase system |
| CN108132757A (en) * | 2016-12-01 | 2018-06-08 | 阿里巴巴集团控股有限公司 | Storage method, device and the electronic equipment of data |
| CN108737890A (en) * | 2018-08-21 | 2018-11-02 | 深圳Tcl数字技术有限公司 | Multi-process channel data switching method, device, television set and storage medium |
| CN110941666A (en) * | 2019-11-01 | 2020-03-31 | 网联清算有限公司 | Database multi-activity method and device |
| CN113792079A (en) * | 2021-11-17 | 2021-12-14 | 腾讯科技（深圳）有限公司 | Data query method and device, computer equipment and storage medium |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101158958A (en) * | 2007-10-23 | 2008-04-09 | 浙江大学 | Fusion enquire method based on MySQL storage engines |
| US20090132536A1 (en) * | 2007-11-19 | 2009-05-21 | Douglas Brown | Dynamic control and regulation of critical database resources using a virtual memory table interface |
| CN101923498A (en) * | 2009-06-11 | 2010-12-22 | 升东网络科技发展(上海)有限公司 | Database full-volume automatic backup system and method |
| CN101944114A (en) * | 2010-09-16 | 2011-01-12 | 深圳天源迪科信息技术股份有限公司 | Data synchronization method between memory database and physical database |
| CN102122285A (en) * | 2010-01-11 | 2011-07-13 | 卓望数码技术（深圳）有限公司 | Data cache system and data inquiry method |
| US20110246448A1 (en) * | 2009-11-04 | 2011-10-06 | Nec Laboratories America, Inc. | Database distribution system and methods for scale-out applications |
-
2011
- 2011-11-29 CN CN2011103873735A patent/CN102937955A/en active Pending
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101158958A (en) * | 2007-10-23 | 2008-04-09 | 浙江大学 | Fusion enquire method based on MySQL storage engines |
| US20090132536A1 (en) * | 2007-11-19 | 2009-05-21 | Douglas Brown | Dynamic control and regulation of critical database resources using a virtual memory table interface |
| CN101923498A (en) * | 2009-06-11 | 2010-12-22 | 升东网络科技发展(上海)有限公司 | Database full-volume automatic backup system and method |
| US20110246448A1 (en) * | 2009-11-04 | 2011-10-06 | Nec Laboratories America, Inc. | Database distribution system and methods for scale-out applications |
| CN102122285A (en) * | 2010-01-11 | 2011-07-13 | 卓望数码技术（深圳）有限公司 | Data cache system and data inquiry method |
| CN101944114A (en) * | 2010-09-16 | 2011-01-12 | 深圳天源迪科信息技术股份有限公司 | Data synchronization method between memory database and physical database |
Non-Patent Citations (1)
| Title |
|---|
| 鸟哥のLINUX: "《MYSQL互相备份同步》《http://blog.chinaunix.net/uid-25266990-id-335448.html》", 25 May 2011 * |
Cited By (14)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| WO2014135011A1 (en) * | 2013-08-29 | 2014-09-12 | 中兴通讯股份有限公司 | Database system and data synchronization method |
| CN104598508A (en) * | 2013-09-18 | 2015-05-06 | Ims保健公司 | System and method for fast query response |
| CN104598508B (en) * | 2013-09-18 | 2021-06-08 | Iqvia 公司 | System and method for fast query response |
| CN107004010B (en) * | 2014-12-08 | 2020-08-11 | 国际商业机器公司 | Controlling multiple database systems |
| CN107004010A (en) * | 2014-12-08 | 2017-08-01 | 国际商业机器公司 | Control multidatabase system |
| US11176171B2 (en) | 2014-12-08 | 2021-11-16 | International Business Machines Corporation | Controlling a multi-database system |
| CN105989124A (en) * | 2015-02-13 | 2016-10-05 | 深圳万兴信息科技股份有限公司 | Method and system for recovering self-increased primary key values of Sqlite file |
| CN105373623A (en) * | 2015-12-08 | 2016-03-02 | 国云科技股份有限公司 | Method for quickly generating big data in custom table |
| CN105740104A (en) * | 2016-02-03 | 2016-07-06 | 网易（杭州）网络有限公司 | Method and device for backup of data in database as well as game system |
| CN105740104B (en) * | 2016-02-03 | 2018-10-30 | 网易（杭州）网络有限公司 | Data back up method, device and games system in database |
| CN108132757A (en) * | 2016-12-01 | 2018-06-08 | 阿里巴巴集团控股有限公司 | Storage method, device and the electronic equipment of data |
| CN108737890A (en) * | 2018-08-21 | 2018-11-02 | 深圳Tcl数字技术有限公司 | Multi-process channel data switching method, device, television set and storage medium |
| CN110941666A (en) * | 2019-11-01 | 2020-03-31 | 网联清算有限公司 | Database multi-activity method and device |
| CN113792079A (en) * | 2021-11-17 | 2021-12-14 | 腾讯科技（深圳）有限公司 | Data query method and device, computer equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN102937955A (en) | Main memory database achieving method based on My structured query language (SQL) double storage engines | |
| Taft et al. | Cockroachdb: The resilient geo-distributed sql database | |
| US7779295B1 (en) | Method and apparatus for creating and using persistent images of distributed shared memory segments and in-memory checkpoints | |
| CN102693324B (en) | Distributed database synchronization system, synchronization method and node management method | |
| KR101602312B1 (en) | Data sending method, data receiving method, and storage device | |
| US7650369B2 (en) | Database system management method and database system | |
| EP3564835B1 (en) | Data redistribution method and apparatus, and database cluster | |
| KR101662212B1 (en) | Database Management System providing partial synchronization and method for partial synchronization thereof | |
| US6823474B2 (en) | Method and system for providing cluster replicated checkpoint services | |
| CN103077222B (en) | Cluster file system distributed meta data consistance ensuring method and system | |
| CN113396407A (en) | System and method for augmenting database applications using blockchain techniques | |
| CN100397803C (en) | N+1 duplicates data real-time synchronising method | |
| US20150213100A1 (en) | Data synchronization method and system | |
| CN102904949B (en) | Replica-based dynamic metadata cluster system | |
| US20050055445A1 (en) | High availability data replication of an R-tree index | |
| WO2012071920A1 (en) | Method, system, token conreoller and memory database for implementing distribute-type main memory database system | |
| CN100371900C (en) | Method and system for synchronizing data | |
| CN111078667B (en) | Data migration method and related device | |
| US20120095960A1 (en) | Multi-master attribute uniqueness | |
| CN103902405A (en) | Quasi-continuity data replication method and device | |
| CN105069152A (en) | Data processing method and apparatus | |
| CN115098229A (en) | Transaction processing method, device, node equipment and storage medium | |
| CN102045187B (en) | Method and equipment for realizing HA (high-availability) system with checkpoints | |
| CN101252464B (en) | Dual system and dual system online upgrading method | |
| US9201685B2 (en) | Transactional cache versioning and storage in a distributed data grid |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C12 | Rejection of a patent application after its publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20130220 |