Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5
-
None
-
5.5.68-MariaDB MariaDB Server
Description
Hi Team,
i'm getting the frequent errors of below in slave node, kindly help me to update the configuration file on slave
Error message getting on slave server:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'master-bin.057804' at 718417804, the last event read from 'master-bin.062834' at 935584838, the last byte read from 'master-bin.062834' at 935584857.'
Mention binglog file is not present as we have seted up the binlog retention policy to 1 day. Please check and kindly help us to find the root cause and permanent solution of this error.
cat /etc/my.cnf.d/server.cnf
- These groups are read by MariaDB server.
- Use it for options that only the server (but not clients) should see
- See the examples of server my.cnf files in /usr/share/mysql/
- this is read by the standalone daemon and embedded servers
[server]
- this is only for the mysqld standalone daemon
[mysqld] - -------------------------------------------------
- GENERAL SYSTEM SETTINGS
- -------------------------------------------------
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
innodb_file_format=barracuda
innodb_large_prefix=1
innodb_file_per_table=1
- -------------------------------------------------
- You don't typically have to change these
- -------------------------------------------------
#max_allowed_packet = 18M
#max_allowed_packet=128M
#max_allowed_packet=512M
max_allowed_packet=950M
table_cache = 600
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
thread_cache_size = 32
- -------------------------------------------------
- for db replication
- -------------------------------------------------
server-id = 2
binlog-format = mixed
relay_log = master-relay-bin
#slave-skip-errors=1032
- -------------------------------------------------
- put table which need to be replicate
- -------------------------------------------------
#replicate_do_table=cacti.grid_jobs
#replicate_do_table=cacti.grid_jobs_finished
#replicate-wild-do-table=cacti.grid_jobs_finished_%
- -------------------------------------------------
- This will help with frequently asked questions
- -------------------------------------------------
query_cache_size = 128M
- -------------------------------------------------
- Try number of CPU's*2 for thread_concurrency
- -------------------------------------------------
thread_concurrency = 16
- -------------------------------------------------
- Cactid is aggressive at thread consumption
- -------------------------------------------------
max_connections = 2000
- -------------------------------------------------
- You need this if running PHP4
- -------------------------------------------------
old_passwords = y
- -------------------------------------------------
- Max Heap are Better for Group By's
- -------------------------------------------------
#max_heap_table_size = 1000M
max_heap_table_size = 8000M
#tmp_table_size = 32216M
tmp_table_size = 16000M
- -------------------------------------------------
- Let's log slow queries of more than 20 seconds
- -------------------------------------------------
#log-queries-not-using-indexes
slow_query_log = ON
long_query_time = 20
- -------------------------------------------------
- MyISAM STORAGE ENGINE SETTINGS
- -------------------------------------------------
- -------------------------------------------------
- Make as Large as Possible for MyISAM Storage
- -------------------------------------------------
key_buffer = 256M
join_buffer_size = 128M
- -------------------------------------------------
- Will keep a temporary table in memory, so don't spare size here
- -------------------------------------------------
myisam_sort_buffer_size = 64M
- -------------------------------------------------
- InnoDB STORAGE ENGINE SETTINGS
- -------------------------------------------------
- -------------------------------------------------
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci - -------------------------------------------------
- -------------------------------------------------
- This is important for tracking the size of the database
- -------------------------------------------------
#innodb_file_per_table
innodb_stats_on_metadata = OFF
#innodb_large_prefix=1
#innodb_file_format=barracuda
innodb_flush_method=O_DIRECT
innodb_doublewrite = 0
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1: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
- e.g. set to 50% if using as local database and
- set to 80% if using as remote database
- -------------------------------------------------
#innodb_buffer_pool_size = 257733M
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 2
- -------------------------------------------------
- Set .._log_file_size to 25 % of buffer pool size
- -------------------------------------------------
innodb_log_file_size = 16G
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
[isamchk]
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
- this is only for embedded server
[embedded]
- This group is only read by MariaDB-5.5 servers.
- If you use the same .cnf file for MariaDB of different versions,
- use this group for options that older servers don't understand
[mysqld-5.5]
- These two groups are only read by MariaDB servers, not by MySQL.
- If you use the same .cnf file for MySQL and MariaDB,
- you can put MariaDB-only options here
[mariadb]
[mariadb-5.5]