MySQL decentralization
Publish: 2021-05-02 01:50:09
1. 1. bitcoin 2, Ethereum: Ethereum is a kind of digital token of Ethereum, which is regarded as "bitcoin version 2.0". Using blockchain technology different from bitcoin, "Ethereum" developers need to pay Ethereum (ETH) to support the operation of the application. Like other digital currencies, they can be traded on trading platforms. 3. lettercoin: lettercoin (LTC, currency symbol:?) It is a kind of network currency based on "point-to-point" technology and an open source software project under MIT / X11 license. It can help users make instant payments to anyone in the world. 4. Dogcoin: dogcoin, known as "dogcoin / dogcoin", was born on December 8, 2013. Based on the crypt algorithm, dogcoin is the second largest virtual currency in the world after bitcoin in terms of number of users. 5. Yuan Bao coin: Yuan Bao coin is a virtual digital currency similar to bitcoin. The founder of yuanbaocoin is a group of bitcoin enthusiasts and the earliest supporters. However, after seeing the problem of bitcoin, he decided to do his best to develop an electronic currency suitable for Chinese people with the latest technology. 6. Diandiancoin: "ppcoin was pre released by sunny king on August 11, 2012 and officially released on August 19, 2012. Ppcoin's biggest innovation is that its mining method combines POW workload proof and POS equity proof. POS mining method only needs ordinary computers and clients to process transactions and maintain network security, so as to achieve the purpose of energy saving and security. 7. Katecoin: katecoin (KTC for short) is a virtual P2P digital currency that can be used all over the world. Point to point transmission means a decentralized payment system. 8. Quark coin: Based on six encryption algorithms (black, BMW, groestl, JH, keccak, skein), 9 rounds of super secure hash operation of virtual currency, 3 rounds of application of random hash function, quark coin has unique technical specifications. 9. Infinity coin: Infinity coin (IFC) is a new digital currency, which has more circulation advantages than bitcoin, and fills the short board of bitcoin in the field of commercial circulation and promoting commercial operation. 10. Common coin: common coin, English name popular coin, short for POC, was born on March 7, 2014. The total POC is close to the world population (7.057 billion).
2. It's not like this, is it? It feels like you've got the wrong version
3. MySQL proxy is an official MySQL middleware proct, which can achieve load balancing, read-write separation, fail over and so on, but it does not support large amount of database and table, and its performance is poor. Here are several MySQL open source middleware procts, Atlas, Cobar and tddl, which can replace them. Let's see their advantages and new features<
atlas
atlas is a data middle tier project based on MySQL protocol developed and maintained by Qihoo 360, the infrastructure team of Web Platform Department. It is based on MySQL proxy version 0.8.2, which has been optimized and added some new features. The MySQL service running with atlas in 360 carries billions of read and write requests every day<
Altas architecture:
atlas is located between the application and mysql. It implements the MySQL client and server protocol. It communicates with the application as the server and MySQL as the client. It shields the details of DB from the application and maintains the connection pool to rece the burden of MySQL
the following is an overall architecture for reference. LVS front end is used for load balancing, and two Altas are used for HA to prevent single point of failure< Some new features of Altas are as follows:
1. When the main library is down, the read operation will not be affected. When the database is down, Atlas will automatically remove the down database, which has no impact on the application. In the official MySQL proxy, the master database is down, and the slave database is not available
2. Through the management interface, the management work can be simplified. The up and down line of DB is completely transparent to the application, and it can be up and down line manually< (1) in order to solve the problem of read-write separation, I want to read immediately after writing, but there may be master-slave synchronization delay. In Altas, I can add / * Master * / in front of the SQL statement to force the read request to the master database
multiple items can be set in the master library, separated by commas, and multiple items and weights can be set in the slave library to achieve load balancing<
4. Implement sub table by yourself
(1) need sub table field
(2) support select, insert, update, delete and replace statements
(3) support the merging and sorting of multiple sub table query results
here we have to make complaints about Atlas's sub table function, which can not realize distributed partition table. All subtables must be in the same database of the same DB, and all the child tables must be built in advance. Atlas does not have the function of automatically building tables.
5. Previously, the official main function logic was written by Lua script, which was inefficient. Atlas was rewritten by C, which improved QPS and reced latency
6. Security improvement
(1) access control of users connecting to atlas through PWDs parameter in configuration file
(2) filter the IP with permission to connect to atlas through the client IPS parameter
(3) all SQL statements processed by Altas are recorded in the log, including the client IP, the DB that actually executed the statement, the execution success or failure, and the execution time, as shown in the following example
Figure 4
7. Smooth restart
realize the smooth restart function by setting LVS IPS parameters in the configuration file, otherwise those SQL requests will fail at the moment of restarting Altas. This parameter is the IP of the physical network card of the LVS attached in front of it. Note that it is not a virtual IP. The condition of smooth restart is that there are at least two atlas with the same configuration and hang after LVS< br />source： https://github.com/Qihoo360/Atlas
Alibaba. Cobar
Cobar is a distributed processing system for relational data developed by Alibaba (B2B) department. It can provide you with massive data services in a distributed environment just like a traditional database. So specifically, why do we use it, or Cobar -- what can we do? The following are some problems in the operation of our business:
1. With the rapid increase of the amount of data and access to the database, we need to split the data horizontally to rece the pressure of a single database, and we need to shield the details of the horizontal split efficiently and relatively transparently
2. To improve the availability of access, data sources need to be backed up
3. Data source availability detection and failure
4. The high concurrency in the foreground causes too many connections in the background database, which reces the performance. How to solve this problem
to solve the above problems, Cobar has its own space. Cobar middleware is located between the foreground application and the actual database in the form of proxy, and the open interface to the foreground is MySQL communication protocol. The foreground SQL statements are changed and forwarded to the appropriate background database according to the data distribution rules, and then the returned results are merged to simulate the database behavior in a single database
Cobar application example
Application Architecture:
Application Introction:
1. Provide a database named test through Cobar, which contains two tables T1 and T2. There are three MySQL instances (IP: port) in the background to provide services, namely: A, B, C
2. It is expected that the data of table t1 will be placed in instance a, the data of table t2 will be divided into four parts horizontally, and two parts will be placed in instances B and C respectively. The data of table t2 should have ha function, that is, if one of instance B or C fails, it will not affect the use and can provide complete data service< Summary of advantages of cabar:
1. Data and access are changed from centralized to distributed:
(1) Cobar supports horizontal splitting of a table into multiple tables and putting them into different libraries respectively to realize horizontal splitting of tables
(2) Cobar also supports putting different tables into different libraries
(3) in most cases, users will mix the above two methods
note!: Cobar does not support splitting a table, such as a test table, into tests_ 1,test_ 2, test_ 3... In the same library, the split tables must be put into different libraries to achieve distributed
2. Solve the problem of too many connections
3. It is less invasive to business code
4. Provide data node's fail over, ha:
(1) there are two trigger modes for Cobar's active / standby switch, one is triggered manually by the user, the other is triggered automatically when Cobar's heartbeat statement detects an exception. Then, when the heartbeat detects that the host is abnormal, switch to the standby machine. If the host recovers, the user needs to switch back to the host manually. Cobar will not switch back to the host automatically when the host recovers, unless the heartbeat of the standby machine also returns abnormal
(2) Cobar only checks the exceptions between the active and standby mysql, and does not care about the data synchronization between them. Therefore, users need to configure bidirectional synchronization on the active and standby MySQL before using Cobar
disadvantages of Cobar:
in the open source version, the database only supports mysql, and does not support read-write separation< br />source： http://code.alibabatech.com/wiki/display/cobar/Home
tddl
Taobao has developed tddl (Taobao distributed data layer) according to its own business characteristics_ It is a JDBC datasource implementation based on centralized configuration, which has the functions of active and standby, read-write separation, dynamic database configuration and so on
location of tddl (tddl universal data access layer, deployed in the jar package of the client, used to route the user's SQL to the specified database):
Taobao has processed the data in sub databases for a long time. The upper system connects multiple databases, and there is a route called dbroute in the middle to access the data uniformly. Dbroute makes multi database operation and data integration, and makes the upper system operate multiple databases like one database. However, with the growth of the amount of data, there are higher requirements for the classification of Library tables. For example, when your commodity data reaches 10 billion level, no library can be stored, so it can be divided into 2, 4, 8, 16, 32... Until 1024, 2048. Well, it can be divided into so many parts, and the data can be stored. How can we query it? At this time, the middleware of data query will be able to undertake this heavy task. For the upper layer, it must query data just like querying a database, and it must be as fast as querying a database (each query is completed in a few milliseconds). Tddl undertakes such a task. Outside, some systems also use the concept of dal (data access layer) to name the middleware
the following figure shows a simple database and table data query strategy:
main advantages:
1. Database active / standby and dynamic switching
2. Weighted read / write separation
3. Single thread read / try
4. Centralized data source information management and dynamic change
5. Detached stable JBoss data source
6. Support for MySQL and Oracle databases
7. Based on JDBC specification, It's easy to expand the data source supporting the implementation of JDBC specification
8. No server, client jar form, application directly connected database
9. Read and write times, concurrency, process control, dynamic change
10. Analyzable log printing, log flow control, dynamic change
atlas
atlas is a data middle tier project based on MySQL protocol developed and maintained by Qihoo 360, the infrastructure team of Web Platform Department. It is based on MySQL proxy version 0.8.2, which has been optimized and added some new features. The MySQL service running with atlas in 360 carries billions of read and write requests every day<
Altas architecture:
atlas is located between the application and mysql. It implements the MySQL client and server protocol. It communicates with the application as the server and MySQL as the client. It shields the details of DB from the application and maintains the connection pool to rece the burden of MySQL
the following is an overall architecture for reference. LVS front end is used for load balancing, and two Altas are used for HA to prevent single point of failure< Some new features of Altas are as follows:
1. When the main library is down, the read operation will not be affected. When the database is down, Atlas will automatically remove the down database, which has no impact on the application. In the official MySQL proxy, the master database is down, and the slave database is not available
2. Through the management interface, the management work can be simplified. The up and down line of DB is completely transparent to the application, and it can be up and down line manually< (1) in order to solve the problem of read-write separation, I want to read immediately after writing, but there may be master-slave synchronization delay. In Altas, I can add / * Master * / in front of the SQL statement to force the read request to the master database
multiple items can be set in the master library, separated by commas, and multiple items and weights can be set in the slave library to achieve load balancing<
4. Implement sub table by yourself
(1) need sub table field
(2) support select, insert, update, delete and replace statements
(3) support the merging and sorting of multiple sub table query results
here we have to make complaints about Atlas's sub table function, which can not realize distributed partition table. All subtables must be in the same database of the same DB, and all the child tables must be built in advance. Atlas does not have the function of automatically building tables.
5. Previously, the official main function logic was written by Lua script, which was inefficient. Atlas was rewritten by C, which improved QPS and reced latency
6. Security improvement
(1) access control of users connecting to atlas through PWDs parameter in configuration file
(2) filter the IP with permission to connect to atlas through the client IPS parameter
(3) all SQL statements processed by Altas are recorded in the log, including the client IP, the DB that actually executed the statement, the execution success or failure, and the execution time, as shown in the following example
Figure 4
7. Smooth restart
realize the smooth restart function by setting LVS IPS parameters in the configuration file, otherwise those SQL requests will fail at the moment of restarting Altas. This parameter is the IP of the physical network card of the LVS attached in front of it. Note that it is not a virtual IP. The condition of smooth restart is that there are at least two atlas with the same configuration and hang after LVS< br />source： https://github.com/Qihoo360/Atlas
Alibaba. Cobar
Cobar is a distributed processing system for relational data developed by Alibaba (B2B) department. It can provide you with massive data services in a distributed environment just like a traditional database. So specifically, why do we use it, or Cobar -- what can we do? The following are some problems in the operation of our business:
1. With the rapid increase of the amount of data and access to the database, we need to split the data horizontally to rece the pressure of a single database, and we need to shield the details of the horizontal split efficiently and relatively transparently
2. To improve the availability of access, data sources need to be backed up
3. Data source availability detection and failure
4. The high concurrency in the foreground causes too many connections in the background database, which reces the performance. How to solve this problem
to solve the above problems, Cobar has its own space. Cobar middleware is located between the foreground application and the actual database in the form of proxy, and the open interface to the foreground is MySQL communication protocol. The foreground SQL statements are changed and forwarded to the appropriate background database according to the data distribution rules, and then the returned results are merged to simulate the database behavior in a single database
Cobar application example
Application Architecture:
Application Introction:
1. Provide a database named test through Cobar, which contains two tables T1 and T2. There are three MySQL instances (IP: port) in the background to provide services, namely: A, B, C
2. It is expected that the data of table t1 will be placed in instance a, the data of table t2 will be divided into four parts horizontally, and two parts will be placed in instances B and C respectively. The data of table t2 should have ha function, that is, if one of instance B or C fails, it will not affect the use and can provide complete data service< Summary of advantages of cabar:
1. Data and access are changed from centralized to distributed:
(1) Cobar supports horizontal splitting of a table into multiple tables and putting them into different libraries respectively to realize horizontal splitting of tables
(2) Cobar also supports putting different tables into different libraries
(3) in most cases, users will mix the above two methods
note!: Cobar does not support splitting a table, such as a test table, into tests_ 1,test_ 2, test_ 3... In the same library, the split tables must be put into different libraries to achieve distributed
2. Solve the problem of too many connections
3. It is less invasive to business code
4. Provide data node's fail over, ha:
(1) there are two trigger modes for Cobar's active / standby switch, one is triggered manually by the user, the other is triggered automatically when Cobar's heartbeat statement detects an exception. Then, when the heartbeat detects that the host is abnormal, switch to the standby machine. If the host recovers, the user needs to switch back to the host manually. Cobar will not switch back to the host automatically when the host recovers, unless the heartbeat of the standby machine also returns abnormal
(2) Cobar only checks the exceptions between the active and standby mysql, and does not care about the data synchronization between them. Therefore, users need to configure bidirectional synchronization on the active and standby MySQL before using Cobar
disadvantages of Cobar:
in the open source version, the database only supports mysql, and does not support read-write separation< br />source： http://code.alibabatech.com/wiki/display/cobar/Home
tddl
Taobao has developed tddl (Taobao distributed data layer) according to its own business characteristics_ It is a JDBC datasource implementation based on centralized configuration, which has the functions of active and standby, read-write separation, dynamic database configuration and so on
location of tddl (tddl universal data access layer, deployed in the jar package of the client, used to route the user's SQL to the specified database):
Taobao has processed the data in sub databases for a long time. The upper system connects multiple databases, and there is a route called dbroute in the middle to access the data uniformly. Dbroute makes multi database operation and data integration, and makes the upper system operate multiple databases like one database. However, with the growth of the amount of data, there are higher requirements for the classification of Library tables. For example, when your commodity data reaches 10 billion level, no library can be stored, so it can be divided into 2, 4, 8, 16, 32... Until 1024, 2048. Well, it can be divided into so many parts, and the data can be stored. How can we query it? At this time, the middleware of data query will be able to undertake this heavy task. For the upper layer, it must query data just like querying a database, and it must be as fast as querying a database (each query is completed in a few milliseconds). Tddl undertakes such a task. Outside, some systems also use the concept of dal (data access layer) to name the middleware
the following figure shows a simple database and table data query strategy:
main advantages:
1. Database active / standby and dynamic switching
2. Weighted read / write separation
3. Single thread read / try
4. Centralized data source information management and dynamic change
5. Detached stable JBoss data source
6. Support for MySQL and Oracle databases
7. Based on JDBC specification, It's easy to expand the data source supporting the implementation of JDBC specification
8. No server, client jar form, application directly connected database
9. Read and write times, concurrency, process control, dynamic change
10. Analyzable log printing, log flow control, dynamic change
4. 1、 General idea of installation:
download the zip package from the official website
unzip and to disk C
configure environment variables
modify the configuration file my-default.ini in the MySQL root directory
initialize mysql, install MySQL and start MySQL service
modify the password of the root user
successfully installed< 2. Specific steps
1. Download the installation package
2. Copy to Disk C:
after decompression, the folder to Disk C: C: & # 92; Program Files\ mysql It is recommended that the folder name be simpler
3. Configure environment variables:
Computer - & gt; Right click - & gt; Advanced system settings - & gt; Advanced - & gt; Environment variables - & gt; Modify path,
change the address column "C: & # 92; Program Files\ mysql\ bin;” Paste to the end,
note: different paths use English '' Separate, confirm, exit
4. Modify the configuration file my-default.ini in the MySQL root directory:
first, create a new data folder in the MySQL directory
secondly, edit the configuration file my-default.ini:
remove the #
in front of basedir and dataDir, and add the decompression path of MySQL: C: & #; Program Files\ MySQL
dataDir adds the data folder path of MySQL: C: &# 92; Program Files\ mysql\ data
finally, save and close. The following is a screenshot of my my-default.ini content:
5. Initialize mysql, install mysql, and start the MySQL service [this step is very important]:
My DOS command runs and the results are as follows:
C: &# 92; Program Files\ mysql\ bin> mysqld --initialize-insecure --user=mysql
C:\ Program Files\ mysql\ bin> mysqld install mysql --defaults-file=" C:\ Program File
s\ mysql\ my-default.ini"< br />Service successfully installed.
C:\ Program Files\ mysql\ bin> Net start MySQL
MySQL service is starting.
MySQL service has been started successfully
6. Modify the password of root user:
after installation, the password of root is empty by default, so it is recommended to modify< br />c:> mysql –u root
mysql> show databases; < br />mysql> use mysql;< br />mysql> UPDATE user SET password=PASSWORD(" 123456") WHERE user=' root';< br />mysql> FLUSH PRIVILEGES;< br />mysql> Quit
7. Success
so far, the installation is successful. It is recommended that the management tool use Navicat for MySQL x64
download the zip package from the official website
unzip and to disk C
configure environment variables
modify the configuration file my-default.ini in the MySQL root directory
initialize mysql, install MySQL and start MySQL service
modify the password of the root user
successfully installed< 2. Specific steps
1. Download the installation package
2. Copy to Disk C:
after decompression, the folder to Disk C: C: & # 92; Program Files\ mysql It is recommended that the folder name be simpler
3. Configure environment variables:
Computer - & gt; Right click - & gt; Advanced system settings - & gt; Advanced - & gt; Environment variables - & gt; Modify path,
change the address column "C: & # 92; Program Files\ mysql\ bin;” Paste to the end,
note: different paths use English '' Separate, confirm, exit
4. Modify the configuration file my-default.ini in the MySQL root directory:
first, create a new data folder in the MySQL directory
secondly, edit the configuration file my-default.ini:
remove the #
in front of basedir and dataDir, and add the decompression path of MySQL: C: & #; Program Files\ MySQL
dataDir adds the data folder path of MySQL: C: &# 92; Program Files\ mysql\ data
finally, save and close. The following is a screenshot of my my-default.ini content:
5. Initialize mysql, install mysql, and start the MySQL service [this step is very important]:
My DOS command runs and the results are as follows:
C: &# 92; Program Files\ mysql\ bin> mysqld --initialize-insecure --user=mysql
C:\ Program Files\ mysql\ bin> mysqld install mysql --defaults-file=" C:\ Program File
s\ mysql\ my-default.ini"< br />Service successfully installed.
C:\ Program Files\ mysql\ bin> Net start MySQL
MySQL service is starting.
MySQL service has been started successfully
6. Modify the password of root user:
after installation, the password of root is empty by default, so it is recommended to modify< br />c:> mysql –u root
mysql> show databases; < br />mysql> use mysql;< br />mysql> UPDATE user SET password=PASSWORD(" 123456") WHERE user=' root';< br />mysql> FLUSH PRIVILEGES;< br />mysql> Quit
7. Success
so far, the installation is successful. It is recommended that the management tool use Navicat for MySQL x64
5. How to cooperate with redis and MySQL
is to store MySQL data in memory through redis.
is to store MySQL data in memory through redis.
6. Leave contact information, not email, leave Qi riding e goose.
7. Unknown_Error
8. Dig an egg and watch the news by yourself. You are not a miner. You dig wool.
Hot content