Re: replication through port forwarded firewall
Van == Van [EMAIL PROTECTED] writes: Van This means you need to re-initialize the slave; i.e. stop the Van master and slave; copy the data dir to the slave; start the Van slave (ensure the perms on the slave are correct first); Van start the master. If the error above had been Van connectivity-related you'd have received different ERROR Van messages in the log. Ok, thanks for all you detailed help. Unfortunately, I am still having the same problem, so I want to provide a few more details. 1) I shut down the master and shut down the slave with '/etc/init.d/mysqld stop' on the master and slave 2) I changed all the ownership on the master to be mysql.mysql with chown -R /path/to/data/dir 3) I cd'd into the slave data dir. This is the dir that has subdirs for the various mysql databases, which contain the *.MYD, *.MYI, *.frm files. I updated the slave data dir with rsync -avz --delete -e ssh root@nitace:/path/to/data/dir/ . This also copied all the mother-001.bin, mother-002.bin etc... files from the data dir of the master 4) I restarted the slave and got saw the message in the log that the slave could not connect to the master. This was expected, because you said to restart the slave before the master (which I found confusing because the manual section 'Replication HOWTO' says to restart the master first 5) I restarted the master 6) After the 60 sec wait on the slave, it reattempted a connect to the master, which was by then alive, and I got the same error message (posted below) about the duplicate insertion from mother-002.bin. Although this is a binary file, I did 'less' on it and it has text that you can read. The first command in that file is INSERT INTO multifile VALUES ( 2472160, '2001-3-9', '101.bmp', 2 Which is the line that the slave is complaining about. When I sync my data dirs, the slave dir also has the mother-bin.* files on it and it gets the mysql user dir from the master. Is this correct? Stumped. Thanks, John Hunter Master mysql version: 3.23.37 Slave mysql version: 3.23.36 Error message: 020315 9:30:52 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 ERROR: 1062 Duplicate entry '2472160-101.bmp-2' for key 1 020315 9:30:52 Slave: error running query 'INSERT INTO multifile VALUES ( 2472160, '2001-3-9', '101.bmp', 2 )' 020315 9:30:52 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'mother-bin.002' position 73 020315 9:30:52 Slave thread exiting, replication stopped in log 'mother-bin.002' at position 73 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: replication through port forwarded firewall
From what you are writing here it looks as though you are porting a database to the slave that has already been bin logging. If your master has been logging the database in question, and you are copying that database over to the slave, then of course when it reads the bin logs from the master the data is already in there. When you port the database into the slave it should be a copy of the master database just at the instance you started the bin logging. My Slave-Master Setup ( at this time if you want to start over, stop the slave and the master) 1. Dump master database ( perhaps you should lock all the tables from writing at this time ) Or stop the master server and tar/gzip the master database directory and send it to the slave server to use later 2. Set master settings( if possible at this time, remove the bin logs and index file from master HOSTNAME-bin.### and HOSTNAME-bin.index) 3. Create database on slave and load it with the master dump ( remove the master.info if starting over ) 4. Set slave settings 5. Restart the master server so it starts the fresh bin logging ( unlock your tables if you locked them before ) 6. Restart the slave server so it starts its replication process You could combine a couple of the steps 1-3 by using this on the slave db mysqladmin -p create databasename mysqldump -p -h0.0.0.0 -c -q themasterdb | mysql -p databasename ^^^ host ip address this will dump right into the database on the slave ( again you might want to lock the master from writing at this time ) don't know, this might be your problem OR you could lock the master from writing, note the position and bin file it is on, copy the master as is, send it to the slave and use the MASTER CHANGE command to set up the binlog and position on the slave, then start up the slave and unlock the master of course hmm what else GRANT FILE on *.* to replicant@'slaveIP' identified by 'passwd'; This must be FILE and the *.* must be *.* not database.* ( FILE is a global grant option and is not available to database, table, column permissions) -Original Message- From: John Hunter [mailto:[EMAIL PROTECTED]] Sent: Friday, March 15, 2002 11:23 AM To: [EMAIL PROTECTED] Cc: MySQL mailing list Subject: Re: replication through port forwarded firewall Van == Van [EMAIL PROTECTED] writes: Van This means you need to re-initialize the slave; i.e. stop the Van master and slave; copy the data dir to the slave; start the Van slave (ensure the perms on the slave are correct first); Van start the master. If the error above had been Van connectivity-related you'd have received different ERROR Van messages in the log. Ok, thanks for all you detailed help. Unfortunately, I am still having the same problem, so I want to provide a few more details. 1) I shut down the master and shut down the slave with '/etc/init.d/mysqld stop' on the master and slave 2) I changed all the ownership on the master to be mysql.mysql with chown -R /path/to/data/dir 3) I cd'd into the slave data dir. This is the dir that has subdirs for the various mysql databases, which contain the *.MYD, *.MYI, *.frm files. I updated the slave data dir with rsync -avz --delete -e ssh root@nitace:/path/to/data/dir/ . This also copied all the mother-001.bin, mother-002.bin etc... files from the data dir of the master 4) I restarted the slave and got saw the message in the log that the slave could not connect to the master. This was expected, because you said to restart the slave before the master (which I found confusing because the manual section 'Replication HOWTO' says to restart the master first 5) I restarted the master 6) After the 60 sec wait on the slave, it reattempted a connect to the master, which was by then alive, and I got the same error message (posted below) about the duplicate insertion from mother-002.bin. Although this is a binary file, I did 'less' on it and it has text that you can read. The first command in that file is INSERT INTO multifile VALUES ( 2472160, '2001-3-9', '101.bmp', 2 Which is the line that the slave is complaining about. When I sync my data dirs, the slave dir also has the mother-bin.* files on it and it gets the mysql user dir from the master. Is this correct? Stumped. Thanks, John Hunter Master mysql version: 3.23.37 Slave mysql version: 3.23.36 Error message: 020315 9:30:52 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 ERROR: 1062 Duplicate entry '2472160-101.bmp-2' for key 1 020315 9:30:52 Slave: error running query 'INSERT INTO multifile VALUES ( 2472160, '2001-3-9', '101.bmp', 2 )' 020315 9:30:52 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We
Re: replication through port forwarded firewall
John Hunter wrote: I have a mysql server behind a firewall. The firewall forwards port 3306 requests to the server, so the outside world can use it. I want to replicate this database with a slave outside the firewall. So the slave would request port 3306 on the firewall, but get the internal server instead. My concern is about the machine names. The slave thinks the master server is named eg, master.name.com, but the master server thinks it is named master.internal.domainname. So I am wondering if this will muck things up. No. Just have the slave connect to the master using the ip address. Has anyone done this before? Certainly. Thanks, John Hunter Regards, Van -- = Linux rocks!!! http://www.dedserius.com/ = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: replication through port forwarded firewall
Treat the master and slave as if the firewall wasn't there. It shouldn't be any different than a router. The outside computer shouldn't care what it is called on the internal network. If that's not fact, then its my strong opinion :) -Original Message- From: John Hunter [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 14, 2002 2:36 PM To: MySQL mailing list Subject: replication through port forwarded firewall I have a mysql server behind a firewall. The firewall forwards port 3306 requests to the server, so the outside world can use it. I want to replicate this database with a slave outside the firewall. So the slave would request port 3306 on the firewall, but get the internal server instead. My concern is about the machine names. The slave thinks the master server is named eg, master.name.com, but the master server thinks it is named master.internal.domainname. So I am wondering if this will muck things up. Has anyone done this before? Thanks, John Hunter Master: mysql Ver 11.13 Distrib 3.23.36, for redhat-linux-gnu (i386) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication through port forwarded firewall
Van == Van [EMAIL PROTECTED] writes: Van No. Just have the slave connect to the master using the ip Van address. I have set up the master and slave now and am getting a problem. When the slave tries to connect, it appears to be attempting to enter a value that is already in one of my database tables, and gives the error: [root@newman mysql]# tail /var/log/mysqld.log 020314 19:37:28 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 ERROR: 1062 Duplicate entry '2472160-101.bmp-2' for key 1 020314 19:37:28 Slave: error running query 'INSERT INTO multifile VALUES ( 2472160, '2001-3-9', '101.bmp', 2 )' 020314 19:37:28 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'mother-bin.002' position 73 020314 19:37:28 Slave thread exiting, replication stopped in log 'mother-bin.002' at position 73 I rsync'd the data dirs on master and slave and tried to follow the directions in the mysql manual on replication. The only thing that was different for me was that on the master the owners of some of the subdirs and files in the mysql data dir are root.root and I needed to change these to mysql.mysql or I got an error about not being able to read the *.frm files on entering the slave. When I go ahead and connect to the slave mysql server, I get the version number of the slave and not the master, and when I make changes to update the master, I do not see them on the slave. So it appears that it is simply reading the data files that I transferred over before restarting master and slave. Here is the master my.cnf: [root@mother var]# cat /etc/my.cnf [mysqld] datadir=/usr/local/mysql/var/ socket=/var/lib/mysql/mysql.sock log-bin server-id=1 [mysql.server] user=mysql basedir=/usr/local/mysql [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid And the slave my.cnf: [root@newman mysql]# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock master-host=128.135.97.130 master-user=repl master-password=somepass master-port=3306 server-id=2 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replication through port forwarded firewall
John Hunter wrote: I have set up the master and slave now and am getting a problem. When the slave tries to connect, it appears to be attempting to enter a value that is already in one of my database tables, and gives the error: [root@newman mysql]# tail /var/log/mysqld.log 020314 19:37:28 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 ERROR: 1062 Duplicate entry '2472160-101.bmp-2' for key 1 020314 19:37:28 Slave: error running query 'INSERT INTO multifile VALUES ( 2472160, '2001-3-9', '101.bmp', 2 )' 020314 19:37:28 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'mother-bin.002' position 73 020314 19:37:28 Slave thread exiting, replication stopped in log 'mother-bin.002' at position 73 This means you need to re-initialize the slave; i.e. stop the master and slave; copy the data dir to the slave; start the slave (ensure the perms on the slave are correct first); start the master. If the error above had been connectivity-related you'd have received different ERROR messages in the log. I rsync'd the data dirs on master and slave and tried to follow the directions in the mysql manual on replication. The only thing that was different for me was that on the master the owners of some of the subdirs and files in the mysql data dir are root.root and I needed to change these to mysql.mysql or I got an error about not being able to read the *.frm files on entering the slave. Very big difference depending on timing. If you started the slave and the master began replicating before the permissions allowed a write on the slave you're going to have out-of-synch data.Correct the perms while both servers are stopped. When I go ahead and connect to the slave mysql server, I get the version number of the slave and not the master, and when I make You should receive the version number of the server you connected to with the mysql -h (which server) command. I'd think you'd want them to be identical in a replication situation, regardless. changes to update the master, I do not see them on the slave. So it appears that it is simply reading the data files that I transferred over before restarting master and slave. Because as soon as the first record update on the master attempted to replicate to the slave the data was read-only so it couldn't have changed. Doesn't look like a port-forwarding problem, although there might still be one, but rather a replication problem. Regards, Van -- = Linux rocks!!! http://www.dedserius.com/ = - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
