0

I followed the steps answered in the following question

Setting Circular Replication in mysql

I applied this steps in only two nods not three nods

I was trying to add commend answer @RolandoMySQLDB but i have to get 50 points in my reputation to add comment

My question is

1- why the grant privilege only for SELECT,REPLICATION SLAVE,REPLICATION CLIENT , what if i set the grant privilege to ALL ?

2- I checked my two databases in two servers and i see different row records not exact same row records

Server One

49 tables   Replication     Sum 1,877,070   MyISAM  latin1_swedish_ci   258.1 MiB   2.7 KiB

Server Two

49 tables   Replication     Sum 1,860,012   MyISAM  latin1_swedish_ci   309.3 MiB   1.7 MiB

why not row records same in both database ?

UPDATE

server 1 replication status

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.1
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 53097368
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 27094674
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 53097368
              Relay_Log_Space: 27094824
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
1 row in set (0.00 sec)

tables count

mysql> SELECT SUM(TABLE_ROWS)
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'database_name';
+-----------------+
| SUM(TABLE_ROWS) |
+-----------------+
|         1884080 |
+-----------------+
1 row in set (0.00 sec)

server 2 replication status

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.2
                  Master_User: replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 53090778
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 26458552
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 53090194
              Relay_Log_Space: 26458985
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

table count

mysql> SELECT SUM(TABLE_ROWS)
    -> FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_SCHEMA = 'database_name';
+-----------------+
| SUM(TABLE_ROWS) |
+-----------------+
|         1871429 |
+-----------------+
1 row in set (0.01 sec)

/etc/my.cnf in both server

[mysqld]
skip-name-resolve
server-id=2
log-bin=/var/log/mysql/mysql-bin.log
relay-log=/var/log/mysql/relay-bin.log
binlog_do_db           = database_name
log-slave-updates
slave_net_timeout = 60
slave-skip-errors=1062
sync_binlog = 1

default-storage-engine=MyISAM

local-infile=0
max_connections=200
max_allowed_packet=268435456
#wait_timeout=10

query_cache_size = 1M
query_cache_limit = 1M
thread_cache_size = 2
table_open_cache = 5586
#table_definition_cache = 1000
read_buffer_size = 1M

tmp_table_size=32M
max_heap_table_size=32M


open_files_limit=22344
tmpdir="/tmp"


innodb_buffer_pool_size=1024M
innodb_buffer_pool_instances=1
innodb_file_per_table=1
#innodb_log_file_size=64M
#innodb_log_buffer_size=32M
innodb_read_io_threads=32
innodb_write_io_threads=32

low_priority_updates=1
concurrent_insert=ALWAYS

########
##Logs##
########

long_query_time = 5
slow_query_log = 1
slow_query_log_file = /var/log/slow.log
#log_queries_not_using_indexes = 1


#general_log = on
#general_log_file=/var/log/mysqlog.log


[mysqld_safe]
log-error="/var/log/mysqld.log"
pid-file="/var/run/mysqld/mysqld.pid"
nice = -10

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
  • have you checked "show slave status\G" and make sure both servers are synced and also it would be better if you could point out difference with table name. You may check count(*) for all tables on both servers and compare and identify which table is out of sync. Moreover slave user requires those(SELECT,REPLICATION SLAVE,REPLICATION CLIENT) permissions and you can give full rights to slave user but it is not recommended to grant all privileges. Some unwanted user may also try exploiting slave user. – Nawaz Sohail Dec 8 '15 at 8:01
  • i update my question with all information you ask for but i don't know how could i identify which table is out of sync and i ask for privilege grant all maybe the row records need more than these privileges because i don't find any reason for why the row records not the same – iLinux85 Dec 8 '15 at 10:19
  • use following query to get count of all tables within one db select concat('select ''',table_name,''' ,count(*) from ',table_name ,' union all') from information_schema.tables where table_schema='db_name'; remove last union and compare result – Nawaz Sohail Dec 8 '15 at 11:19
  • Moreover can't see binlog_do_db in slave status output where as you have provided the configurations in cnf file – Nawaz Sohail Dec 8 '15 at 12:02
  • A slave needs only REPLICATION SLAVE. – Rick James Dec 11 '15 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.