Re: Replication - urgent
Running this statement on the master should do it: DROP TABLE table1, table2...; That will remove the tables. Baron Ratheesh K J wrote: Hello all, I cannot afford to stop my slave server. I have list of tables of the master that are being replicated on the slave. Now I want to remove a couple of tables from this list without affecting the master and slave. How is this possible? Thanks & regards, Ratheesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] - urgent
Ratheesh K J schrieb: Thanks, It helped me a lot. I wanted to know 1. what are the various scenarios where my replication setup can fail? (considering even issues like network failure and server reboot etc). What is the normal procedure to correct the failure when something unpredicted happens? You should first read the right parts of the manual at https//dev.mysql.com/doc before asking such questions. Basically: -Use good hardware with ECC-RAM and RAID-Controllers in order to minimize trouble with faulty hardware. -Never write on the slaves without knowing what this could do to your replication setup -Watch the diskspace and make sure it's always enough space for the binlogs. Otherwise you might end up with half-written binlogs on either the slave or master because of a full disk which can cause trouble and some work to get it up and running again. When a master goes down or network connection is lost, the slave automatically tries to reconnect once a minute or so. Restarting the master or exchanging some network equipment is no problem. When the slave reboots, it tries to reconnect on startup, too. This is "out-of-the-box"-behaviour. You can modify it in the my.cnf (i.e. use the "skip-slave-start" option etc) 1. What are the scenarios where the SQL THREAD stops running and what are the scenarios where the IO THREAD stops running? SQL thread stops when it can't run a SQL-Query from the binlogs for any reason, as you have experiences when the table already existed. The IO-Thread only stops when it has an error reading a binlog from the master. When its only a lost connection, it automatically reconnects. Other problems (i.e. unable to read a binlog) should never happen as long a you don't delete binlogs on the master that have not yet been copied over to the slave by the io-thread ("show master status" and "show slave status" commands and their output) or you have faulty hardware (io_errors on the harddisk or such things) 1. Does SQL_SLAVE_SKIP_COUNTER skip the statement of the master binlog from being replicated to the slave relay log OR Has the statement already been copied into the slave relay log and has been skipped from the relay log? it skips the entry on the local copy of the binlog. The IO-Thread replicates the whole binlog and the sql-thread skips an entry in it when you use sql_slave_skip_counter 1. How do I know immediately that replication has failed? ( have heard that the enterprise edition has some technique for this )? watch the logfile, it is written there. Or run a cronjob once a minute with something like mysql -e 'show slave status\G' |grep '_Running:' >/dev/null || bash my_alarm_script_that_sends_mail_or_whatever.sh regards Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] - urgent
Thanks, It helped me a lot. I wanted to know 1.. what are the various scenarios where my replication setup can fail? (considering even issues like network failure and server reboot etc). What is the normal procedure to correct the failure when something unpredicted happens? 2.. What are the scenarios where the SQL THREAD stops running and what are the scenarios where the IO THREAD stops running? 3.. Does SQL_SLAVE_SKIP_COUNTER skip the statement of the master binlog from being replicated to the slave relay log OR Has the statement already been copied into the slave relay log and has been skipped from the relay log? 4.. How do I know immediately that replication has failed? ( have heard that the enterprise edition has some technique for this )? Thanks & regards, Ratheesh - Original Message - From: "Jan Kirchhoff" <[EMAIL PROTECTED]> To: "Ratheesh K J" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, October 02, 2007 4:16 PM Subject: Re: [Replication] - urgent > Ratheesh K J schrieb: >> Hello all, >> >> I issued a create table statement on the master for a table which was not >> present on the master but present on the slave. >> I did this purposely to see the error on slave. >> >> I am a newbie to replication. Now when i see SLave status on the slave >> machine it shows that the SQL Thread has stopped. >> >> When I start the SQL thread it does not start and gives the error message >> that the table exists. How do i correct this and how do I calculate the next >> position that the slave must start executing from the relay log. >> >> Is there any article on MySQL replication that tells me how to deal when >> errors occur. >> >> Thanks & regards, >> Ratheesh >> > > You have 2 options: > > 1. > on the slave, enter "SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;" and then > "SLAVE START;" on the slave. This skips the upcoming entry in the binlog > which is the create table command that causes your problem. > > 2. > if you don't have any data in the table on the slave, just drop the > table and do a "slave start;", it will then create the table again as > this is the next command in the binlog. > > Remember: never write on the slave without knowing what you do and > you'll be happy with your replication ;) > > Jan
Re: [Replication] - urgent
Ratheesh K J schrieb: Hello all, I issued a create table statement on the master for a table which was not present on the master but present on the slave. I did this purposely to see the error on slave. I am a newbie to replication. Now when i see SLave status on the slave machine it shows that the SQL Thread has stopped. When I start the SQL thread it does not start and gives the error message that the table exists. How do i correct this and how do I calculate the next position that the slave must start executing from the relay log. Is there any article on MySQL replication that tells me how to deal when errors occur. Thanks & regards, Ratheesh You have 2 options: 1. on the slave, enter "SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;" and then "SLAVE START;" on the slave. This skips the upcoming entry in the binlog which is the create table command that causes your problem. 2. if you don't have any data in the table on the slave, just drop the table and do a "slave start;", it will then create the table again as this is the next command in the binlog. Remember: never write on the slave without knowing what you do and you'll be happy with your replication ;) Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]