Archive

Archive for the ‘MySQL backup and recovery’ Category

Partial Data Recovery(restoring .ibd file)

Mövzumuz advance olub fiziki olaraq data-nın recover olunması haqqındadır. Qeyd edək ki bu yalnız MySQL 5.6.x versiyalar üçün geçərlidir.
Biraz teorik olaraq məsələni izah edək. MySQL-də cədvəllər database daxilində yerləşir. Cədvəlin öz strukturu .frm faylında, cədvəlin index və data-ları isə .ibd faylında yerləşir.
.ibd cədvəlin tablespace-dir. Və bu hər cədvəl üçün ayrılıqda yaradılır(Əgər MySQL innodb_file_per_table=1 olaraq start olunubsa)
Qeyd edək ki, əgər MySQL innodb_file_per_table=1 şəklində start OLUNMAYIBSA, cədvəlin bütün index və dataları system tablepspace-də yer alır. system tablespace isə shared tablespace olduğu üçün biz onun daxilindən yalnız 1 cədvəlin taplespace-ini çıxardıb restore edə bilmərik. Yəni partial(ayrıca cədvəl) recovery üçün ilkin şərt innodb_file_per_table=1-dir.
system tablespace haqqında qısa məlumat:
A small set of data files (the ibdata files) containing the metadata for InnoDB-related objects (the data dictionary), and the storage areas for the undo log, the change buffer, and the doublewrite buffer. Depending on the setting of the innodb_file_per_table, when tables are created, it might also contain table and index data for some or all InnoDB tables. The data and metadata in the system tablespace apply to all the databases in a MySQL instance.

Bütün bunları nəzərə alıb MySQL-imizi bütün proyektlərimizdə innodb_file_per_table=1 ilə start edirik.
Bunu əyani olaraq göstərək. Database və cədvəl yaradaq:

mysql> create database blog;
Query OK, 1 row affected (0.09 sec)

mysql> use blog;
Database changed
mysql> create table partial_test(id int not null);
Query OK, 0 rows affected (0.36 sec)

mysql> insert into partial_test() values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.27 sec)
Records: 5  Duplicates: 0  Warnings: 0

Və yoxlayaq:

[root@localhost ~]# cd /var/lib/mysql/blog
[root@localhost blog]# ls
db.opt  partial_test.frm  partial_test.ibd

Gördüyümüz kimi .frm və .idb faylları yaradılmışdır. Və bu da onu göstərir ki biz partial restore edə bilərik.

Bizim cədvəlimizdə olan məlumatlar:

mysql> select * from partial_test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

Və biz çox ənənəvi hal kimi .ibd faylını başqa bir direktoriyaya copy edək:

[root@localhost blog]# cp partial_test.ibd /home/sh

[root@localhost sh]# ls | grep partial*
partial_test.ibd

İndi də cədvəlimizi səhvən, çox kədərli hal kimi truncate edək:

mysql> truncate table partial_test;
Query OK, 0 rows affected (0.35 sec)

mysql> select * from partial_test;
Empty set (0.00 sec)

İddia edirik ki, əgər kopyaladığımız və içində datası olan .ibd file-ı geri qaytardıqda bizim cədvəlimizdə itirilmiş məlumatlar da geri dönəcək. Sınayaq.
Shutting down MySQL:

root@localhost sh]# service mysql stop
Shutting down MySQL....                                    [  OK  ]

Geri kopyalama:

[root@localhost sh]# cp partial_test.ibd /var/lib/mysql/blog
cp: overwrite `/var/lib/mysql/blog/partial_test.ibd'? y

MySQL start:

[root@localhost sh]# service mysql start
Starting MySQL.                                            [  OK  ]

Connect oluruq və yoxlayırıq və nə pis ki, xəyallar boşa çıxdı:

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

Və ERROR log-a baxdıqda biz görürük:

InnoDB: Error: table ‘blog/partial_test’
InnoDB: in InnoDB data dictionary has tablespace id 14,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name blog/partial_test and id 13, though. Have
InnoDB: you deleted or moved .ibd files?
.
.
.
[ERROR] InnoDB: Failed to find tablespace for table ‘”blog”.”partial_test”‘ in the cache. Attempting to load the tablespace with space id 14.
.
.
.
InnoDB: cannot calculate statistics for table “blog”.”partial_test” because the .ibd file is missing.

Dolayısı ilə artıq bizim cədvəl itirilmişdir(çox təəssüf). Bu da physical copy alıb backup ümidi ilə yaşayanlara kiçik bir dərs olsun.
Bir daha etdiklərimizi ümumiləşdirsək, biz cədvəl yaratdıq, məlumat daxil etdik, daxil edilmiş məlumatlı .ibd faylımızı başqa bir direktoriyaya kopyaladıq. Daha sonra cədvəlimizi truncate etdik və .idb faylını geri kopyaladıq o niyyətlə ki, data-lar geri qayıtsın. NƏTİCƏ= alınmadı.
Davam edək cədvəlimizi drop edək:

mysql> drop table partial_test;
Query OK, 0 rows affected (0.17 sec)

Lakin nə möcüzədirsə partial_test cədvəli-nin tablespace-i silinmir(discard olunmur):

[root@localhost blog]# ls
db.opt  partial_test.ibd

ERROR log-a baxdıqda:
2013-08-10 13:39:27 7fc7c69b6700 InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.

İnnoDB: cannot calculate statistics for table “blog”.”partial_test” because the .ibd file is missing.

Hətta düzgün drop da getmədi. Məcburən:

[root@localhost blog]# rm partial_test.ibd
rm: remove regular file `partial_test.ibd'? y
[root@localhost sh]# service mysql stop
Shutting down MySQL....                                    [  OK  ]
[root@localhost sh]# service mysql start
Starting MySQL.                                            [  OK  ]

2-ci ssenarimizi yaradaq. Cədvəl truncate etmək əvəzinə gəlin .ibd faylın özünü bir-başa silək və geri kopyalayaq:

mysql> create table remove_test(id int not null);
Query OK, 0 rows affected (0.32 sec)

mysql> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| remove_test    |
+----------------+
1 row in set (0.00 sec)

mysql> insert into remove_test() values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 0

Və kopyalayaq:

[root@localhost blog]# ls
db.opt  remove_test.frm  remove_test.ibd
[root@localhost blog]# cp remove_test.ibd /home/sh

Daha sonra təsəvvür edək ki, yeni məlumatlar daxil edilib və index əlavə olunub:

mysql> alter table remove_test add index(id);
mysql> insert into remove_test() values(6),(7),(8),(9),(10);
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

Düşünün ki, kimsə server işlək vəziyyətdə ola-ola remove_test.ibd faylını datadir-dən remove edir:

[root@localhost blog]# rm remove_test.ibd
rm: remove regular file `remove_test.ibd'? y

Bizim bundan dərhal xəbərimiz olmayacaq. Lakin bir müddət sonra server-ə restart verdikdə və daha sonra baxdıqda:

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

ERROR log-a nəzər yetirdikdə:

[ERROR] InnoDB: Could not find a valid tablespace file for ‘blog/remove_test’
[ERROR] InnoDB: Tablespace open failed for ‘”blog”.”remove_test”‘, ignored.

İndi isə server-i shut edib kopyaladığımız .ibd faylını geri gətirək. Məqsəd heç olmasa data-larımızın yarısını geri qaytarmaqdır:

[root@localhost sh]# cp remove_test.ibd /var/lib/mysql/blog
[root@localhost sh]# chown -R mysql:mysql /var/lib/mysql
[root@localhost blog]# ls
db.opt  remove_test.frm  remove_test.ibd

Start edirik və hətta MySQL-imiz crash olur…
Bunu mən BUG kimi report etdim artıq:
#69980
Bunun səbəbi dokumentasiyaya əks getməyimizdir 🙂

You cannot freely move .ibd files between database directories as you can with MyISAM table files. The table definition stored in the InnoDB shared tablespace includes the database name. The transaction IDs and log sequence numbers stored in the tablespace files also differ between databases.

Dolayısı ilə yenidən fiziki olaraq cədvəlləri kopyalayanların kədərini artıracaq bir hadisə baş verdi ki, bu tip “backup” backup deyildir.

Ümumi yuxarıda qeyd olunanlardan çıxan nəticələr:
1)
Heç bir şəkildə əməliyyat sisteminin köməyi ilə cədvəllərə dəyişiklik etməyin, onları silməyin.
Yəni rm qəbul olunmazdır(yuxarıda əyani nümunə var)
2)
Fiziki olaraq .ibd fayllarının kopyalanması sizə recover edə biləcəyiniz bir backup vermir.Yenə də yuxarıda göstərdiyimiz 2 hal buna sübutdur.

Bəs sual olunsun ki, partial restore-u necə edə bilərik?
Bunun üçün yeni cədvəl yaradaq onun düzgün backup-ını alaq və düzgün restore-unu göstərək.
Ardıcıllığa riayət etməyiniz mütləqdir:

mysql> create database blog;
Query OK, 1 row affected (0.06 sec)

mysql> use blog;
Database changed

mysql> create table restore_it(id int not null);
Query OK, 0 rows affected (0.46 sec)

mysql> insert into restore_it() values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.33 sec)
Records: 5  Duplicates: 0  Warnings: 0

Düzgün physical backup-ı biz XtraBackup(innobackupex) [online hot backup tool] ilə alırıq. Bu bizə “safe”=”clean” .ibd file verəcək. Yəni tam dokumentasiyada göstərildiyi kimi:
In this context, a “clean” .ibd file backup is one for which the following requirements are satisfied:

There are no uncommitted modifications by transactions in the .ibd file.

There are no unmerged insert buffer entries in the .ibd file.

Purge has removed all delete-marked index records from the .ibd file.

mysqld has flushed all modified pages of the .ibd file from the buffer pool to the file.

Backup alaq:

[root@localhost ~]# innobackupex --user=root --password=12345 /home/sh/backup5 --no-timestamp --defaults-file=/usr/my.cnf
.
.
.
130811 00:54:21  innobackupex: Connection to database server closed
130811 00:54:21  innobackupex: completed OK!

Backup-dan sonra işimizi çətinləşdirmək məqsədilə index əlavə edək:

mysql> alter table restore_it add index(id);
Query OK, 0 rows affected (0.62 sec)
Records: 0  Duplicates: 0  Warnings: 0

Daha sonra cədvəlimizi truncate edək ki, restore-a səbəb olsun 😉

mysql> truncate table restore_it;
Query OK, 0 rows affected (0.50 sec)

Restore üçün physical backup-ımızı prepare etməliyik:

[root@localhost ~]# innobackupex --apply-log --redo-only /home/sh/backup5
.
.
.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1679817
130811 01:07:06  innobackupex: completed OK!

Və:
[root@localhost ~]# innobackupex --apply-log /home/sh/backup5
.
.
InnoDB: Shutdown completed; log sequence number 1679904
130811 01:07:29  innobackupex: completed OK!

Bizə full restore gərəkli olmadığına görə. Yəni biz bütün database-i yox, məhz 1 cədvəli geri qaytarmaq istədiyimiz üçün. İnnobackupex-in copy-back funksiyasından yox, məhz MySQL-in öz imkanlarından istifadə edəcik. Xahiş olunur ən əhəmiyyətli hissəyə diqqət yetirək ani səhv yenə də cədvəlimizin itirilməsinə gətirib çıxardacaq:
1-ci addım köhnə tablespace-i discard edirik:

mysql> alter table restore_it discard tablespace;
Query OK, 0 rows affected (0.08 sec)

2-ci addım İnnobackupex-lə hazırladımız backup olan direktory-dən .ibd faylını kopyalamaq:

[root@localhost ~]# cd /home/sh/backup5
[root@localhost blog]# cp restore_it.ibd /var/lib/mysql/blog
[root@localhost blog]# chown -R mysql:mysql /var/lib/mysql

3-cü addım yeni tablespace-i import edirik:

mysql> alter table restore_it import tablespace;
Query OK, 0 rows affected, 2 warnings (0.38 sec)

Bu zaman warning-lər çıxır:

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                         |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1810 | InnoDB: IO Read error: (2, No such file or directory) Error opening './blog/restore_it.cfg', will attempt to import without schema verification |
| Warning | 1817 | InnoDB: Index corrupt: Index '"id"' not found or corrupt, you should recreate this index. 

restore_it.cfg faylına hələ ki nəzər yetirmirik. Bu haqda ayrıca yazım olacaq.
2-ci Warning index-in tapılmadığını ve bizim onu yenidən yaratmalı olduğumuzu bildirir.

4-cü addım data-nın qayıdıb-qayıtmadığını yoxlamaqdır:

mysql> select * from restore_it;
ERROR 1712 (HY000): Index restore_it is corrupted

Köhnə index-i drop edək:

mysql> alter table restore_it drop index id;
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

Və mutlu sonu görək:

mysql> select * from restore_it;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

🙂 Məlumatlarımız geri döndü.

Partial restore üçün lazım olanları ümumiləşdirsək:
*) innodb_file_per_table=1 ilə start olmuş server.
*) Xtrabackup-la alınmış clean physical backup
*) 1 ədəd MySQL-i sevən beyin

Təşəkkürlər.

Installing Xtrabackup 2.1.3 on CentOS 6.4

Ən əsas backup tool-lardan olan Xtrabackup-ın Centos 6.4-ə qurulumu zamanı yaşanan bəzi xırda çətinliklərdən dolayı bu yazını yazdım:

Xtrabackup-ı aşağıdakı linkdən yükləyə bilərsiniz:
Xtrabackup

İnstall etmək istədikdə:

[root@localhost Downloads]# rpm -ivh percona-xtrabackup-2.1.3-608.rhel6.x86_64.rpm
warning: percona-xtrabackup-2.1.3-608.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
	perl(DBD::mysql) is needed by percona-xtrabackup-2.1.3-608.rhel6.x86_64
	perl(Time::HiRes) is needed by percona-xtrabackup-2.1.3-608.rhel6.x86_64

Adətən perl modullarını cpan ilə install edirik:

[root@localhost Downloads]# cpan DBD::mysql
.
.
.
Running make install
  Make had returned bad status, install seems impossible

Dolayısı ilə başqa yolu sınamağa məcburuq:

[root@localhost Downloads]# yum install perl-DBD-MySQL
.
.
.Installed:
  perl-DBD-MySQL.x86_64 0:4.013-3.el6                                           

Dependency Installed:
  perl-DBI.x86_64 0:1.609-4.el6                                                 

Complete!

Daha sonra da digər dependency:

[root@localhost Downloads]# yum install perl-Time-HiRes
.
.
.
Installed:
  perl-Time-HiRes.x86_64 4:1.9721-131.el6_4                                     

Complete!

Son mərhələ:

[root@localhost Downloads]# rpm -ivh percona-xtrabackup-2.1.3-608.rhel6.x86_64.rpm
warning: percona-xtrabackup-2.1.3-608.rhel6.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
Preparing...                ########################################### [100%]
   1:percona-xtrabackup     ########################################### [100%]


[root@localhost Downloads]# xtrabackup --version
xtrabackup version 2.1.3 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 608)

Point-in-time recovery with MySQL row based binary log

İyun 28, 2013 2 şərh

/* Replication setup edənlər mütləq şəkildə binlog_format=row qeyd etməyi unutmasınlar */

Binary log-un nə olduğunu və hansı zamanlarda istifadə olunduğuna qısa olaraq toxunsaq əsasları:
1. Point-in-time recovery
2. Replication

Biz indi point-in-time recovery-yə baxacıq. Statement binary log formatında(default format)
hər şey çox sadədir yəni cədvələ edilən dəyişikliklər elə məhz statement şəklində bin log-a qeyd olunur. Statement bin log formatı üçün artıq bir yazım var : Statemen Binary log format

İndi isə row based binary log-la işləyək biraz.
Mövcud binary log-larımız tapaq:

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000031 |       120 |
+------------------+-----------+
1 row in set (0.13 sec)

Hansı hal hazırda istifadədir ona baxaq:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000031
         Position: 120
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

mysql-bin.000031 hal hazırda istifadədir. Position-u 120.

Cədvəl yaradaq və məlumat daxil edək:

mysql> create schema backup_test character set=utf8;
Query OK, 1 row affected (0.26 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.74 sec)

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

Və hər hansı dəyişikliklərin qeyd olunduğuna baxaq:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000031
         Position: 725
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Position 120-dən 725-ə dəyişdiyi üçün deyə bilərik ki binary log-a qeyd olunub. Binary log-a nəzər yetirək. Binary log-larla işləmək üçün mysqlbinlog utility default olaraq installation zamanı gəlir. Məndə installation path fərqli olduğu üçün məcburi full path verəcəm:

root@sh-ubuntu:~# /opt/mysql/server-5.6/bin/mysqlbinlog /var/lib/mysql/data/mysql-bin.000031
.
.
/*!*/;
# at 252
#130628 11:21:59 server id 1  end_log_pos 453 CRC32 0x5de3dc2f 	Query	thread_id=1	exec_time=0	error_code=0
use `backup_test`/*!*/;
SET TIMESTAMP=1372400519/*!*/;
create table test_table(
id int not null auto_increment,
name varchar(15),
surname varchar(20),
primary key(id)
)
/*!*/;
# at 453
#130628 11:23:06 server id 1  end_log_pos 532 CRC32 0x1e299628 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1372400586/*!*/;
BEGIN
/*!*/;
# at 532
#130628 11:23:06 server id 1  end_log_pos 598 CRC32 0x02542031 	Table_map: `backup_test`.`test_table` mapped to number 70
# at 598
#130628 11:23:06 server id 1  end_log_pos 694 CRC32 0xb71ce174 	Write_rows: table id 70 flags: STMT_END_F

BINLOG '
yivNURMBAAAAQgAAAFYCAAAAAEYAAAAAAAEAC2JhY2t1cF90ZXN0AAp0ZXN0X3RhYmxlAAMDDw8E
LQA8AAYxIFQC
yivNUR4BAAAAYAAAALYCAAAAAEYAAAAAAAEAAgAD//gBAAAACVNoYWhyaXlhcgZSemF5ZXb4AgAA
AAZLaGF0YWkGUnpheWV2+AMAAAAFRWx2aW4IQmlueWF0b3Z04Ry3
'/*!*/;
# at 694
#130628 11:23:06 server id 1  end_log_pos 725 CRC32 0x5509e700 	Xid = 23
COMMIT/*!*/;
DELIMITER ;
# End of logfile

Burda diqqət yetirək:

BINLOG '
yivNURMBAAAAQgAAAFYCAAAAAEYAAAAAAAEAC2JhY2t1cF90ZXN0AAp0ZXN0X3RhYmxlAAMDDw8E
LQA8AAYxIFQC
yivNUR4BAAAAYAAAALYCAAAAAEYAAAAAAAEAAgAD//gBAAAACVNoYWhyaXlhcgZSemF5ZXb4AgAA
AAZLaGF0YWkGUnpheWV2+AMAAAAFRWx2aW4IQmlueWF0b3Z04Ry3

Bunun izahı:
By default, mysqlbinlog displays row events encoded as base-64 strings using BINLOG statements

Dolayısı ilə bas64 encoded string olduğu üçün biz görə bilmirik ki, konkret olaraq nə baş verib və nə düşüb bin log-a(bir daha qeyd edirəm ki bu row based binary log format-a aiddir).
Bizim anlaya biləcəyimiz bir dildə göstərilməsi üçün aşağıdakı qaydada decode edirik:

root@sh-ubuntu:~# /opt/mysql/server-5.6/bin/mysqlbinlog -vv --base64-output=DECODE-ROWS /var/lib/mysql/data/mysql-bin.000031
.
.
/*!*/;
# at 252
#130628 11:21:59 server id 1  end_log_pos 453 CRC32 0x5de3dc2f 	Query	thread_id=1	exec_time=0	error_code=0
use `backup_test`/*!*/;
SET TIMESTAMP=1372400519/*!*/;
create table test_table(
id int not null auto_increment,
name varchar(15),
surname varchar(20),
primary key(id)
)
/*!*/;
# at 453
#130628 11:23:06 server id 1  end_log_pos 532 CRC32 0x1e299628 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1372400586/*!*/;
BEGIN
/*!*/;
# at 532
#130628 11:23:06 server id 1  end_log_pos 598 CRC32 0x02542031 	Table_map: `backup_test`.`test_table` mapped to number 70
# at 598
#130628 11:23:06 server id 1  end_log_pos 694 CRC32 0xb71ce174 	Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Shahriyar' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Rzayev' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Khatai' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Rzayev' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Elvin' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Binyatov' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 694
#130628 11:23:06 server id 1  end_log_pos 725 CRC32 0x5509e700 	Xid = 23
COMMIT/*!*/;
DELIMITER ;
# End of log file

İndi isə fərqə diqqət edirik:

### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Shahriyar' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Rzayev' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Khatai' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Rzayev' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='Elvin' /* VARSTRING(45) meta=45 nullable=1 is_null=0 */
###   @3='Binyatov' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */

İnsert bizə aydın şəkildə göstərildi. İndi isə yuxarıdakıların nə anlama gəldiyini izah edək:
@1, @2, @3 column sırasıni göstərir. @N olaraq dava edir, harda ki N sütün sayıdır.

/* INT meta=0 nullable=0 is_null=0 */ İNT data tipini göstərir.
/* VARSTRING(45) meta=45 nullable=1 is_null=0 */ varstring=varchar.

İndi isə binary log-un nə dərəcədə əhəmiyyətli olduğunu nümayiş etdirək. Deyək ki, cədvəlimizi səhvən drop etmişik:

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

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

Və əlimizdə backup yoxdur. Lakin vaxtında binary log-umuzu aktiv etdiyimiz üçün bəxtimiz gətirib. Biz drop-a qədər olan bütün dəyişiklikləri replay edə bilərik. Bunun üçün binlog position-ları öyrənməliyik. Bir daha binary log-a nəzər yetirək:

/*!*/;
# at 252
#130628 11:21:59 server id 1  end_log_pos 453 CRC32 0x5de3dc2f 	Query	thread_id=1	exec_time=0	error_code=0
use `backup_test`/*!*/;
SET TIMESTAMP=1372400519/*!*/;
create table test_table(
id int not null auto_increment,
name varchar(15),
surname varchar(20),
primary key(id)
)
/*!*/;
# at 453
#130628 11:23:06 server id 1  end_log_pos 532 CRC32 0x1e299628 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1372400586/*!*/;
BEGIN
/*!*/;
# at 532
#130628 11:23:06 server id 1  end_log_pos 598 CRC32 0x02542031 	Table_map: `backup_test`.`test_table` mapped to number 70
# at 598
#130628 11:23:06 server id 1  end_log_pos 694 CRC32 0xb71ce174 	Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=1
###   @2='Shahriyar'
###   @3='Rzayev'
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=2
###   @2='Khatai'
###   @3='Rzayev'
### INSERT INTO `backup_test`.`test_table`
### SET
###   @1=3
###   @2='Elvin'
###   @3='Binyatov'
# at 694
#130628 11:23:06 server id 1  end_log_pos 725 CRC32 0x5509e700 	Xid = 23
COMMIT/*!*/;
# at 725
#130628 11:48:11 server id 1  end_log_pos 862 CRC32 0xb911519a 	Query	thread_id=2	exec_time=1	error_code=0
SET TIMESTAMP=1372402091/*!*/;
DROP TABLE `test_table` /* generated by server */
/*!*/;

# at 252 -də başlayacıq # at 725-ə qədər replay edəcik. Yəni drop-a qədər:

root@sh-ubuntu:~# /opt/mysql/server-5.6/bin/mysqlbinlog --start-position=252 --stop-position=725 /var/lib/mysql/data/mysql-bin.000031 | mysql -u root -p
Enter password:

Möcüzəli şəkildə cədvəlimiz və içindəki məlumatlar geri qayıtdı:

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

Bizim işlərimizi asanlaşdırmaq üçün və öz məlumat təhlükəsizliyinizi təmin etmək üçün mütləq binary log-u aktiv edin!
Təşəkkürlər 😉

Installing Xtrabackup on Ubuntu 12.04

Xtrabackup haqqında artıq bir neçə yazılarımız olub. qısa olaraq Xtrabackup MySQL hot online backup tool-dur.
Xtrabackup-ı Ubuntu üzərinə install etmək üçün ardıcıllıqla:
1.

 apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

2.
/etc/apt/sources.list faylına qeyd edirik:

deb http://repo.percona.com/apt precise main
deb-src http://repo.percona.com/apt precise main

precise yerinə ubuntu version-u yazmaq lazımdır.
Məsələn precise=12.04

10.04LTS (lucid)
12.04LTS (precise)
12.10 (quantal)
13.04 (raring)

Debian üçün:

6.0 (squeeze)
7.0 (wheezy)

3.

apt-get update
.
.
Fetched 3,254 kB in 46s (70.2 kB/s)

4.

apt-get install percona-xtrabackup
.
.
.
Setting up percona-xtrabackup (2.1.3-608.precise) ...

5. Bitdi.

root@sh-ubuntu:~# xtrabackup --version
xtrabackup version 2.1.3 for Percona Server 5.1.59 unknown-linux-gnu (x86_64) (revision id: 608)

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 🙂