Teach you to manually MySQL to split the library

Most Internet Web applications use MySQL as a DB storage, limited to MySQL single-machine performance bottlenecks, in order to support greater capacity and greater access, DBAs generally through the establishment of distributed clusters, so that multiple MySQL to provide services together. The so-called MySQL distributed cluster, the essence is to split the original data into multiple copies, placed on multiple MySQL database storage, application through the middle tier routing to the corresponding database shards, access to the required data, the basic schema 1 is shown. The key point here is "demolition", how to open the library, according to the business scenario, you can generally take the horizontal split and vertical split. The so-called horizontal split refers to a large table according to a certain number of shards, distributed in multiple MySQL database; Vertical splitting refers to the distribution of different modules in different MySQL databases according to the Business module division. Whether it's horizontal splitting or vertical splitting, the nature of the migration expansion is the same for the bottom ops. This article will take a specific example to explain the specific steps of MySQL demolition.

Premise: MYSQ cluster deployment using mm architecture, master and slave with two-way replication, master external services, slave as a hot standby.

Hypothesis: There are libraries A and B on the instance, which is limited by the performance bottleneck of single-machine MySQL, and needs to be expanded.

Objective: To disassemble library B so that library A and library B occupy the physical machine separately, as shown in 2

Implementation steps:

1. Build a Repository

There are two main ways to build a repository for MySQL, logical backup (mysqldump) or physical backup (extrabackup). Since we need to split one of the libraries, it would be appropriate to choose 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 &

Description

1) Parameters

--master-data=2,--single-transaction These two parameters are used together, the global read lock will only add a short period of time when the dump starts, by setting the REPEATABLE read isolation level to ensure that the data at the beginning of the transaction is read, Gets the consistency data and displays the bit points (file,position) at the beginning of the backup file.

2) Why to back up MySQL library

this is because the metadata information of the database is stored in MySQL, such as the table definition, the user information, so you need to back up the past together.

2. Check if the backup was successful

See if there is a dump complete at the end of/u01/bak/b_dump.sql See if the/u01/bak/b_dump.log file is abnormal output

3. Import the backup to the new machine

Mysql–uroot</u01/bak/b_dump.sql>b_import.log 2>&1 &

4. Import increments

1) as the old Library has a A, B two libraries, the new library is only a C library, through the replication to obtain the increment, will inevitably lead to error, so before the import need to set the new library replication filtering parameters, Replicate-do-db

Replicate-do-db=mysql Replicate-do-db=b

2) The new library and the old Library to establish a replication relationship, where you need to use step 1 to obtain the bit information (file,position)

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 the new library to synchronize with the old library, so that the new library and the old library copy structure

6. Toggle

1) set new m to writable state and make the old m and new m a dual-m architecture

Note: Red represents the copy change of this operation

2) notify the app to switch B library traffic to new Master, as this is set to the details of the middleware, different companies use the same middleware, not described here

3) b Library traffic all switch to new master, check if old master also has B library traffic access, OK No, adjust the replication structure

remark: Check if there is still traffic, and see if there is a connection to verify through show processlist.

7. Switch off, disconnect new master and old master from replication

8. Rehabilitation

Cleans up the B library data of Old master and frees up disk space.