Bii o ṣe le Ṣeto MariaDB (Master-Slave) Idapọ ni CentOS/RHEL 7 ati Debian 8/9

Paapaa nigbati diẹ ninu awọn eniyan IT ba gbọ gbolohun naa “ẹda data“ Lakoko ti o jẹ otitọ ni diẹ ninu iye, ọpọlọpọ diẹ sii wa si isọdọtun data ju imọran ti o wọpọ ti nše afẹyinti data ati wiwa data.

Laarin awọn anfani miiran ti ẹda data ni ipilẹ oluwa-ẹrú a le darukọ:

Awọn afẹyinti le ṣee ṣe lori olupin ẹrú laisi ni ipa (ati ni ipa nipasẹ) awọn iṣẹ kikọ ni oluwa.
Awọn iṣẹ ipanilaya orisun (gẹgẹbi onínọmbà data) le ṣee ṣe lori ẹrú laisi ni ipa iṣẹ oluwa.

Ninu nkan yii a yoo ṣe alaye bi o ṣe le ṣeto atunṣe oluwa-ẹrú ni MariaDB 10.1. Bi o ṣe lodi si atunse alailẹgbẹ, MariaDB ṣafihan imọran ti Awọn ID Idarudapọ Agbaye (GTIDs) ni v10.0, eyiti ngbanilaaye lati yi ẹrú kan pada lati sopọ si ati tun ṣe lati ọdọ oluwa ti o yatọ ni irọrun. Lori oke ti eyi, a ti gbasilẹ ipinle ti ẹrú ni ọna ailewu-jamba (awọn imudojuiwọn si ipinlẹ ni a ṣe ni iṣowo kanna bi awọn imudojuiwọn si data).

Ti o ba n wa ẹda MySQL labẹ CentOS/RHEL 6, tẹle itọsọna yii Setup MySQL (Master-Slave) Replication on CentOS/RHEL 6

Fifi MariaDB 10.1 sori CentOS/RHEL 7 ati Debian 8/9

Ayika idanwo wa ni awọn ẹrọ atẹle (mejeeji ni CentOS 7):

Master: 192.168.0.18
Slave: 192.168.0.19

Lati fi ẹya tuntun ti MariaDB sori ẹrọ, a yoo nilo lati ṣafikun awọn ibi ipamọ wọn si awọn olupin wa. Ti o ba nlo ẹya ti atijọ ti MariaDB sọ 5.5, ronu igbesoke si ẹya 10.1 tuntun nipa lilo nkan isalẹ.

Upgrde MariaDB 5.5 si MariaDB 10.1

Ṣẹda faili ti a npè ni MariaDB.repo inu /etc/yum.repos.d pẹlu awọn akoonu wọnyi lori mejeeji Titunto si ati awọn eto Slave:

# MariaDB 10.1 CentOS repository list - created 2016-01-23 14:16 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Fipamọ faili naa ki o fi MariaDB sori awọn olupin mejeeji nipa lilo yum:

# yum update && yum install MariaDB-server MariaDB-client

Ṣafikun bọtini lati jẹrisi awọn idii ati ibi ipamọ MariaDB:

# apt-get install software-properties-common
# apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
# add-apt-repository 'deb [arch=amd64,i386] http://mirror.edatel.net.co/mariadb/repo/10.1/debian jessie main'

Akiyesi: Rọpo orukọ pinpin ati orukọ coden ninu laini afihan loke.

Fi sii nipa lilo pipaṣẹ-gba aṣẹ:

# apt-get update
# apt-get install mariadb-server

Lọgan ti o ba ti fi sii MariaDB, ṣiṣe ilana mysql_secure_installation ilana lori oluwa ati ẹrú mejeeji, jẹ ki a ṣeto ipilẹ data idanwo kan ninu ẹrọ oluwa.

Ṣiṣeto Aaye data MySQL Ayẹwo lori Titunto

A yoo ṣeto bayi ni olupin oluwa Awọn oṣiṣẹ ibi ipamọ data lati https://github.com/datacharmer/test_db (eyiti o pese ipilẹ data ti awọn igbasilẹ miliọnu 4 tan kaakiri awọn tabili mẹfa) ni awọn igbesẹ meji ti o rọrun:

Oniye ibi ipamọ naa ki o lo lati gbe ibi-ipamọ data apẹẹrẹ wọle si fifi sori ẹrọ MariaDB rẹ:

# git clone https://github.com/datacharmer/test_db
# cd test_db
# mysql < employees.sql

Tito leto MySQL Server lori Titunto

Lati tunto oluwa, tẹle awọn igbesẹ wọnyi:

Igbesẹ 1: Ṣatunkọ faili /etc/my.cnf . Labẹ apakan [mysqld] , ṣafikun awọn ila mẹrin wọnyi:

log-bin
server_id=1
replicate-do-db=employees
bind-address=192.168.0.18

ki o tun bẹrẹ MariaDB:

# systemctl restart mariadb

Igbesẹ 2: Wọle si olupin MariaDB bi gbongbo, ṣẹda ẹrú olumulo ki o fi awọn ifunni pataki ranṣẹ:

MariaDB [(none)]> CREATE USER 'slave'@'localhost' IDENTIFIED BY 'SlavePassword';
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
MariaDB [(none)]> SHOW MASTER STATUS;

Ofin ti o kẹhin (SHOW MASTER STATUS) pada ipo lọwọlọwọ ninu iwe alakomeji (awọn ipoidojuko deede ti o tọka ni deede aaye ti ọmọ-ọdọ yẹ ki o bẹrẹ ẹda lati:

Igbesẹ 3: Jade ni iyara MariaDB (pẹlu ijade; ) ati lo aṣẹ atẹle lati ya aworan kan ti ibi ipamọ data awọn oṣiṣẹ. Nigbati o ba lu Tẹ, iwọ yoo ti ọ lati tẹ ọrọ igbaniwọle fun gbongbo ti o ṣeto tẹlẹ nipasẹ mysql_secure_installation :

# mysqldump -u root -p employees > employees-dump.sql

Lẹhin ti ida silẹ ti pari, sopọ si olupin data lẹẹkansii lati ṣii awọn tabili ati lẹhinna jade:

MariaDB [(none)]> UNLOCK TABLES;
MariaDB [(none)]> exit;

Igbesẹ 4: Daakọ da silẹ si ẹrú naa:

# scp employees-dump.sql [email :/root/

Igbesẹ 5: Ṣiṣe ilana mysql_upgrade lati ṣe igbesoke awọn tabili eto (o yoo ti ọ lati tẹ ọrọ igbaniwọle MariaDB sii):

# mysql_upgrade -u root -p



Igbesẹ 6: Gba iṣẹ iṣẹ data laaye nipasẹ ogiriina:
# firewall-cmd --add-service=mysql
# firewall-cmd --add-service=mysql --permanent
# firewall-cmd --reload
Bayi jẹ ki a tunto ẹrú naa.
Tito leto MySQL Server lori Ẹrú
Lati tunto ẹrú naa, tẹle awọn igbesẹ wọnyi:
Igbesẹ 1: Ṣẹda akọọlẹ lati ṣe awọn iṣẹ ṣiṣe ẹda. Sopọ si olupin MariaDB agbegbe pẹlu:
# mysql -u root –p
ki o tẹ ọrọ igbaniwọle ti o ṣeto tẹlẹ.
Igbesẹ 2: Lọgan ti a ti sopọ si olupin data, ṣẹda olumulo ati ibi ipamọ data ofo kan, ati fifun awọn igbanilaaye:
MariaDB [(none)]> CREATE DATABASE employees;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON employees.* TO 'slave'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
Igbesẹ 3: Jade ni iyara MariaDB ki o gbe ẹrù ti a ṣẹda sinu olupin oluwa:
# mysql -u root -p employees < employees-dump.sql
Igbesẹ 4: Ṣatunkọ faili  /etc/my.cnf  lati fi idanimọ olupin kan si ẹrú labẹ apakan  [mysqld] . Akiyesi pe o nilo lati jẹ odidi oriṣiriṣi ju 1 lọ, bi a ṣe lo 1 ninu oluwa:
server_id=2
replicate-do-db=employees
Tun olupin olupin data bẹrẹ:
# systemctl restart mariadb
Igbesẹ 5: Ṣiṣe ilana  mysql_upgrade  lati ṣe igbesoke awọn tabili eto (o yoo ti ọ lati tẹ ọrọ igbaniwọle MariaDB sii):
# mysql_upgrade -u root -p
Igbesẹ 6: Ni kete ti a ti gbe nkan silẹ si ẹrú, a jẹ awọn igbesẹ diẹ lati bẹrẹ ẹda. Wọle si ibi ipamọ data ki o ṣiṣe awọn ofin wọnyi ni iyara MariaDB. San ifojusi pataki si awọn oniyipada  MASTER_LOG_FILE  ati  MASTER_LOG_POS , eyiti o yẹ ki o baamu awọn iye ti SHOW MASTER STATUS da pada ni Igbesẹ 2 ti “Tito leto oluwa” loke.
MariaDB [(none)]> CHANGE MASTER TO
  MASTER_HOST='192.168.0.18',
  MASTER_USER='slave',
  MASTER_PASSWORD='SlavePassword',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master-bin.000001',
  MASTER_LOG_POS=314,
  MASTER_CONNECT_RETRY=10,
  MASTER_USE_GTID=current_pos;
Igbesẹ 7: Bẹrẹ ẹrú naa ki o ṣayẹwo ipo rẹ laisi jijade iyara MariaDB:
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;
Kii ṣe pe o nilo eyi ni bayi, ṣugbọn ṣe akiyesi pe o le da ẹrú naa duro pẹlu:
MariaDB [(none)]> STOP SLAVE;
ti aṣẹ  SHOW STATUS\G;  aṣẹ ba pada awọn aṣiṣe eyikeyi. Lo awọn aṣiṣe wọnyẹn lati ṣoro laasigbotitusita ati lẹhinna ṣiṣe  Bẹrẹ ẸRỌ;  lati ṣe idanwo lẹẹkansi.
Idanwo MySQL/MariaDB Database Idahun data
Jẹ ki a ṣafikun igbasilẹ si tabili awọn oṣiṣẹ ni olupin oluwa:
MariaDB [(none)]> INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (500000, '1983-07-12', 'Dave', 'Null', 'M', '2014-12-12');
Lẹhinna rii daju pe iyipada yii tun ṣe ni ẹrú:
MariaDB [(none)]> USE employees;
MariaDB [(none)]> SELECT * FROM employees WHERE emp_no=500000;
Bi o ti le rii, atunse n ṣiṣẹ ni deede lati ọdọ oluwa si ẹrú.
Akopọ
Ninu nkan yii a ti ṣalaye bawo ni a ṣe le fi ẹya tuntun ti MariaDB sori ẹrọ ni CentOS/RHEL 7 ati Debian 8/9, ati jiroro lori bawo ni a ṣe le ṣeto atunkọ ẹrú pẹlu awọn GTID. Fun alaye diẹ sii, o le fẹ tọka si Itọsọna Itọsọna MariaDB, ati ma ṣe ṣiyemeji lati kan si wa nipa lilo fọọmu ti o wa ni isalẹ ti o ba ni awọn ibeere tabi awọn asọye.