[SOLVED] MySQL 5.1 not replicating properly with slave

gimpy530 · 07-31-2010, 06:22 PM

I recently set up replication on a production server and some rows are not making it to the tables on the slave. After 3 days I can see 200+ entries (of 161,000~) that are on the master and not the slave. Logs under /var/log including mysqld.log, mysql.status, messages do not show any problem.

I have already tried dropping the DB on the slave and setting up the replication again by doing a stop slave, set slave options such as were in the logging to start, drop table, restore the dump previously made by the master, start slave, but the issue persists.

I following the documentation on the MySQL site for setting up replication so I'm not sure why this is having issues.

What can I do to investigate and fix this?

A show slave status gives me this:

Code:

mysql> show slave status;
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host    | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File        | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | <MASTER REMOVED> | <USER REMOVED>        |        3306 |            60 | mysql-bin.000001 |           179092474 | mail-relay-bin.000002 |     179090996 | mysql-bin.000001      | Yes              | Yes               | unit_delta      |                     |                    |                        |                         |                             |          0 |            |            0 |           179092474 |       179090996 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+-----------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

gimpy530 · 08-01-2010, 03:39 PM

Just discovered more information on this.

On the primary there are PHP scripts which do the work of putting the data into MySQL. Any data which is entered into MySQL via PHP does not get replicated. None of it.

If I manually update anything via MySQL command line, such as by creating a record, it WILL replicate just fine.

So, anything from PHP does not replicate, anything via command line does replicate.

Any ideas what would cause this?

Note that I did not create the PHP that handles this and I know nothing about it.

gimpy530 · 08-03-2010, 09:44 AM

Figured it out. When using the option --replicate-do-db=db_name it will only care about data entered in the DB in a certain way. The PHP code referenced the DB by doing dbname.tablename and not the equivalent of "use dbname;" then doing the work on the table. During my testing I logged in, did a use, then did the tests which is why it worked.

By removing that option and instead having it ignore the dbs I don't want replicated, it works.