Archive

Posts Tagged ‘mysqldump’

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 🙂

mysqldump – A Database Backup Program

MySQL Documentation 5.5: The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both.
mysqldump-ın nə olduğunu oxuduq qəşəng,sadə şəkildə yazılıb tərcüməyə də ehtiyac yoxdur…amma ancaq lakin burda bir neçə maraqlı hadisələr var…
İlk öncə onu deyim ki, mysqldump-ın 2 output növü var –tab option-lu və –tab-sız…–tab-lıdan başdıyaq
***
Deyək ki, bizdə language_aze cədvəli var…cədvəl strukturu və içindəkilər bunlardır:

mysql> desc language_aze\g
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   |     |         |       |
| Language    | char(30)      | NO   |     |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from language_aze;
+-------------+-------------+------------+------------+
| CountryCode | Language    | IsOfficial | Percentage |
+-------------+-------------+------------+------------+
| AZE         | Armenian    | F          |        2.0 |
| AZE         | Azerbaijani | T          |       89.0 |
| AZE         | Lezgian     | F          |        2.3 |
| AZE         | Russian     | F          |        3.0 |
+-------------+-------------+------------+------------+
4 rows in set (0.00 sec)

İndi biz bu cədvəli dump etmək istəsək:

[root@sh ~]# mysqldump --tab='/var/lib/mysql' -u root -p my_new_db language_aze
Enter password: 

Və əgər /var/lib/mysql-ə getsək orada 2 faylın əmələ gəldiyini görərik…biri language_aze.sql digəri isə language_aze.txt

Maraqlıdır ki, bu zaman table create definition language_aze.sql faylında, cədvəlin içindəki məlumatlar isə language_aze.txt faylında yerləşəcək:


🙂 yaxşı dedim deməyinə… 😀
Bu mysqldump-ın birinci –tab-lı output-u idi…İndi isə –tab-sıza baxaq. İlk öncə fayllarımızı silək:

[root@sh ~]# cd /var/lib/mysql
[root@sh mysql]# rm language_aze.sql
rm: remove regular file `language_aze.sql'? y
[root@sh mysql]# rm language_aze.txt
rm: remove regular file `language_aze.txt'? y

Və dump edək:

[root@sh ~]# mysqldump -u root -p my_new_db language_aze > /var/lib/mysql/dump.sql
Enter password: 

Və həqiqətən də dump.sql adlı faylımız yarandı:

İndi isə bu faylın içini açıb orda nələr olduğuna bir baxaq…

Deməli şəkilə diqqət yetirsək görərik ki,faylımızda birinci olaraq:

DROP TABLE IF EXISTS `language_aze`; -- əgər belə bir table varsa onu sil!

Daha sonra :

CREATE TABLE `language_aze` (
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `Language` char(30) NOT NULL DEFAULT '',
  `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
  `Percentage` float(4,1) NOT NULL DEFAULT '0.0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- Cədvəli yaratmaq üçün komanda!

Daha sonra bu 2 yazı:

LOCK TABLES `language_aze` WRITE;
.
.
UNLOCK TABLES;

Deməli bu 2 yazı bizə qaranti verir ki, gələcəkdə biz bu dump faylından restore edəndə, language_aze cədvəli hər hansı digər insert (WRITE) üçün bağlı olacaq…Yəni yalnız dump faylındakı insert-lər daxil olunacaq…Daha sonra unlock olunacaq və əlavə hər hansı digər user tərəfindən olunacaq insert-ləri qəbul edəcək…

Və İnsert hissə

INSERT INTO `language_aze` VALUES ('AZE','Armenian','F',2.0),('AZE','Azerbaijani','T',89.0),
('AZE','Lezgian','F',2.3),('AZE','Russian','F',3.0);

Gördüyümüz kimi mysqldump default olaraq multiple insert-dən istifadə edir…Bu çox faydalıdır…çünki multiple insert adi insert-dən daha sürətlidir və gələcəkdə restore vaxtı daha sürətli insert əldə edə biləcik…
Bəs yaxşı bu yazılar hardan gəlir düşür bura? 😛 Yəni, əslində biz heçnə qeyd etməmişik…
Bunun səbəbi odur ki, mysqldump default\implicit olaraq –opt opsiyası ilə gəlir…default olaraq bu belə olduğu üçün mən yuxarıda –opt qeyd etməmişdim.
–opt opsiyasını yazmaq aşağıdakıları yazmaq kimi bir şeydir. Yəni aşağıdakılar –opt -nin içində var (əgər belə demək mümkündürsə):
1. –add-drop-table -> Add a DROP TABLE statement before each CREATE TABLE statement.
2. –add-locks -> Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded.
3. –create-options -> Include all MySQL-specific table options in the CREATE TABLE statements.
4. –disable-keys -> For each table, surround the INSERT statements with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for nonunique indexes of MyISAM tables. It has no effect for other tables.
5. –extended-insert -> Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.
6. –lock-tables -> For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to permit concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, –single-transaction is a much better option than –lock-tables because it does not need to lock the tables at all.
7. –quick -> This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.
8. –set-charset -> Add SET NAMES default_character_set to the output. This option is enabled by default. To suppress the SET NAMES statement, use –skip-set-charset.

Bəli məncə indi aydın oldu ki, niyə bizim dump faylımızda o yazılar peyda olmuşdu…
–opt-nin nə qədər faydalı olduğunu nümayiş etdirmək üçün –skip-opt (yəni opt-dən istifadə etmə,onu skip elə)

[root@sh ~]# mysqldump --skip-opt -u root -p my_new_db language_aze > /var/lib/mysql/dump2.sql
Enter password: 

Özünüz müqayisə edin 🙂

mysqldump haqqında daha ətraflı documentation-da:
mysqldump
Using mysqldump for Backups

Kateqoriyalar: MySQL Etiketlər: , , ,