List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:May 8 1999 2:28pm
Subject:Re: Moving db
View as plain text  
At 9:08 AM -0500 5/8/1999, Darrell Shifflett wrote:
>On Sat, 8 May 1999, Paul DuBois wrote:
>
>> At 8:21 AM -0500 5/8/1999, Darrell Shifflett wrote:
>> >On Sat, 8 May 1999, Orlando Andico wrote:
>> >
>> >> On Sat, 8 May 1999, Darrell Shifflett wrote:
>> >> ..
>> >> > question: I would like to know the safest way to move my databases
>>i have
>> >> >           created on existing box to the new box. I know i can tar
> cvfz
>> >> >           the db's and just ftp them to the /var/lib/mysql
> directory.
>> >> >           But! will db's from an older version be compatable with
>>the new
>> >> >           version? Basically, im moving everything from one server
> to
>> >> >           another. I presume the mysqld daemon will have to be
>>restarted.
>> >>
>> >> mysqldump -uroot -pXXXX -hOLDSERVER NAME_OF_DATABASE | \
>> >> mysql -uroot -pYYYY -hNEWSERVER NAME_OF_NEW_DATABASE
>> >>
>> >We trying to do this?
>> >
>> >mysqldump -u root -p 'password' -h 'torch.blueznet.com' my_database |
>> >mysql -u root -p 'password' -h 'pegasus.blueznet.com' renamed_database
>> >
>>
>> If you compare the command you're using with the one that was suggested,
>> you will notice that there is no space after the -p options in the suggested
>> command, and there is a space after the -p options in the command you
>> are using.  If that's really how you are typing the command it will
>> fail because -p and the password must be given with no intervening space.
>>
>> Assuming you can get the command to work as you want, this is what happens:
>>
>> mysqldump connects to the server on torch and tells it to dump the contents
>> of my_database.  The dump output consists of SQL statements to create
>> the tables in that database and populate them.  That output gets piped
>> to mysql, which connects to the server on pegasus.  mysql reads its
>> input, which creates and populates identical tables in the renamed_database
>> database.
>
>I hate to be a bug on the mailing list guys, but i want to get this right
>and understand it.
>
>Paul, so
>mysqldump -u root -ppassword -htorch.blueznet.com my_database |
>mysql -u root -ppassword -hpegasus.blueznet.com renamed_database
>
>should work, as what you were saying in the previous post.
>Both servers are connected and seeing each other. Your saying that
>mysql will connect to pegasus.blueznet.com's mysql server without
>setting any privileges, and recreate the database and populate it.
>This db will be named the same as the one coming from torch.blueznet.com.
>Just a little confused cause i was told to rename the database, and it has
>to be named exactly like the original to work.


Not quite.  The output from mysqldump doesn't contain any reference to the
name of the database you're dumping.  That's why you name a database on the
mysql command.  That makes "renamed_database" the current database, so
that any tables that are created are created in that database.

I forgot to mention -- you must create renamed_database on pegasus first,
otherwise the mysql command will fail.  Sorry about that.  That's kind of
important. :-)

As far as setting privileges, that shouldn't be an issue if you connect
as the MySQL root user and that user can do anything.  To allow *other*
users to use renamed_database after it's created, you'll need to set up
privileges appropriately, of course.

You might want to run "mysqldump --help" to see what your dump options
are.  And you might want to try piping the output of mysqldump into "more"
rather than into mysql, to get an idea of what that dump output looks
like.

--
Paul DuBois, paul@stripped
Northern League Chronicles: http://www.snake.net/nl/
Thread
Moving dbDarrell Shifflett8 May
  • Re: Moving dbOrlando Andico8 May
    • Re: Moving dbDarrell Shifflett8 May
      • Re: Moving dbPaul DuBois8 May
        • Re: Moving dbDarrell Shifflett8 May
          • Re: Moving dbPaul DuBois8 May
            • Re: Moving dbDarrell Shifflett8 May
      • Re: Moving dbOrlando Andico8 May