Women in Technology

Hear us Roar



Article:
  Live Backups of MySQL Using Replication
Subject:   Replication Problems
Date:   2005-11-26 04:31:34
From:   russell_dyer
Response to: Replication Problems

Sorry for the delay in responding--I've been on the road and all, and dealing with hurricanes and what not. In case you haven't resolved this yet, though, here are some suggestions.


Regarding the lines to include the binary file name and the starting position, the --master-data option with mysqldump should put these at the end of the dump file. If not, you can manually run them on the slave:


CHANGE MASTER TO MASTER_LOG_FILE = 'bin.000001';
CHANGE MASTER TO MASTER_LOG_POS = 4;


You can get the correct values for your server by entering this statement on the master:


SHOW MASTER STATUS;


Regarding the user information, if there are already users on the slave--meaning it's not going to be used just for backups--and for security purposes, you probably should add the --ignore-table=mysql.users option to the mysqldump command when backing up the master. This will exclude the users table and thereby the passwords from the dump file. This should take care of the host name problem you mentioned related to the users, as well. If you utilize the other tables in the mysql database, since they contain references to users and hostnames, you should add them to the mysqldump line. You will have to add a separate key/value pair for each: --ignore-table=mysql.xxxx for each. I should have included all of this in the article.


Let me make two more clarification points to my article: don't start the slave replicating until you have the data copied or else it will try to start replicating before you can get the dump file unloaded. The problem is that you need to have MySQL running on the slave so that you can add the dump file. To get around this, after you have the slave configured initially, restart the mysqld daemon (or mysqld_safe) with the --skip-slave-start option. Actually, put that option in the my.cnf file to be safe. Just delete it after replication is running.


There is another point I'd like to add now that I'm reading your comments and a couple others: if your master has been binary logging for quite a while before you tried setting up replication, unless you have use for the logs, you might want to start fresh by issuing a RESET MASTER statement on the master. This will delete all of the binary log files and it will commit any outstanding transactions (e.g., on InnoDB and BDB tables), so be sure that you want to do this. You might want to make an extra backup of the databases and the binary logs before you reset the master, by the way. After you reset the mater, make your backup and copy it to the slave server while replication isn't running. Then start the slave with the START SLAVE statement. Starting fresh makes it easy to be assured of a good clean start. Check the SHOW PROCESSLIST; on both servers to see the states of each. Also, run SHOW SLAVE STATUS; on the slave to see if everything looks okay. It will list the last error number and message if replication failed after starting. Check the error logs for clues if there's a problem.

Full Threads Oldest First

Showing messages 1 through 1 of 1.

  • Replication Problems
    2007-03-23 05:46:26  Apix [View]

    070320 8:28:26 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
    Version: '5.0.27-community-nt' socket: '' port: 3306 MySQL Community Edition (GPL)
    070320 8:28:26 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '.\server_web1_relay_bin.000001' position: 4
    070320 8:28:26 [Note] Slave I/O thread: connected to master 'repl@ulfs01:3306', replication started in log 'FIRST' at position 4
    070320 9:18:47 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 9:41:01 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 10:34:36 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 10:41:53 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 10:55:50 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 10:57:07 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 11:59:38 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 15:30:09 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 18:23:18 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 18:25:05 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 19:45:55 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 22:00:28 [Warning] Warning: Enabling keys got errno 137, retrying
    070320 23:19:49 [Warning] Warning: Enabling keys got errno 137, retrying
    070321 2:29:43 [Warning] Warning: Enabling keys got errno 137, retrying
    070321 5:56:14 [Warning] Warning: Enabling keys got errno 137, retrying
    070321 8:10:33 [Warning] Warning: Enabling keys got errno 137, retrying
    070321 8:12:44 [Warning] Warning: Enabling keys got errno 137, retrying
    070321 9:22:39 [ERROR] Error reading packet from server: error reading log entry ( server_errno=1236)
    070321 9:22:39 [ERROR] Got fatal error 1236: 'error reading log entry' from master when reading data from binary log
    070321 9:22:39 [Note] Slave I/O thread exiting, read up to log 'ULFS01_LOGbv2.000001', position 30549740
    070321 10:06:16 [Warning] Warning: Enabling keys got errno 137, retrying
    070321 10:42:33 [Warning] Warning: Enabling keys got errno 137, retrying
    070321 11:59:32 [Warning] Warning: Enabling keys got errno 137, retrying
    070321 12:17:02 [Warning] Warning: Enabling keys got errno 137, retrying

    Stuck on this. Read a lot of things about. But you cant restart master and slave when the replication breaks every day.

    Any advice?