Що таке Partitioning / Sharding / Replication

Dmytro Tus

Full Stack Web developer

Partitioning

Partitioning: Це процес розбиття великої бази даних на менші, легше керовані частини (партитії). Це може бути зроблено по горизонталі (розбиття по рядках) або по вертикалі (розбиття по стовпцях). Горизонтальне розбиття часто використовується для розподілу даних між різними серверами або регіонами.

Розбиття базу MySQL

Вертикальне розбиття бази даних (або partitioning) в MySQL включає в себе розділення таблиці на декілька менших таблиць, кожна з яких містить підмножину стовпців оригінальної таблиці.

Як зробити розбиття таблиці в mysql:

• Визначити, які стовпці треба винести в окрему таблицю. Це зазвичай стовпці, які не використовуються так часто, як інші.
• Створити нову таблицю з цими стовпцями.
• Додати стовпець зовнішнього ключа до нової таблиці, який посилається на оригінальну таблицю.
• Перемістити дані з оригінальної таблиці до нової.
• Видалити відповідні стовпці з оригінальної таблиці.

-- Створюємо нову таблицю
CREATE TABLE new_table (
    id INT AUTO_INCREMENT,
    column_to_move VARCHAR(255),
    original_table_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (original_table_id) REFERENCES original_table(id)
);

-- Переміщуємо дані
INSERT INTO new_table (column_to_move, original_table_id)
SELECT column_to_move, id
FROM original_table;

-- Видаляємо стовпець з оригінальної таблиці
ALTER TABLE original_table
DROP COLUMN column_to_move;

Sharding

Sharding: Це специфічний тип горизонтального розбиття, де дані розбиваються на менші частини (шарди) і розподіляються по різних базах даних. Кожна шарда може працювати незалежно від інших, що дозволяє збільшити продуктивність і масштабованість.

Приклад. В нас є велика таблиця в БД з користувачами. 

users
|---------------------|------------------|------------------|
|         id          |      email       |   is_premium     |
|---------------------|------------------|------------------|
|          1          |  user1@email.com |        0         |
|---------------------|------------------|------------------|
|          2          |  user2@email.com |        1         |
|---------------------|------------------|------------------|
|          3          |  user3@email.com |        0         |
|---------------------|------------------|------------------|
|          4          |  user4@email.com |        1         |
|---------------------|------------------|------------------|

Уявимо що ця таблиця має 100 000 000 записів. А програма у нас зроблена таким чином, що ми шукаємо користувачів по email тільки тоді коли вони мають статус premium.

Якщо це так то ми можемо розбити цю таблицю на 2 інші ( шарди ). Один буде мати користувачів де is_premium === 1 а інший де is_premium === 0

На мові SQL це буде виглядати приблизно так:

-- Створюємо таблиці для преміум та непреміум користувачів
CREATE TABLE users_premium LIKE users;
CREATE TABLE users_non_premium LIKE users;

-- Переміщуємо преміум користувачів до users_premium
INSERT INTO users_premium
SELECT * FROM users WHERE is_premium = 1;

-- Переміщуємо непреміум користувачів до users_non_premium
INSERT INTO users_non_premium
SELECT * FROM users WHERE is_premium = 0;

-- Опціонально: видаляємо дані з оригінальної таблиці
DELETE FROM users;

Тепер ми маємо 2 таблиці і тепер якщо нам потрібно знайти користувача по email то ми шукаємо його відразу в таблиці з premium користувачами

SELECT * FROM users_premium WHERE email LIKE '%foo%';

Replication

Replication: Це процес копіювання даних з одного сервера на інший, щоб забезпечити резервне копіювання, балансування навантаження, або для розподілу даних ближче до користувачів. Є два основних типи реплікації: master-slave (де один сервер є основним, а інші - копіями) і peer-to-peer (де всі сервери є однорідними і можуть приймати зміни).

Приклад реалізації master-slave в базі даних mysql в docker container.

#!/bin/bash

docker-compose down -v
rm -rf ./master/data/*
rm -rf ./slave/data/*
docker-compose build
docker-compose up -d

until docker exec mysql_master sh -c 'export MYSQL_PWD=111; mysql -u root -e ";"'
do
    echo "Waiting for mysql_master database connection..."
    sleep 4
done

priv_stmt='CREATE USER "mydb_slave_user"@"%" IDENTIFIED BY "mydb_slave_pwd"; GRANT REPLICATION SLAVE ON *.* TO "mydb_slave_user"@"%"; FLUSH PRIVILEGES;'
docker exec mysql_master sh -c "export MYSQL_PWD=111; mysql -u root -e '$priv_stmt'"

until docker-compose exec mysql_slave sh -c 'export MYSQL_PWD=111; mysql -u root -e ";"'
do
    echo "Waiting for mysql_slave database connection..."
    sleep 4
done

MS_STATUS=`docker exec mysql_master sh -c 'export MYSQL_PWD=111; mysql -u root -e "SHOW MASTER STATUS"'`
CURRENT_LOG=`echo $MS_STATUS | awk '{print $6}'`
CURRENT_POS=`echo $MS_STATUS | awk '{print $7}'`

start_slave_stmt="CHANGE MASTER TO MASTER_HOST='mysql_master',MASTER_USER='mydb_slave_user',MASTER_PASSWORD='mydb_slave_pwd',MASTER_LOG_FILE='$CURRENT_LOG',MASTER_LOG_POS=$CURRENT_POS; START SLAVE;"
start_slave_cmd='export MYSQL_PWD=111; mysql -u root -e "'
start_slave_cmd+="$start_slave_stmt"
start_slave_cmd+='"'
docker exec mysql_slave sh -c "$start_slave_cmd"

docker exec mysql_slave sh -c "export MYSQL_PWD=111; mysql -u root -e 'SHOW SLAVE STATUS \G'"

Робочий код є в репозиторії.

https://github.com/dmytrotus/docker-mysql-master-slave

Happy coding.🥳

Photo by Daniel Olah Unsplash