Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2014
    Posts
    4

    Unanswered: Missing MySQL Database

    Hello everybody,

    First of all, please bear with me as I am quite new to HeidiSQL, MySQL, and the concept of relational databases as a whole. I recently started using HeidiSQL in order to create tables for import into QlikView. A couple of weeks ago I upgraded from Windows 8.1 from Windows 8.0. This resulted in a number of errors that hindered my already set agenda for how to import files from Excel into HeidiSQl.

    First of all, upon trying to open a new HeidiSQL session I am met by an error message stating the following:

    “Error: SQL Error (2003) in statement #0: Can't connect to MySQL server on '(localhost)' (10061)”
    Upon checking my MySQL workbench, I could see that my local server was not running, but was “stopped”. Attempting to start the server in the MySQL workbench did not help, however upon Googling the problem I managed to get the server running by finding the mysqld.exe file and running the file “as administrator”.

    Getting the server running allowed me to start a session in HeidiSQL with the following details:
    - Network type: MySQL (TCP/IP)
    - Port: 3306
    - Databases: Separated by semicolon

    However, the only databases that show up are “information_schema”, “mysql”, and “performance_schema” and not the database that I am looking for, namely “(databasename)”. In the MySQL workbench, the following “schemas” are visible; “information_schema” and “performance_schema”. My question is, how do I go about finding and opening/restoring the aforementioned database?

    I was hoping that some of you might have experienced this problem and could shed some light on a solution. If any more information is need regarding my setup, please let me know.
    Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    search for where your db's are located
    its is installation specific, but will probably be in mysql\data prepended with where ever the MySQL directory is in your file structure

    its possible as part of your update the data has been wiped. but if so no doubt you can restore to your last known backup
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2014
    Posts
    4
    Hi healdem,

    Thanks for your reply.

    In C:\ProgramData\MySQL\MySQL Server 5.6 there is a folder with the database name. However, the folder only includes files in non-MySQL formats, namely ".frm", ".ibd", and ".opt". Can I use these files for anything or do I need to search elsewhere?

    Thanks in advance!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    .frm is the table definition
    .http://dev.mysql.com/doc/internals/e...ile-format.htm
    .ibd is an innodb data file
    .myd is (I think a MyIsam data file
    .myi is (I think) a MyIsam index file
    .opt is I think the original table definition

    it looks liek you are missing the data files.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2014
    Posts
    4
    Thank you for your reply!

    What type of files should I then look for in order to restore the missing data?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    see above for MyISAM if you are using INNODB as the engine then I think the data is stored in ibdata1, as per MyISAM in a separate directory for each database
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2014
    Posts
    4
    I can see an ibdata 1 file that is approximately 1GB within a file directory that is currently not linked to the database (C:\ProgramData\MySQL\MySQL Server 5.6\data) in the MySQL workbench, and also an ibdata 1 file that is approximately 12 MB in the directory that is currently linked to the database in the MySQL workbench (C:\Program Files\MySQL\MySQL Server 5.6\data).

    I have previously been advised on another forum to move a folder with the database name from the directory not currently linked to the database to the directory that currently is, i.e. folder "database name" from C:\ProgramData\MySQL\MySQL Server 5.6\data to C:\Program to C:\Program Files\MySQL\MySQL Server 5.6\data.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So you are using INNODB engine...
    the IBDATA file is your database....

    there's nowt wrong with COPYING a file to somewhere else as a safety copy, but whoever advised you MOVE the file AND directory is frankly scary.
    MySQL expects to find its databases in specific locations, those locations are installation specific. Databases are usually stored as subdirectories in the data directory in your MySQL directory, but they could be anywhere dependent on your MySQL configuration file

    on the face of it it sounds like the IBDATA file in the workbench tree is where your DB has ended up, or where you copied it to. I don't recommned copying or moving the directory accross OVER the existing database folder just incase the directory to be moved is corrupt or is not the correct one, So I'd always recommend that you move it (MOVE will be a feck of a lot quicker than copy BUT you will loose the comfort of having a copy) to a new durectory (effectively a new database) within the MySQL/data directory tree

    prove the newly moved file is the 'right' one and is fully visible (all tables, run some sample queries)

    if it is the right one then you can get rid of the current imposter and MOVE or COPY the FILE from one directory to the correct database directory

    I would always recommend that you stop the MySQL server BEFORE doing this (remember to RESTART the server afterwards)

    you need to work out how the heck the database was removed from the server (or perhaps bone up on what you need to do PRIOR to upgrading a MySQL server), hint Backups are always a smart call, you can never have too many backups but unless you test those backups to make certain you can restore from them they are just a waste of time and effort.
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •