Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    64

    Thumbs up Unanswered: How to copy a database

    Hi,

    I have a database "DB1".
    I want to copy this database, with data, as
    "DB2" .

    Without using MySQLdump ,is there any other command which can directly copy the Database DB1 to DB2

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sushma007
    Without using MySQLdump ...
    why would you not want to use mysqldump???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    Posts
    64
    Because it is thrweing some exceptions while dumping

    : Can't create/write to file '/root/tmp/#sql_2a27_0.MYI' (Errcode: 13) (1)

  4. #4
    Join Date
    Sep 2009
    Posts
    64
    You can do it the "hard" way ... The idea is to query INFORMATION_SCHEMA database in such a way that the resulting result is a valid SQL string that you can simply run.

    I don't have a full article talking about exactly what you want ... but this one will give you pretty much the same idea: Microshell Comparing data from 2 database tables

    I'd imagine you'll write queries something like this:

    Code:
    SELECT
    	CONCAT('CREATE TABLE DB2.', TABLE_NAME, ' LIKE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') AS create_table_sql,
    	CONCAT('INSERT INTO DB2.', TABLE_NAME, ' SELECT * FROM ', TABLE_SCHEMA, '.', TABLE_NAME, ';') AS populate_table_sql
    FROM
    	information_schema.TABLES
    WHERE
    	TABLE_SCHEMA = 'DB1'
    ;
    Then once you get the result, simply copy, paste and run the SQLs.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by sushma007
    Because it is thrweing some exceptions while dumping
    so wither you need to get permissions to that directory, or make certain there's enough room their to write the file

    OR change the location of the file to a directory that has enough room and/or you do have permissions to write to.

    according to da manuel MySQL :: MySQL 5.1 Reference Manual :: 4.5.4 mysqldump ? A Database Backup Program, assuming you are running from the command line thats
    mysqldump db_name > backup-file.sql
    if you are suign MySQLAdmin I'm guessing tis a setting on the relevant options page
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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