Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post

    Where are my databases!?

    Hey, this isn't exactly coding related but server. I basically need to backup a database as I need to restore my PC to factory settings. Apparently my databases should be stored in mysql\data.

    This folder for me is here: C:\xampp\mysql\data

    Although, for some reason the mysql cmd displays all of my databases, but some which are listed are not in the folder :S They are not hidden either. The "test" database was created using the CMD although the database I need to backup is "minecraft", but that is not shown in the folder...

    Any idea where it is?

  • #2
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    Instead of just backing up the files you can/should use the mysqldump program that is in the bin folder under MySQL which is designed to backup one or all of the databases on the MySQL server.

    But to answer your question as to where the files are, if some of those DBs are using the InnoDB storage engine they the data is all stored in the ib* files you see in that directory. If memory serves just copying those files to a new installation doesn't work, at least not without effort.
    OracleGuy

  • Users who have thanked oracleguy for this post:

    martynball (09-23-2011)

  • #3
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    I tried opening the mysqldump but it just closes. I have also tried the command that I have seen on some websites and that also doesn't work

  • #4
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    It is a command line application so you need to run it from an already open command window.

    So:
    Start > Run > cmd
    cd \xampp\mysql\bin
    mysqldump
    OracleGuy

  • Users who have thanked oracleguy for this post:

    martynball (09-24-2011)

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Ummm...and you need to direct the output of the dump to a file.

    And you need the username and password and databasename.

    So:

    Code:
    c:\some\folder> mysqldump -uUSER -pPASSWORD DATABASENAME > databaseName.sql
    There are other options available. When in doubt, you know, you can read the manual:
    http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    martynball (09-24-2011)

  • #6
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Okay, an error occured.

    Code:
    C:\Users\Martyn Ball>cd \xampp\mysql\bin
    
    C:\xampp\mysql\bin>mysqldump -martynball -password minecraft > minecraft.sql
    
    mysqldump: unknown option '-m'
    
    C:\xampp\mysql\bin>
    Is the last option (minecraft.sql) the name of the file which is to be created? Or where it is saved?

  • #7
    Rockstar Coder
    Join Date
    Jun 2002
    Location
    USA
    Posts
    9,074
    Thanks
    1
    Thanked 328 Times in 324 Posts
    You forgot the -u and -p in front of your username and password respectively. minecraft.sql will be the file where the database is backed up to.

    You can run mysqldump -h to see all the different arguments you can pass the program.
    OracleGuy

  • Users who have thanked oracleguy for this post:

    martynball (09-24-2011)

  • #8
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    oooh, didnt realise i needed that :P haha. It has worked now thanks, how do I restore this after and merge it or add it back to mysql?

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,014
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    You'll love this:
    Code:
    C:\xampp\mysql\bin>mysqldump -umartynball -pPassword < minecraft.sql
    In other words, you just send the SQL commnands that are in the ".sql" file to MYSQL and presto.

    Caution: Don't do this if the database you are trying to restore still exists!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    martynball (09-24-2011)


  •  

    Posting Permissions

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