0

I'm experiencing some trouble setting up mysql replication between a master & a slave..

I did the setup successfully, but data doesn't update.

Master : show master status;

[File]: mysql-bin.000033
[Position]: 1757196
[Binlog_Do_DB]: ciel

Master : show processlist;

[User]: slave
[Host]: 92.222.177.xxx:57578 ( right slave ip )
[db]: 
[Command]: Binlog Dump
[Time]: 1231
[State]: Has sent all binlog to slave; waiting for binlog to be updated

Slave : show slave status;

[Slave_IO_State]: Waiting for master to send event
[Master_Host]: 46.105.122.xxx
[Master_User]: slave
[Master_Port]: 3306
[Connect_Retry]: 60
[Master_Log_File]: mysql-bin.000033
[Read_Master_Log_Pos]: 1757196
[Relay_Log_File]: mysqld-relay-bin.000006
[Relay_Log_Pos]: 252
[Relay_Master_Log_File]: mysql-bin.000033
[Slave_IO_Running]: Yes
[Slave_SQL_Running]: Yes
[Replicate_Do_DB]: ciel
[Exec_Master_Log_Pos]: 1757196
[Relay_Log_Space]: 409
[Until_Condition]: None
[Master_SSL_Allowed]: No
[Master_SSL_Verify_Server_Cert]: No
[Master_Server_Id]: 1

Slave : show proccesslist;

[User]: system user
[Host]: 
[db]: 
[Command]: Connect
[Time]: 1231
[State]: Waiting for master to send event
[Info]: 

[Id]: 2
[User]: system user
[Host]: 
[db]: 
[Command]: Connect
[Time]: 1231
[State]: Slave has read all relay log; waiting for the slave I/O thread to update it

then selecting data on master :

master: lastmod: 2014-10-26 17:14:55
slave:  lastmod: 2014-10-26 15:45:45

I'm feeling lost, because I'm still not finding after 8 hours, how to set this up correctly.

Here's the masters my.cnf

[client]
port          = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice          = 0
[mysqld]
wait_timeout=24000
interactive_timeout=24000

server-id=1
log-bin=/var/log/mysql/mysql-bin.log
expire_logs_days  = 10
max_binlog_size   = 100M
binlog-do-db=ciel

max_connections=0
tmp_table_size=52M
max_heap_table_size=52M
table_cache = 1000
thread_cache_size = 12
query_cache_limit=2M
query_cache_size = 62M
query_cache_min_res_unit=220

long_query_time=3
log_error=/l/mysqlerr.log
log_slow_queries=/l/mysqlslow.log
#bind-address=0.0.0.0
user          = mysql
pid-file     = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port          = 3306
basedir          = /usr
datadir          = /var/lib/mysql
tmpdir          = /tmp
language     = /usr/share/mysql/english
skip-external-locking
key_buffer =80M
max_allowed_packet = 80M
myisam-recover         = BACKUP
innodb_file_per_table = 1
thread_concurrency = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M

[mysqldump]
quick
quote-names
max_allowed_packet=128M
[mysql]
[isamchk]
key_buffer = 256M
write_buffer = 2M
read_buffer = 2M
sort_buffer_size = 256M
!includedir /etc/mysql/conf.d/

and then the slave cnf

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
log_bin     = /var/log/mysql/mysql-bin.log
server-id=2
log-slave-updates = 1
replicate-do-db='ciel'
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir     = /usr
datadir = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer      = 16M
max_allowed_packet = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit   = 1M
query_cache_size = 16M
expire_logs_days    = 10
max_binlog_size         = 100M
innodb_file_per_table = 1
default-storage-engine = InnoDB
[mysqldump]
quick
quote-names
max_allowed_packet  = 16M
[mysql]
[isamchk]
key_buffer      = 16M
!includedir /etc/mysql/conf.d/
4
  • According to these displays it is all working perfectly. What do the relevant portions of the respective my.ini files look like?
    – user207421
    Nov 1, 2014 at 22:29
  • How did you initially copy the database from master to slave? Nov 3, 2014 at 7:57
  • locking tables, dumping the whole database, importing it into slave, then configuration, then unlocking tables on master
    – Jack
    Nov 3, 2014 at 8:44
  • Found out the solution, It was a single statement in both my.cnf binlog-format=row;
    – Jack
    Jan 21, 2015 at 8:31

2 Answers 2

0
replicate-do-db='ciel'

Remove the quotes. Your replication system is working perfectly but doing nothing because of this.

1
  • Isn't working neither, fail2ban logs & mysql.err & mysql.log are just empty one at this time funny part is that rsync works quite good between those 2 hosts ..
    – Jack
    Nov 17, 2014 at 8:04
0

I definitely found out what was wrong.

Setup was correct, but all queries within php code were using the format :

update database.table

So both my.cnf were needing that parameter.

binlog-format=row;

1
  • 1
    If your english is buggy, is well tolerated. But if you can't learn, one need to put exactly a single "." to the end of the sentences...
    – peterh
    Jan 21, 2015 at 9:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .