MYSQL Master-Slave replication not working with PHP

Question

My data gets replicated from the master database when I do it manually but when it gets inserted from PHP into the master database it doesn't get replicated to the slave database.

My Master database - my.cnf

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = database

My slave database - my.cnf

server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
relay-log = /var/log/mysql/mysql-relay-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = database
replicate_do_db = database
slave_net_timeout = 30
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1

My database table setup

 `id` int(11) NOT NULL AUTO_INCREMENT,
 `tmdbid` varchar(9) NOT NULL,
 `poster` varchar(256) NOT NULL,
 `title` varchar(128) NOT NULL,
 `tagline` varchar(128) NOT NULL,
 `plot` text NOT NULL,
 `year` year(4) NOT NULL,
 `release` varchar(18) NOT NULL,
 `runtime` char(6) NOT NULL,
 `genres` varchar(256) NOT NULL,
 `actors` text NOT NULL,
 `votes` varchar(5) NOT NULL,
 `mpaa` varchar(6) NOT NULL,
 `file2` text NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `link` text NOT NULL,
 `file` text NOT NULL,
 `link1` text,
 `link2` text,
 `uploaded` text,
 `been_sent_to_folder` text,
 `poster_backup` varchar(256) NOT NULL,
 `saved_poster_name` text NOT NULL,
 `poster140x100` varchar(256) NOT NULL,
 `poster65x95` varchar(256) NOT NULL,
 `poster_orig` varchar(256) NOT NULL,
 PRIMARY KEY (`tmdbid`),
 KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

PHP to insert to DB

mysql_query("INSERT INTO `database`.`tmdb` (`id`,`tmdbid`,`poster`,`title`,`tagline`,`plot`,`year`,`release`,`runtime`,`genres`,`actors`,`votes`,`mpaa`,`file2`,`link`,`file`,`poster_backup`,`saved_poster_name`,`poster140x100`,`poster65x95`,`poster_orig`) VALUES (NULL,'".$tmdbid."','".$poster."','".$title."','".$tagline."','".$plot."','".$year."','".$imrelease."','".$runtime."','".$genres."','".$actors."','".$votes."','".$mpaa."','".$file2."','".$link."','".$file."','".$poster_backup."','".$saved_poster_name."','".$finalthumb."','".$imgfinal."','".$imgfinal225."');")...

• Mihai: IF those value are external,it will be a matter of time before you are hacked.
• iC0NiC: @Mihai I have changed the information.

Answer 1

You have an syntax error in your php code, dbname.tblname and atlast semicolon needed, you had that inbetween

INSERT INTO `database`.`tmdb` (`id`...

• iC0NiC: Sorry, I mistakenly deleted it when I was editing it to hide my information.

Answer 2

First you should eliminate PHP as possible cause of error. You can log in via mysql command line with the credentials you normally would use in PHP and execute the INSERT statement by hand directly there. Since this should be exactly the same as if PHP executes the query, you should double check your PHP code if the replication works flawlessly this way.

Notice the database you choose when connecting via PHP and by hand. You configured your replication to only replicate a specific database! This is maybe not what you want and it has some side effects too, see manual. I would take a critic look at this too. Maybe your PHP connects to another database, which is not replicated.

Another way to check what is going on would be to read the binlogs of master and slave, which can be achieved with the tool mysqlbinlog.