3

I'm trying to get replication working between 2 servers running MariaDB 10.1.7 64 bit. The database I'm trying to replicate is huge, 520G and the export/import on the slave with disabled autocommits took 5 days. When I initially started the replication, it seemed that the slave is going to catch up. It actually looked very promising. Sadly, after 30 minutes when I checked the slave again I realized that it is actually falling behind. 1 week after during night time when the master wasn't so busy, it really seemed that it's catching up again but unfortunately now Seconds_Behind_Master is almost 1 week. I set slave_parallel_mode to aggressive and on the slave I've set slave_parallel_threads to 30, while on the master this is set to 10. The innodb_flush_log_at_trx_commit variable is 1 on the slave and 0 on the master. Here is the config for the master:

[client]
port          = 3306
socket        = /var/lib/mysql/mysqld.sock
[mysqld]
port          = 3306
socket        = /var/lib/mysql/mysqld.sock
log-error=/var/log/mysql/logs/mysql.log
datadir=/RAID50/innodb/mysql/
init-connect = 'SET NAMES UTF8'
character-set-server = utf8
server_id=1
sync_binlog=1
back_log = 50
max_connections = 450
max_connect_errors = 9999999
table_cache = 4096
max_allowed_packet = 16M
binlog_cache_size = 1M
max_binlog_size = 100M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 128M
query_cache_limit = 4M
query_alloc_block_size = 16K
expire_logs_days=14
# Set the default table type

event_scheduler=ON
default-storage-engine=InnoDB 
thread_stack = 192K
transaction_isolation = READ-COMMITTED
tmp_table_size = 64M
log-bin=/binlog/mysql/master-bin
# binary logging format - mixed recommended
binlog_format=mixed

slow_query_log_file=/var/log/mysql/logs/slow-queries.log
slow_query_log=1
long_query_time=1
tmpdir = /tmp 
slave_load_tmpdir = /var/log/mysql/tmpfs
innodb_additional_mem_pool_size = 32M

# This config file assumes a main memory of at least 8G
# innodb_buffer_pool_size = 6.5G    # Default setting
innodb_buffer_pool_size = 8G

# Set this option if you would like the InnoDB tablespace files to be
# stored in another location. By default this is the MySQL datadir.
innodb_file_per_table = 1
innodb_data_home_dir = /RAID50/innodb/mysql/
innodb_file_format = Barracuda
innodb_data_file_path = ibdata1:156M;ibdata2:156M:autoextend
# innodb_data_file_path = /data1/innodb/mysql/
innodb_autoextend_increment=156M


# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
# innodb_thread_concurrency = 16
innodb_thread_concurrency = 32

innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M

# make sure the log files are large enough that you don't hold up
# checkpoints when the logs rotate!
innodb_log_file_size = 200M

# Total number of files in the log group. A value of 2-3 is usually good
# enough.
innodb_log_files_in_group = 2
innodb_log_group_home_dir = /var/log/mysql/innodb_logs
innodb_max_dirty_pages_pct = 80
# use directIO to bypass filesystem cache where possible
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 30
innodb_commit_concurrency=0
innodb_open_files=2000
[mysqldump]
# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

default-character-set = utf8

# Only allow UPDATEs and DELETEs that use keys.
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
open-files-limit = 102400

This is the configuration file for the slave:

[mysqld]
datadir=/data1/mysql
socket=/var/lib/mysql/mysql.sock
# replication implementation
server-id=101
log-bin=mysql-bin
binlog_format=mixed
read-only=1
relay-log=mysql-relay-bin
log-slave-updates=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

init-connect = 'SET NAMES UTF8'
character-set-server = utf8
innodb_file_per_table = 1
innodb_file_format = Barracuda
innodb_data_file_path = ibdata1:156M;ibdata2:156M:autoextend
innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 80
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 30
innodb_commit_concurrency=0
innodb_open_files=2000
slave_parallel_threads = 10

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit = 102400

#
# include all files from the config directory
#
#!includedir /etc/my.cnf.d

Here is the output for SHOW SLAVE STATUS \G:

MariaDB [(none)]> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.25
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.005174
          Read_Master_Log_Pos: 86423579
               Relay_Log_File: mysql-relay-bin.000109
                Relay_Log_Pos: 77202510
        Relay_Master_Log_File: master-bin.005116
             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: 1932
                   Last_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 77202221
              Relay_Log_Space: 6273918609
              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: 580994
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1932
               Last_SQL_Error: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: aggressive
1 row in set (0.00 sec)

I do know about the Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist in engine and it should be fixed, but I am not sure if it is the cause for the replication lag. Does anyone have any ideas what I should be tweaking? The slave has a better CPU than the master.

2

The error saying "mysql.gtid_slave_pos does not exist" is blocking the replication process on the slave. You need to fix this error before replication can go on.

You can try to ignore this error and see what happens. Connect with mysql CLI on the slave and type the following:

mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
2
  • Thanks for your reply - I have fixed those errors and restarted the MySQL import process but replication is still lagging.
    – Mugurel
    Dec 10 '15 at 11:06
  • For me this was not enought, i had to remove and had table in mysql database : dba.stackexchange.com/a/122620/30692
    – NicoMinsk
    Jul 27 '16 at 13:21
-1

You can try this steps as below given

Run the mysql upgrade ..

mysql_upgrade -u root -p

it should be repare all well, if its show errors like

Error : The user specified as a definer ('appuser'@'%') does not exist error : Corrupt Phase 3/6: Running 'mysql_fix_privilege_tables' ERROR 1813 (HY000) at line 64: Tablespace for table 'mysql.innodb_table_stats' exists. Please DISCARD the tablespace before IMPORT. ERROR 1813 (HY000) at line 68: Tablespace for table 'mysql.innodb_index_stats' exists. Please DISCARD the tablespace before IMPORT. ERROR 1813 (HY000) at line 136: Tablespace for table 'mysql.gtid_slave_pos' exists. Please DISCARD the tablespace before IMPORT. ERROR 1146 (42S02) at line 581: Table 'mysql.innodb_index_stats' doesn't exist ERROR 1146 (42S02) at line 584: Table 'mysql.innodb_table_stats' doesn't exist FATAL ERROR: Upgrade failed

than remove the all .ibd file from the mysql/mysql folder after backup the all files\folder.

rm gtid_slave_pos.ibd rm innodb_index_stats.ibd innodb_table_stats.ibd

now again run the mysql_upgrade -u root -p

and its upgrade all databases and auto create the all required files.

-1

You just have to recreate the table to fix this

http://alidba.blogspot.in/2017/05/table-mysqlgtidslavepos-doesnt-exist.html

1
  • Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
    – Jenny D
    Jun 7 '17 at 13:01

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.