Otu esi edozi MariaDB (Master-Slave) mmegharị na CentOS/RHEL 7 na Debian 8/9

Ọbụlagodi mgbe ụfọdụ ndị IT nụrụ nkebi ahịrịokwu ahụ “Ntugharị nchekwa data“, ha na-ejikọkarị ya na mkpa ọ dị inwe ọtụtụ mbipụta nke otu ozi iji zere mfu data n'ọnọdụ ọdịda ngwaike ma ọ bụ mmebi data. Ọ bụ ezie na nke ahụ bụ eziokwu n'ókè ụfọdụ, enwere ọtụtụ ihe na ntinye nchekwa data karịa echiche nkịtị nke ịkwado nchekwa data na nnweta data.

N'ime uru ndị ọzọ nke ntinye nchekwa data na ntọala nna ukwu-ohu anyị nwere ike ikwu:

  1. Enwere ike ime nkwado ndabere na mpaghara nkesa ohu na-enweghị emetụta (yana emetụta ya) ọrụ ide na nna ukwu.
  2. Enwere ike ịrụ ọrụ kpụ ọkụ n'ọnụ (dịka nyocha data) n'ahụ ohu ahụ n'emetụtaghị ọrụ nna ya ukwu.

N'isiokwu a, anyị ga-akọwa otú e si emepụta nsụgharị nna-ukwu na MariaDB 10.1. N'adịghị ka nsụgharị kpochapụwo, MariaDB webatara echiche nke Global Transaction IDs (GTIDs) na v10.0, nke na-enye ohere ịgbanwe ohu iji jikọọ na imepụtaghachi site na nna ukwu dị iche iche n'ụzọ dị mfe. N'elu nke ahụ, a na-edekọ ọnọdụ nke ohu ahụ n'ụzọ na-adịghị mma (mmelite na steeti na-eme n'otu azụmahịa dị ka mmelite na data).

Ọ bụrụ na ị na-achọ nsụgharị MySQL n'okpuru CentOS/RHEL 6, soro ntuziaka a Setup MySQL (Master-Slave) Replication on CentOS/RHEL 6

Ịwụnye MariaDB 10.1 na CentOS/RHEL 7 na Debian 8/9

Gburugburu ule anyị nwere igwe ndị a (ha abụọ bụ CentOS 7):

Master: 192.168.0.18
Slave: 192.168.0.19

Iji wụnye ụdị MariaDB kachasị ọhụrụ, anyị ga-achọ ịgbakwunye ebe nchekwa ha na sava anyị. Ọ bụrụ na ị na-eji ụdị ochie nke MariaDB kwuo 5.5, tụlee ịkwalite ụdị 10.1 ọhụrụ site na iji isiokwu dị n'okpuru.

  1. Welite MariaDB 5.5 gaa na MariaDB 10.1

Mepụta faịlụ aha ya bụ MariaDB.repo n'ime /etc/yum.repos.d yana ọdịnaya ndị a na sistemụ Master na 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

Chekwaa faịlụ ma wụnye MariaDB na sava abụọ ahụ site na iji yum:

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

Tinye igodo iji nyochaa ngwugwu yana ebe nchekwa 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'

Cheta na: Dochie aha nkesa na codename n'ahịrị ahụ akọwapụtara n'elu.

Wụnye site na iji apt-nweta iwu:

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

Ozugbo i tinyechara MariaDB, mee usoro mysql_secure_installation na nna ukwu na ohu, ka anyị guzobe nchekwa data nlele n'ime igwe ukwu.

Ịtọlite Sample MySQL Database na Master

Ugbu a, anyị ga-edobe na nna ukwu ihe nkesa Ndị ọrụ nchekwa data sitere na https://github.com/datacharmer/test_db (nke na-enye ihe ndekọ data nke nde 4 gbasaa na tebụl isii) n'ime usoro abụọ dị mfe:

Mechie ebe nchekwa ahụ wee jiri ya bubata ihe nlele data na nrụnye MariaDB gị:

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

Ịhazi MySQL Server na Master

Iji hazie nna ukwu, soro usoro ndị a:

Nzọụkwụ 1: Dezie /etc/my.cnf faịlụ. N'okpuru ngalaba [mysqld], tinye ahịrị anọ ndị a:

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

ma malitegharịa MariaDB:

# systemctl restart mariadb

Nzọụkwụ 2: Banye na ihe nkesa MariaDB dị ka mgbọrọgwụ, mepụta ohu onye ọrụ ma kenye onyinye ndị dị mkpa:

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;

Iwu ikpeazụ (SHOW MASTER STATUS) na-eweghachi ọnọdụ dị ugbu a na ndekọ ọnụọgụ abụọ (nhazi nhazi nke na-egosi kpọmkwem ebe ohu ahụ kwesịrị ịmalite imepụtaghachi site na:

Kwụpụ 3: Wepụ ngwa ngwa MariaDB (yana wepụ;) wee jiri iwu na-esonụ were foto nke nchekwa data ndị ọrụ. Mgbe ị kụrụ Tinye, a ga-akpali gị pịnye paswọọdụ maka mgbọrọgwụ nke ị hibere na mbụ site na mysql_secure_installation:

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

Mgbe emechara mkpofu ahụ, jikọọ na sava nchekwa data ọzọ ka imeghe tebụl wee pụọ:

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

Nzọụkwụ 4: Detuo ihe mkpofu na ohu ahụ:

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

Kwụpụ 5: Gbaa usoro mysql_upgrade iji kwalite tebụl sistemụ (a ga-akpali gị itinye paswọọdụ mgbọrọgwụ MariaDB):

# mysql_upgrade -u root -p

Nzọụkwụ 6: Kwe ka ọrụ nchekwa data site na firewall:

# firewall-cmd --add-service=mysql
# firewall-cmd --add-service=mysql --permanent
# firewall-cmd --reload

Ugbu a, ka anyị hazie ohu ahụ.

Ịhazi MySQL Server na Ohu

Iji hazie ohu ahụ, soro usoro ndị a:

Ntụpụ ụkwụ 1: Mepụta akaụntụ iji rụọ ọrụ mmegharị ahụ. Jikọọ na sava MariaDB mpaghara na:

# mysql -u root –p

ma tinye paswọọdụ ị hibere na mbụ.

Nzọụkwụ 2: Ozugbo ejikọrọ na nchekwa data nkesa, mepụta onye ọrụ na nchekwa data efu, nyekwa ikike:

MariaDB [(none)]> CREATE DATABASE employees;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON employees.* TO 'slave'@'localhost' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;

Kwụpụ 3: Wepụ ngwa ngwa MariaDB wee buru ihe mkpofu emebere na sava ukwu:

# mysql -u root -p employees < employees-dump.sql

Ntụpụ ụkwụ 4: Dezie faịlụ /etc/my.cnf ka ekenye onye nkesa ihe NJ n'okpuru ngalaba [mysqld]. Rịba ama na ọ kwesịrị ịbụ ọnụọgụ dị iche karịa 1, dịka anyị jiri 1 na nna ukwu:

server_id=2
replicate-do-db=employees

Malitegharịa ekwentị nchekwa data:

# systemctl restart mariadb

Kwụpụ 5: Gbaa usoro mysql_upgrade iji kwalite tebụl sistemụ (a ga-akpali gị itinye paswọọdụ mgbọrọgwụ MariaDB):

# mysql_upgrade -u root -p

Nzọụkwụ 6: Ozugbo ebubatara ohu ahụ, anyị nwere naanị nzọụkwụ ole na ole iji malite imegharị ya. Banye na nchekwa data wee mee iwu ndị a na ngwa ngwa MariaDB. Lezienụ anya n'ụzọ pụrụ iche na mgbanwe MASTER_LOG_FILE na MASTER_LOG_POS, nke kwesịrị dakọtara na ụkpụrụ SHOW MASTER STATUS weghachiri n'ụkwụ 2 nke Configuring the master n'elu.

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;

Kwụpụ 7: Malite ohu ahụ wee lelee ọkwa ya na-apụghị ozugbo MariaDB:

MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUS\G;

Ọ bụghị na ị chọrọ nke a ugbu a, mana rịba ama na ị nwere ike iji:

MariaDB [(none)]> STOP SLAVE;

ma ọ bụrụ na iwu GOSỌ STATUS ohu\G; na-eweghachi mperi ọ bụla. Jiri mperi ndị ahụ dozie nsogbu wee gbaa START SLAVE; iji nwalee ọzọ.

Nwalee MySQL/MariaDB Database Replication

Ka anyị tinye ndekọ na tebụl ndị ọrụ na sava ukwu:

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');

Wee chọpụta na e megharịrị mgbanwe a n'ime ohu ahụ:

MariaDB [(none)]> USE employees;
MariaDB [(none)]> SELECT * FROM employees WHERE emp_no=500000;

Dị ka ị na-ahụ, mmegharị ahụ na-arụ ọrụ nke ọma site na nna ukwu gaa na ohu.

Nchịkọta

N'isiokwu a, anyị akọwala otu esi etinye MariaDB kacha ọhụrụ na CentOS/RHEL 7 na Debian 8/9, ma tụlere ka esi ahazi nsụgharị-ohu na GTID. Maka ozi ndị ọzọ, ị nwere ike ịtu aka na MariaDB Replication Guide, ma egbula oge ịkpọtụrụ anyị site na iji ụdị dị n'okpuru ma ọ bụrụ na ị nwere ajụjụ ma ọ bụ kwuo.