Article:		Live Backups of MySQL Using Replication
Subject:		Replication Problems
Date:		2005-11-14 08:41:13
From:		scandog
I have gone through your tutorial but have ended up with problems. When I do a mysql dump I don't get any lines for bin file or start position. Also when I upload the sql statement to the slave it remove all the slave user info I added. I would think you wouldn't want to export the mysql database. What is the point of adding a mysql replicant user that points to the master if your going to over write it with the master dump file. Also my servers have different names, and after dumping the file and adding it to the slave I can't access the database from my database software because the host names have changed. I would also assume that you would have to restart the mysql servers after altering the my.cnf info, am I correct? Thanks for any help you can give.

Showing messages 1 through 3 of 3.

• Replication Problems
  2005-12-22 07:51:58  aquariusrick [View]
  
  
  It works really nicely the way the article describes if you create the replicant user without a specific host name, instead create it as 'replicant'@'%'. If you change your root account to 'root'@'localhost' you should be able to replicate the mysql database to the slave without causing problems when you overwrite it with the master dump file.
  
  (for those that MAY not know that @'%' and @'localhost' means, '%' is for ANY host and 'localhost' is for the logging in from the box itself only)
  
  If you think you may ever have more than one slave, I'd recommend doing it this way, that way all slaves could become the master with no required changes to the user table. The only command needed to change master on the slaves would be 'CHANGE MASTER TO'.
  
  This way if you have more than one slave you can promote any of the slaves to become a master and all remaining slaves can be pointed to replicate from the new master easily.
  
  If you really want to have a different set of user accounts on the slave than on the master then I'd recommend using the --ignore-table=mysql.xxxx for each table you want to ignore replication.
  
  Remember the whole point of this article is to set up replication for a working backup solution. So if you have different user accounts on the slave when you perform the backup, you'll need to have some way of restoring the user accounts associated with the master before you can call it a complete backup solution.

• Replication Problems
  2005-11-26 04:31:34  Russell Dyer | [View]
  
  
  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.
• 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?