15 Mai Amfani MySQL/MariaDB Daidaita Ayyukan Ayyuka da Tips ingantawa

MySQL shine tushen budewa mai ƙarfi Tsarin Gudanar da Database Management System ko a takaice RDBMS. An sake shi a cikin 1995 (shekaru 20). Yana amfani da Harshen Tambaya Mai Tsari wanda tabbas shine mafi mashahuri zaɓi don sarrafa abun ciki a cikin bayanan bayanai. Sabuwar sigar MySQL ita ce 5.6.25 kuma an sake shi a ranar 29 ga Mayu 2015.

Gaskiya mai ban sha'awa game da MySQL shine gaskiyar cewa sunan ya fito ne daga Micheal Widenius's ( mahaliccin MySQL) 'yar My. Ko da yake akwai abubuwa masu ban sha'awa da yawa game da MySQL, wannan labarin ana nufin ya nuna muku wasu ayyuka masu amfani don taimaka muku sarrafa sabar MySQL.

A cikin Afrilu 2009 Oracle ya sayi aikin MySQL. Sakamakon haka an ƙirƙiri cokali mai yatsu na al'umma na MySQL da ake kira MariaDB. Babban dalilin ƙirƙirar cokali mai yatsa shine don kiyaye aikin kyauta a ƙarƙashin lasisin Jama'a.

A yau MySQL da MariaDB ɗaya ne daga cikin mafi yawan (idan ba mafi yawan) ana amfani da RDBMS akai-akai don aikace-aikacen yanar gizo kamar WordPress, Joomla, Magento da sauransu.

Wannan labarin zai nuna muku wasu na asali, tukwici masu amfani yadda ake haɓaka ingantaccen aikin MySQL/MariaDB. Da fatan za a tuna cewa wannan labarin yana ɗauka cewa kun riga kun shigar da MySQL ko MariaDB. Idan har yanzu kuna mamakin yadda ake shigar da su akan tsarin ku, zaku iya bin jagororin mu masu yawa anan:

  1. Shigar da LAMP akan RHEL/CentOS 7
  2. Shigar da LAMP akan Fedora 22
  3. Kafa LAMP akan Ubuntu 15.04
  4. Shigar da MariaDB akan Debian 8
  5. Shigar da MariaDB akan Linux Gentoo
  6. Shigar da MariaDB akan Arch Linux

Muhimmi: Kafin mu fara - kar a yarda da wannan shawarwarin a makance. Kowane saitin MySQL na musamman ne kuma yana buƙatar ƙarin tunani, kafin yin kowane canje-canje.

Abubuwan da kuke buƙatar sani:

  1. Fayil ɗin sanyi na MySQL/MariaDB yana cikin /etc/my.cnf. Duk lokacin da kuka canza wannan fayil ɗin kuna buƙatar sake kunna sabis na MySQL don sabbin canje-canje su yi tasiri.
  2. Don rubuta wannan labarin an yi amfani da sigar MySQL 5.6 azaman samfuri.

1. Kunna InnoDB fayil-per-tebur

Da farko yana da mahimmanci a bayyana cewa InnoDB injin ajiya ne. MySQL da MariaDB suna amfani da InnoDB azaman ingin ajiya na asali. A baya MySQL ana amfani da ita don adana tebur na bayanai da fihirisa a cikin tsarin tebur. An yi amfani da wannan hanyar don sabobin wanda kawai manufar sarrafa bayanai ne kuma ba a amfani da faifan ma'adanar su don wasu dalilai.

InnoDB yana ba da mafi sassaucin tsari kuma kowane bayanan bayanan ana adana su a cikin fayil ɗin bayanai .ibd. Kowane fayil .ibd yana wakiltar sararin tebur na kansa. Ta wannan hanyar za a iya kammala ayyukan bayanai kamar TRUNCATE da sauri kuma za ku iya dawo da sararin da ba a yi amfani da su ba lokacin da ake zubarwa ko yanke teburin bayanai.

Wani fa'idar wannan tsarin shine gaskiyar cewa zaku iya ajiye wasu tebur ɗin bayanan a cikin na'urar ajiya daban. Wannan zai iya inganta nauyin I/O akan faifan ku.

An kunna innodb_file_per_table ta tsohuwa a cikin MySQL 5.6 da sama. Kuna iya ganin hakan a cikin /etc/my.cnf fayil. Umarnin yayi kama da haka:

innodb_file_per_table=1

2. Ajiye MySQL Database Data akan Rarraba Partition

Lura: Wannan saitin yana aiki tare da MySQL kawai, amma ba tare da MariaDB ba.

Wani lokaci OS karanta/rubutu na iya rage ayyukan uwar garken MySQL ɗinku, musamman idan yana kan rumbun kwamfyuta ɗaya. Madadin haka, zan ba da shawarar yin amfani da rumbun kwamfutarka daban (mafi dacewa SSD) don sabis na MySQL.

Don kammalawa, wannan kuna buƙatar haɗa sabon drive ɗin zuwa kwamfutarku/uwar garken ku. Don manufar wannan labarin, zan ɗauka cewa drive ɗin zai kasance ƙarƙashin /dev/sdb.

Mataki na gaba shine shirya sabon tuƙi:

# fdisk /dev/sdb

Yanzu danna \n don ƙirƙirar sabon partition. Na gaba danna \p don maida sabon bangare na farko. Bayan haka, saita lambar ɓangaren daga 1-4. Bayan haka za ku zaɓi girman ɓangaren. Danna shiga nan. A mataki na gaba za ku buƙaci saita girman ɓangaren.

Idan kana son amfani da faifan gabaɗaya latsa sake shigar da shi sau ɗaya. In ba haka ba zaka iya saita girman sabon bangare da hannu. Lokacin da aka shirya danna \w don rubuta canje-canje. Yanzu muna buƙatar ƙirƙirar tsarin fayil don sabon ɓangaren mu. Ana iya yin wannan cikin sauƙi tare da:

# mkfs.ext4 /dev/sdb1

Yanzu za mu dora sabon bangare a cikin babban fayil. Na sanya wa babban fayil suna \ssd kuma na ƙirƙira a cikin tushen directory:

# mkdir /ssd/

Muna shirye don hawan sabon ɓangaren da muka yi a cikin sabon babban fayil:

# mount /dev/sdb1  /ssd/

Kuna iya yin dutsen a farawa ta ƙara layin mai zuwa a /etc/fstab fayil.

/dev/sdb1 /ssd ext3 defaults 0 0

Yanzu kun shirya don matsar MySQL zuwa sabon faifai. Da farko dakatar da sabis na MySQL tare da:

# service mysqld stop

Zan ba da shawarar ku dakatar da Apache/nginx kuma don hana duk wani yunƙurin rubutawa a cikin bayanan bayanai:

# service httpd stop
# service nginx stop

Yanzu kwafi dukan adireshin MySQL a cikin sabon drive:

# cp /var/lib/mysql /ssd/ -Rp

Wannan na iya ɗaukar ɗan lokaci dangane da rukunin bayanan MySQL ɗinku. Da zarar wannan tsari ya cika sake suna MySQL directory:

# mv /var/lib/mysql /var/lib/mysql-backup

Na gaba za mu ƙirƙiri symlink.

# ln -s /ssd/mysql /var/lib/mysql

Yanzu kun shirya don fara MySQL da sabis na yanar gizo:

# service mysqld start
# service httpd start
# service nginx start

A wannan lokacin za a sami isa ga bayanan bayanan MySQL daga sabon drive.