SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    copying a database

    another question for you all

    is there a query that will copy an entire database (including tables) with a new name?


    thanx

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the easiest method for doing this is to use mysqldump and create a new database then import the dump to the new database.

    1) Login using ssh
    2) mysqldump -uusername -ppassword dbname > dbname.sql
    3) mysqladmin -uusername -ppassword create newdatabase
    4) mysql -uusername -ppassword newdatabase < dbname.sql
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    mysqldump -username -password goldensports dbname.sql;
    ERROR 1064You have an error in your SQL syntax near 'mysqldump -username -password goldensports > dbname.sql' at line 1 
    Died on the first step

    whats with hte
    ppassword
    and
    uusername

    is that a typo or something
    ?
    or am I supposed to type u then my username?

  4. #4
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    slightlywacked,

    You are executing the command from the mysql prompt. You need to do that from the command prompt. You can type exit to quit mysql and return to command prompt.

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes you are supposed to type -uyourusername -pyourpassword

    where you are replacing yourusername with your username and your password with yourpassword
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do it like this also and it will prompt you for your password, however by appending your password to the -p you can bypass the prompt for your password. And like tipiyanos said you must be at the command prompt not the mysql prompt


    mysqldump -u username -p dbname > dbname.sql
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  7. #7
    SitePoint Enthusiast cragthehack's Avatar
    Join Date
    Jul 2001
    Location
    Ft. Lauderdale, FL. USA
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can't phpAdmin handle this also? Easier then using mysqldump I think.
    - crag
    net geek for non profits and political campaings

  8. #8
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know it can copy tables... But i dont think it can copy entire databases

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes PHPMyAdmin can handle this task. Although I find it incredibly annoying. In order to create a new table it takes thirty steps, which would take about 3 minutes from the commandline. Also be careful with phpMyAdmin and restoring databases, I have never successfully imported a dump file through phpMyAdmin, it always breaks somewhere in the file, on the other hand using mysqldump and mysql < sqlfile from the commandline works everytime.

    So I guess it a personal choice, I find the command line faster and easier. Also in light of protecting my clients' data, I would never pass a database through HTTP like phpMyAdmin does. I would always login to the server using ssh and work on the data from there.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    SitePoint Enthusiast cragthehack's Avatar
    Join Date
    Jul 2001
    Location
    Ft. Lauderdale, FL. USA
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've imported small tables using PHPmyAdmin. Nothing over 200 records. I've hear that anything over 1000 and PHPmyAdmin begins to drops records here and there.
    - crag
    net geek for non profits and political campaings

  11. #11
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So now why would you recommend using something that you have heard drops a record here and there. Especially when working on important data, I would think maintaining the integrity and the privacy of that data would be a number 1 priority. So with that, again I recommend for the safest and fastest solution, always use the command line interface for these types of situations.

    That was not meant to be an insult cragthehack, just making sure everyone knows the different options and the pros and cons of each.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  12. #12
    SitePoint Enthusiast cragthehack's Avatar
    Join Date
    Jul 2001
    Location
    Ft. Lauderdale, FL. USA
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well since he didn't say how sensitive the data was or the size - I figured PHPmyAdmin might be a good solution.
    - crag
    net geek for non profits and political campaings

  13. #13
    SitePoint Guru
    Join Date
    Apr 2001
    Location
    BC, Canada
    Posts
    630
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    its a business listings..


    anyways.. I just phoned the hosting company and got them to copy it
    thanx anyways....


Bookmarks

Posting Permissions

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