Re: how to setup replication - MySQL 5.0.x - Migration and new databases
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 pelj...@yahoo.co.uk 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.reini...@filmakademie.de 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 - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to setup replication - MySQL 5.0.x - Migration and new databases
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.reini...@filmakademie.de 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.
Re: how to setup replication - MySQL 5.0.x - Migration and new databases
Am 08.06.10 12:05, schrieb Rob Wultsch: On Mon, Jun 7, 2010 at 11:59 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de 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.reini...@filmakademie.de 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to setup replication - MySQL 5.0.x - Migration and new databases
Hi all! Götz Reinicke - IT-Koordinator wrote: Am 08.06.10 12:05, schrieb Rob Wultsch: On Mon, Jun 7, 2010 at 11:59 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de 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. Even 5.0 should be just an intermediate step, given that it is in extended support only. You didn't specify whether you are with a customer having a contract for extended support or not - if not, you cannot be assured to get fixes even for severe bugs, should they become known in 5.0. [[...]] Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
how to setup replication - MySQL 5.0.x - Migration and new databases
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) 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...) 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? Thanks for any comments and sugesstions. Best Regards, Götz -- Götz Reinicke IT-Koordinator Tel. +49 7141 969 420 Fax +49 7141 969 55 420 E-Mail goetz.reini...@filmakademie.de 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: how to setup replication - MySQL 5.0.x - Migration and new databases
On Mon, Jun 7, 2010 at 11:59 PM, Götz Reinicke - IT-Koordinator goetz.reini...@filmakademie.de 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. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
