List:General Discussion« Previous MessageNext Message »
From:Götz Reinicke - IT-Koordinator Date:June 10 2010 7:31am
Subject:Re: how to setup replication - MySQL 5.0.x - Migration and new databases
View as plain text  
Am 08.06.10 12:05, schrieb Rob Wultsch:
> On Mon, Jun 7, 2010 at 11:59 PM, Götz Reinicke - IT-Koordinator
> <goetz.reinicke@stripped> wrote:
>> Hi,
>>
>> we do have different LAMP systems and recently I started to put some
>> mysql databases on one, new master server. (RedHat, Fredora, MySQL 4.x -
>> 5.0.xx)
> 
> MySQL 4.X is EOL. I strongly suggest not using it for new projects, if
> you have the option. If possible, MySQL 5.1 is recommended,

Thanks, I'm aware of that. The 4.x servers are old and should be
migrated to the recent redhat EL released mysql 5.0.x.

> 
>>
>> I did this by exporting some databases with mysqldump and importing tham
>> on the new server.
>>
>> Now I'd like to add a slave mysqlserver and so I started to read some
>> docs from the web and manuals from addison-wesley but some questions do
>> remain or occur.
>>
>> What is the best way to "copy" the databases from the master to the
>> slave? I thought that I can shut down the master and copie the database
>> directory to the slave and than go on with the config, restarting the
>> servers, etc.
>>
>> Doing so, do I have to lock any InnoDB tables or anything else? (May be
>> I missunderstand some docs...)
> 
> Perhaps I am misunderstanding what you are doing, but shutting down
> the master instance will make it inaccessible until it is restarted.

That would be no problem.

> 
> Please read http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html
> .  That document has the basics right, other than snapshoting. In
> terms of getting a snapshot, if you have a innodb only instance*
> (which is good idea), and can stop ddl commands, you can use mysqldump
> with the master-data and single-transaction flags in order to take a
> non-blocking dump suitable for replication use. For MyISAM only
> instances FLUSH TABLES WITH READ LOCK can be used. The easiest way to
> make a snapshot is to shut down the master instance and make a copy of
> the data files. When you restart the master make note of which binary
> log file it starts to write to.
> 
> *Other than the mysql schema, of course.
> 
>>
>> So far I only copied a few databases from the different servers to the
>> new master.
>>
>> The second "big" question is: How to add new databases to the master
>> after sucessfully running a master-slave-setup? Will the new database be
>> copied/created on the slave automatically? Or do I have to create tham
>> twice?
> 
> New databases will be automatically created. Once you have the Master
> setup with binary logging you can inspect what it will have have the
> slave execute by using the mysqlbinlog command on the log files or the
> SHOW EVENT (http://dev.mysql.com/doc/refman/5.1/en/show-events.html)
> syntax.
> 

Thanks for your suggestion. I'll try that.

Regards - Götz

-- 
Götz Reinicke
IT-Koordinator

Tel. +49 7141 969 420
Fax  +49 7141 969 55 420
E-Mail goetz.reinicke@stripped

Filmakademie Baden-Württemberg GmbH
Akademiehof 10
71638 Ludwigsburg
www.filmakademie.de

Eintragung Amtsgericht Stuttgart HRB 205016
Vorsitzende des Aufsichtsrats:
Prof. Dr. Claudia Hübner

Geschäftsführer:
Prof. Thomas Schadt
Thread
how to setup replication - MySQL 5.0.x - Migration and new databasesGötz Reinicke - IT-Koordinator8 Jun
  • Re: how to setup replication - MySQL 5.0.x - Migration and new databasesRob Wultsch8 Jun
    • Re: how to setup replication - MySQL 5.0.x - Migration and new databasesGötz Reinicke - IT-Koordinator10 Jun
      • Re: how to setup replication - MySQL 5.0.x - Migration and newdatabasesJoerg Bruehe10 Jun
    • Re: how to setup replication - MySQL 5.0.x - Migration and new databaseslejeczek13 Jul
      • Re: how to setup replication - MySQL 5.0.x - Migration and new databasesMichael Dykman14 Jul