Re: Replication config
On Thu, May 14, 2009 at 04:45:44PM -0700, Scott Haneda wrote: It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. I have it set up and working in test. I will redo it again once I get a better handle on it. I am still a little confused on one aspect. In the mysql sample cfg file, the section that has: #Replication Slave there is a very clear OR to use either #1 OR #2. I did the suggestions of #2, issuing #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; on the slave. Sounds fine. I also, in section [mysqld] # Begin slave config 05/14/2009 server-id = 2 master-host = ip.add.re.ss master-user = user-replicate master-password = xx master-port = 3306 # End slave config No. not necessary as the information is stored in the master info file. Am I correct in that this is not needed. I know I for certain need server_id, but is that all I need, and I have redundant data? I figure also better to not have raw user and pass in a cnf file if it is not needed. The server-id IS needed and MUST be different on each server. ... log-bin = /usr/local/mysql/var/bin.log This can be in the datadir just fine. If you server is very busy with updates some people recommend putting this on a different filesystem to spread the I/O. Depending on your setup that may or may not help. If you don't need it now don't bother. log-slave-updates Only needed if you have a daisy-chained replication environment you need this. Without it the salve will only store the commands run on the slave itself thus missing the commands run on the original master. If you want to make a slave from the SLAVE server then without this option you won't pick up all the replication commands. auto_increment_increment = 10 Unless you are running master-master replication ignore this. replicate-do-db = somedbname1 replicate-do-db = somedbname2 required if you don't want to replicate all the dbs on the server. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On Fri, May 15, 2009 at 12:48:18AM -0700, Scott Haneda wrote: Also, how do I set the slave to be read only? I set read-only in my.cnf and it made all databases read only. SET GLOBAL read_only = true; and as you've done in the my.cnf file. Unless the user has SUPER rights he can't change things in the database. There are some minor exceptions: - you can create temporary tables - you can run ANALYZE TABLE These are normally not an issue. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On May 16, 2009, at 12:28 AM, Simon J Mudd wrote: I also, in section [mysqld] # Begin slave config 05/14/2009 server-id = 2 master-host = ip.add.re.ss master-user = user-replicate master-password = xx master-port = 3306 # End slave config No. not necessary as the information is stored in the master info file. Thanks Simon, I will test. Looks like if this is the case, literally, 99% of every tutorial out there is wrong, as they all do this in duplicate, along with the CHANGE MASTER SQL command. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On May 12, 2009, at 11:11 PM, Simon J Mudd wrote: 1. Ensure binlogging is enabled on the master. 2. Ensure you setup grant permissions so the slave can connect to the master. 3. Configure on the slave the replication (which databases need to be replicated) 4. Get the master and slave in sync (via rsync, load/dump or whatever) 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. Also, how do I set the slave to be read only? I set read-only in my.cnf and it made all databases read only. I want to limit just the replicated database to be read only. The rest of them are production databases in normal use by clients. I suppose just not having a login and pass to the replicated database is more than enough? -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
Hi Scott
You may use the script below to reload replication if you can ensure that
the master db doesn't change during the dump operation. Otherwise you may
set a lock on the master manually.
Regards, Thomas
#!/bin/bash
#
# replicate-reload
#
# This is free software. There is no warranty at all.
# The program may melt your computer and kill your cat.
# Use at your own risk.
#
# restart new replication of DBASE on localhost; dump from MASTER
#
# Note: No changes to DBASE may take place on the master during
# the dump operation. See comments below.
#
# Set your values here:
DBASE=adbtoreplicate
MASTER=host.domain.tld
MYUSER=useronlocalhost
MYPWD=thisisagoodpassword
# Set replication user and password
REPLUSER=replicationuser
REPLPWD=replicationuserpassword
# End of user configuration
SPACE=' '
TAB=$(echo -ne \t)
MASTER_ALIAS=$(echo $MASTER | sed -e s/\\..*//)
MASTER_POS=$(echo FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; | mysql -u $MYUSER -h $MASTER -p$MYPWD $DBASE \
| sed -e /^${MASTER_ALIAS}-bin/ !d)
#
# Beware: From this point on no changes on the master may be made
# until the dump has finished. If this can't be enforced you
# have to place a lock manually on the master and release it
# once the dump is complete.
#
MASTER_FILE=$(echo $MASTER_POS | cut -s -d $TAB -f 1)
MASTER_LOGPOS=$(echo $MASTER_POS | cut -s -d $TAB -f 2)
#echo MASTER_POS=$MASTER_POS
echo MASTER_FILE=$MASTER_FILE
echo MASTER_LOGPOS=$MASTER_LOGPOS
# Get the dump
echo Dumping '$DBASE' from $MASTER
#
# User: set your own dump options here as needed
mysqldump -u $MYUSER -h $MASTER -p$MYPWD \
--skip-opt \
--add-drop-table \
--max_allowed_packet=1M \
--character-sets-dir=/usr/share/mysql/charsets \
--skip-set-charset \
--extended-insert --lock-all-tables --quick \
--quote-names --master-data=2 $DBASE \
| sed -e /^SET / d ${DBASE}.sql
#
# Note: Changes on the master are allowed from here on
#
echo -e \nCHANGE MASTER TO MASTER_HOST='$MASTER', \
MASTER_USER='$REPLUSER', MASTER_PASSWORD='$REPLPWD', \
MASTER_LOG_FILE='$MASTER_FILE', MASTER_LOG_POS=${MASTER_LOGPOS}; \
${DBASE}.sync.sql
echo STOP SLAVE; | mysql -u $MYUSER -h localhost -p$MYPWD $DBASE
# reload dumped database
echo Reloading '${DBASE}' on localhost
cat ${DBASE}.sql ${DBASE}.sync.sql | mysql \
-u $MYUSER -h localhost -p$MYPWD $DBASE
echo Starting slave $(hostname)
echo START SLAVE; | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE
sleep 2
echo SHOW SLAVE STATUS; | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE
rm -f ${DBASE}.sql ${DBASE}.sync.sql
exit 0
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
On May 12, 2009, at 11:11 PM, Simon J Mudd wrote: talkli...@newgeo.com (Scott Haneda) writes: Hello, I am confused about repliction setup. Reading a config file, and the docs, leads me to believe this is an either code choice, pick #1 or #2. If that is the case, why would I want to use #1 over #2? My confusion comes from several online references where there is a combination of #1 and #2 going on: # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). Use this method. it works and is the correct way to do things. It also will keep working if you stop and restart the server with replication carrying on from where it left off. The procedure is quite simply: 1. Ensure binlogging is enabled on the master. 2. Ensure you setup grant permissions so the slave can connect to the master. 3. Configure on the slave the replication (which databases need to be replicated) 4. Get the master and slave in sync (via rsync, load/dump or whatever) 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. I have it set up and working in test. I will redo it again once I get a better handle on it. I am still a little confused on one aspect. In the mysql sample cfg file, the section that has: #Replication Slave there is a very clear OR to use either #1 OR #2. I did the suggestions of #2, issuing #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; on the slave. I also, in section [mysqld] # Begin slave config 05/14/2009 server-id = 2 master-host = ip.add.re.ss master-user = user-replicate master-password = passw3rd master-port = 3306 # End slave config Am I correct in that this is not needed. I know I for certain need server_id, but is that all I need, and I have redundant data? I figure also better to not have raw user and pass in a cnf file if it is not needed. I would say 99% of the examples on the internets are using both methods, the MySql docs are not entirely clear to me on this specific issue. Conflicting data on the comments for sure. I also have seen a good deal more options specified than I have. To be clear, I am only looking for one way replication, the most basic, a master that is read/write by clients, and a slave that is neither read/ write by clients, but only reading in data from the master, syncing it. Here is a example of other options I am seeing, comments interspersed... server-id = 1 log-bin = /usr/local/mysql/var/bin.log I take it I do not need this if I am ok with default data dir? log-slave-updates I am pretty sure I do not not need this, since I am only doing master to slave, and no cascading replication, or circular rep. log-bin-index = /usr/local/mysql/var/log-bin.index Same as log-bin, of I am ok with default data dir? log-error = /usr/local/mysql/var/error.log Again, if I am ok with default data-dir? relay-log = /usr/local/mysql/var/relay.log I do not seem to have this file anywhere. relay-log-info-file = /usr/local/mysql/var/relay-log.info relay-log-index = /usr/local/mysql/var/relay-log.index I do not see that I have these on the master, I have it on the slave. Maybe all these logs are best to be defined. Perhaps if they are not, the hostname may be used in the naming of the logs, and if a hostname ever changes, I would have issues on getting replication to fine the logs? auto_increment_increment = 10 auto_increment_offset = 1 I am definitely not clear on these. I want a 100% replica. Why do I need to worry of key id collisions at all? Or is This n/a to a simple master/slave setup master-host = other master hostname master-user = replication username master-password = replication password These I wonder if are needed at all, if I use the sql CHANGE replicate-do-db = somedbname1 replicate-do-db = somedbname2 Good to know, wondered how to add more, if you comma seperated
Re: Replication config
talkli...@newgeo.com (Scott Haneda) writes: Hello, I am confused about repliction setup. Reading a config file, and the docs, leads me to believe this is an either code choice, pick #1 or #2. If that is the case, why would I want to use #1 over #2? My confusion comes from several online references where there is a combination of #1 and #2 going on: # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). Use this method. it works and is the correct way to do things. It also will keep working if you stop and restart the server with replication carrying on from where it left off. The procedure is quite simply: 1. Ensure binlogging is enabled on the master. 2. Ensure you setup grant permissions so the slave can connect to the master. 3. Configure on the slave the replication (which databases need to be replicated) 4. Get the master and slave in sync (via rsync, load/dump or whatever) 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
Thanks, a few comments inline below... On May 12, 2009, at 11:11 PM, Simon J Mudd wrote: talkli...@newgeo.com (Scott Haneda) writes: Hello, I am confused about repliction setup. Reading a config file, and the docs, leads me to believe this is an either code choice, pick #1 or #2. If that is the case, why would I want to use #1 over #2? My confusion comes from several online references where there is a combination of #1 and #2 going on: # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=host, MASTER_PORT=port, #MASTER_USER=user, MASTER_PASSWORD=password ; # #where you replace host, user, password by quoted strings and #port by the master's port number (3306 by default). Use this method. it works and is the correct way to do things. It also will keep working if you stop and restart the server with replication carrying on from where it left off. The procedure is quite simply: 1. Ensure binlogging is enabled on the master. Done, I can see the binlogs as well. 2. Ensure you setup grant permissions so the slave can connect to the master. From the remote MySql I have mysql -u user -h example.com -p It allows me in so I think I am good there. 3. Configure on the slave the replication (which databases need to be replicated) This is where I need a little clarification, is the only thing I need to do is adjust my.cnf to have in the [mysqld] section server-id = 2 4. Get the master and slave in sync (via rsync, load/dump or whatever) Is this mandatory? There is not a lot of data, hundred rows or so, can I use LOAD DATA FROM MASTER; ? Seems most instructions say to use a dump. This does not make a lot of sense to me, I am setting up replication, is it not the point to be able to pull the data down? Why does it need priming like this? 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master I can do this now, gives back a position. It seems to change over time. Since it is a moving target, if I am using LOAD DATA FROM MASTER; I take it I need to lock the tables while the first load is happening? 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. 7. Run: START SLAVE Will do, thanks. 8. Use: show slave status\G to check how the replication is working, and and adjust as necessary. Thanks. It's true that initial mysql replication setup is a bit fiddly, but once you've done it once or twice it's not so hard. While not part of my plan, if the master goes down and I want to start using the slave as the master while I am fixing the master server What is the best way to do this? Can the slave be treated like a master by just pointing any client to the slave assuming I set a user to allow it? With the slave temporarily becoming the master, the data will of course change. When I bring the master back online, what is the best way to reverse sync and get back to where I was? Probably take the entire thing thing down, copy the database from the current temp live slave that has been used as a master, and go from there? Thank you. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication config
replying only to the list... On Wed, May 13, 2009 at 10:19:21AM -0700, Scott Haneda wrote: 3. Configure on the slave the replication (which databases need to be replicated) This is where I need a little clarification, is the only thing I need to do is adjust my.cnf to have in the [mysqld] section server-id = 2 That's the minimal configuration. You may need to specify which databases need to be replicated or which tables. By default everything is replicated which is probably fine. 4. Get the master and slave in sync (via rsync, load/dump or whatever) Is this mandatory? There is not a lot of data, hundred rows or so, can I use LOAD DATA FROM MASTER; ? I think that only works in MySQL 4, and have never used it on our production servers (5.0). Yes, checking the MySQL 5. documentation it says: http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html --quote-- 12.6.2.2. LOAD DATA FROM MASTER Syntax LOAD DATA FROM MASTER This feature is deprecated. We recommend not using it anymore. It is subject to removal in a future version of MySQL. --quote-- If you've only got hundreds of rows then just do a normal mysqldump. The problem is that if you have a database with GB or hundreds of GB of data then this process is really slow, and if at the same time you can't afford to stop your master then that makes life harder. Seems most instructions say to use a dump. This does not make a lot of sense to me, I am setting up replication, is it not the point to be able to pull the data down? Why does it need priming like this? For 5.0 and above because the you can't load DATA from master, so just use the dump, and don't change the master while you are doing this. 5. Run show master status on the master (assuming binlogging is enabled) to get the current position on the master I can do this now, gives back a position. It seems to change over time. Since it is a moving target, if I am using LOAD DATA FROM MASTER; I take it I need to lock the tables while the first load is happening? If you're using 5.0 you shouldn't be using LOAD DATA FROM MASTER. Do a mysqldump and load from that, or if you use some sort of unix with snapshotting possibilities then make a (lvm) snapshot of the filesystem and copy that. That's what we typically do at work and it leaves the master down for just a second or so. The later copy can take place while the master is running. 6. use CHANGE MASTER TO on the slave providing the appropriate permissions. This just tells the slave where to start replicating from. That is which statements or rows in the binlog to download from the master and apply on the slave. 7. Run: START SLAVE This starts the replication process. While not part of my plan, if the master goes down and I want to start using the slave as the master while I am fixing the master server What is the best way to do this? Can the slave be treated like a master by just pointing any client to the slave assuming I set a user to allow it? You can do this from the point of view of the database users but then the slave will be more up to date than the master and if you've not configured things properly and don't have the right information you won't be able to get the master back in sync. So you can't just switch between boxes without taking special care. With the slave temporarily becoming the master, the data will of course change. When I bring the master back online, what is the best way to reverse sync and get back to where I was? Probably take the entire thing thing down, copy the database from the current temp live slave that has been used as a master, and go from there? If the end that may be necessary. You can configure master / master replication but as I said you have to be careful with this as it can be quite critical how you actually setup your tables. If you don't do things correctly it won't work. I think it is documented however in the MySQL manual so I'd suggest you read that. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: replication config without stopping master server
Hi Thomas, Here I have one more confusion that after the binary log value is `mysql-bin.003' and the offset is 73. Record the values. where should I put it in the slave? Suppose I have copied that snapshot on the slave server and start the MySQL and replication. Where I need to put the mysql-bin.xxx and offset value? Thanks in advance. Regards Naveen - Original Message - From: Thomas Spahni [EMAIL PROTECTED] To: Naveen C Joshi [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 9:48 PM Subject: Re: replication config without stopping master server Naveen, You don't need to shut down the master but you have to stop writing to the database for taking the snapshot. Follow the description in the manual: 3. If you are using MyISAM tables, flush all the tables and block write queries by executing `FLUSH TABLES WITH READ LOCK' command. mysql FLUSH TABLES WITH READ LOCK; and then take a snapshot of the data on your master server. snip While the read lock placed by `FLUSH TABLES WITH READ LOCK' is in effect, read the value of the current binary log name and offset on the master: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | mysql-bin.003 | 73 | test,bar | foo,manual,mysql | +---+--+--+--+ 1 row in set (0.06 sec) The `File' column shows the name of the log, while `Position' shows the offset. In the above example, the binary log value is `mysql-bin.003' and the offset is 73. Record the values. You will need to use them later when you are setting up the slave. Once you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master: mysql UNLOCK TABLES; Regards, Thomas Spahni On Tue, 31 Aug 2004, Naveen C Joshi wrote: Hi All : I am still waiting for the response. Thanks in advance. Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:17 PM Subject: Re: replication config without stopping master server The replication slave server has the Physical memory 3.6 GB and the my.cnf file is as below : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /data1/mysql basedir = / port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M set-variable= thread_stack=256k log-bin server-id = 1 master-host=xxx.xxx.xxx.xxx master-user= yyy master-password= zzz master-port= 3306 innodb_data_home_dir = /data1/mysql/ innodb_data_file_path = ibdata1:800M:autoextend innodb_log_group_home_dir = /data1/mysql/ innodb_log_arch_dir = /data1/mysql/ set-variable = innodb_buffer_pool_size=2000M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=150M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable= innodb_file_io_threads=4 transaction-isolation = READ-COMMITTED innodb_thread_concurrency = 4 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 2:04 PM Subject: replication config without stopping master server Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This machine is as a replication master server. I have configured the other machine as replication slave with same version of MySQL and OS. Now, I want to start replication slave server
Re: replication config without stopping master server
Hi All : I am still waiting for the response. Thanks in advance. Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:17 PM Subject: Re: replication config without stopping master server The replication slave server has the Physical memory 3.6 GB and the my.cnf file is as below : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /data1/mysql basedir = / port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M set-variable= thread_stack=256k log-bin server-id = 1 master-host=xxx.xxx.xxx.xxx master-user= yyy master-password= zzz master-port= 3306 innodb_data_home_dir = /data1/mysql/ innodb_data_file_path = ibdata1:800M:autoextend innodb_log_group_home_dir = /data1/mysql/ innodb_log_arch_dir = /data1/mysql/ set-variable = innodb_buffer_pool_size=2000M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=150M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable= innodb_file_io_threads=4 transaction-isolation = READ-COMMITTED innodb_thread_concurrency = 4 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 2:04 PM Subject: replication config without stopping master server Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This machine is as a replication master server. I have configured the other machine as replication slave with same version of MySQL and OS. Now, I want to start replication slave server without stopping the master server. Is it possible? What I did,-- I configured the slave server and started the slave replication. Also gave the command LOAD DATA FROM MASTER. This starts replication but the log files (localhost-relay-bin.xxx) taking to much space and the disk being 100% full. The database is InnoDB. Actual data for replication=1500MB (at master replication server) Disk space available=25GB (at slave server) After starting the slave the logfiles taking 24.5GB and only 0.5GB the actual data. What is wrong with my configuration?? I have the limitation that I have not to stop the master replication server. Please suggest me the solution. Regards Naveen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication config without stopping master server
Naveen, You don't need to shut down the master but you have to stop writing to the database for taking the snapshot. Follow the description in the manual: 3. If you are using MyISAM tables, flush all the tables and block write queries by executing `FLUSH TABLES WITH READ LOCK' command. mysql FLUSH TABLES WITH READ LOCK; and then take a snapshot of the data on your master server. snip While the read lock placed by `FLUSH TABLES WITH READ LOCK' is in effect, read the value of the current binary log name and offset on the master: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | mysql-bin.003 | 73 | test,bar | foo,manual,mysql | +---+--+--+--+ 1 row in set (0.06 sec) The `File' column shows the name of the log, while `Position' shows the offset. In the above example, the binary log value is `mysql-bin.003' and the offset is 73. Record the values. You will need to use them later when you are setting up the slave. Once you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master: mysql UNLOCK TABLES; Regards, Thomas Spahni On Tue, 31 Aug 2004, Naveen C Joshi wrote: Hi All : I am still waiting for the response. Thanks in advance. Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:17 PM Subject: Re: replication config without stopping master server The replication slave server has the Physical memory 3.6 GB and the my.cnf file is as below : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /data1/mysql basedir = / port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M set-variable= thread_stack=256k log-bin server-id = 1 master-host=xxx.xxx.xxx.xxx master-user= yyy master-password= zzz master-port= 3306 innodb_data_home_dir = /data1/mysql/ innodb_data_file_path = ibdata1:800M:autoextend innodb_log_group_home_dir = /data1/mysql/ innodb_log_arch_dir = /data1/mysql/ set-variable = innodb_buffer_pool_size=2000M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=150M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable= innodb_file_io_threads=4 transaction-isolation = READ-COMMITTED innodb_thread_concurrency = 4 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 2:04 PM Subject: replication config without stopping master server Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This machine is as a replication master server. I have configured the other machine as replication slave with same version of MySQL and OS. Now, I want to start replication slave server without stopping the master server. Is it possible? What I did,-- I configured the slave server and started the slave replication. Also gave the command LOAD DATA FROM MASTER. This starts replication but the log files (localhost-relay-bin.xxx) taking to much space and the disk being 100% full. The database is InnoDB. Actual data for replication=1500MB (at master replication server) Disk space available=25GB (at slave server) After starting the slave the logfiles taking 24.5GB and only 0.5GB the actual data. What is wrong with my configuration?? I have the limitation that I have not to stop the master replication server. Please suggest me
Re: replication config without stopping master server
Hi Thomas, Many thanks for valuable suggestion. My database is InnoDB here and the tables are in .frm, .MYD and .MYI. Is the same procedure for this database also? My master server database is being update in every minute and I will prefer to not lock on write. If there is no other way to replicate data on slave than I will do the same. As my database is being update in every minute and if I lock it for write, than is there any side effect or it will recover all after unlock. Please provide me help in this situation. Thanks Regards Naveen - Original Message - From: Thomas Spahni [EMAIL PROTECTED] To: Naveen C Joshi [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, August 31, 2004 9:48 PM Subject: Re: replication config without stopping master server Naveen, You don't need to shut down the master but you have to stop writing to the database for taking the snapshot. Follow the description in the manual: 3. If you are using MyISAM tables, flush all the tables and block write queries by executing `FLUSH TABLES WITH READ LOCK' command. mysql FLUSH TABLES WITH READ LOCK; and then take a snapshot of the data on your master server. snip While the read lock placed by `FLUSH TABLES WITH READ LOCK' is in effect, read the value of the current binary log name and offset on the master: mysql SHOW MASTER STATUS; +---+--+--+--+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +---+--+--+--+ | mysql-bin.003 | 73 | test,bar | foo,manual,mysql | +---+--+--+--+ 1 row in set (0.06 sec) The `File' column shows the name of the log, while `Position' shows the offset. In the above example, the binary log value is `mysql-bin.003' and the offset is 73. Record the values. You will need to use them later when you are setting up the slave. Once you have taken the snapshot and recorded the log name and offset, you can re-enable write activity on the master: mysql UNLOCK TABLES; Regards, Thomas Spahni On Tue, 31 Aug 2004, Naveen C Joshi wrote: Hi All : I am still waiting for the response. Thanks in advance. Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 4:17 PM Subject: Re: replication config without stopping master server The replication slave server has the Physical memory 3.6 GB and the my.cnf file is as below : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /data1/mysql basedir = / port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M set-variable= thread_stack=256k log-bin server-id = 1 master-host=xxx.xxx.xxx.xxx master-user= yyy master-password= zzz master-port= 3306 innodb_data_home_dir = /data1/mysql/ innodb_data_file_path = ibdata1:800M:autoextend innodb_log_group_home_dir = /data1/mysql/ innodb_log_arch_dir = /data1/mysql/ set-variable = innodb_buffer_pool_size=2000M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=150M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable= innodb_file_io_threads=4 transaction-isolation = READ-COMMITTED innodb_thread_concurrency = 4 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 2:04 PM Subject: replication config without stopping master server Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine
Re: replication config without stopping master server
The replication slave server has the Physical memory 3.6 GB and the my.cnf file is as below : [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir = /data1/mysql basedir = / port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M set-variable= thread_stack=256k log-bin server-id = 1 master-host=xxx.xxx.xxx.xxx master-user= yyy master-password= zzz master-port= 3306 innodb_data_home_dir = /data1/mysql/ innodb_data_file_path = ibdata1:800M:autoextend innodb_log_group_home_dir = /data1/mysql/ innodb_log_arch_dir = /data1/mysql/ set-variable = innodb_buffer_pool_size=2000M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=300M set-variable = innodb_log_buffer_size=150M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable= innodb_file_io_threads=4 transaction-isolation = READ-COMMITTED innodb_thread_concurrency = 4 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Regards Naveen - Original Message - From: Naveen C Joshi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, August 27, 2004 2:04 PM Subject: replication config without stopping master server Hi, I have MySQL-4.0.5a-beta installed on my RH9.0 Linux machine. This machine is as a replication master server. I have configured the other machine as replication slave with same version of MySQL and OS. Now, I want to start replication slave server without stopping the master server. Is it possible? What I did,-- I configured the slave server and started the slave replication. Also gave the command LOAD DATA FROM MASTER. This starts replication but the log files (localhost-relay-bin.xxx) taking to much space and the disk being 100% full. The database is InnoDB. Actual data for replication=1500MB (at master replication server) Disk space available=25GB (at slave server) After starting the slave the logfiles taking 24.5GB and only 0.5GB the actual data. What is wrong with my configuration?? I have the limitation that I have not to stop the master replication server. Please suggest me the solution. Regards Naveen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Config Bug (Re: master.info and my.cnf updates)
On Friday 24 August 2001 00:09, Jeremy Zawodny wrote: On Thu, Aug 23, 2001 at 01:15:25PM -0400, Robinson, Mike wrote: I just set up a master/slave replication scheme and all is well, but through the procedure did get bit on the tail. After the setup, and after the master and slave mysql servers had been started, I got some error messages that pointed to a type in the slave's my.cnf file. I shut the slave down, fixed the typo, and restarted. Got the same error. I had a look at the master.info, and noticed it contained old information from the previous my.cnf. There were no binary log entries in it, so I shut the slave down, deleted the master.info, and restarted the slave. This fixed the problem and they're replicating fine. (A terrific feature, my thanks to the mysql team for that one.) Question is, is this behaviour by design? Seems to me if you edit my.cnf and restart the mysql slave the master.info entries corresponding to the my.cnf file should be updated. Thanks for any insight. That's a common problem. Very common. You have to remove the master.info file, or edit it by hand. :-( I've called it a bug several times in the past, but it occurs to me that I may have never told the person who wrote the replication code. So... Sasha: Can this be fixed? In my mind it's a bug. One that has bitten too many people already. If not, we at least to get it documented here: http://www.mysql.com/doc/R/e/Replication_Features.html Thoughts? It is a feature that needs to be better documented. The main reason master.info is more authoritative that my.cnf is to support dynamic master changes. That is if you did CHANGE MASTER TO a different host, you want to continue replicating from that new master if you restart the slave in the middle. The options in my.cnf should really be called default-master-host, default-master-port, etc, but it is too late to change now - it would cause more problems that it would solve. In fact, we should probably change the replication HOWTO to suggest CHANGE MASTER TO command for replication setup instead of using my.cnf options. The recommended procedure to change masters is also to use CHANGE MASTER TO command. Thanks, Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 6 days, processed 88,944,778 queries (148/sec. avg) Jeremy, your server has been slowing down - it started out at 154 queries per second, and now is down to 148 :-) -- MySQL Development Team For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA ___/ - 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
