List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:July 14 2010 2:14pm
Subject:Re: how to setup replication - MySQL 5.0.x - Migration and new
databases
View as plain text  
Options such as these are set in my.cnf/my.ini (depending on your host
operating system).

innodb_file_per_table is a very useful option but not neccessarily the
best choice for a novice trying to set up replication.

 - michael dykman

On Tue, Jul 13, 2010 at 5:43 AM, lejeczek <peljasz@stripped> wrote:
> I'm a novice myself and yes I must say on-line documentation is not easy nor
> comprehensive,
> let's take this page as an example:
> http://dev.mysql.com/doc/refman/5.1/en/replication-howto-rawdata.html
>
> if I don't use InnoDB Hot Backup, doc says..
>
> "..Otherwise, you can obtain a reliable binary snapshot of |InnoDB| tables
> only after shutting down the MySQL Server..
> .. If you are replicating only certain databases then make sure you copy
> only those files that related to those tables. (For InnoDB, all tables in
> all databases are stored in the shared tablespace files, unless you have the
> innodb_file_per_table option enabled.).."
>
> but then nothing of how to do that!
> I do not have - innodb_file_per_table - enabled.
> how to do it?
>
> thanks
>
> On 08/06/10 11:05, Rob Wultsch wrote:
>>
>> 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,
>>
>>
>>>
>>> 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.
>>
>> 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.
>>
>>
>
>



-- 
 - michael dykman
 - mdykman@stripped

 May the Source be with you.
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