Re: Restarting slave after interruption

2008-05-24 Thread Moon's Father 
This is a classic error.
You just reconfigurate the parameter master_log_file and master_log_pos
.Good luck.

On Fri, May 23, 2008 at 5:13 AM, Mike [EMAIL PROTECTED] wrote:

 On Thu, May 22, 2008 at 10:41 AM, François Beausoleil 
 [EMAIL PROTECTED]
 wrote:

  Hi all!
  I must be stupid or something.  I can't find what my problem is.
 
  I searched this list, and did find a couple of hits, but nothing that
  seemed fully relevant.  This one in particular was interesting:
  http://lists.mysql.com/mysql/212863
 
  I have a single master (server-id=1) and a single slave (server-id=2).
  Replication was correctly setup, and I was doing backups from the slave.
  To
  test recovery, I terminated the slave server (I'm on EC2), and I now wish
 to
  start a new one.
 

 If your taking a dump of the master open two windows.  Don't close either
 In the first lock all tables with FLUSH ALL TABLES WITH READ LOCK;  master
 status;
 Then in the second window procedure with mysqldump.


 
  First, I dump my master using this:
 
  mysqldump --master-data --flush-logs --extended-insert
 --single-transaction
  -u root -papassword mydb thedump.sql
 
  Then, I copy the dump to the slave and load the dump using:
 
  mysql -u root -papassword mydb thedump.sql
 
  Next, I login to the slave server using the mysql command line client and
  issue the following commands:
 
  CHANGE MASTER TO MASTER_HOST='10.252.155.80',
 
   MASTER_USER='root',
 
   MASTER_PASSWORD='apassword',
 
   MASTER_LOG_FILE='mysql-bin.54',
 
   MASTER_LOG_POS=98;
 
  I read the log file and position from the dump (--master-data),
  specifically, this line:
 
  CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.54', MASTER_LOG_POS=98;
 
  Then, I issue a START SLAVE on the slave, and SHOW SLAVE STATUS\G
 
  Here's the output:
 
  *** 1. row ***
  Slave_IO_State: Waiting for master to send event
 Master_Host: 10.252.155.80
 Master_User: root
 Master_Port: 3306
   Connect_Retry: 60
 Master_Log_File: mysql-bin.54
 Read_Master_Log_Pos: 39727978
  Relay_Log_File: mysqld-relay-bin.02
   Relay_Log_Pos: 1381
   Relay_Master_Log_File: mysql-bin.54
Slave_IO_Running: Yes
   Slave_SQL_Running: No
 Replicate_Do_DB:
 Replicate_Ignore_DB:
  Replicate_Do_Table:
  Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
  Last_Errno: 1062
  Last_Error: Error 'Duplicate entry '479084' for key


 This is a Duplicate primary key.  Usually means the position error.

 
  1' on query. Default database: 'mydb'. Query: 'INSERT INTO assets
  (`content_type`, `email_id`, `size`, `thumbnail`, `updated_at`,
  `account_id`, `archive_id`, `title`, `type`, `folder_id`,
  `description`, `filename`, `height`, `owner_id`, `parent_id`,
  `created_at`, `width`) VALUES('image/jpeg', NULL, 6571, 'small',
  '2008-05-21 23:24:10', 125, NULL, NULL, NULL, NULL, NULL,
  '0101100102000104022008052003d78e21fe087d9623000b5c-21_small.jpg',
  180, NULL, 479082, '2008-05-21 23:24:10', 240)'
Skip_Counter: 0
 Exec_Master_Log_Pos: 1244
 Relay_Log_Space: 39728115
 Until_Condition: None
  Until_Log_File:
   Until_Log_Pos: 0
  Master_SSL_Allowed: No
  Master_SSL_CA_File:
  Master_SSL_CA_Path:
 Master_SSL_Cert:
   Master_SSL_Cipher:
  Master_SSL_Key:
   Seconds_Behind_Master: NULL
 
  Anybody can tell me what I'm doing wrong ?  Am I dumping my master DB
 using
  the right combination of options ?
 
  Thanks !
  François Beausoleil
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 




-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Re: Restarting slave after interruption

2008-05-24 Thread François Beausoleil 

Thanks Mike!

Le 2008-05-22 à 17:13, Mike a écrit :


If your taking a dump of the master open two windows.  Don't close  
either
In the first lock all tables with FLUSH ALL TABLES WITH READ LOCK;   
master status;

Then in the second window procedure with mysqldump.


The exact syntax was FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;

Bye !
François
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Restarting slave after interruption

2008-05-22 Thread Mike 
On Thu, May 22, 2008 at 10:41 AM, François Beausoleil [EMAIL PROTECTED]
wrote:

 Hi all!
 I must be stupid or something.  I can't find what my problem is.

 I searched this list, and did find a couple of hits, but nothing that
 seemed fully relevant.  This one in particular was interesting:
 http://lists.mysql.com/mysql/212863

 I have a single master (server-id=1) and a single slave (server-id=2).
 Replication was correctly setup, and I was doing backups from the slave.  To
 test recovery, I terminated the slave server (I'm on EC2), and I now wish to
 start a new one.


If your taking a dump of the master open two windows.  Don't close either
In the first lock all tables with FLUSH ALL TABLES WITH READ LOCK;  master
status;
Then in the second window procedure with mysqldump.



 First, I dump my master using this:

 mysqldump --master-data --flush-logs --extended-insert --single-transaction
 -u root -papassword mydb thedump.sql

 Then, I copy the dump to the slave and load the dump using:

 mysql -u root -papassword mydb thedump.sql

 Next, I login to the slave server using the mysql command line client and
 issue the following commands:

 CHANGE MASTER TO MASTER_HOST='10.252.155.80',

  MASTER_USER='root',

  MASTER_PASSWORD='apassword',

  MASTER_LOG_FILE='mysql-bin.54',

  MASTER_LOG_POS=98;

 I read the log file and position from the dump (--master-data),
 specifically, this line:

 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.54', MASTER_LOG_POS=98;

 Then, I issue a START SLAVE on the slave, and SHOW SLAVE STATUS\G

 Here's the output:

 *** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: 10.252.155.80
Master_User: root
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: mysql-bin.54
Read_Master_Log_Pos: 39727978
 Relay_Log_File: mysqld-relay-bin.02
  Relay_Log_Pos: 1381
  Relay_Master_Log_File: mysql-bin.54
   Slave_IO_Running: Yes
  Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 1062
 Last_Error: Error 'Duplicate entry '479084' for key


This is a Duplicate primary key.  Usually means the position error.


 1' on query. Default database: 'mydb'. Query: 'INSERT INTO assets
 (`content_type`, `email_id`, `size`, `thumbnail`, `updated_at`,
 `account_id`, `archive_id`, `title`, `type`, `folder_id`,
 `description`, `filename`, `height`, `owner_id`, `parent_id`,
 `created_at`, `width`) VALUES('image/jpeg', NULL, 6571, 'small',
 '2008-05-21 23:24:10', 125, NULL, NULL, NULL, NULL, NULL,
 '0101100102000104022008052003d78e21fe087d9623000b5c-21_small.jpg',
 180, NULL, 479082, '2008-05-21 23:24:10', 240)'
   Skip_Counter: 0
Exec_Master_Log_Pos: 1244
Relay_Log_Space: 39728115
Until_Condition: None
 Until_Log_File:
  Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
Master_SSL_Cert:
  Master_SSL_Cipher:
 Master_SSL_Key:
  Seconds_Behind_Master: NULL

 Anybody can tell me what I'm doing wrong ?  Am I dumping my master DB using
 the right combination of options ?

 Thanks !
 François Beausoleil
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]