How to manually split mysql Databases and mysql Databases

How to manually split mysql Databases and mysql Databases

Most Internet website applications use mysql as the database storage, which is limited to the bottleneck of mysql Single-host performance. To support larger capacity and larger access volumes, DBAs generally establish distributed clusters, let multiple mysql provide services together. The so-called mysql distributed cluster essentially splits the original data into multiple copies and stores them on multiple mysql databases. The application routes the data to the corresponding database shards through the middle layer to access the required data, the basic architecture 1 is shown in. The key point here is "split". How to split the database, according to the business scenario, you can generally adopt horizontal split and Vertical Split. Horizontal Split refers to sharding a large table according to certain rules and distributed in multiple mysql databases. Vertical Split refers to sharding based on business modules, distribute different modules in different mysql databases. Whether horizontal or vertical, the nature of migration and resizing is the same for the underlying O & M personnel. This article will take a specific example to explain in detail the specific steps of mysql database splitting.

Premise: the mysq cluster is deployed in the MM architecture. The Master and Slave adopt two-way replication, the Master provides external services, and the Slave serves as hot backup.

Assume that databases A and B exist on the instance. Currently, the performance bottleneck of A single mysql instance is limited and the instance needs to be resized.

Objective: To split database B so that database A and database B occupy physical machines separately, as shown in Figure 2

Implementation steps:

1. Build a slave Database

Mysql can be used to build a standby database in two ways: Logical backup (mysqldump) or physical backup (extrabackup ). Because we need to split one of the databases, it is appropriate to select mysqldump.

Mysqldump-uxxx-pxxx-h ip_addr-P port -- databases B mysql -- master-data = 2 -- single-transaction -- default-character-set = xxx>/u01/bak/B _dump. SQL 2>/u01/bak/B _dump.log &

Note:

1) Parameters

-- Master-data = 2, -- single-transaction are used together. The global read lock will only add a short period of time at the beginning of dump. By setting the repeatable read isolation level, ensure that the data at the beginning of the transaction is read, the consistent data is obtained, and the Position (File, Position) is displayed at the beginning of the backup File ).

2) Why back up the mysql database?

This is because the metadata information of the database is stored in mysql, such as table definitions and user information. Therefore, you need to back up the metadata together.

2. Check whether the backup is successful

Check whether dump complete exists at the end of/u01/bak/B _dump. SQL. Check whether the/u01/bak/B _dump.log file is output abnormally.

3. Import the backup data to the new machine.

Mysql-uroot </u01/bak/B _dump. SQL> B _import.log 2> & 1 &

4. Import incremental data

1) because the old database has two databases A and B, and the new database only has database B, an error is inevitable when incremental data is obtained through replication, therefore, you need to set the copy filter parameter for the new database before importing, replicate-do-db

Replicate-do-db = mysql Replicate-do-db = B

2) create a replication relationship between the new database and the old database. here we need to use the information (File, Position) obtained in step 1)

CHANGE master TO master_host = xxx, master_port = xxx, master_user = 'slave ', master_password = 'slave ', Master_log_file = File, master_log_pos = Position;

5. Wait for synchronization between the new database and the old database. The replication structure of the new database and the old database is as follows:

6. Switch

1) set New M to writable, and combine Old M and New M to form a dual-M architecture.

Note: Red represents the replication change for this operation.

2) notify the application to switch the traffic of database B to the New Master. Because the middleware details are set here, different companies adopt different middleware, which is not described here.

3) After all traffic of database B is switched to the New Master, check whether there is any traffic access to database B in the Old Master. check whether there is any traffic access to database B. Adjust the replication structure.

Note: Check whether there is any traffic. You can use show processlist to check whether there is any connection.

7. After switching, disconnect the replication between the New Master and the Old Master.

8. aftercare

Clean up database B of the Old Master and release disk space.

Mysql sharding database now has many tables in a database under my Mysql. How can I migrate old tables to another database?

Directly go to the mysql database directory.
In linux, It is mysql/var/database name/table name .*
In windows, it is mysql \ data \ Database Name \ table name. *
Copy it out. Of course, it is best to interrupt mysql first.

Han shunping teaches you how to use mysql Databases

This is an example of oracle.
Www.ixueyun.com/...4.html