Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MYSQL ERROR 2002 (HY000) - Can't Start

Posted on 2011-09-05
4
Medium Priority
?
1,657 Views
Last Modified: 2012-05-12
Hi,

I have a crash on my Linux Ubuntu 9 with Mysql. I have this error :

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


And in my log fil :

Var/log/mysql/error.log



InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 22 2307965626
110905 12:14:48  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
110905 12:14:49  InnoDB: Started; log sequence number 22 2307965626
110905 12:14:49 [Note] Event Scheduler: Loaded 0 events
110905 12:14:49 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
110905 12:14:50 /usr/sbin/mysqld: Table './wp_cefrio_blogue/global_zd_stats_entry' is marked as crashed and last (automatic?) repair failed
110905 12:14:50 /usr/sbin/mysqld: Table './wp_cefrio_blogue/global_zd_stats_entry' is marked as crashed and last (automatic?) repair failed


Anyone can help me,

thank's

Jean-Sebastien
0
Comment
Question by:jeansebgrenon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 

Author Comment

by:jeansebgrenon
ID: 36485484
I put this option in my my.cnf file :

innodb_force_recovery = 2

And I can open myswl, but I have this message in log file :


InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.



Any suggestion?

Thank's

Jean-Sebastien
0
 
LVL 13

Accepted Solution

by:
LinuxGuru earned 2000 total points
ID: 36486422
First of all try to shutdown the mysql server and then start it. If it still shows the error I would recommend you to dump the table or the database that caused the problem, delete, recreate it, and import the data back in.
0
 

Assisted Solution

by:jeansebgrenon
jeansebgrenon earned 0 total points
ID: 36488157
Hi testez, I tried your suggestion, but with some add.

This is what I do to restore my MYSQL Server :



First step was turning-on InnoDB force-recovery mode, where InnoDB starts but ignores all UPDATEs and INSERTs.
 
Add this line to /etc/my.cnf:
 

innodb_force_recovery = 2



Now we can restart the database:
 

/usr/local/bin/mysqld_safe

(Note: If MySQL doesn't restart, keep increasing the innodb_force_recovery number until you get to innodb_force_recovery = 8)
 
Save all data into a temporary alldb.sql (this next command can take a while to finish):
 

mysqldump --force --compress --triggers --routines --create-options -uUSERNAME -pPASSWORD --all-databases > /usr/alldb.sql

Shutdown the database again:
 

mysqladmin -uUSERNAME -pPASSWORD shutdown

Delete the database directory. (Note: In my case the data was under /usr/local/var. Your setup may be different. Make sure you're deleting the correct directory)
 

rm -fdr /usr/local/var

Recreate the database directory and install MySQL basic tables
 

mkdir /usr/local/var
chown -R mysql:mysql /usr/local/var
/usr/local/bin/mysql_install_db
chown -R mysql:mysql /usr/local/var

Remove innodb_force_recovery from /etc/my.cnf and restart database:
 

/usr/local/bin/mysqld_safe

Import all the data back (this next command can take a while to finish):
 

mysql -uroot --compress < /usr/alldb.sql

And finally - flush MySQL privileges (because we're also updating the MySQL table)
 

/usr/local/bin/mysqladmin -uroot flush-privileges

Credit to this solution, Mike Peters : http://www.softwareprojects.com/resources/programming/t-how-to-fix-mysql-database-myisam-innodb-1634.html
0
 

Author Closing Comment

by:jeansebgrenon
ID: 36518344
I my comment I have put the command to dump all DB, like me using innoDB crasching.

And some other few command to help.
0

Featured Post

Optimum High-Definition Video Viewing and Control

The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question