LinuxQuestions.org
Register a domain and help support LQ
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices

Reply
 
LinkBack Search this Thread
Old 07-31-2010, 06:22 PM   #1
gimpy530
Member
 
Registered: Oct 2007
Posts: 98

Rep: Reputation: 16
MySQL 5.1 not replicating properly with slave


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)

Last edited by gimpy530; 07-31-2010 at 06:27 PM.
 
Old 08-01-2010, 03:39 PM   #2
gimpy530
Member
 
Registered: Oct 2007
Posts: 98

Original Poster
Rep: Reputation: 16
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.
 
Old 08-03-2010, 09:44 AM   #3
gimpy530
Member
 
Registered: Oct 2007
Posts: 98

Original Poster
Rep: Reputation: 16
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Master-SLave Replication?? linuxshadow Linux - Server 7 06-13-2008 09:22 AM
replicating mysql on windows to mysql on linux sunethj Linux - Server 0 01-22-2007 08:40 PM
mysql slave replication question muxman Linux - Software 2 10-22-2006 07:49 PM
MySQL Table Replicating question... dolvmin Programming 3 02-21-2006 01:35 AM
MySQL Table Replicating question... dolvmin Linux - Software 1 02-20-2006 11:28 AM


All times are GMT -5. The time now is 01:41 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration