Shulou(Shulou.com)05/31 Report--

This article mainly explains "the location and use scene of MySQL data cloning". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the location and use scene of MySQL data cloning".

1. Product positioning:

Data cloning is efficient and secure to clone data from the database / table specified online, so as to quickly build a virtual environment and provide more efficient data delivery services. In terms of efficiency, it can support the business to extract data by itself, quickly build the environment at the minute level, and access the data through tools such as workbench. The whole process basically does not require manual operation of DBA. In terms of security, it supports data operation log audit, provides library / table access filtering, random virtual environment and temporary password delivery, and limits the duration of the virtual environment to ensure the security of data as much as possible.

two。 Applicable scenarios:

Currently, data cloning supports the following scenarios:

1) Quick view of online configuration data

2) extract the structure of the online table

3) Log data query, large online table

4) online SQL exception, quickly build a virtual environment for SQL optimization, pressure testing, etc.

5) specify changes to large tables and impact assessment of data operations

6) data patch merging, data operation based on business logic and data patch collation

There are a lot of details to consider in the whole implementation process, but it is solved by my colleagues and me in the design and implementation.

When it is time for delivery, we think that there is a key point to add, that is, the permissions of the database and the user, which means that the database that everyone can see and use should be different, because do some permission isolation, so next I will talk about the user rights design in the direction of data cloning.

The user rights design of data cloning is a basic function for business use. at present, the design of user rights can be based on the database level.

The implementation of permissions can be done in two phases:

1) in the data initialization stage, user groups can be initialized in batches, and some data can be obtained from the work order history.

2) customized configuration phase, fine-tuning the mapping relationship between database and users according to the change of business requirements and the adjustment of organizational structure.

Among them

Instance information and library information can be based on database baseline table mysql_db_baseline

User group information and user information can be based on the user table user_info

Database-user relation table needs to be created. The table name is: mysql_db_user_rel (id,ip_addr,db_port,db_name,user_id)

Four sub-functions need to be implemented:

1) Database-user relation mapping, which realizes the relationship between a single database and a single user, and is a record in the relational table.

2) Database-user relationship list. View related user list information through database dimension (IP+ port + database name)

3) Database-user group relationship mapping to realize the relationship between a single database and a single user group. The specific logic is as follows:

A) enter database information (IP+ port + database name), UserID

B) Associate user group information according to UserID

C) get the UserID list within the group based on the user group information

D) Loop the user list, establish a mapping based on the database-user relationship, and consider the filtering of duplicate records

4) instance list information, which is obtained according to the user UserID. The specific logic is as follows:

A) enter UserID

B) obtain the relevant database list in the database-user relationship table according to UserID, and further filter the IP+ port information of the instance

Thank you for your reading, the above is the content of "the location and use scene of MySQL data cloning". After the study of this article, I believe you have a deeper understanding of the location and use scene of MySQL data cloning, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.