MySQL Enterprise combat

========================== Business Vertical Segmentation:
1 "Description:

As the business scale expands, DBAs need to cut according to enterprise data business, vertical cutting is also called longitudinal cutting, vertical data cutting is based on enterprise website business, product segmentation, such as: User data, blog post data, photo data, label data type, group number, etc. Each business is separated by a separate sub-table for cutting. If an application is only for the simple business function module, then can directly connect to the corresponding vertically segmented database, but some complex applications need to use a considerable amount of business data, and even involved in all business data so the vertical cutting will bring some complexity to the application, and will have a certain impact on the developers, the guidance To increase the complexity of the application, then amoeba in it as a façade function , dredge the use of multiple databases of communication if the vertical segmentation function: If there are 3 databases: UserDB blogdb Otherdb

2 " DB architecture Diagram :

================================ Business Level Segmentation

1 " Introduction Description :

< EM id= "__mcedel" >     Last time we talked about SAG cutting. So what if the vertical cut does not meet the needs of the enterprise?? Then we further optimize the architecture, let the horizontal cutting to solve the problem, the horizontal cutting root user_id       surrounding value division, and then go in to modulus% 2, then the resulting value is only 0 and 1 defined to each DB Server up. This range is suitable for small-to medium-sized DB architecture extensions, and we'll cover the 360-degree ring library     extension in the next chapter for large DB architecture design;

Requirements Description:
Total number of users 5 million
USER_DB Store user basic information < such as, login authentication, personal information >
blog_db Store User Blog posts
OTHER_DB Store Other business
Split the 3 db into different servers by a vertical split, then split the DB user with a horizontal cut,
USER_DB1 load basic information for 2.5 million users user_id range value <500 user_id modulo% 2 value 0 Read this server
USER_DB2 load basic information for 2.5 million users user_id range value <500 user_id modulo% 2 value 1 Read this server

BLOG_DB1 hosting 2.5 million users blog post user_id range value <500 user_id modulo% 2 value 0 Read this server
BLOG_DB2 hosting 2.5 million users blog post user_id range value <500 user_id modulo% 2 value 1 Read this server

OTHER_DB1 other information that hosts 2.5 million users user_id range value <500 user_id modulo% 2 value 0 Read this server
OTHER_DB2 other information that hosts 2.5 million users user_id range value <500 user_id modulo% 2 value 1 Read this server
Equivalent to 2 machines for 5 million users. Each bearer. 2 by 1, this range can be extended indefinitely based on the ID range value.

2 " DB architecture Diagram

3 " Environmental Case

Because we've already talked about the read and write separation, this actual case is mainly about horizontal segmentation, through amoeba realization segmentation, Judge ID range value and take the model
1>amoeba_server (Linux) 10.0.0.203 for horizontal cutting
2>user_dba_server1 (Linux) 10.0.0.201 load 2.5 million user basic information user_id range value <500 million user_id modulo% 2 value 0 Read this server
3>user_dba_server2 (Linux) 10.0.0.202 load 2.5 million user basic information user_id range value <500 user_id modulo% 2 value 1 Read this server
　　The equivalent of 2 machines for 5 million with three households. Each bearer. 2 by 1, this range can be extended indefinitely based on the ID range value. Pay attention to these 2 machines. And no master from that is separate two single-machine MySQL

4 " set db_server1 db_server2 settings Amoeba Remote access authorization

Mysql-->grant all privileges on * * to [e-mail protected] '% ' identified by ' 123456 ';

Mysql-->flush privileges;

5 " Create a test table

　　On Db_server1 and db_server2, the table structure on both sides is the same as the table name. Attention.
　　Create a T_user table
CREATE TABLE Zyalvin (
user_id integer unsigned NOT NULL,
User_name varchar (45),
user_address varchar (100),
Primary KEY (USER_ID)
) Engine=innodb;
Query OK, 0 rows affected (0.01 sec

6 " Configuring Amoeba IP with User

Vim/usr/local/amoeba/conf/amoeba.xml

7 " configuring Server1 and Server2

Configuration Server1:

Configure Server2

8 " Configure a horizontal split rule

# Vim/usr/local/amoeba/conf/rule.xml
　　Tablerule name= "Zyalvin,t_user" can be more than one table,

Start the service
#:nohup bash-x Amoeba &

9 " inserting test Data

　　 Log in Amoeba server:
　　 # mysql-uroot-p123456-h 192.168.100.214-p 9006
Mysql->use zytest;
Mysql->insert into Zyalvin (user_id,user_name,user_address) VALUES (' 1 ', ' user1 ', ' China ');
Mysql->insert into Zyalvin (user_id,user_name,user_address) VALUES (' 2 ', ' User1 ', ' China ');
Mysql->insert into Zyalvin (user_id,user_name,user_address) VALUES (' 3 ', ' user1 ', ' China ');
Mysql->insert into Zyalvin (user_id,user_name,user_address) VALUES (' 4 ', ' User1 ', ' China ');
Mysql->insert into Zyalvin (user_id,user_name,user_address) VALUES (' 5 ', ' User1 ', ' China ');
Mysql->insert into Zyalvin (user_id,user_name,user_address) VALUES (' 6 ', ' User1 ', ' China ');
Mysql->insert into Zyalvin (user_id,user_name,user_address) VALUES (' 7 ', ' User1 ', ' China ');
Mysql->insert into Zyalvin user_id,user_name,user_address) VALUES (' 8 ', ' User1 ', ' China ');
Mysql->insert into Zyalvin (user_id,user_name,user_address) VALUES (' 9 ', ' User1 ', ' China ');
Mysql->insert into Zyalvin (user_id,user_name,user_address) VALUES (' Ten ', ' User1 ', ' China ');
Db_server1 View, even all write Server1

Db_server2 view, odd all write Server2

Summarize:
(1) Amoeba is split horizontally based on SQL parsing, the key field of the Shard (here is user_id) is added to the INSERT SQL statement, otherwise the sharding rule is invalid. Invalid, the data is inserted in Server1, Server2.
(2) Amoeba Insert data must first use dbname (such as to use the test library name, or query with the Library name and table name, Test.t_user) Otherwise insert data will be inserted by default on Server1
(3) in the Rule.xml specified ID range, when inserting data user_id, cannot exceed this range, otherwise the Shard is invalid, for example, the defined ID range is: 1-500000 number
Insert into Zyalvin (user_id,user_name,user_address) VALUES (' 5000001 ', ' user1 ', ' China '); If the insertion ID is more than 5000000, it becomes 5000001 then this data will be written to both Server1 and Server2 causing the Shard to be invalid.

When you open a file that ends in. xml, it is handled in the following ways:

MySQL Enterprise combat