Hanyoyi masu fa'ida don magance Kurakurai gama gari a cikin MySQL


MySQL tsarin tsarin kula da bayanai na tushen tushen tushen tushen bayanai ne da ake amfani da shi sosai (RDMS) mallakar Oracle. Ya kasance tsawon shekaru shine zaɓin tsoho don aikace-aikacen tushen yanar gizo kuma har yanzu ya kasance sananne idan aka kwatanta da sauran injunan bayanai.

An tsara MySQL kuma an inganta shi don aikace-aikacen yanar gizo - yana samar da wani muhimmin sashi na manyan aikace-aikacen yanar gizo kamar Facebook, Twitter, Wikipedia, YouTube, da sauran su.

Ana amfani da rukunin yanar gizon ku ko aikace-aikacen yanar gizon MySQL? A cikin wannan cikakken labarin, zamuyi bayanin yadda ake magance matsaloli da kurakurai na yau da kullun a cikin uwar garken bayanan MySQL. Za mu bayyana yadda za a tantance musabbabin matsalolin da abin da za a yi don magance su.

1. Ba za a iya Haɗa zuwa Local MySQL Server ba

Ɗayan abokin ciniki gama gari zuwa kurakuran haɗin uwar garken a cikin MySQL shine\KUSKURE 2002 (HY000): Ba za a iya haɗawa zuwa uwar garken MySQL na gida ta soket '/var/run/mysqld/mysqld.sock' (2).

Wannan kuskuren yana nuna cewa babu uwar garken MySQL (mysqld) da ke gudana akan tsarin runduna ko kuma kun ayyana sunan fayil ɗin soket na Unix mara kyau ko tashar TCP/IP lokacin ƙoƙarin haɗawa da sabar.

Tabbatar cewa uwar garken yana gudana ta hanyar duba tsari mai suna mysqld akan uwar garken bayanan ku ta amfani da umarnin grep tare kamar yadda aka nuna.

$ ps xa | grep mysqld | grep -v mysqld

Idan dokokin da ke sama ba su nuna fitarwa ba, sabar bayanan ba ta aiki. Don haka abokin ciniki ba zai iya haɗawa da shi ba. Don fara uwar garken, gudanar da umarnin systemctl mai zuwa.

$ sudo systemctl start mysql        #Debian/Ubuntu
$ sudo systemctl start mysqld       #RHEL/CentOS/Fedora

Don tabbatar da matsayin sabis na MySQL, yi amfani da umarni mai zuwa.

$ sudo systemctl status mysql       #Debian/Ubuntu
$ sudo systemctl status mysqld      #RHEL/CentOS/Fedora

Daga fitowar umarnin da ke sama, sabis na MySQL ya gaza. A irin wannan yanayin, zaku iya ƙoƙarin sake kunna shi kuma sake duba matsayinsa sau ɗaya.

$ sudo systemctl restart mysql
$ sudo systemctl status mysql

Bugu da ƙari, idan uwar garken yana gudana kamar yadda aka nuna ta hanyar umarni mai zuwa, amma har yanzu kuna ganin kuskuren da ke sama, ya kamata ku kuma tabbatar da cewa tashar TCP/IP ta toshe ta hanyar wuta ko duk wani sabis na toshe tashar jiragen ruwa.

$ ps xa | grep mysqld | grep -v mysqld

Don umarnin netstat kamar yadda aka nuna.

$ sudo netstat -tlpn | grep "mysql"

2. Ba za a iya Haɗa zuwa MySQL Server ba

Wani kuskuren haɗin da aka saba ci karo da shi shine \(2003) Ba za a iya haɗawa da uwar garken MySQL akan 'uwar garke' (10061), wanda ke nufin an ƙi haɗin yanar gizon.

Anan, fara da bincika cewa akwai uwar garken MySQL da ke aiki akan tsarin kamar yadda aka nuna a sama. Hakanan tabbatar da cewa uwar garken tana da haɗin haɗin yanar gizo kuma tashar sadarwar da kuke amfani da ita don haɗa ita ce wacce aka saita akan uwar garken.

Sauran kurakuran gama gari da wataƙila za ku iya fuskanta lokacin da kuke ƙoƙarin haɗawa da uwar garken MySQL sune:

ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)

Waɗannan kurakurai suna nuna cewa uwar garken na iya gudana, duk da haka, kuna ƙoƙarin haɗawa ta amfani da tashar TCP/IP, mai suna bututu, ko fayil ɗin soket na Unix daban da wanda uwar garken ke sauraro.

3. Samun Kurakurai da aka ƙi a cikin MySQL

A cikin MySQL, an ayyana asusun mai amfani ta cikin sharuddan sunan mai amfani da mai masaukin baki ko runduna wanda mai amfani zai iya haɗawa da sabar. Bugu da kari, asusun yana iya samun takaddun shaida kamar kalmar sirri.

Kodayake akwai dalilai daban-daban na kurakuran An hana shiga, ɗaya daga cikin abubuwan gama gari shine alaƙa da asusun MySQL waɗanda uwar garken ke ba da izinin shirye-shiryen abokin ciniki don amfani da su lokacin haɗawa. Yana nuna cewa sunan mai amfani da aka ƙayyade a cikin haɗin ba shi da gata don shiga. database.

MySQL yana ba da damar ƙirƙirar asusun da ke ba masu amfani da abokin ciniki damar haɗi zuwa uwar garken da samun damar bayanan da uwar garken ke sarrafawa. Dangane da wannan, idan kun ci karo da kuskuren samun damar shiga, duba idan an ba da izinin asusun mai amfani ya haɗa zuwa uwar garken ta hanyar shirin abokin ciniki da kuke amfani da shi, da kuma yiwuwar gidan yanar gizon da haɗin ke fitowa daga.

Kuna iya ganin irin gatan da aka bayar ta hanyar gudanar da umarni NUNA KYAUTA kamar yadda aka nuna.

> SHOW GRANTS FOR 'tecmint'@'localhost';

Kuna iya ba da gata ga wani mai amfani akan takamaiman bayanai zuwa adireshin IP mai nisa ta amfani da waɗannan umarni a cikin harsashi MySQL.

> grant all privileges on *.test_db to 'tecmint'@'192.168.0.100';
> flush privileges;

Bugu da ƙari, samun kurakurai da aka hana kuma na iya haifar da matsaloli tare da haɗawa da MySQL, koma ga kurakuran da aka bayyana a baya.

4. Haɗin da aka rasa zuwa uwar garken MySQL

Kuna iya fuskantar wannan kuskuren saboda ɗaya daga cikin dalilai masu zuwa: rashin kyawun haɗin yanar gizo, lokacin haɗin yanar gizo ko matsala tare da ƙimar BLOB waɗanda suka fi max_allowed_packet girma. Idan akwai matsalar haɗin yanar gizo, tabbatar da cewa kuna da kyakkyawar hanyar sadarwa musamman idan kuna shiga uwar garken bayanan nesa.

Idan matsala ce ta ƙarewar haɗin gwiwa, musamman lokacin da MySQL ke ƙoƙarin amfani da haɗin farko zuwa uwar garken, ƙara ƙimar siginar connect_timeout. Amma idan akwai ƙimar BLOB waɗanda suka fi max_allowed_packet girma, kuna buƙatar saita ƙima mafi girma ga max_allowed_packet a cikin fayil ɗin daidaitawar ku /etc/my.cnf ƙarƙashin [mysqld] ko [abokin ciniki] sashe kamar yadda aka nuna.

[mysqld]
connect_timeout=100
max_allowed_packet=500M

Idan fayil ɗin sanyi na MySQL ba ya isa gare ku, to zaku iya saita wannan ƙimar ta amfani da umarni mai zuwa a cikin harsashi MySQL.

> SET GLOBAL connect_timeout=100;
> SET GLOBAL max_allowed_packet=524288000;

5. Yawan Haɗin MySQL

Idan abokin ciniki na MySQL ya ci karo da kuskuren haɗin da yawa, yana nufin cewa duk hanyoyin haɗin yanar gizo suna amfani da wasu abokan ciniki. Adadin haɗin (default shine 151) ana sarrafa su ta tsarin max_connections m; zaku iya magance matsalar ta haɓaka ƙimarta don ba da izinin ƙarin haɗin gwiwa a cikin fayil ɗin sanyi na /etc/my.cnf.

[mysqld]
max_connections=1000

6. Daga Memory MySQL

Idan kuna gudanar da tambaya ta amfani da shirin abokin ciniki na MySQL kuma ku ci karo da kuskuren da ake tambaya, yana nufin MySQL ba shi da isasshen ƙwaƙwalwar ajiya don adana duk sakamakon tambayar.

Mataki na farko shine tabbatar da cewa tambayar daidai ce, idan ta kasance, to sai a yi kamar haka:

  • idan kana amfani da abokin ciniki na MySQL kai tsaye, fara shi da --sauri mai sauri, don kashe sakamakon cache ko
  • idan kuna amfani da direban MyODBC, ƙirar mai amfani da daidaitawa (UI) tana da shafin ci gaba don tutoci. Duba Kada a cache sakamakon.

Wani babban kayan aiki shine, MySQL Tuner - rubutun mai amfani wanda zai haɗa zuwa uwar garken MySQL mai gudana kuma yana ba da shawarwari game da yadda za'a iya daidaita shi don babban aiki.

$ sudo apt-get install mysqltuner     #Debian/Ubuntu
$ sudo yum install mysqltuner         #RHEL/CentOS/Fedora
$ mysqltuner

Don haɓaka MySQL da nasihu na daidaita aiki, karanta labarinmu: 15 Mai Amfani MySQL/MariaDB Gyara Ayyukan Ayyuka da Tips ingantawa.

7. MySQL yana ci gaba da faɗuwa

Idan kun haɗu da wannan matsalar, yakamata kuyi ƙoƙarin gano ko matsalar ita ce uwar garken MySQL ta mutu ko abokin ciniki yana da matsala. Lura cewa yawancin hadarurruka na uwar garken suna haifar da gurbatattun fayilolin bayanai ko fayilolin fihirisa.

Kuna iya duba matsayin uwar garken don tabbatar da tsawon lokacin da aka ɗauka da aiki.

$ sudo systemctl status mysql       #Debian/Ubuntu
$ sudo systemctl status mysqld      #RHEL/CentOS/Fedora

A madadin, gudanar da umarnin mysqladmin mai zuwa don nemo lokacin sabar MySQL.

$ sudo mysqladmin version -p 

Sauran mafita sun haɗa da amma ba'a iyakance ga dakatar da uwar garken MySQL da ba da damar gyara kuskure ba, sannan fara sabis ɗin kuma. Kuna iya ƙoƙarin yin gwajin gwaji wanda za'a iya amfani dashi don maimaita matsalar. Bugu da ƙari, buɗe ƙarin taga tasha kuma gudanar da umarni mai zuwa don nuna ƙididdigar tsarin MySQL yayin da kuke gudanar da sauran tambayoyinku:

$ sudo mysqladmin -i 5 status
OR
$ sudo mysqladmin -i 5 -r status 

Ko da yake mun duba wasu matsaloli da kurakurai na MySQL da kuma samar da hanyoyin magance su da magance su, abu mafi mahimmanci tare da gano kuskure shine fahimtar abin da ake nufi (dangane da abin da ke haifar da shi).

To ta yaya za ku iya tantance wannan? Abubuwan da za su biyo baya za su jagorance ku kan yadda ake gano ainihin abin da ke haifar da matsala:

  1. Mataki na farko kuma mafi mahimmanci shine duba cikin rajistan ayyukan MySQL waɗanda aka adana a cikin directory /var/log/mysql/ . Kuna iya amfani da kayan aikin layin umarni kamar wutsiya don karantawa cikin fayilolin log.
  2. Idan sabis na MySQL ya kasa farawa, duba matsayinsa ta amfani da systemctl ko amfani da journetctl (tare da alamar -xe) a ƙarƙashin systemd don bincika matsalar.
  3. Hakanan kuna iya bincika fayil ɗin log ɗin tsarin kamar /var/log/messages ko makamancin haka saboda dalilan matsalarku.
  4. Yi gwada amfani da kayan aiki irin su htop don bincika ko wane shiri ke ɗaukar duk CPU ko yana kulle injin ko don bincika ko ƙwaƙwalwar ajiyar ku ta ƙare, sarari diski, bayanin fayil, ko wasu mahimman bayanai.
  5. Dauka cewa matsalar wasu tsari ne na gudu, koyaushe zaka iya ƙoƙarin kashe ta (ta amfani da pkill ko kashe utility) domin MySQL yayi aiki akai-akai.
  6. Da ace uwar garken mysqld yana haifar da matsala, zaku iya gudanar da umarni: mysqladmin -u root ping ko mysqladmin -u rootprocesslist don samun kowane amsa daga gare ta.
  7. Idan matsalar ta kasance tare da shirin abokin ciniki yayin ƙoƙarin haɗi zuwa uwar garken MySQL, duba dalilin da yasa baya aiki lafiya, gwada samun kowane fitarwa daga gare ta don dalilai na warware matsalar.

Hakanan kuna iya son karanta waɗannan labarai masu alaƙa da MySQL:

  1. Koyi MySQL/MariaDB don Mafari - Kashi na 1
  2. Yadda ake saka idanu MySQL/MariaDB Databases ta amfani da Netdata akan CentOS 7
  3. Yadda ake Canja wurin Duk Databases MySQL Daga Tsohuwar Zuwa Sabuwar Sabar
  4. Mytop - Kayan aiki Mai Amfani don Kula da Ayyukan MySQL/MariaDB a cikin Linux
  5. 12 MySQL/MariaDB Mafi kyawun Ayyuka na Tsaro don Linux

Don ƙarin bayani, tuntuɓi littafin Magana na MySQL game da Matsaloli da Kurakurai na gama-gari, gabaɗaya ya lissafa matsalolin gama gari da saƙonnin kuskure waɗanda za ku iya fuskanta yayin amfani da MySQL, gami da waɗanda muka tattauna a sama da ƙari.