Arxiv
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
və 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) backup — Physical 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 backup — A 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
LIMIT və OFFSET optimization
LIMIT və OFFSET bütün pagination-larda bolca istifadə olunan bir şeydir. Mənə elə gəlir ki hamı tanıyır bu nədir…
Biz yenə əlimizdə olan 2.5 mln row-luq Sales cədvəli ilə işləyəcəyik…Sales table download
Sales cədvəlimizdə 2500004 row var…Bu cədvəlin strukturu:
CREATE TABLE `sales` ( `SALES_ID` int(8) NOT NULL AUTO_INCREMENT, `CUSTOMER_ID` decimal(8,0) NOT NULL, `PRODUCT_ID` decimal(8,0) NOT NULL, `SALE_DATE` datetime NOT NULL, `QUANTITY` decimal(8,0) NOT NULL, `SALE_VALUE` decimal(8,0) NOT NULL, `DEPARTMENT_ID` decimal(8,0) DEFAULT '0', `SALES_REP_ID` decimal(8,0) DEFAULT '0', `GST_FLAG` decimal(8,0) DEFAULT NULL, `sale_status` char(1) DEFAULT NULL, `FREE_SHIPPING` char(1) DEFAULT '', `DISCOUNT` decimal(8,0) unsigned DEFAULT '0', PRIMARY KEY (`SALES_ID`), KEY `sales_cust_idx` (`CUSTOMER_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2500005 DEFAULT CHARSET=latin1
2 index-li sütunumuz var sales_id və customer_id…
Bu mövzu limit və offset-in optimization üsullarından bəhs edir. Siz özünüzə uyğununu seçib istifadə edin…
1-ci üsul bildiyimiz gündəlik istifadə olunandır:
mysql> select sales_id,sale_date from sales order by sales_id limit 2500000,4; +----------+---------------------+ | sales_id | sale_date | +----------+---------------------+ | 2500001 | 2005-11-25 22:56:24 | | 2500002 | 2005-11-25 22:56:58 | | 2500003 | 2005-11-25 23:12:30 | | 2500004 | 2012-11-22 18:09:00 | +----------+---------------------+ 4 rows in set (0.85 sec)
vaxt : 0.85 saniyə
show status-a baxaq:
mysql> show status Where variable_name like 'Handler%' or Variable_name like 'Created%'; +----------------------------+---------+ | Variable_name | Value | +----------------------------+---------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 0 | | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 1 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 2500003 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+---------+ 19 rows in set (0.00 sec)
Handler_read_next = 2500003
From Documentation:
Handler_read_next
The number of requests to read the next row in the data file. This value is high if you are doing a lot of table scans.
Explain-e baxaq:
mysql> explain select sales_id,sale_date from sales order by sales_id limit 2500000,4\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 2500004
Extra:
1 row in set (0.00 sec)
rows: 2500004
From Documentation
rows: Approximation of # of rows returned
İndi isə limit-in işləmə prinsipini izah edək: limit və offset bütün cədvəli oxuyur…və yalnız bu oxunma bitdikdən sonra öz işlərini görürlər. daha dəqiq bizim misal üçün 2500003 row generate olunur və yalnız bundan sonra 2500000 atılır və son 4 saxlanılır.
Məhz buna görədir ki, bu qədər vaxt gedir. əgər sales_id index-lənməmiş olsaydı onda daha çox vaxt gedərdi:
mysql> select sales_id,sale_date from sales_test order by sales_id limit 2500000,4; +----------+---------------------+ | sales_id | sale_date | +----------+---------------------+ | 2500001 | 2005-11-25 22:56:24 | | 2500002 | 2005-11-25 22:56:58 | | 2500003 | 2005-11-25 23:12:30 | | 2500004 | 2012-11-22 18:09:00 | +----------+---------------------+ 4 rows in set (4.35 sec)
vaxt : 4.35 saniyə
2-ci üsul eyni nəticəni daha tez alırıq:
select sales_id,sale_date from sales inner join (select sales_id from sales order by sales_id limit 2500000,4 ) as lim using(sales_id); +----------+---------------------+ | sales_id | sale_date | +----------+---------------------+ | 2500001 | 2005-11-25 22:56:24 | | 2500002 | 2005-11-25 22:56:58 | | 2500003 | 2005-11-25 23:12:30 | | 2500004 | 2012-11-22 18:09:00 | +----------+---------------------+ 4 rows in set (0.77 sec)
vaxt : 0.77 saniyə
Ümumiyyətlə deyə bilərik ki, limit və offset-lə işləyəndə problem yaradan yalnız və yalnız offset-dir. Aşağıdakı 2 üsul offset-sizdir.
3-cü üsul:
select sales_id,sale_date from sales where sales_id>2500000 order by sales_id limit 4; +----------+---------------------+ | sales_id | sale_date | +----------+---------------------+ | 2500001 | 2005-11-25 22:56:24 | | 2500002 | 2005-11-25 22:56:58 | | 2500003 | 2005-11-25 23:12:30 | | 2500004 | 2012-11-22 18:09:00 | +----------+---------------------+ 4 rows in set (0.00 sec)
vaxt : 0.0 saniyə
4-cü üsul:
select sales_id, sale_date from sales where sales_id between 2500001 and 2500004 order by sales_id; +----------+---------------------+ | sales_id | sale_date | +----------+---------------------+ | 2500001 | 2005-11-25 22:56:24 | | 2500002 | 2005-11-25 22:56:58 | | 2500003 | 2005-11-25 23:12:30 | | 2500004 | 2012-11-22 18:09:00 | +----------+---------------------+ 4 rows in set (0.00 sec)
vaxt : 0.0 saniyə
Nətər söhbətdi? 🙂 Bəli həqiqətən də dəhşətli imiş…0.85 sec, 0.77 sec və 0.0 sec…
Gəlin status-a da baxaq:
mysql> show status Where variable_name like 'Handler%' or Variable_name like 'Created%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Created_tmp_disk_tables | 0 | | Created_tmp_files | 0 | | Created_tmp_tables | 0 | | Handler_commit | 1 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_last | 0 | | Handler_read_next | 3 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 19 rows in set (0.00 sec)
Handler_read_next : 3. Bəli bizim yazdığımız son 2 üsul zamanı (3 və 4) query cəmi 3 row oxuyur…
Səbəbini araşdırarkən maraqlı bir fakta rast gəldim və google-lladım…Oracle-da “Index Range Scan” deyilən bir şey var imiş…Axtardığım bütün məlumatlar yalnız Oracle üçün çıxdı. MySQL-ə aid 1 dənə düz əməlli şey yenə documentation-da:
The Range Access Method for Single-Part Indexes
Explain-e baxsaq “Index Range Scan”-ı görmüş olarıq:
mysql> explain select sales_id, sale_date from sales where sales_id between 2500001 and 2500004 order by sales_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 4
Extra: Using where
1 row in set (0.00 sec)
type: range
range: KEY compared with =, <, , >=, IS NULL, ,BETWEEN, or IN([list]), the index is used to select rows that fall within a given range of index values
Bütün bunlardan belə nəticə çıxarmaq olar ki, böyük cədvəllər üçün offset-dən uzaq durun…Onun əvəzinə yuxarıdakı 2 üsuldan(3 və 4) birini istifadə edin və yaxud özünüz daha fərqlı bir üsul tapın
Təşəkkürlər 🙂
ALTER TABLE zamanı lock və performance problemi
Bu mövzumuz çoxlarımızın istifadə etdiyi ALTER TABLE komandasının maraqlı və eyni zamanda ziyanlı xüsusiyyətindən danışacıq. Təbii ki bu kiçik ölçülü cədvəllərdə bir çətinlik yaratmır amma olduqca böyük cədvəllər üçün baş ağrısı ola bilər. Hətta bir forumda oxuduğuma görə 1 gün və daha artıq davam edən ALTER var imiş…
ALTER TABLE nə edir? Bu komandanın iş prinsipi belədir. Məsələn bizdə table A var. A ibarətdir 3 sütundan və 100 row-dan. indi biz A-ya index əlavə etmək istəyirik(məsələn: alter table A add index(id)).
Bu zaman MySQL
*) A-nı lock edəcək(yəni A-ya heç bir insert,update,select getmiyəcək).
*) A-ya bənzər boş bir table yaradacaq (təbii ki A adlı).
*) A-da olan bütün məlumatları (indiki halda 100 row-nu) az öncə yaratdığı boş table-a copy\insert edəcək.
*) bütün insert-ləri bitirdikdən sonra köhnə A-ni drop edəcək (siləcək)
Qısa olaraq Alter bunu edir. Təbii ki bu 40-50 milyon row-luq cədvəllərdə dəhşətli bir vaxt aparacaq. Ən pis yanı da odur ki, əgər Alter 1 gün davam etməli olsa onda belə çıxır ki sizin table 1 gün select\update\insert -lərə bağlı olacaq.
İnanmıyanlara əyani olaraq göstərək…Sınaq üçün mənim əlimdə olan 2.5 milyon row-luq table olan sales-dən istifadə edəcik. Sales dump faylını burdan yükləyə bilərsiniz Sales table dump
Sales table-ı yaratdıqdan sonra və məlumatları insert etdikdən sonra original sales-ə dəyməmək məqsədilə. sales_test1 cədvəli yaradırıq.
Original sales strukturumuz budur:
CREATE TABLE `sales` ( `SALES_ID` int(8) NOT NULL AUTO_INCREMENT, `CUSTOMER_ID` decimal(8,0) NOT NULL, `PRODUCT_ID` decimal(8,0) NOT NULL, `SALE_DATE` datetime NOT NULL, `QUANTITY` decimal(8,0) NOT NULL, `SALE_VALUE` decimal(8,0) NOT NULL, `DEPARTMENT_ID` decimal(8,0) DEFAULT '0', `SALES_REP_ID` decimal(8,0) DEFAULT '0', `GST_FLAG` decimal(8,0) DEFAULT NULL, `sale_status` char(1) DEFAULT NULL, `FREE_SHIPPING` char(1) DEFAULT '', `DISCOUNT` decimal(8,0) unsigned DEFAULT '0', PRIMARY KEY (`SALES_ID`), KEY `sales_cust_idx` (`CUSTOMER_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2500005 DEFAULT CHARSET=latin1
Yeni sales_test1 cədvəlini yaradaq:
mysql> create table sales_test1 as select * from mysqlspp.sales; Query OK, 2500004 rows affected (1 min 11.30 sec) Records: 2500004 Duplicates: 0 Warnings: 0
sales_test1 cədvəl strukturumuz bu cürdür:
CREATE TABLE `sales_test1` ( `SALES_ID` int(8) NOT NULL DEFAULT '0', `CUSTOMER_ID` decimal(8,0) NOT NULL, `PRODUCT_ID` decimal(8,0) NOT NULL, `SALE_DATE` datetime NOT NULL, `QUANTITY` decimal(8,0) NOT NULL, `SALE_VALUE` decimal(8,0) NOT NULL, `DEPARTMENT_ID` decimal(8,0) DEFAULT '0', `SALES_REP_ID` decimal(8,0) DEFAULT '0', `GST_FLAG` decimal(8,0) DEFAULT NULL, `sale_status` char(1) DEFAULT NULL, `FREE_SHIPPING` char(1) DEFAULT '', `DISCOUNT` decimal(8,0) unsigned DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
Gördüyümüz kimi bizim yeni cədvəlimiz olan sales_test1-də original sales cədvəlində olan 2 məlumat yoxdur
PRIMARY KEY (`SALES_ID`) və KEY `sales_cust_idx` (`CUSTOMER_ID`).
Bunları əlavə etmək lazımdır ki, ALTER TABLE bayaqdan elə öz vaxtını gözləyir 😛
Bəli primary key əlavə edək:
mysql> alter table sales_test1 add primary key(sales_id); Query OK, 0 rows affected (1 min 27.46 sec) Records: 0 Duplicates: 0 Warnings: 0
İşləmə vaxtı 1 min 27.46 sec
Alter-lə eyni vaxtda bir dənə select versək:
mysql> select * from sales_test1 where sales_id=1; . . . 1 row in set (1 min 28.44 sec)
İşləmə vaxtı 1 min 28.44 sec
Gördüyümüz kimi bizim select işləməyə başlamaq üçün Alter-in bitməyini gözlədi.Yəni həqiqətən də Alter bizim table-ı lock etdi.
Sözsüz ki, bu arzu olunan hal deyil. Yəni bizə elə bir şey lazımdır ki, Alter zamanı digər istifadəçilər rahatlıqla öz update,insert və select-lərini verə bilsinlər.
Bunun üçün pt-online-schema-change tool-u mövcuddur. Bu cür tool-ların əsas məntiqi ondan ibarətdir ki, lock etmədən alter etmək.
pt-online-schema-change Percona Toolkit-in tərkibinə daxildir.
Daha ətraflı: pt-online-schema-change
Buna oxşar 3 əlavə tool var buyurun baxın:
1. Facebook Online Schema Change for MySQL
2. Large Hadron Migrator
3. oak-online-alter-table
Mən Percona Toolkit-dən istifadə edəcəm. İni isə pt-online-schema-change necə gözəl bir tool olduğuna baxin:
[root@sh ~]# pt-online-schema-change --progress time,1 --execute --user root --password 12345 --alter "add index(customer_id)" D=my_new_db,t=sales_test1 Altering `my_new_db`.`sales_test1`... Creating new table... Created new table my_new_db._sales_test1_new OK. Altering new table... Altered `my_new_db`.`_sales_test1_new` OK. Creating triggers... Created triggers OK. Copying approximately 2500273 rows... Copying `my_new_db`.`sales_test1`: 1% 00:58 remain Copying `my_new_db`.`sales_test1`: 2% 01:13 remain Copying `my_new_db`.`sales_test1`: 3% 01:13 remain Copying `my_new_db`.`sales_test1`: 4% 01:24 remain . . . . Copying `my_new_db`.`sales_test1`: 99% 00:02 remain Copying `my_new_db`.`sales_test1`: 99% 00:01 remain Copying `my_new_db`.`sales_test1`: 99% 00:01 remain Copying `my_new_db`.`sales_test1`: 99% 00:00 remain Copied rows OK. Swapping tables... Swapped original and new tables OK. Dropping old table... Dropped old table `my_new_db`.`_sales_test1_old` OK. Dropping triggers... Dropped triggers OK. Successfully altered `my_new_db`.`sales_test1`.
Bəli məncə aydın olur hər şey. Bu tool-un vasitəsilə ALTER TABLE-ın nələr etdiyini incə-incə izləmək olur…
Və həmçinin eyni zamanda verdiyimiz select-ə baxın:
mysql> select * from sales_test1 where sales_id=5; . . . 1 row in set (0.00 sec)
Gördüyümüz kimi heç bir lock baş vermədi…Məhz buna görə də Select-in müddəti 0.00 sec
MySQL-də Transaction və Lock haqqında daha ətraflı: Transactions and Locks
Ümidvaram ki, faydalı oldu… Təşəkkürlər 🙂
innodb_buffer_pool_size ERROR in Fedora 17
Deməli dünən gecə
innodb_buffer_pool_size -ı artırmaq istədikdə MySQL 5.5 start olunmadı və eləcə qaldı…Yani heç bir ERROR mesajı filan da verilmədiyindən biraz çətinlik yaratdı.
Məndə i3-350M processor və 3GB memory var. 32 bit-lik Fedora 17 istifadə edirəm. Zənnimcə 3 RAM üçün 32 bitlik OS yaxşı seçim olmalı idi.
Documentation-da deyilir ki, innodb_buffer_pool_size-ı memory-nin 80%-nə qədər artırmaq mümkündü. Bəlkə də bunu 64 bitlik sistemlər üçün deyiblər çünki x32-də bu % doğru deyil.
Mən də documentation-a qulaq asdım… və müəyyən elədim ki Fedora 17 mənim memory-mi tamamilə doğru görür:
[root@sh ~]# free -m
total used free shared buffers cached
Mem: 2950 1441 1508 0 65 622
-/+ buffers/cache: 754 2195
Swap: 4991 0 4991
Çünki məsələn 6 GB-lıq memory ilə x32 istifadə etdikdə max 3.5-ni görəcək ən azından belə şayələr var.
Həmçinin system manitor-da da hər şey yolunda görünür.
Bəli keçək işə..
2.9-un 80%-i=2.32. Yəni burdan belə nəticə çıxarmaq olar ki, innodb_buffer_pool_size=2.32GB verə bilərəm. Amma mən o qədər yox ilk öncə 1.9 GB verdim. Bu çox asan bir şeydir. my.cnf faylında [mysqld] kataloqunun altına innodb_buffer_pool_size=2040109465 (byte-la yazıram. 1.9 gb=2040109465 byte) yazırıq.
my.cnf default olaraq yerləşir /etc/my.cnf
Sizin my.cnf faylınızın harda yerləşdiyini öyrənmək üçün:
[root@sh ~]# mysql --verbose --help mysql Ver 14.14 Distrib 5.5.28, for Linux (i686) using readline 5.1 Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. . . . Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
my.cnf-i Yuxarıdakı directory-lərdən birində tapa biləcəksiz.
Bəli innodb_buffer_pool_size=2040109465-i eyd etdikdən sonra server-imizi stop\start edirik. Və dəhşətli mənzərə:
Terminaldan start edərkən belə:
[root@sh ~]# systemctl start mysqld.service Job failed. See system journal and 'systemctl status' for details.
MySQL Workbench-dən start edərkən isə
Checked server status: Server is stopped.
Yəni heç bir hadisə baş vermir.Çox pis təsir edir adama 😛
Dərhal log-lara nəzər salmaq lazımdı təbii ki. mysqld.log yerləşir /var/log/mysqld.log…
Log-da yazılan hər şeyi aydın edir:
121201 12:13:07 InnoDB: Fatal error: cannot allocate memory for the buffer pool
Bəli indi isə 1.8 GB verək. innodb_buffer_pool_size=1932735283 (yenə byte-la). Və yenə də eyni ERROR
Və ən nəhayət 1.7 GB verək. innodb_buffer_pool_size=1825361100
Nəhayət ki üzümüz güldü 😀
İndi isə ümumiləşdirmə aparaq.
2.9-un 80%-i=2.32 etdiyi halda bizim i3-350 M 3 GB-lıq x32 Fedora 17 sistemimiz İnnoDB buffer pool üçün yalnızca 1.7gb ayıra bildi.
1.7 isə 2.9-un 58.62%-dir. Deməli ki bu cür mənim sistemimə oxşar sistemlər üçün 80% yox ən çoxu 58% hesablamaq lazımdır. Əks halda buna oxşar crash-lar qaçınılmaz olacaq
Təşəkkürlər 🙂
Oracle/MySQL Blog 