GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Percona Cluster as replication slave - problems

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Percona Cluster as replication slave - problems

    I've got a 3-node Percona cluster, which I've set up to be a replication slave of an old MySQL server, by replicating into node A (the others being B and C).

    I can see that the cluster is functioning - if I make a change on A it is replicated to B; if I make a change on B it is replicated to A.

    I can also see that the replication is working - new changes are arriving on A.

    But these are not being replicated from A to B. On A I have log-slave-updates=true, and I can see mysqld-relay-bin* logfiles being produced.

    Any suggestions for how I can diagnose why the consumed changes on A aren't then being passed on to the other nodes in the cluster?

  • #2
    Can you please share my.cnf files from node A & B to check it further.

    Comment


    • #3
      Here you are. Apologies for putting them inline, but when I try to upload as attachments it says they're invalid files.

      I've edited out the passwords, and some comments to get below the 10000 character limit.

      As you can see they're messy.

      ----------------Config for A

      ################################################## ###########
      # The following options will be passed to all MySQL clients #
      ################################################## ###########

      [client]

      port = 3306
      socket = /var/run/mysqld/mysqld.sock

      #############################
      # mysqld - the MySQL server #
      #############################

      [mysqld]

      # EH set these explicitly to allow xtrabackup to pick them up
      datadir = /mnt/ssd/mysql

      ssl
      ssl-ca=/etc/mysql/certs/ca-cert.pem
      ssl-cert=/etc/mysql/certs/client-cert.pem
      ssl-key=/etc/mysql/certs/client-key.pem

      # EH end

      port = 3306
      socket = /var/run/mysqld/mysqld.sock
      pid-file = /var/run/mysqld/mysqld.pid

      skip-external-locking
      key_buffer_size = 384M
      max_allowed_packet = 1M
      table_open_cache = 512
      sort_buffer_size = 2M
      # EH change for replication issues
      read_buffer_size = 2M
      read_rnd_buffer_size = 8M
      #
      myisam_sort_buffer_size = 64M
      thread_cache_size = 8

      # EH No query cache with clustering?

      query_cache_size = 0
      query_cache_type = 0

      # Try number of CPU's*2 for thread_concurrency
      thread_concurrency = 8

      # Don't listen on a TCP/IP port at all. This can be a security enhancement,
      # if all processes that need to connect to mysqld run on the same host.
      # All interaction with mysqld must be made via Unix sockets or named pipes.
      # Note that using this option without enabling named pipes on Windows
      # (via the "enable-named-pipe" option) will render mysqld useless!
      #
      #skip-networking

      #
      # Logging and Replication
      #
      # Both location gets rotated by the cronjob.
      # Be aware that this log type is a performance killer.
      # As of 5.1 you can enable the log at runtime!
      #general_log_file = /var/log/mysql/mysql.log
      #general_log = 1
      #
      # Error log - should be very few entries.
      #
      log_error = /var/log/mysql/error.log

      # Replication Master Server (default)
      # binary logging is required for replication
      log-bin=mysql-bin
      binlog-format=ROW

      # Logging of slave updates is required so that we replicate out changes consumed from spcok
      # to other cluster members. This can be removed once we have finished migration
      log-slave-updates=true
      slave-skip-errors = 1062,1032

      # required unique id between 1 and 2^32 - 1
      # defaults to 1 if master-host is not set
      # but will not function as a master if omitted
      server-id = 101

      # Uncomment the following if you are using InnoDB tables
      innodb_file_per_table
      innodb_data_home_dir = /mnt/ssd/mysql
      innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
      innodb_log_group_home_dir = /mnt/ssd/mysql

      # You can set .._buffer_pool_size up to 50 - 80 %
      # of RAM but beware of setting memory usage too high
      innodb_buffer_pool_size = 384M
      innodb_additional_mem_pool_size = 20M

      # Set .._log_file_size to 25 % of buffer pool size
      innodb_log_file_size = 100M
      innodb_log_buffer_size = 8M
      innodb_flush_log_at_trx_commit = 1
      innodb_lock_wait_timeout = 50

      ########################################
      # mysqld - Galera Custer Configuration #
      ########################################

      ### Configuration for the cluster as a whole ###

      # bootstrapping
      wsrep_cluster_address = gcomm://

      # Normal operation
      #wsrep_cluster_address = gcomm://80.68.84.41,80.68.84.40,80.68.84.39

      wsrep_provider = /usr/lib/libgalera_smm.so # Path to Galera library
      wsrep_provider_options=’socket.checksum=1′
      wsrep_cluster_name = freegle # Identify the cluster
      wsrep_sst_method = xtrabackup-v2 # How to synchronize nodes
      wsrep_sst_auth = root:<password> # Should find a way to use SSH keys

      binlog_format = ROW # Required for Galera - binlog format must be ROW
      innodb_autoinc_lock_mode = 2 # Required for Galera - this changes how InnoDB autoincrement locks are managed
      innodb_locks_unsafe_for_binlog = 1 # Performance
      default_storage_engine = InnoDB # MyISAM storage engine has only experimental support

      ### Configuration for specific nodes ###

      # fd1
      wsrep_node_name = fd1
      wsrep_node_address = 80.68.84.41

      # mail1
      #wsrep_node_name = mail1
      #wsrep_node_address = 80.68.84.40

      # web1
      #wsrep_node_name = web1
      #wsrep_node_address = 80.68.84.39

      ###########################
      # application - mysqldump #
      ###########################

      [mysqldump]
      quick
      max_allowed_packet = 16M

      #######################
      # application - mysql #
      #######################

      [mysql]
      no-auto-rehash
      # Remove the next comment character if you are not familiar with SQL
      #safe-updates

      ###########################
      # application - myisamchk #
      ###########################

      [myisamchk]
      key_buffer_size = 256M
      sort_buffer_size = 256M
      read_buffer = 2M
      write_buffer = 2M

      ##############################
      # application - mysqlhotcopy #
      ##############################

      [mysqlhotcopy]
      interactive-timeout


      ------------------ Config for B

      ################################################## ###########
      # The following options will be passed to all MySQL clients #
      ################################################## ###########

      [client]

      port = 3306
      socket = /var/run/mysqld/mysqld.sock

      #############################
      # mysqld - the MySQL server #
      #############################

      [mysqld]

      # EH set these explicitly to allow xtrabackup to pick them up
      datadir = /var/lib/mysql
      # EH end

      port = 3306
      socket = /var/run/mysqld/mysqld.sock
      pid-file = /var/run/mysqld/mysqld.pid

      skip-external-locking
      key_buffer_size = 384M
      max_allowed_packet = 1M
      table_open_cache = 512
      sort_buffer_size = 2M
      read_buffer_size = 2M
      read_rnd_buffer_size = 8M
      myisam_sort_buffer_size = 64M
      thread_cache_size = 8

      # EH No query cache with clustering?

      query_cache_size = 0
      query_cache_type = 0

      # Try number of CPU's*2 for thread_concurrency
      thread_concurrency = 8

      # Don't listen on a TCP/IP port at all. This can be a security enhancement,
      # if all processes that need to connect to mysqld run on the same host.
      # All interaction with mysqld must be made via Unix sockets or named pipes.
      # Note that using this option without enabling named pipes on Windows
      # (via the "enable-named-pipe" option) will render mysqld useless!
      #
      #skip-networking

      #
      # Logging and Replication
      #
      # Both location gets rotated by the cronjob.
      # Be aware that this log type is a performance killer.
      # As of 5.1 you can enable the log at runtime!
      #general_log_file = /var/log/mysql/mysql.log
      #general_log = 1
      #
      # Error log - should be very few entries.
      #
      log_error = /var/log/mysql/error.log

      # Replication Master Server (default)
      # binary logging is required for replication
      log-bin=mysql-bin

      # required unique id between 1 and 2^32 - 1
      # defaults to 1 if master-host is not set
      # but will not function as a master if omitted
      server-id = 101


      # Uncomment the following if you are using InnoDB tables
      innodb_file_per_table
      innodb_data_home_dir = /var/lib/mysql
      innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
      innodb_log_group_home_dir = /var/lib/mysql

      # You can set .._buffer_pool_size up to 50 - 80 %
      # of RAM but beware of setting memory usage too high
      innodb_buffer_pool_size = 384M
      innodb_additional_mem_pool_size = 20M

      # Set .._log_file_size to 25 % of buffer pool size
      innodb_log_file_size = 100M
      innodb_log_buffer_size = 8M
      innodb_flush_log_at_trx_commit = 1
      innodb_lock_wait_timeout = 50

      ########################################
      # mysqld - Galera Custer Configuration #
      ########################################

      ### Configuration for the cluster as a whole ###

      # bootstrapping
      #wsrep_cluster_address = gcomm://

      # Normal operation
      wsrep_cluster_address = gcomm://80.68.84.41,80.68.84.40,80.68.84.39

      wsrep_provider = /usr/lib/libgalera_smm.so # Path to Galera library
      wsrep_provider_options=’socket.checksum=1′
      wsrep_cluster_name = freegle # Identify the cluster
      wsrep_sst_method = xtrabackup-v2 # How to synchronize nodes
      wsrep_sst_auth = root:<password> # Should find a way to use SSH keys

      binlog_format = ROW # Required for Galera - binlog format must be ROW
      innodb_autoinc_lock_mode = 2 # Required for Galera - this changes how InnoDB autoincrement locks are managed
      innodb_locks_unsafe_for_binlog = 1 # Performance
      default_storage_engine = InnoDB # MyISAM storage engine has only experimental support

      ### Configuration for specific nodes ###

      # fd1
      #wsrep_node_name = fd1
      #wsrep_node_address = 80.68.84.41

      # mail1
      #wsrep_node_name = mail1
      #wsrep_node_address = 80.68.84.40

      # web1
      wsrep_node_name = web1
      wsrep_node_address = 80.68.84.39

      ###########################
      # application - mysqldump #
      ###########################

      [mysqldump]
      quick
      max_allowed_packet = 16M

      #######################
      # application - mysql #
      #######################

      [mysql]
      no-auto-rehash
      # Remove the next comment character if you are not familiar with SQL
      #safe-updates

      ###########################
      # application - myisamchk #
      ###########################

      [myisamchk]
      key_buffer_size = 256M
      sort_buffer_size = 256M
      read_buffer = 2M
      write_buffer = 2M

      ##############################
      # application - mysqlhotcopy #
      ##############################

      [mysqlhotcopy]
      interactive-timeout

      Comment


      • #4
        Possible doh moment - does the clustering stuff work with MyISAM tables yet?

        (Yes - http://www.percona.com/doc/percona-x...er-5.5.23.html)

        Comment


        • #5
          Just noticed that A has the empty gcomm list from bootstrapping. I'll try removing that.

          Comment


          • #6
            No, that didn't help. It would stop A from joining the cluster, but in this case I had bootstrapped on A.

            Comment


            • #7
              Even odder. I tried creating a test table on an InnoDB database which is replicated into A. That change is replicated into A and on to B. However if I add data into that table, it is replicated into A but not on to B. If I then DROP the table, that change is replicated to both.

              So structural changes are working but data is not. Does that help?

              Comment


              • #8
                And in this example I can see using mysqlbinlog that the row change has been written out to the logfile on A.

                # at 272132
                #140505 2:44:35 server id 1 end_log_pos 402169 Query thread_id=1605 exec_time=0 error_code=0
                SET TIMESTAMP=1399254275/*!*/;
                /*!\C utf8 *//*!*/;
                SET @@session.character_set_client=33,@@session.collat ion_connection=33,@@session.collation_server=8/*!*/;
                BEGIN
                /*!*/;
                # at 272205
                # at 272248
                #140505 2:44:35 server id 1 end_log_pos 402212 Table_map: `wiki`.`test` mapped to number 293
                #140505 2:44:35 server id 1 end_log_pos 402246 Write_rows: table id 293 flags: STMT_END_F
                ### INSERT INTO wiki.test
                ### SET
                ### @1=2
                # at 272282
                #140505 2:44:35 server id 1 end_log_pos 402320 Query thread_id=1605 exec_time=0 error_code=0
                SET TIMESTAMP=1399254275/*!*/;
                COMMIT
                /*!*/;
                # at 272356
                #140505 2:44:40 server id 1 end_log_pos 402400 Query thread_id=546 exec_time=0 error_code=0

                ...but not replicated to other cluster members.

                Comment


                • #9
                  This is looking like a MyISAM issue. Even with wsrep_replicate_myisam set, the DML changes don't seem to replicate. The docs say that MyISAM replication is still experimental, so I think I'll have to see if I can convert my tables to InnoDB.

                  Comment

                  Working...
                  X