MariaDB Mati, Bagaimana Cara Backup Database?

mgilank · 1 Apr 2019

Itu sequence numbernya yg corrupt ya.
coba cara ini
- Convert all tables to myisam, remove ibdata1 & ib_logfile*, after server restart, convert all tables back to InnoDB

ref : https://www.percona.com/blog/2013/09/11/how-to-move-the-innodb-log-sequence-number-lsn-forward/

Milea Adnan · 1 Apr 2019

coba pastekan kesini my.cnf nya dulu
barangkali storage engine nya pakai myisam.

kalau pakai myisam, settingan innodb nya bisa di kasih pagar dulu.

luncur · 2 Apr 2019

Milea Adnan said:
coba pastekan kesini my.cnf nya dulu
barangkali storage engine nya pakai myisam.

kalau pakai myisam, settingan innodb nya bisa di kasih pagar dulu.

Code:

[client]
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 128M

[mysqld]
#innodb_force_recovery = 1
local-infile=0
ignore-db-dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# centminmod.com

#tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 512
max_connections = 750
key_buffer_size = 384M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 512K
table_definition_cache = 8192
table_open_cache = 8192
thread_cache_size = 256
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet = 128M
#max_seeks_for_key = 4294967295
#group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1024K
query_cache_size = 128M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads=1
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 8000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
#innodb_buffer_pool_instances=2

innodb_log_files_in_group = 2
innodb_log_file_size = 384M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 400
innodb_io_capacity_max = 2000
innodb_read_io_threads = 4
innodb_write_io_threads = 2
innodb_flush_neighbors = 1

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 64M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 64M
aria_sort_buffer_size = 64M

[mariadb-5.5]
innodb_file_format = Barracuda
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 128M

[myisamchk]
tmpdir=/home/mysqltmp
key_buffer = 384M
sort_buffer = 64M
read_buffer = 64M
write_buffer = 64M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_format = Barracuda
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

luncur · 2 Apr 2019

Input untuk journalctl -u mariadb --no-pager

Code:

Apr 01 11:16:18 sg2.domain.com mysqld[4868]: 2019-04-01 11:16:18 7efd334e1900 InnoDB: Error: page 1135 log sequence number 179058754035
Apr 01 11:45:20 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 11:45:21 sg2.domain.com mysqld[5662]: 2019-04-01 11:45:21 140397864040704 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 11:45:21 sg2.domain.com mysqld[5662]: 2019-04-01 11:45:21 140397864040704 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 5662 ...
Apr 01 11:45:37 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 11:45:37 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 11:45:37 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 11:45:37 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 12:14:09 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 12:14:10 sg2.domain.com mysqld[6228]: 2019-04-01 12:14:10 140571133217024 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 12:14:10 sg2.domain.com mysqld[6228]: 2019-04-01 12:14:10 140571133217024 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 6228 ...
Apr 01 12:14:14 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:14:14 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:14:14 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:14:14 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 12:14:45 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 12:14:45 sg2.domain.com mysqld[6505]: 2019-04-01 12:14:45 139723412056320 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 12:14:45 sg2.domain.com mysqld[6505]: 2019-04-01 12:14:45 139723412056320 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 6505 ...
Apr 01 12:14:49 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:14:49 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:14:49 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:14:49 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 12:18:03 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 12:18:03 sg2.domain.com mysqld[6811]: 2019-04-01 12:18:03 139789632010496 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 12:18:03 sg2.domain.com mysqld[6811]: 2019-04-01 12:18:03 139789632010496 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 6811 ...
Apr 01 12:18:07 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:18:07 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:18:07 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:18:07 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 12:34:36 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 12:34:37 sg2.domain.com mysqld[7429]: 2019-04-01 12:34:37 139723509590272 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 12:34:37 sg2.domain.com mysqld[7429]: 2019-04-01 12:34:37 139723509590272 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 7429 ...
Apr 01 12:34:41 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:34:41 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:34:41 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:34:41 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 12:53:54 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 12:53:54 sg2.domain.com mysqld[8038]: 2019-04-01 12:53:54 139861143828736 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 12:53:54 sg2.domain.com mysqld[8038]: 2019-04-01 12:53:54 139861143828736 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 8038 ...
Apr 01 12:53:58 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:53:58 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:53:58 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:53:58 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 12:58:02 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 12:58:03 sg2.domain.com mysqld[8359]: 2019-04-01 12:58:03 139754407459072 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 12:58:03 sg2.domain.com mysqld[8359]: 2019-04-01 12:58:03 139754407459072 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 8359 ...
Apr 01 12:58:06 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:58:06 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:58:06 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:58:06 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 13:03:06 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 13:03:07 sg2.domain.com mysqld[8694]: 2019-04-01 13:03:07 140328711383296 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 13:03:07 sg2.domain.com mysqld[8694]: 2019-04-01 13:03:07 140328711383296 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 8694 ...
Apr 01 13:03:10 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 13:03:10 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 13:03:10 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 13:03:10 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 13:10:22 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 13:10:23 sg2.domain.com mysqld[9357]: 2019-04-01 13:10:23 139905791641856 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 13:10:23 sg2.domain.com mysqld[9357]: 2019-04-01 13:10:23 139905791641856 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 9357 ...
Apr 01 13:10:27 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 13:10:27 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 13:10:27 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 13:10:27 sg2.domain.com systemd[1]: mariadb.service failed.
[00:59][[email protected] ~]#

natanetwork · 2 Apr 2019

kalo ga salah lbh enak pake default myisam. kalo db corrupt di innodb agak sulit benerinnya kalo ga salah inget

luncur · 2 Apr 2019

Ada solusi forum di Centmin saat InnoDB database ada masalah: https://community.centminmod.com/th...to-reset-your-mysql-database-directory.10001/
Cuma mysqldump-nya harus berhasil dulu agar bisa direstor setelah pakai mysql_install_db. Karena Mariadb masih down, jadinya nggak bisa mysqldump. Solusi dari tuan @mgilank untuk force recovery, apalagi sampai tingkat 6 belum memungkinkan kalau belum bisa dump.

Mohon pencerahan bagaimana Mariadb bisa start lagi.

mas.satriyo · 2 Apr 2019

luncur said:
Input untuk journalctl -u mariadb --no-pager

coba option 'ignore-db-dirs=lost+found' di comment out dulu mas
variabel log_warnings set jadi 2
paste-kan output error log mariadb ke sini mas

luncur · 2 Apr 2019

mas.satriyo said:
coba option 'ignore-db-dirs=lost+found' di comment out dulu mas
variabel log_warnings set jadi 2
paste-kan output error log mariadb ke sini mas

Code:

Apr 01 11:16:19 sg2.domain.com mysqld[4868]: 2019-04-01 11:16:19 7efd334e1900 InnoDB: Error: page 1184 log sequence number 179057809143
Apr 01 11:16:19 sg2.domain.com mysqld[4868]: InnoDB: is in the future! Current system log sequence number 178723625312.
Apr 01 11:16:19 sg2.domain.com mysqld[4868]: InnoDB: Your database may be corrupt or you may have copied the InnoDB
Apr 01 11:16:19 sg2.domain.com mysqld[4868]: InnoDB: tablespace but not the InnoDB log files. See
Apr 01 11:16:19 sg2.domain.com mysqld[4868]: InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
Apr 01 11:16:19 sg2.domain.com mysqld[4868]: InnoDB: for more information.
Apr 01 12:18:03 sg2.domain.com mysqld[6811]: 2019-04-01 12:18:03 139789632010496 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 6811 ...
Apr 01 12:18:07 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:18:07 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:18:07 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:18:07 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 12:34:36 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 12:34:37 sg2.domain.com mysqld[7429]: 2019-04-01 12:34:37 139723509590272 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 12:34:37 sg2.domain.com mysqld[7429]: 2019-04-01 12:34:37 139723509590272 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 7429 ...
Apr 01 12:34:41 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:34:41 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:34:41 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:34:41 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 12:53:54 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 12:53:54 sg2.domain.com mysqld[8038]: 2019-04-01 12:53:54 139861143828736 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 12:53:54 sg2.domain.com mysqld[8038]: 2019-04-01 12:53:54 139861143828736 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 8038 ...
Apr 01 12:53:58 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:53:58 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:53:58 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:53:58 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 12:58:02 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 12:58:03 sg2.domain.com mysqld[8359]: 2019-04-01 12:58:03 139754407459072 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 12:58:03 sg2.domain.com mysqld[8359]: 2019-04-01 12:58:03 139754407459072 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 8359 ...
Apr 01 12:58:06 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 12:58:06 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 12:58:06 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 12:58:06 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 13:03:06 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 13:03:07 sg2.domain.com mysqld[8694]: 2019-04-01 13:03:07 140328711383296 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 13:03:07 sg2.domain.com mysqld[8694]: 2019-04-01 13:03:07 140328711383296 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 8694 ...
Apr 01 13:03:10 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 13:03:10 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 13:03:10 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 13:03:10 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 01 13:10:22 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 01 13:10:23 sg2.domain.com mysqld[9357]: 2019-04-01 13:10:23 139905791641856 [Note] Using unique option prefix 'ignore-db-dir' is error-prone and can break in the future. Please use the full name 'ignore_db_dirs' instead.
Apr 01 13:10:23 sg2.domain.com mysqld[9357]: 2019-04-01 13:10:23 139905791641856 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 9357 ...
Apr 01 13:10:27 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 01 13:10:27 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 01 13:10:27 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 01 13:10:27 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 02 02:00:08 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 02 02:00:09 sg2.domain.com mysqld[5545]: 2019-04-02  2:00:09 140121620789504 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 5545 ...
Apr 02 02:00:13 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 02 02:00:13 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 02 02:00:13 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 02 02:00:13 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 02 02:09:18 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 02 02:09:19 sg2.domain.com mysqld[6664]: 2019-04-02  2:09:19 140395443386624 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 6664 ...
Apr 02 02:09:28 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 02 02:09:28 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 02 02:09:28 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 02 02:09:28 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 02 02:47:12 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 02 02:47:12 sg2.domain.com mysqld[8880]: 2019-04-02  2:47:12 139659753134336 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 8880 ...
Apr 02 02:47:16 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 02 02:47:16 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 02 02:47:16 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 02 02:47:16 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 02 02:49:51 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 02 02:49:51 sg2.domain.com mysqld[9153]: 2019-04-02  2:49:51 140608383023360 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 9153 ...
Apr 02 02:49:55 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 02 02:49:55 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 02 02:49:55 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 02 02:49:55 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 02 02:54:16 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 02 02:54:17 sg2.domain.com mysqld[9471]: 2019-04-02  2:54:17 140443222706432 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 9471 ...
Apr 02 02:54:21 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 02 02:54:21 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 02 02:54:21 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 02 02:54:21 sg2.domain.com systemd[1]: mariadb.service failed.
Apr 02 03:18:13 sg2.domain.com systemd[1]: Starting MariaDB 10.1.38 database server...
Apr 02 03:18:14 sg2.domain.com mysqld[10862]: 2019-04-02  3:18:14 139739413940480 [Note] /usr/sbin/mysqld (mysqld 10.1.38-MariaDB) starting as process 10862 ...
Apr 02 03:18:18 sg2.domain.com systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Apr 02 03:18:18 sg2.domain.com systemd[1]: Failed to start MariaDB 10.1.38 database server.
Apr 02 03:18:18 sg2.domain.com systemd[1]: Unit mariadb.service entered failed state.
Apr 02 03:18:18 sg2.domain.com systemd[1]: mariadb.service failed

Milea Adnan · 2 Apr 2019

luncur said:

Code:

[client]
socket=/var/lib/mysql/mysql.sock

[mysql]
max_allowed_packet = 128M

[mysqld]
#innodb_force_recovery = 1
local-infile=0
ignore-db-dirs=lost+found
character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# centminmod.com

#tmpdir=/home/mysqltmp

innodb=ON
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 512
max_connections = 750
key_buffer_size = 384M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 512K
table_definition_cache = 8192
table_open_cache = 8192
thread_cache_size = 256
wait_timeout = 1800
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet = 128M
#max_seeks_for_key = 4294967295
#group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 512K
bulk_insert_buffer_size = 8M
# query_cache boost for MariaDB >10.1.2+
# https://community.centminmod.com/posts/30811/
query_cache_limit = 1024K
query_cache_size = 128M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
log-error=/var/log/mysqld.log

# innodb settings
innodb_large_prefix=1
innodb_purge_threads=1
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_open_files = 8000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M

## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
#innodb_buffer_pool_instances=2

innodb_log_files_in_group = 2
innodb_log_file_size = 384M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 400
innodb_io_capacity_max = 2000
innodb_read_io_threads = 4
innodb_write_io_threads = 2
innodb_flush_neighbors = 1

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 64M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 64M
aria_sort_buffer_size = 64M

[mariadb-5.5]
innodb_file_format = Barracuda
innodb_file_per_table = 1

#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 128M

[myisamchk]
tmpdir=/home/mysqltmp
key_buffer = 384M
sort_buffer = 64M
read_buffer = 64M
write_buffer = 64M

[mysqlhotcopy]
interactive-timeout

[mariadb-10.0]
innodb_file_format = Barracuda
innodb_file_per_table = 1

# 2 variables needed to switch from XtraDB to InnoDB plugins
#plugin-load=ha_innodb
#ignore_builtin_innodb

## MariaDB 10 only save and restore buffer pool pages
## warm up InnoDB buffer pool on server restarts
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0
## Disabled settings
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

Ok, skrg pastekan hasil command :
# ls -al /var/lib/mysql

luncur · 2 Apr 2019

Milea Adnan said:
Ok, skrg pastekan hasil command :
# ls -al /var/lib/mysql

Code:

[03:51][[email protected] tmp]# ls -al /var/lib/mysql
total 864208
drwxr-xr-x  32 mysql mysql      4096 Apr  2 03:51 .
drwxr-xr-x. 36 root  root       4096 Apr  2 02:02 ..
-rwxr-xr-x   1 mysql mysql         0 Mar 31 22:25 aria_log_control
drwxr-xr-x   2 mysql mysql      4096 Jun  8  2017 cmsetiofiotest
-rwxr-xr-x   1 mysql mysql  77594624 Apr  2 03:51 ibdata1
-rwxr-xr-x   1 mysql mysql 402653184 Apr  2 03:51 ib_logfile0
-rwxr-xr-x   1 mysql mysql    494301 Jun  8  2017 ib_logfile0.gz
-rwxr-xr-x   1 mysql mysql 402653184 Apr  1 11:45 ib_logfile1
-rwxr-xr-x   1 mysql mysql    130290 Jun  8  2017 ib_logfile1.gz
-rwxr-xr-x   1 mysql mysql         0 Jun  8  2017 multi-master.info
drwxr-xr-x   2 mysql mysql      4096 Jun  8  2017 mysql
srwxr-xr-x   1 mysql mysql         0 Apr  2 03:51 mysql.sock
-rwxr-xr-x   1 mysql mysql        15 Jun  8  2017 mysql_upgrade_info
drwxr-xr-x   2 mysql mysql      4096 Jun  8  2017 performance_schema
-rwxr-xr-x   1 mysql mysql   1141220 Apr  1 09:17 sg2.luncur.com.err
-rwxr-xr-x   1 mysql mysql         0 Mar 31 19:13 sg2.pid
drwxr-xr-x   2 mysql mysql      4096 Mar 31 09:24 wp117666149db_1577
drwxr-xr-x   2 mysql mysql     12288 Mar 31 17:20 wp120613102db_9953
drwxr-xr-x   2 mysql mysql      4096 Mar 22 07:07 wp122864463db_20115
drwxr-xr-x   2 mysql mysql      4096 Dec  7 16:02 wp1266423172db_9524
drwxr-xr-x   2 mysql mysql      4096 Aug 12  2018 wp161541985db_5744
drwxr-xr-x   2 mysql mysql      4096 Mar 19 20:34 wp1724225203db_24837
drwxr-xr-x   2 mysql mysql     20480 Mar 31 09:01 wp1987918db_4599
drwxr-xr-x   2 mysql mysql      4096 Sep  4  2018 wp23158290db_17021
drwxr-xr-x   2 mysql mysql      4096 Mar 30 18:05 wp2419923678db_25686
drwxr-xr-x   2 mysql mysql      4096 Mar 22 02:56 wp2531420282db_25430
drwxr-xr-x   2 mysql mysql      4096 Oct 21 07:04 wp2653511664db_18646
drwxr-xr-x   2 mysql mysql      4096 Aug 12  2018 wp2748218323db_2707
drwxr-xr-x   2 mysql mysql      4096 Nov  6 16:49 wp2838812067db_8903
drwxr-xr-x   2 mysql mysql     12288 Mar 20 19:52 wp289277783db_4347
drwxr-xr-x   2 mysql mysql     12288 Mar 26 23:15 wp2971029681db_30108
drwxr-xr-x   2 mysql mysql     12288 Mar 31 13:06 wp314925437db_26104
drwxr-xr-x   2 mysql mysql     12288 Mar 20 08:16 wp3151623734db_12378
drwxr-xr-x   2 mysql mysql     20480 Mar 20 00:48 wp40878265db_22805
drwxr-xr-x   2 mysql mysql      4096 Mar 31 09:04 wp479627266db_5125
drwxr-xr-x   2 mysql mysql      4096 Feb 22 00:11 wp493923626db_4233
drwxr-xr-x   2 mysql mysql     12288 Mar 21 00:02 wp582522237db_690
drwxr-xr-x   2 mysql mysql     12288 Mar 20 08:04 wp594127001db_3669
drwxr-xr-x   2 mysql mysql     12288 Mar 29 11:10 wp793020092db_817
drwxr-xr-x   2 mysql mysql     12288 Mar 30 15:30 wp892515631db_19289
drwxr-xr-x   2 mysql mysql     20480 Mar 30 04:57 wp904625704db_651
drwxr-xr-x   2 mysql mysql     12288 Mar 26  2018 wp909121964db_24550
drwxr-xr-x   2 mysql mysql      4096 Oct 14 07:02 wp99689558db_25289

MariaDB Mati, Bagaimana Cara Backup Database?

mgilank

Expert 1.0

Milea Adnan

Expert 1.0

luncur

Beginner 2.0

luncur

Beginner 2.0

natanetwork

Hosting Guru

luncur

Beginner 2.0

mas.satriyo

Hosting Guru

luncur

Beginner 2.0

Milea Adnan

Expert 1.0

luncur

Beginner 2.0