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. 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
