Arxiv

Archive for İ

incremental backup-innobackupex

incremental backup nədir?
increment yəni davam etmə, artırma.
Deyək ki, biz bir Bazar günü full backup almışıq full backup həcmi olub bizdə 50GB. Adlandırırıq bunu base_backup.
Bazar ertəsi biz full backup almağın bir mənası var mı? incremental backup məhz burda kömək olur.Yəni biz yalnız bazar günündən bazar ertəsinə qədər olan dəyişikliklərin backup-ını alırıq. bunu da adlandırırıq incremental_backup_1 və həcmi olur 5GB.
Daha sonra Çərşənbə axşamı bazar ertəsindən həmin vaxta qədər baş vermiş dəyişikliklərin backup-ını alırıq onu da adlandırıq incremental_backup_2 , həcmi 3GB və.s bu şəkildə irəliləyirik. Və restore gərəkli olsa bütün increment-ləri base_backup-da birləşdirib restore edirik.
Bu şəkildə biz hem storage-ə qənaət edirik həm də backup alma müddətinə.
Base backup:

[root@sh ~]# innobackupex --user=root --password=12345 /home/shahriyar/data/base_backup --no-timestamp
.
.
.
innobackupex: Backup created in directory '/home/shahriyar/data/base_backup'
innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 107
121229 14:41:26  innobackupex: completed OK!

əgər base_backup direktoriyasındakı xtrabackup-checkpoints faylına baxsaq o zaman full backup-ın alındığından tam əmin olarıq:

QEYD: full physical backup -dan fərqli olaraq incremental backup almaq məqsədilə biz prepare etmirik. Yəni əgər yuxarıdakı kimi full backup(base backup)-dan dərhal sonra biz prepare etsək o zaman incremental backup ala bilməyəcik.
prepare addımını biz yalnız restore lazım olanda edirik. Yəni deyək ki 2 incremental backup-dan sonra restore lazım olsa prepare edib , restore edirik

Sınaq üçün base backup(full backup)-dan sonra bir table yaradaq və ona 3 insert verək:

CREATE TABLE test_table2 (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(15) DEFAULT NULL,
  surname varchar(20) DEFAULT NULL,
  PRIMARY KEY (id)
);

mysql> insert into test_table2(name,surname) values('Elmar','Huseynov'),('Ziya','Bunyadov'),('Rail','Rzayev');
Query OK, 3 rows affected (0.26 sec)
Records: 3  Duplicates: 0  Warnings: 0

test_table2 və onda olan 3 row base_backup-da yoxdur.
Davam edək.
İncremental backup 1:

[root@sh ~]# innobackupex --user=root --password=12345 --incremental /home/shahriyar/data/incremental_backup1 --incremental-basedir=/home/shahriyar/data/base_backup --no-timestamp
.
.
.
innobackupex: Backup created in directory '/home/shahriyar/data/incremental_backup1'
innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 633
121229 16:29:04  innobackupex: completed OK!

Yenə də xtrabackup-checkpoints faylına baxdıqda aydın görünür:

Yadımıza salaq ki, full backup-dan sonra biz bir table yaratdıq və 3 insert verdik, belə nəticə çıxarmaq olar ki bizim incremental_backup1 məhz bu dəyişikliyi özündə saxlayır.
incremental_backup1-dən sonra yenə sınaq üçün həmin table-a 3 əlavə insert verək:

mysql> insert into test_table2(name,surname) values('Fəxrəddin','Şahbazov'),('İsmət','Qayıbov'),('Tofiq','İsmayılov');
Query OK, 3 rows affected (0.18 sec)
Records: 3  Duplicates: 0  Warnings: 0

Və daha sonra da incremental_backup2-ni icra edək.
İncremental backup 2:

[root@sh ~]# innobackupex --user=root --password=12345 --incremental /home/shahriyar/data/incremental_backup2 --incremental-basedir=/home/shahriyar/data/incremental_backup1 --no-timestamp
.
.
.
innobackupex: Backup created in directory '/home/shahriyar/data/incremental_backup2'
innobackupex: MySQL binlog position: filename 'mysql-bin.000002', position 951
121229 16:44:46  innobackupex: completed OK!

xtrabackup-checkpoints faylına bir daha baxırıq.

Bu fayla 3 dəfə baxmışıq və hər dəfə to_lsn-də artma müşahidə etmişik. Bəs nədir bu LSN:
Each InnoDB page (usually 16kb in size) contains a log sequence number, or LSN. The LSN is the system version number for the entire database. Each page’s LSN shows how recently it was changed.

Bir daha ümumiləşdirmə aparaq. Deməli biz base_backup-dan sonra cədvəl yaratdıq və ora 3 insert etdik. bu 3 insert bizim incremental_backup1-dədir. incremental_backup1-dən sonra da 3 insert etdik və deməli son 3 insert-də incremental_backup2-dədir. Və son nəticədə əgər biz restore etmək istəsək o zaman teorik olaraq deyə bilərik bizdə 1 table və 6 row olmalıdır elə deyil mi? Gəlin sınayaq. Bunun üçün bütün aldığımız backup-ları prepare etməliyik.
DİQQƏT! bütün incremental backup-lar prepare olunduqdan sonra base_backup-da birləşdirilir və son nəticədə biz restore-u base_backup-dan edirik

Preparing base_backup:

[root@sh ~]# innobackupex --apply-log --redo-only /home/shahriyar/data/base_backup
.
.
.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
121229 17:09:50  InnoDB: Starting shutdown...
121229 17:09:51  InnoDB: Shutdown completed; log sequence number 5462136332
121229 17:09:51  innobackupex: completed OK!

Prepare and apply incremental_backup1 to base_backup:

[root@sh ~]# innobackupex --apply-log --redo-only /home/shahriyar/data/base_backup --incremental-dir=/home/shahriyar/data/incremental_backup1
.
.
.
121229 17:15:42  innobackupex: completed OK!

Prepare and apply incremental_backup2 to base_backup:

[root@sh ~]# innobackupex --apply-log /home/shahriyar/data/base_backup --incremental-dir=/home/shahriyar/data/incremental_backup2
.
.
.
121229 17:18:52  innobackupex: completed OK!

ən sonda da base_backup-ı bir daha prepare edirik və restore-a tam hazır vəziyyətə gətiririk.

[root@sh ~]# innobackupex --apply-log /home/shahriyar/data/base_backup
.
.
.
121229 17:21:41  innobackupex: completed OK!

Vəssəlam.backup-ımız restore üçün hazır vəziyyətə gətirilmişdir.

İndi belə çıxır ki əgər biz test üçün yaratdığımız cədvəli test_table2-ni drop etsək və aldığımız backup-dan restore etsək son nəticədə bizdə 6 row-luq test_table2 cədvəli olacaq elə deyil mi? Sınayaq:

mysql> drop table test_table2;
Query OK, 0 rows affected (0.25 sec)

mysql> select * from test_table2;
ERROR 1146 (42S02): Table 'backup_test.test_table2' doesn't exist

Restore mərhələsi:

[root@sh ~]# systemctl stop mysqld.service
[root@sh ~]# mkdir /tmp/mysql
[root@sh ~]# mv /var/lib/mysql/* /tmp/mysql
[root@sh ~]# ls /var/lib/mysql
[root@sh ~]# innobackupex --copy-back /home/shahriyar/data/base_backup
.
.
.
innobackupex: back to original InnoDB log directory '/var/lib/mysql'
innobackupex: Finished copying back files.
121229 17:28:55  innobackupex: completed OK!
[root@sh ~]# chown -R mysql:mysql /var/lib/mysql
[root@sh ~]# systemctl start mysqld.service

Və yoxlayaq:

mysql> select * from test_table2;
+----+-------------+-------------+
| id | name        | surname     |
+----+-------------+-------------+
|  1 | Elmar       | Huseynov    |
|  2 | Ziya        | Bunyadov    |
|  3 | Rail        | Rzayev      |
|  4 | Fəxrəddin   | Şahbazov    |
|  5 | İsmət       | Qayıbov     |
|  6 | Tofiq       | İsmayılov   |
+----+-------------+-------------+
6 rows in set (0.08 sec)

Təbriklər 6 row və test_table2 bərpa olunmuşdur 🙂

Beynəlxalq məsləhət:
Böyük proyektlərdəki təcrübələrdən belə nəticə çıxarmaq olar ki, əcnəbilər:
1. həftədə 1 dəfə məsələn bazar günləri full logical backup alırlar.
Həftənin digər günləri də binary log vasitəsilə incremental backup alırlar.
Making logical backup-binary log,mysqldump (part 2)
2. Həftədə 1 dəfə full logical backup-la bərabər həmçinin full physical backup alirlar.
Və həftənin digər günləri də bu yazıda göstərildiyi kimi incremental backup alırlar.
Və dolayısı ilə elə bir nəticə əldə edirlər ki, heç bir şəkildə zərrə qədər məlumat itgisi baş vermir. Misal üçün facebook:
Facebook making Hybrid Incremental MySQL Backups

Təşəkkürlər 🙂

Making full physical backup-innobackupex

Installing Xtrabackup on Fedora 17
innobackupex vasitəsilə Hot MySQL backup alınmasına baxacıq. Yuxarıdakı linkdən Xtrabackup-ın install olunması var.
Onu qeyd etmək lazımdır ki, Xtrabackup əslində 3 tool-dan ibarətdir ibarətdir:
innobackupex — Perl-də yazılmiş script-dir. MyİSAM,İnnoDB,XtraDB cədvəllərin backup-ını almaqla full database backup edir. İnnoDB üçün xtrabackup-dan istifadə edir
xtrabackup — yalnızca İnnoDB və XtraDB backup alır
xbstream — new utility that allows streaming and extracting files to/from the xbstream format.

xtrabackup-ın özünün restore funksiyası olmadığı üçün biz backup-lar üçün innobackupex istifadə edəcik.

Backup-ın yaradılması:

[root@sh ~]# innobackupex --user=root --password=12345 /home/shahriyar/Data/base_backup --no-timestamp

base_backup adlı directory yaranacaq və /var/lib/mysql datadirectory-də olan hər şey backup olunacaq.
innobackupex özü bu datadirectory-ni my.cnf-dən oxuyur.
Komandanı işlətdikdən sonrakı output belə davam edir:

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona Inc 2009-2012.  All Rights Reserved.
.
.
121228 21:32:06  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/var/lib/mysql'
.
.
innobackupex: Backup created in directory '/home/shahriyar/Data/base_backup'
innobackupex: MySQL binlog position: filename 'mysql-bin.000009', position 107
121228 21:32:12  innobackupex: completed OK!

OK! o deməkdir ki, hər şey əla keçdi. bundan əlavə maraqlı məlumat da ən sonda print olunur mysql-bin.000009 bu binary log, backup-dan sonra yaradılır və bu o deməkdir ki point-time-recovery zamanı məhz bu binary log-dan istifadə edəcik.

Lakin bu hələ son deyil mütləq şəkildə biz bu backup-ı prepare etməliyik. Prepare məqsədi:
After creating a backup, the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.
Documentation-dan da göründüyü kimi backup yalnız prepare edildikdən sonra restore-a hazır vəziyyətə gəlir:

[root@sh ~]# innobackupex --apply-log /home/shahriyar/Data/base_backup
.
.
.
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
121228 21:38:06  InnoDB: Starting shutdown...
121228 21:38:10  InnoDB: Shutdown completed; log sequence number 5462135820
121228 21:38:10  innobackupex: completed OK!

Restore etməyin bir mənası olması üçün hər hansı bir schema-nı drop edək:

mysql> drop schema sakila;
Query OK, 23 rows affected (0.95 sec)

mysql> use sakila;
ERROR 1049 (42000): Unknown database 'sakila'

indi isə restore edək:

[root@sh ~]# innobackupex --copy-back /home/shahriyar/Data/base_backup
IMPORTANT: Please check that the copy-back run completes successfully.
           At the end of a successful copy-back run innobackupex
           prints "completed OK!".

Original data directory is not empty! at /bin/innobackupex line 575.

Yuxarıdakı ERROR-u alırıq. Səbəbi- full backup restore zamanı belə bir tələb var ki, datadirectory indiki halda /var/lib/mysql boş olmalıdır ki, heçnə overwrite olunmasın. Partial restore-da belə bir tələb yoxdur.
Çıxış yolu:

[root@sh ~]# systemctl stop mysqld.service
[root@sh ~]# mkdir /tmp/mysql
[root@sh ~]# mv /var/lib/mysql/* /tmp/mysql
[root@sh ~]# ls /var/lib/mysql

Və həqiqətən /var/lib/mysql boşaldıqdan sonra restore etsək:

[root@sh ~]# innobackupex --copy-back /home/shahriyar/Data/base_backup
.
.
innobackupex: Finished copying back files.
121228 21:57:06  innobackupex: completed OK!

Daha sonra da:

[root@sh ~]# chown -R mysql:mysql /var/lib/mysql
[root@sh ~]# systemctl start mysqld.service

Və drop olunmuş sakila schema-sına baxdıqda:

mysql> show schemas like 'sa%';
+----------------+
| Database (sa%) |
+----------------+
| sakila         |
+----------------+
1 row in set (0.00 sec)

sakila schema bərpa olundu təbriklər 🙂
Gələcək yazılar incremental backup-innobackupex, full physical backup-xtrabackup,incremental backup-xtrabackup və.s haqqında olacaq
Təşəkkürlər 🙂

Installing Xtrabackup on Fedora 17

Xtrabackup Percona tərəfindən yaradılmış MySQL Hot Backup tool-dur. Open Source-dur. Backup alarkən MySQL-i lock etmir və dünyada production səviyyədə istifadə olunur. MySQL Enterprise Backup-a oxşardır(bu məhsul Oracle tərəfində MySQL Enterprise Edition-la bərabər təklif olunur.Yəni pulludur)
İnstall edərkən bəzi problemlər oldu, sizdə də olmasın deyə yazıram.
Xtrabackup, Percona Release .rpm paketində gəlir. və 32-bit OS-lər üçün sonu i386.rpm istifadə etmək lazımdır. Percona release
Aşağıdakı komandaları ardıcıllıqla işlədək:
1.

[root@sh ~]# wget  http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm
--2012-12-28 13:50:15--  http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm
Resolving www.percona.com (www.percona.com)... 74.121.199.234
Connecting to www.percona.com (www.percona.com)|74.121.199.234|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 6115 (6.0K) [application/x-redhat-package-manager]
Saving to: `percona-release-0.0-1.i386.rpm'

100%[======================================>] 6,115       19.5K/s   in 0.3s    

2012-12-28 13:50:16 (19.5 KB/s) - `percona-release-0.0-1.i386.rpm' saved [6115/6115]

2.

[root@sh ~]# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm
Retrieving http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm
Preparing...                ########################################### [100%]
   1:percona-release        ########################################### [100%]

3. bu addımda ERROR çıxdı

[root@sh ~]# yum list | grep percona
http://repo.percona.com/centos/17/os/i386/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found : http://repo.percona.com/centos/17/os/i386/repodata/repomd.xml
Trying other mirror.
Error: failure: repodata/repomd.xml from percona: [Errno 256] No more mirrors to try.

Biraz google-da gəzdikdən sonra son nəticədə aşkarladım ki, problem /etc/yum.repos.d/Percona.repo faylında imiş.
Faylın ilkin install olunmuş variantı belədir:

[percona]
name = CentOS $releasever - Percona
baseurl=http://repo.percona.com/centos/$releasever/os/$basearch/
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
gpgcheck = 1

Fayl-da aşağıdakı kimi dəyişiklik edirik:

[percona]
name = CentOS 6 - Percona
baseurl=http://repo.percona.com/centos/6/os/i386/
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
gpgcheck = 0

Və yuxarıdakı 3-cü addımı təkrarlayırıq:
3.

[root@sh ~]# yum list | grep percona
percona-release.i386                       0.0-1                        installed
percona-toolkit.noarch                     2.1.7-1                      @/percona-toolkit-2.1.7-1.noarch
.
.
.
percona-xtrabackup.i686                    2.0.4-484.rhel6              percona 
percona-xtrabackup-debuginfo.i686          2.0.4-484.rhel6              percona 
percona-xtrabackup-test.i686               2.0.4-484.rhel6              percona 

4-cü və sonuncu addım

[root@sh ~]# yum install xtrabackup
Loaded plugins: langpacks, presto, refresh-packagekit
Resolving Dependencies
--> Running transaction check
---> Package percona-xtrabackup.i686 0:2.0.4-484.rhel6 will be installed
--> Finished Dependency Resolution
.
.
.
Installed:
  percona-xtrabackup.i686 0:2.0.4-484.rhel6                                     
Complete!

Təşəkkürlər 🙂

Making logical backup-binary log,mysqldump (part 2)

Yazının 1ci hissəsi
Bəli 2ci yazıda mysqldump-la full logical backup alacıq, restore edəcik.
Və ən sonda da binary log ilə çox sadə point-in-time recovery edəcik.
Ilk öncə nümunə schema və cədvəlimizi yaradaq:

mysql> create schema backup_test character set=utf8;
Query OK, 1 row affected (0.12 sec)

mysql> use backup_test;
Database changed
mysql> create table test_table(
    -> id int not null auto_increment,
    -> name varchar(15),
    -> surname varchar(20),
    -> primary key(id)
    -> );
Query OK, 0 rows affected (0.20 sec

3 İnsert-imizi edək:

mysql> insert into test_table(name,surname) values('Shahriyar','Rzayev'),('Khatai','Rzayev'),('Elvin','Binyatov');
Query OK, 3 rows affected (0.18 sec)
Records: 3  Duplicates: 0  Warnings: 0

Cədvəlimizin halı:

mysql> select * from test_table;
+----+-----------+----------+
| id | name      | surname  |
+----+-----------+----------+
|  1 | Shahriyar | Rzayev   |
|  2 | Khatai    | Rzayev   |
|  3 | Elvin     | Binyatov |
+----+-----------+----------+
3 rows in set (0.00 sec)

Və deyək ki cədvəlimizin bu halında biz full backup etmək qərarına gəldik…(yalnızca bu cədvəl yox bütün cədvəlləri)
Full logical backup mysqldump vasitəsilə yerinə yetirilir…Amma ilk öncə binary log-larımızı araşdıraq:

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       126 |
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |      6969 |
| mysql-bin.000005 |       150 |
| mysql-bin.000006 |       710 |
+------------------+-----------+
5 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      710 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Bu outputlardan aydın olur ki,bizim 6 ədəd binary log var. Və hal-hazırda istifadə olunanı da mysql-bin.000006-dır.

indi isə full logical backup:

[root@sh ~]# mysqldump -uroot -p --all-databases --single-transaction --flush-logs --master-data=2 > /home/shahriyar/dumps/full_logical_backup.sql
Enter password: 

1. –all-databases — bütün schema-ları çıxardır.
2. –single-transaction — bir transaction başladır və hər şeyi bir transaction-lar birdəfəlik yerinə yetirir.
–flush-logs — çox faydalı bir opsiya. backup-dan sonra yeni binary log yaradır. bu o deməkdir ki full backup-dan sonrakı bütün əməliyyatlar həmin bu yeni yaranan binary log-da qeyd olunur.
–master-data=2 — yeni yaranmış binary log kordinatlarını comment şəklində .sql faylına qeyd edəcək. indiki halda full_logical_backup.sql-a.
Və həqiqətən də faylı açdıqda ən başda bunu görəcik:

Yoxladıqda görürük ki həqiqətən də yeni binary log-umuz yaranıb:

Və davam edirik…bizim test_table-a gəlin əlavə 3 ədəd də insert verək:

mysql> insert into test_table(name,surname) values('Senan','Quliyev'),('Faxri','Xalidov'),('Elvin','Dursunov');
Query OK, 3 rows affected (0.23 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test_table;
+----+-----------+----------+
| id | name      | surname  |
+----+-----------+----------+
|  1 | Shahriyar | Rzayev   |
|  2 | Khatai    | Rzayev   |
|  3 | Elvin     | Binyatov |
|  4 | Senan     | Quliyev  |
|  5 | Faxri     | Xalidov  |
|  6 | Elvin     | Dursunov |
+----+-----------+----------+
6 rows in set (0.00 sec)

Deməli belə, biz full backup aldıqdan sonra test_table cədvəlimizə əlavə 3 insert gedib. və son nəticədə bizdə 6 row olub. Burdan belə nəticə çıxarmaq olar ki, əgər faciə nəticəsində test_table drop olsa və sonra biz yaratdığımız .sql fayldan test_table-ı restore etsək bizim əlimizdə backup-dan əvvəlki 3 row olacaq elə deyil mi?
backup-dan sonrakı 3 row-nu isə birdəfəlik itirəcik? Qəşəng sualdı. itiriləcək row sayı 1000 olsa bəs onda necə?

Bir kənaraçıxma olsun bu: full logical backup almaq çox sadə görünsə də server-i yükləyə bilir.Mənim backup həcmim 517 mb oldu. Təbii ki production database-də bu 10 gb-lərlə ola bilər. İtiriləcək vaxtı nəzərə alın.
Bundan əlavə böyük .sql faylla işləmək olduqca çətindi və hər text editor bunu başarmır. Ən azından linux-da default olan gedit ümumiyyətlə donur. Mənim faylımı 20 dəqiqədən çox bir vaxtda aça bilmədi.
Bu əsasən o zaman problem olur ki, bizə bəlkə də full restore lazım olmadı bir cədvəli o boyda faylın içindən tapıb edit-ləmək təbii ki, əziyyətdir. Nə isə ki bunun üçün də bir çıxış yolu mövcuddur: GREP
SED

Və davam edirik. test_table-ı drop edirik:

mysql> drop table test_table;
Query OK, 0 rows affected (0.23 sec)

Indi isə full_logical_backup.sql fayl-ından yalnız test_table-ı restore edək.
ilk öncə sed vasitəsilə test_table-ın create-ni tapaq:

[root@sh dumps]# sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `test_table`/!d;q' full_logical_backup.sql
DROP TABLE IF EXISTS `test_table`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  `surname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

Və copy edib run edirik:

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(15) DEFAULT NULL,
  `surname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.19 sec)

grep vasitəsilə bizə lazım olan insertləri-i tapırıq və elə yerindəcə insert edirik:

[root@sh dumps]# grep 'INSERT INTO `test_table`' full_logical_backup.sql | mysql backup_test -u root -p
Enter password:

Və select:

mysql> select * from test_table;
+----+-----------+----------+
| id | name      | surname  |
+----+-----------+----------+
|  1 | Shahriyar | Rzayev   |
|  2 | Khatai    | Rzayev   |
|  3 | Elvin     | Binyatov |
+----+-----------+----------+
3 rows in set (0.00 sec)

Bəli ilk 3 row var amma digər son 3 row-nu itirmək istəmirəm deyirsinizsə. Çarəsi binary log
binary log-un aktiv edilməsi haqqında artıq yazım var:MySQL binary log-un aktiv edilməsi

Digər 3 row-nu da aşağıdakı qaydada bərpa edə bilərik. İlk öncə bizə lazım olan binary log-un içinə baxaq.bunun mysqlbinlog-dan istifadə olunur. və əgər nəzər alsaq ki bizə backup-dan sonrakı 3 insert lazımdır onu axtarıb tapmaq elə da çətin deyil:

[root@sh data]# mysqlbinlog mysql-bin.000008
.
.
# at 210
#121227 18:55:14 server id 1  end_log_pos 383 	Query	thread_id=2	exec_time=0	error_code=0
use backup_test/*!*/;
SET TIMESTAMP=1356620114/*!*/;
insert into test_table(name,surname) values('Senan','Quliyev'),('Faxri','Xalidov'),('Elvin','Dursunov')
/*!*/;
# at 383
#121227 18:55:14 server id 1  end_log_pos 410 	Xid = 9284
COMMIT/*!*/;
.
.

Burda əsas yadda saxlamalı olduğumuz at 210 və at 383…Yəni bizim restore-umuz başlıyacaq 210-dan bitəcək 383-də!

[root@sh data]# mysqlbinlog --start-position=210 --stop-position=383 /var/lib/mysql/data/mysql-bin.000008 | mysql -u root -p
Enter password: 

İnanmağınız üçün yoxlayaq:

mysql> select * from test_table;
+----+-----------+----------+
| id | name      | surname  |
+----+-----------+----------+
|  1 | Shahriyar | Rzayev   |
|  2 | Khatai    | Rzayev   |
|  3 | Elvin     | Binyatov |
|  4 | Senan     | Quliyev  |
|  5 | Faxri     | Xalidov  |
|  6 | Elvin     | Dursunov |
+----+-----------+----------+
6 rows in set (0.01 sec)

Sizi canı-könüldən təbrik edirəm itirilmiş 3 row bərpa olunmuşdur 🙂

Təbii ki bu binary log-un istifadəsinin sadə misalı idi. Amma elə burdan da görünür ki bu necə vacib bir məsələdir.
Binary log, Physical (Raw) backup, incremental backup, XtraBackup tool haqqında daha ətraflı yazılar olacaq. inşallah…
Hələlik isə beynəlxalq məsləhət. Production database-də yalnızca logical backup-la kifayətlənirsinzə,(Bizdə çox zaman bunu php script-lər vasitəsilə yerinə yetirirlər) O zaman heç olmasa mysqldump-dan istifadə edin.
Amma qətiyyən məsləhət görülməyən bir şeydir bu! Yalnızca logical backup yuxarıdakı sadə misalda göstərildiyi kimi məlumatların itirilməsinə gətirib çıxarır!
Mütləq şəkildə binary log-dan istifadə edin…məsələn həftədə 1 dəfə logical backup alın hər gün isə binary log ilə incremental backup alın. Yəni deyək ki, bazar günü full logical backup aldınız. Bazar ertəsi binary log1, bazar ertəsi binary log2, çərşənbə axşamı binary log3 və.s şəklində increment edin.
Ya da ki, hər 3 gündən bir full logical backup alın daha sonra binary log ilə increment edin və.s
Bu və digər metodlar haqqında yazılar olacaq.

Təşəkkürlər 🙂

Making logical backup-mysqldump (part 1)

Bu yazı 2 hissədən ibarət olub full logical backup almaq haqqındadır. Bu məqsədlə mysqldump-dan istifadə edəcik.
Ümumiyyətlə gələcək üçün biz belə qəbul edəcik ki, database-imizdə yalnız və yalnız İnnoDB table-lar yerləşir.
İrəliki vaxtlarda Physical (Raw) backup,incremental backup,point-in-time recovery və.s kimi mövzular üçün bu əsas şərtlərdən biridir. Və ümumiyyətlə öz məlumat təhlükəsizliyimiz məqsədilə MyİSAM-dan bir dəfəlik əl çəkməyiniz məsləhət görülür.

Mən yoxladım və gördüm ki, özümdə çoxlu MyİSAM table-lar mövcuddur. Biz bu table-ları alter edib İnnoDB-yə çevirmək fikrindəyik.
İlk öncə hansı cədvəllər-in engine-ı MyİSAM-dı ona baxaq. Sözsüz ki bizim bir-bir cədvəllərin engine-na baxası halımız yoxdu. Əgər belə ürəkdən bunu istəyirsinzə show create table sizin_table_ad -la baxa bilərsiz.

Lakin daha professional yanaşma üçün biz istifadə edəcik information_schema-dan.
MySQL installation-la birlikdə 3 schema gəlir: performance_schema, mysql, information_schema.
Beləliklə MyİSAM table-ları tapaq:

mysql> use information_schema;
Database changed

mysql> select table_name from tables where engine='MyISAM';
+---------------------------+
| table_name                |
+---------------------------+
| COLUMNS                   |
| EVENTS                    |
| PARAMETERS                |
| PARTITIONS                |
| PLUGINS                   |
.
.
| max_sales_by_customer     |
| product_codes             |
| timestamp_check           |
| film_text                 |
+---------------------------+
51 rows in set (1.36 sec)

51 cədvəlimiz var imiş lakin output-dan da hiss elədiyimiz kimi bəzi cədvəllər yuxarıda qeyd olunan 3 schema-ya aiddi!
Onları ataq:

mysql> select table_name from tables where engine='MyISAM' and table_schema not in('mysql','information_schema','performance_schema');
+-----------------------+
| table_name            |
+-----------------------+
| aircraft              |
| aircrafttype          |
| class                 |
| flight                |
| flightclass           |
| flightdep             |
| p                     |
| pax                   |
| route                 |
| stats                 |
| test_85               |
| Product_Codes         |
| books                 |
| creditcards           |
| enum_test             |
| limit_test            |
| max_sales_by_customer |
| product_codes         |
| timestamp_check       |
| film_text             |
+-----------------------+
20 rows in set (0.01 sec)

20 cədvəlimiz var ki, onlar MyİSAM cədvəllərdir.
Təbii ki 20 ədəd alter table yazmaq da biraz çətin və əziyyətli işdir.bu say 100 olsaydı onda necə?
Bu məqsədlə procedure yazmışam, müəllif hüquqları mənə aiddi 😛
Procedure bütün MyİSAM cədvəlləri tapır və onları 1-1 alter edir. Procedure kodu:

delimiter $$

create procedure multiple_alter()
begin 
	declare v_table_schema varchar(30);
	declare v_table_name varchar(30);
	declare v_last_row_fetched int default 0;
	
	declare cursor1 cursor for
	select 
        a.table_schema, a.table_name from
        information_schema.tables as a
        where a.engine = 'MyISAM'
	and a.table_schema not in ('mysql' , 'information_schema','performance_schema');

	declare continue handler for not found set v_last_row_fetched=1;

	set v_last_row_fetched=0;
	open cursor1;
	cursor_loop: loop
	fetch cursor1 into v_table_schema,v_table_name;
			if v_last_row_fetched=1 then
					leave cursor_loop;
			end if;
			
			set @sql_v=concat('alter table ',v_table_schema,'.',v_table_name,' engine=innodb');
			prepare stmt from @sql_v;
			EXECUTE stmt;
			deallocate prepare stmt;
	end loop cursor_loop;
	close cursor1;
	set v_last_row_fetched=0;
	
end$$

Proseduru işlətdiyim anda bir dənə error çıxdı:

mysql> call multiple_alter();
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

Hansı table-lların alter olunmadığına baxmaq üçün yuxarıdakı query-ni bir də verək:

mysql> select      a.table_schema, a.table_name from     information_schema.tables as a     where a.engine = 'MyISAM' and a.table_schema not in ('mysql' , 'information_schema','performance_schema');
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| sakila       | film_text  |
+--------------+------------+
1 row in set (0.01 sec)

Burdan belə nəticə çıxır ki film_text cədvəlində FULLTEXT index istifadə olunub. Lakin MySQL 5.5 İnnoDB-də FULLTEXT dəstəyi olmadığı üçün yuxarıdakı ERROR çıxır(MySQL 5.6-da bu dəstək var).
Ona görə də geriyə qalan 1 cədvəlimizi özümüz alter edəcik:

mysql> alter table sakila.film_text drop index idx_title_description;
Query OK, 1000 rows affected (0.23 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql> alter table sakila.film_text engine=innodb;
Query OK, 1000 rows affected (0.27 sec)
Records: 1000  Duplicates: 0  Warnings: 0

Və yenidən MyİSAM cədvəl axtarırıq:

mysql> select      a.table_schema, a.table_name from     information_schema.tables as a     where a.engine = 'MyISAM' and a.table_schema not in ('mysql' , 'information_schema','performance_schema');
Empty set (0.00 sec)

Daha yoxdur.
Bütün cədvəllərimiz İnnoDB-dir.
Və bu Yazının 2ci hissəsi mysqldump-la full logical backup-ın alınması, restore olunması və point-time-recovery haqqında olacaq.
Təşəkkürlər 🙂

MySQL backup and recovery ümumi baxış

Backup-ın vacibliyindən danışmayacam. Təsadüfi deyil ki böyük proyektlərdə ayrıca backup\recovery team ayrılır. Və onların işi həqiqətən də recovery-yə yarayan backup-ları almaqdan ibarətdir. Yəni backup yalnızca mysqldump-la və yaxud da hər hansı php scritp-lə .sql və yaxud .txt faylı almaq deyildir.
Terminləri izah etməklə başlayaq.
1. Physical (Raw) backupPhysical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur. A backup that copies the actual data files
MySQL data directory-də olan bütün fayllar,həmçinin binary log-u və.s backup etmək metodudur.
Yuxarıda da deyildiyi kimi böyük database-lər üçün əlverişlidir.
3 üsulla alınır:
Hot — MySQL işləyə-işləyə isti-isti backup almaq üsulu.MySQL Enterprise Backup bu üsuldan istifadə edir.
Cold — MySQL shut-down olunur daha sonra backup alınır
Warm — MySQL işlək vəziyyətdə saxlanılır,lakin table-lar lock olur, connection-lar bağlanır və.s

2. Logical backupA backup that reproduces table structure and data, without copying the actual data files.
Adətən mysqldump vasitəsilə alınır.

Bəs yaxşı restore nədir? recovery nədir?
Restore — sadəcə alınan backup-ı geri bərpa etməkdir.
Recovery — hər hansı crash, bədbəxt hadisə və.s-dan sonra backup-ı bərpa etməklə bərabər MySQL-in stabil işləməsini qarantiliyəcək əməliyyatlar toplusudur. Innodb crach recovery buna misal ola bilər.

Backup strategiyasını düşünərkən çox vacib suallara özünüzdə cavab tapın:
1. Siz recovery zamanı hansısa məlumatı itirməyi gözə alırsınız mı? Yəni Bazar günü saat 00:30-da backup aldıqdan sonra üstündən 20 saat keçdikdən sonra nəsə baş versə və siz bazar günkü backup-dan istifadə etsəniz son 20 saat ərzində baş vermiş dəyişiklikləri itirməyi gözə alırsınız mi?
Əgər alırsınızsa işiniz asanddır. Yox əgər deyirsinizsə ki,xeyr əsla heç bir əməliyyat itirilməməlidir o zaman sizə point-in-time recovery lazım olacaq. Yəni adicə mysqldump-la və yaxud php script-lə aldığınız full backup(logical backup) sizə yardım etməyəcək.

2. Sizin recovery zamanınız və sürətiniz nə qədər olmalıdır? Sizin üçün 3-4 saat və bəlkə də daha artıq davam edən recovery vaxtı qəbul ediləndir mi? bu 3-4-5 saat ərzində user-ləriniz dözə bilərlər mi? işləriniz yarımçıq qalar mı? və.s

3. Siz nələri recover etmək istəyirsiz? Bəlkə bütün server,bütün schema, bütün table və yaxud sadəcə 1 row, 1 statement?

Tələblərinizi gözdən keçirdin və onu bir kənara aydın şəkildə yazın.

MySQL backup və recovery teknikləri(daha advance),tool-lar və.s haqqında yazılar olacaq…
Təşəkkürlər 🙂

MySQL binary log-un aktiv edilməsi

MySQL Binary log-u enable etmək istəyərkən mysql start olmadı. Başıma gələn bədbəxt hadisə sizə də olmasın deyə yazıram…
İlk öncə binary log nədir? From Documentation:
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data

Və binary log-un 2 əsas yerdə istifadə olunur. Yenə də from Documentation:
The binary log has two important purposes:

1. For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

2. Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

Bizim üçün indiki halda 2-ci bənd maraqlıdır. Qısaca onu deyim ki binary log vasitəsilə Point-in-Time (Incremental) Recovery icra olunur. MySQL backup and recovery haqqında mövzunu genişləndirəcəm təbii ki indiki halda sadəcə binary log-u necə enable edirlər ona baxırıq.

Deməli bunun üçün my.cnf faylına [mysqld] kataloqunun altından bunları yazırıq:

log_bin=/var/lib/mysql/data/mysql-bin
expire_logs_days=14
sync_binlog=1
binlog_format=row
log_bin_index=/var/lib/mysql/data/mysql-bin

1. log_bin=/var/lib/mysql/data/mysql-bin onu göstərir ki, bizim yaradılacaq binary log fayllarımızın adları mysql-bin-le başlayacaq. Məsələn mysql-bin.000003
2. log_bin_index=/var/lib/mysql/data/mysql-bin onu göstərir ki, mysql-bin.index adlı bir faylımız yaranacaq…bu fayl məqsədi: To keep track of which binary log files have been used, mysqld also creates a binary log index file that contains the names of all used binary log files..
3. expire_logs_days=14 hər 14 gündən bir köhnə binary log-lar avtomatik silinəcək.
4. sync_binlog=1 If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk. Default qiymət =0-dır bu isə crash zamanı itkilərə yol aça bilər,ona görə də =1 yazmışıq.
5. binlog_format=row In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected

my.cnf faylını Save edirik və mysql-i start etməyə çalışırıq.

[root@sh ~]# systemctl start mysqld.service
Job failed. See system journal and 'systemctl status' for details.

oops və dərhal log-a baxırıq:

/usr/libexec/mysqld: File '/var/lib/mysql/data/mysql-bin.000001' not found (Errcode: 13)
121225 22:29:02 [ERROR] Could not use /var/lib/mysql/data/mysql-bin for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
121225 22:29:02 [ERROR] Aborting

Errorcode: 13
Vaxt itirmədən perror utility vasitəsilə bunun nə olduğuna baxırıq:

[root@sh ~]# perror 13
OS error code  13:  Permission denied

Permission denied bu bizə ipucu verir ki, problem MySQL-də yox məhz Linux-dadır yani OS imkan vermir ki MySQL /var/lib/mysql/data/ direktoriyasına nəsə yazsın.
Həlli:

[root@sh ~]# chown mysql:mysql /var/lib/mysql/data -R

MySQL-i start edirik:

[root@sh ~]# systemctl start mysqld.service

Və həqiqətən də ilk mysql-bin.000001 binary log-umuz yaradıldı.. Təbriklər 🙂

[root@sh ~]# ls /var/lib/mysql/data
mysql-bin.000001  mysql-bin.index  sh-slow.log