MySQL #1046 error, kuidas lahendada

[-IFFI-]

Taustaks nii palju juttu, et webmin teeb mul /var/lib/mysql kataloogist igaöö backupid. Seal siis kõik andmebaasid kataloogidena sees.

Üritan nüüd taastada ühte konkreetset databaasi kuid saan vastuseks igakord

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'admin_saidinimi/ts_tutorials_comments.MYI' at line 1

Pakkisin selle suure TAR faili lahti ning seal sees oleva vajaliku andmebaasi kataloogi uuesti kokku TAR.GZ-i ning üritades importida seda uuesti andmebaasi saan aina selliseid vastuseid.

Googeldades mina oma umbkeelsuse tõttu ei leia õiget lahendust. Küll kuskil kirjutati, et peaks kustutama sellised error read mis teade näitab aga noid faile on must miljon ja kesse kurat jõuab kõiki neid läbi käia ja kustutada.

Failid on kataloogis *.frm *.MYD *.MYI lõpulised. Igast reast (ma täpselt ei tea kuda neid nimetatakse õieti) on siis 3 sellist varjanti Näiteks comments.frm comments.MYD comments.MYI

Kuida saaks selle kataloogi teha importimiseks söödavaks???

-E- See line 1 sisaldab eneses enamasti sellist jama (ja ka ülejäänud on sama) kuid pärast selle 1 line kustutamist ei kisa enam errorit

Kood: Vali kõik

\FF\00\00\00\00\00\00\00\00\00\FF\00\00\00\00\00\00!\00\00

[sander85]

Mis moodi sa neid küll importida üritad? Need on ju tavalised failisüsteemipõhised MySQL'i tabelite failid. Nii nagu nad eksisteerivad /var/lib/mysql/andmebaasi_nimi kaustas. Sellisel kujul backupi tegemiseks on sobilik ehk mysqlhotcopy, aga tegelikult peaks backupi tegemiseks ikka mysqldump käsku kasutama. Lihtsalt kaustast faile varundades võid väga vabalt kopeerida endale pooliku ja mittetöötava varunduse, millest hilisemalt ei taasta sa enam midagi.

[-IFFI-]

Nüüd keerasin midagi puhta peeti nagu ikka.

Nimelt kopeerisin läbi sFTP backupis oleva andmebaasi kataloogi otse /var/lib/mysql kataloogi. Peale seda tegevust ei tahtnud phpmyadmin enam kuidagi hakata pilti kuvama ja lõpuks kadus kõik.
Kasutan Parallel Pleski ja üritades nüüd minna paneeli saan vastuseks

ERROR: PleskFatalException: Unable to connect to database: mysql_connect(): No such file or directory (auth.php:142)

Mõtlesin, et kustutan ära selle kataloogi uuesti ftps aga nuid pole seal enam ühtegi andmebaasi kataloogi.

Mis teha nuid edasi ja asja parandada.

-E- Webminis vaadates siis mysql server seisab ja ei anna kuidagi käivitada. Ei anna ka mingeid erroreid
Terminalis käivitades ei saa ka midagi muud vastuseks kui aint FAILED

# /etc/init.d/mysql start
[FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

ja

# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

ja

# ls -l /var/run/ | grep mysqld
drwxr-xr-x 2 mysql root 40 Feb 15 06:29 mysqld

ning sellega ei saa mingit vastust

# ls -l | grep mysql

ja

# ps -ef |grep mysql
root 23242 16722 0 06:38 pts/0 00:00:00 grep mysql

ja

# apt-get install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
mysql-server is already the newest version.
The following packages were automatically installed and are no longer required:
libidn11:i386 libssh2-1:i386
Use 'apt-get autoremove' to remove them.
0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded.

Et ei saa midagi enam aru, mida rohkem googeldan seda segasemaks läheb

/etc/mysql/my.cnf

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock


[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
local-infile=0

user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
set-variable=max_connections=200


key_buffer = 16M
ft_min_word = 3
max_allowed_packet = 20M
thread_stack = 192K
thread_cache_size = 8


myisam-recover = BACKUP


query_cache_limit = 1M
query_cache_size = 20M


expire_logs_days = 10
max_binlog_size = 100M


[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[mysql]


[isamchk]
key_buffer = 16M

!includedir /etc/mysql/conf.d/

[chris]

Mida kirjutab MySQL syslog'i; täpsemalt faili /var/log/daemon.log?
Relevantsed lõigud saab kätte nii:

Kood: Vali kõik

grep mysqld /var/log/daemon.log

[-IFFI-]

Miskit sellist ta siia mulle trükkis, seda oli jube kuhi ja pool on kindlasti puudu

Kood: Vali kõik

Feb 15 08:06:55 site mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
Feb 15 08:07:07 site /etc/init.d/mysql[8873]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Feb 15 08:07:07 site /etc/init.d/mysql[8873]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Feb 15 08:43:40 site mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Feb 15 08:43:40 site mysqld: 140215  8:43:40 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
Feb 15 08:43:40 site mysqld: 140215  8:43:40 [Warning] Using unique option prefix ft_min_word instead of ft_min_word_len is deprecated and will be removed in a future release. Please use the full name instead.
Feb 15 08:43:40 site mysqld: 140215  8:43:40 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
Feb 15 08:43:40 site mysqld: 140215  8:43:40 [Note] Plugin 'FEDERATED' is disabled.
Feb 15 08:43:40 site mysqld: /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist
Feb 15 08:43:40 site mysqld: 140215  8:43:40 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Feb 15 08:43:40 site mysqld: 140215  8:43:40 InnoDB: The InnoDB memory heap is disabled
Feb 15 08:43:40 site mysqld: 140215  8:43:40 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Feb 15 08:43:40 site mysqld: 140215  8:43:40 InnoDB: Compressed tables use zlib 1.2.7
Feb 15 08:43:40 site mysqld: 140215  8:43:40 InnoDB: Using Linux native AIO
Feb 15 08:43:40 site mysqld: 140215  8:43:40 InnoDB: Initializing buffer pool, size = 128.0M
Feb 15 08:43:40 site mysqld: 140215  8:43:40 InnoDB: Completed initialization of buffer pool
Feb 15 08:43:40 site mysqld: 140215  8:43:40 InnoDB: highest supported file format is Barracuda.
Feb 15 08:43:40 site mysqld: 140215  8:43:40  InnoDB: Waiting for the background threads to start
Feb 15 08:43:41 site mysqld: 140215  8:43:41 InnoDB: 5.5.35 started; log sequence number 1595675
Feb 15 08:43:41 site mysqld: 140215  8:43:41 [ERROR] /usr/sbin/mysqld: unknown variable 'set-variable=max_connections=200'
Feb 15 08:43:41 site mysqld: 140215  8:43:41 [ERROR] Aborting
Feb 15 08:43:41 site mysqld: 
Feb 15 08:43:41 site mysqld: 140215  8:43:41  InnoDB: Starting shutdown...
Feb 15 08:43:42 site mysqld: 140215  8:43:42  InnoDB: Shutdown completed; log sequence number 1595675
Feb 15 08:43:42 site mysqld: 140215  8:43:42 [Note] /usr/sbin/mysqld: Shutdown complete
Feb 15 08:43:42 site mysqld: 
Feb 15 08:43:42 site mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
Feb 15 08:43:54 site /etc/init.d/mysql[29310]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Feb 15 08:43:54 site /etc/init.d/mysql[29310]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
Feb 15 08:44:17 site mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Feb 15 08:44:17 site mysqld: 140215  8:44:17 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
Feb 15 08:44:17 site mysqld: 140215  8:44:17 [Warning] Using unique option prefix ft_min_word instead of ft_min_word_len is deprecated and will be removed in a future release. Please use the full name instead.
Feb 15 08:44:17 site mysqld: 140215  8:44:17 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
Feb 15 08:44:17 site mysqld: 140215  8:44:17 [Note] Plugin 'FEDERATED' is disabled.
Feb 15 08:44:17 site mysqld: /usr/sbin/mysqld: Table 'mysql.plugin' doesn't exist
Feb 15 08:44:17 site mysqld: 140215  8:44:17 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
Feb 15 08:44:17 site mysqld: 140215  8:44:17 InnoDB: The InnoDB memory heap is disabled
Feb 15 08:44:17 site mysqld: 140215  8:44:17 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Feb 15 08:44:17 site mysqld: 140215  8:44:17 InnoDB: Compressed tables use zlib 1.2.7
Feb 15 08:44:17 site mysqld: 140215  8:44:17 InnoDB: Using Linux native AIO
Feb 15 08:44:17 site mysqld: 140215  8:44:17 InnoDB: Initializing buffer pool, size = 128.0M
Feb 15 08:44:17 site mysqld: 140215  8:44:17 InnoDB: Completed initialization of buffer pool
Feb 15 08:44:17 site mysqld: 140215  8:44:17 InnoDB: highest supported file format is Barracuda.
Feb 15 08:44:17 site mysqld: 140215  8:44:17  InnoDB: Waiting for the background threads to start
Feb 15 08:44:18 site mysqld: 140215  8:44:18 InnoDB: 5.5.35 started; log sequence number 1595675
Feb 15 08:44:18 site mysqld: 140215  8:44:18 [ERROR] /usr/sbin/mysqld: unknown variable 'set-variable=max_connections=200'
Feb 15 08:44:18 site mysqld: 140215  8:44:18 [ERROR] Aborting
Feb 15 08:44:18 site mysqld: 
Feb 15 08:44:18 site mysqld: 140215  8:44:18  InnoDB: Starting shutdown...
Feb 15 08:44:19 site mysqld: 140215  8:44:19  InnoDB: Shutdown completed; log sequence number 1595675
Feb 15 08:44:19 site mysqld: 140215  8:44:19 [Note] /usr/sbin/mysqld: Shutdown complete
Feb 15 08:44:19 site mysqld: 
Feb 15 08:44:19 site mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
Feb 15 08:44:31 site /etc/init.d/mysql[30324]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)'
Feb 15 08:44:31 site /etc/init.d/mysql[30324]: Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!

-E- see daemon.log on 11MB suur

[chris]

See rida annab esimese vihje

Feb 15 08:44:17 site mysqld: 140215 8:44:17 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.

MySQLi tabelid on pihta saanud. Ainult, et ... mysqld üritab edasi tööd teha, mis viitab sellele, et antud käsu käivitamiseks peab MySQL töötama. Natukenegi.
Käivitamise ebaõnnestumine on kirjas ridadel

Feb 15 08:44:18 site mysqld: 140215 8:44:18 [ERROR] /usr/sbin/mysqld: unknown variable 'set-variable=max_connections=200'
Feb 15 08:44:18 site mysqld: 140215 8:44:18 [ERROR] Aborting

Kui nüüd vaadata faili /etc/mysql/my.cnf, siis leiab sealt tõesti rea set-variable=max_connections=200.
Kustuta sealt set-variable= eest ära ja proovi MySQLi uuesti käivitada.

Edit: hosti nimi muudetud

[imre]

set-variable=max_connections=200
Selle võid ka välja kommenteerida. my.cnf failist.
Edasi proovi mysql käima tõmmata. Kui ei käivitu, siis tuleks jälle logi uurida.

[-IFFI-]

Kustutasin tolle rea ka juba varem ja ka nüüd kuid vastuseks saan ikka kui käivitada üritan mysqli

Kood: Vali kõik

# /etc/init.d/mysql start
[FAIL] Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

Kas sa onlines ei soovi suhelda? msn - irc? Oleks mugavam realajas suhelda ja leida kiiremini vastus kui siin postitades Lahenduse paneks hiljem siia kirja

[imre]

http://www.pingviin.org/irc.php
Mine siis ircu

[-IFFI-]

IRC-s pika müdistamise peale saime lõpuks kambaga MySQL-ile pildi ette.

Lahendusteks sai kasutatud selliseid käske.

Kood: Vali kõik

mysql_install_db --user=mysql --ldata=/var/lib/mysql

sedasi sai käima MySQL

Järgmiseks sai antud uus parool käsuga

Kood: Vali kõik

/mysqladmin -u root password 'siia-uus-parool'

Kui mysql ei taha seiskuda käsu peale killalla -9 mysqld siis proovi killall mysqld

Kuna DB-d olid veid valesti tehtud backupid siis nende taastamiseks on minupuhul vaja teha veidi näputööd.

Aga igapäevaseks varundamiseks pakuti välja selline lihtne lahendus

mysqldump -u root -pPAROOL --all-databases | gzip > /kataloog/teekond/database_'date -I'.sql.gz

Pole küll veel jõudnud proovida aga usun, et küll toimetab