Options such as these are set in my.cnf/my.ini (depending on your host
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
> let's take this page as an example:
> 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?
> 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:
>>> 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 -
>> 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
>> 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)
- michael dykman
May the Source be with you.