MRatWork Forum by Mustafa Ramadhan
Language Specific Discussions => Indonesia Users => Topic started by: Miki on 2014-01-01, 18:51:29
-
berdasarkan lapiran pada gambar yang didapat dari '/var/lib/mysql' yang mana sajakah yang boleh dihapus?
mohon petunjuknya master..
Terima kasih.
-
1. Jadikan '#log_bin = /var/log/mysql/mysql-bin.log' (ada '#') pada /etc/my.cnf
2. Stop mysql
3. Hapus semua file mysql-bin
4. start mysql
-
lapor tidak ditemukan "log_bin = /var/log/mysql/mysql-bin.log' pada '/etc/my.cnf' :
[mysqld]
;###123###;###123###skip-innodb
default-storage-engine=myisam
## General
datadir = /var/lib/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
;###123###;###123###skip-name-resolve
sql-mode = NO_ENGINE_SUBSTITUTION
#event-scheduler = 1
## Cache
thread-cache-size = 16
table-open-cache = 4096
table-definition-cache = 2048
query-cache-size = 32M
query-cache-limit = 1M
## Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 1M
join-buffer-size = 1M
## Temp Tables
tmp-table-size = 32M
max-heap-table-size = 64M
## Networking
back-log = 100
#max-connections = 200
max-connect-errors = 10000
max-allowed-packet = 100M
interactive-timeout = 3600
wait-timeout = 600
### Storage Engines
innodb = FORCE
## MyISAM
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
## InnoDB
#innodb-buffer-pool-size = 128M
#innodb-log-file-size = 100M
#innodb-log-buffer-size = 8M
#innodb-file-per-table = 1
#innodb-open-files = 300
## Replication
server-id = 1
#log-bin = /var/log/mysql/bin-log
#relay-log = /var/log/mysql/relay-log
relay-log-space-limit = 16G
expire-logs-days = 7
#read-only = 1
#sync-binlog = 1
#log-slave-updates = 1
#binlog-format = STATEMENT
#auto-increment-offset = 1
#auto-increment-increment = 2
## Logging
#log-output = FILE
#slow-query-log = 1
#slow-query-log-file = /var/log/mysql/slow-log
#log-slow-slave-statements
#long-query-time = 2
[mysqld_safe]
log-error = /var/log/mysqld.log
open-files-limit = 65535
[mysql]
no-auto-rehash
-
tampilkan saja 'dir -l /var/lib/mysql/mysql*'.
Coba baca juga http://www.cyberciti.biz/faq/what-is-mysql-binary-log/
-
Juga http://www.marcinrybak.com/2009/09/clean-mysql-binary-logs-with.html
-
dir -l /var/lib/mysql/mysql*
-rw-rw---- 1 mysql mysql 344185532 Dec 26 21:29 /var/lib/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 2123726 Dec 26 21:38 /var/lib/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql 57170964 Dec 26 21:46 /var/lib/mysql/mysql-bin.000003
-rw-rw---- 1 mysql mysql 121767188 Dec 26 22:21 /var/lib/mysql/mysql-bin.000004
-rw-rw---- 1 mysql mysql 42893952 Dec 26 22:31 /var/lib/mysql/mysql-bin.000005
-rw-rw---- 1 mysql mysql 13761246 Dec 26 22:37 /var/lib/mysql/mysql-bin.000006
-rw-rw---- 1 mysql mysql 33119769 Dec 26 22:44 /var/lib/mysql/mysql-bin.000007
-rw-rw---- 1 mysql mysql 1073818928 Dec 27 03:33 /var/lib/mysql/mysql-bin.000008
-rw-rw---- 1 mysql mysql 1073743105 Dec 27 10:31 /var/lib/mysql/mysql-bin.000009
-rw-rw---- 1 mysql mysql 1073746086 Dec 27 16:12 /var/lib/mysql/mysql-bin.000010
-rw-rw---- 1 mysql mysql 267515992 Dec 27 17:23 /var/lib/mysql/mysql-bin.000011
-rw-rw---- 1 mysql mysql 5895359 Dec 27 17:25 /var/lib/mysql/mysql-bin.000012
-rw-rw---- 1 mysql mysql 228 Dec 27 17:23 /var/lib/mysql/mysql-bin.index
srwxrwxrwx 1 mysql mysql 0 Jan 2 05:56 /var/lib/mysql/mysql.sock
/var/lib/mysql/mysql:
total 1060
-rw-rw---- 1 mysql mysql 8820 Sep 22 11:40 columns_priv.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 columns_priv.MYD
-rw-rw---- 1 mysql mysql 4096 Sep 22 11:40 columns_priv.MYI
-rw-rw---- 1 mysql mysql 9582 Sep 22 11:40 db.frm
-rw-rw---- 1 mysql mysql 29480 Jan 1 08:22 db.MYD
-rw-rw---- 1 mysql mysql 9216 Jan 1 08:26 db.MYI
-rw-rw---- 1 mysql mysql 10223 Sep 22 11:40 event.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 event.MYD
-rw-rw---- 1 mysql mysql 2048 Sep 22 11:40 event.MYI
-rw-rw---- 1 mysql mysql 8665 Sep 22 11:40 func.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 func.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 func.MYI
-rw-rw---- 1 mysql mysql 8776 Sep 22 11:40 general_log.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 general_log.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 general_log.MYI
-rw-rw---- 1 mysql mysql 8700 Sep 22 11:40 help_category.frm
-rw-rw---- 1 mysql mysql 1120 Sep 22 11:40 help_category.MYD
-rw-rw---- 1 mysql mysql 3072 Sep 22 11:40 help_category.MYI
-rw-rw---- 1 mysql mysql 8612 Sep 22 11:40 help_keyword.frm
-rw-rw---- 1 mysql mysql 91999 Sep 22 11:40 help_keyword.MYD
-rw-rw---- 1 mysql mysql 17408 Sep 22 11:40 help_keyword.MYI
-rw-rw---- 1 mysql mysql 8630 Sep 22 11:40 help_relation.frm
-rw-rw---- 1 mysql mysql 9423 Sep 22 11:40 help_relation.MYD
-rw-rw---- 1 mysql mysql 18432 Sep 22 11:40 help_relation.MYI
-rw-rw---- 1 mysql mysql 8770 Sep 22 11:40 help_topic.frm
-rw-rw---- 1 mysql mysql 465272 Sep 22 11:40 help_topic.MYD
-rw-rw---- 1 mysql mysql 21504 Sep 22 11:40 help_topic.MYI
-rw-rw---- 1 mysql mysql 9510 Sep 22 11:40 host.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 host.MYD
-rw-rw---- 1 mysql mysql 2048 Sep 22 11:40 host.MYI
-rw-rw---- 1 mysql mysql 8778 Dec 31 17:36 ndb_binlog_index.frm
-rw-rw---- 1 mysql mysql 8586 Sep 22 11:40 plugin.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 plugin.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 plugin.MYI
-rw-rw---- 1 mysql mysql 9996 Sep 22 11:40 proc.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 proc.MYD
-rw-rw---- 1 mysql mysql 2048 Sep 22 11:40 proc.MYI
-rw-rw---- 1 mysql mysql 8875 Sep 22 11:40 procs_priv.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 procs_priv.MYD
-rw-rw---- 1 mysql mysql 4096 Sep 22 11:40 procs_priv.MYI
-rw-rw---- 1 mysql mysql 8800 Sep 22 11:40 proxies_priv.frm
-rw-rw---- 1 mysql mysql 1386 Sep 22 11:40 proxies_priv.MYD
-rw-rw---- 1 mysql mysql 5120 Sep 22 11:40 proxies_priv.MYI
-rw-rw---- 1 mysql mysql 8838 Sep 22 11:40 servers.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 servers.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 servers.MYI
-rw-rw---- 1 mysql mysql 8976 Sep 22 11:40 slow_log.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 slow_log.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 slow_log.MYI
-rw-rw---- 1 mysql mysql 8955 Sep 22 11:40 tables_priv.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 tables_priv.MYD
-rw-rw---- 1 mysql mysql 4096 Sep 22 11:40 tables_priv.MYI
-rw-rw---- 1 mysql mysql 8636 Sep 22 11:40 time_zone.frm
-rw-rw---- 1 mysql mysql 8624 Sep 22 11:40 time_zone_leap_second.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 time_zone_leap_second.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 time_zone_leap_second.MYI
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 time_zone.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 time_zone.MYI
-rw-rw---- 1 mysql mysql 8606 Sep 22 11:40 time_zone_name.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 time_zone_name.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 time_zone_name.MYI
-rw-rw---- 1 mysql mysql 8686 Sep 22 11:40 time_zone_transition.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 time_zone_transition.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 time_zone_transition.MYI
-rw-rw---- 1 mysql mysql 8748 Sep 22 11:40 time_zone_transition_type.frm
-rw-rw---- 1 mysql mysql 0 Sep 22 11:40 time_zone_transition_type.MYD
-rw-rw---- 1 mysql mysql 1024 Sep 22 11:40 time_zone_transition_type.MYI
-rw-rw---- 1 mysql mysql 10630 Sep 22 11:40 user.frm
-rw-rw---- 1 mysql mysql 6584 Jan 1 08:22 user.MYD
-rw-rw---- 1 mysql mysql 4096 Jan 1 08:26 user.MYI
-
Coba lihat saja directory (artinya itu sekaligus name domain) mana saja yang menghabiskan ruang.
Menjalankan 'sh /script/mysql-optimize --select=optimize' akan 'merapikan' (semacam defrag) database sehingga akan memperkecil pemakaian ruang.
-
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.35 Distributed by The IUS Community Project
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> set global expire_logs_days=14;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "expire%";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 14 |
+------------------+-------+
1 row in set (0.01 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> quit;
Bye
-
Coba lihat saja directory (artinya itu sekaligus name domain) mana saja yang menghabiskan ruang.
Menjalankan 'sh /script/mysql-optimize --select=optimize' akan 'merapikan' (semacam defrag) database sehingga akan memperkecil pemakaian ruang.
lama banget selesainya egk sabaran di ctrl+cencel :
( note : Table does not support optimize, doing recreate + analyze instead )
hampir semua begitu..
-
Innodb memang tidak support optimize. Hanya MyISAM dan Aria yang support optimize.
-
dir -l /var/lib/mysql
total 388430616
mysql mysql 4096 Jan 3 02:26 admin
mysql mysql 4096 Jan 7 12:47 afterlogic
mysql mysql 397353680896 Jan 9 07:41 ibdata1
mysql mysql 5242880 Jan 9 07:41 ib_logfile0
mysql mysql 5242880 Jan 9 07:41 ib_logfile1
Bolehkah yang diberi warna merah dihapus? ada efek samping nya egk?
Thx
-
dir -l /var/lib/mysql
total 388430616
mysql mysql 4096 Jan 3 02:26 admin
mysql mysql 4096 Jan 7 12:47 afterlogic
mysql mysql 397353680896 Jan 9 07:41 ibdata1
mysql mysql 5242880 Jan 9 07:41 ib_logfile0
mysql mysql 5242880 Jan 9 07:41 ib_logfile1
Bolehkah yang diberi warna merah dihapus? ada efek samping nya egk?
Thx
Jangan didelete. Pelajari http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablespaces.html dan http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table
-
The paths to individual InnoDB data files and their sizes. The full directory path to each data file is formed by concatenating innodb_data_home_dir to each path specified here. The file sizes are specified KB, MB or GB (1024MB) by appending K, M or G to the size value. If specifying data file size in kilobytes (KB), do so in multiples of 1024. Otherwise, KB values are rounded off to nearest megabyte (MB) boundary. The sum of the sizes of the files must be at least slightly larger than 10MB. If you do not specify innodb_data_file_path, the default behavior is to create a single auto-extending data file, slightly larger than 10MB, named ibdata1. The size limit of individual files is determined by your operating system. You can set the file size to more than 4GB on those operating systems that support big files. You can also use raw disk partitions as data files. For detailed information on configuring InnoDB tablespace files, see Section 14.3.6, “InnoDB Configuration”.
http://dev.mysql.com/doc/refman/5.5/en/innodb-configuration.html
semakin hari semakin bengkak:
375.088G ibdata1[mysqld]
innodb_file_per_table=on
;###123###;###123###;###123###;###123###;###123###;###123###skip-innodb
default-storage-engine=myisam
## General
datadir = /var/lib/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
;###123###;###123###;###123###;###123###;###123###;###123###skip-name-resolve
sql-mode = NO_ENGINE_SUBSTITUTION
#event-scheduler = 1
## Cache
thread-cache-size = 16
table-open-cache = 4096
table-definition-cache = 2048
query-cache-size = 32M
query-cache-limit = 1M
## Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 1M
join-buffer-size = 1M
## Temp Tables
tmp-table-size = 32M
max-heap-table-size = 64M
## Networking
back-log = 100
#max-connections = 200
max-connect-errors = 10000
max-allowed-packet = 100M
interactive-timeout = 3600
wait-timeout = 600
### Storage Engines
innodb = FORCE
## MyISAM
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
## InnoDB
#innodb-buffer-pool-size = 128M
#innodb-log-file-size = 100M
#innodb-log-buffer-size = 8M
#innodb-file-per-table = 1
#innodb-open-files = 300
## Replication
server-id = 1
#log-bin = /var/log/mysql/bin-log
#relay-log = /var/log/mysql/relay-log
relay-log-space-limit = 16G
expire-logs-days = 3
#read-only = 1
#sync-binlog = 1
#log-slave-updates = 1
#binlog-format = STATEMENT
#auto-increment-offset = 1
#auto-increment-increment = 2
## Logging
#log-output = FILE
#slow-query-log = 1
#slow-query-log-file = /var/log/mysql/slow-log
#log-slow-slave-statements
#long-query-time = 2
[mysqld_safe]
log-error = /var/log/mysqld.log
open-files-limit = 65535
[mysql]
no-auto-rehash
-
pusing master.. ::)
-
Coba lihat saja directory (artinya itu sekaligus name domain) mana saja yang menghabiskan ruang.
Menjalankan 'sh /script/mysql-optimize --select=optimize' akan 'merapikan' (semacam defrag) database sehingga akan memperkecil pemakaian ruang.
lama banget selesainya egk sabaran di ctrl+cencel :
( note : Table does not support optimize, doing recreate + analyze instead )
hampir semua begitu..
akhirnya ketemu yang bikin lama saat melakukan optimize:
There is a common issue whereby a sites 'cache_form' table gets really big. It basically doesn't clear on Cron runs and just keeps growing, often getting to a couple GB in size.
There are various posts about possible causes and cures, but if you find that you aren't able to log into your site to clear your caches due to your DB size limit being breached, then you can use this MySQL query to clear the 'cache_form' table manually. This will just clear the table - it won't break your site and you won't lose any data.
dan saya juga sudah melakukan 'sh /script/mysql-convert --engine=MyISAM' lanjut dengan 'sh /script/mysql-optimize --select=optimize'
sedikit lega.. walau belum ke tahap penyelesaian bengkaknya pada:
mysql 404525940736 Jan 10 03:58 ibdata1
nunggu kode dari master yang akan saya paste di ssh master :D
:D Terima kasih master..
-
Jika anda yakin semua table sudah myisam (periksa melalui phpmyadmin), maka ibdata1 tidak diperlukan lagi.
-
:) dari seluruh hasil cek semua sudah myisam kecuali 2 gampar yang dilampirkan:
apakah itu tidak menjadikan sebuah trouble jika saya menghapus ibdata1 ?
Terima kasih
-
Coba lihat di /usr/share/mysql/my-huge.cnf ada 'innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend'. Ini menunjukkan dimana ibdata* bisa diset ukuran maksimalnya.
Silahkan pelajari apa yang dimaksud.
BTW, anda bisa pakai setting my.cnf lain dengan menyalin /usr/share/mysql/my-huge.cnf ke /etc/my.cnf misalnya.
Forum ini menggunakan my-huge.cnf sebagai 'template' untuk /etc/my.cnf.
-
serem juga main-main dengan ibdata1 ;D tadinya mau langsung hapus' karena ragu akhirnya rename aja ibdata1.bak dan restart sql menunjukan daemon failed.
untung aja egk langsung dihapus jadi masih bisa 'mv /var/lib/mysql/ibdata1.bak /var/lib/mysql/ibdata1
mv: overwrite `/var/lib/mysql/ibdata1'? y'
mengenai /usr/share/mysql/my-huge.cnf saya hanya mengabil beberapa bagian dari isinya dan disertakan pada yang sedang digunakan saja :
## -----------------------------------------------------------------------
## \ ^__^
## \ (oo)\_______
## (__)\ )\/\
## ||----w |
## || ||
[mysqld]
skip-innodb
default-storage-engine=myisam
innodb_file_per_table=on
## General
datadir = /var/lib/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
;###123###;###123###;###123###;###123###;###123###;###123###;###123###skip-name-resolve
sql-mode = NO_ENGINE_SUBSTITUTION
#event-scheduler = 1
## Cache
thread-cache-size = 16
table-open-cache = 4096
table-definition-cache = 2048
query-cache-size = 32M
query-cache-limit = 1M
## Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 4M
join-buffer-size = 2M
## Temp Tables
tmp-table-size = 32M
max-heap-table-size = 64M
## Networking
#back-log = 100
#max-connections = 200
#max-connect-errors = 10000
max-allowed-packet = 100M
interactive-timeout = 3600
wait-timeout = 600
### Storage Engines
innodb = FORCE
# binary logging format - mixed recommended
binlog_format=mixed
## MyISAM
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
## InnoDB
#innodb-buffer-pool-size = 128M
#innodb-log-file-size = 100M
#innodb-log-buffer-size = 8M
#innodb-file-per-table = 1
#innodb-open-files = 300
# Uncomment the following if you are using InnoDB tables
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
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
## Replication
server-id = 1
#log-bin = /var/log/mysql/bin-log
#relay-log = /var/log/mysql/relay-log
relay-log-space-limit = 16G
expire-logs-days = 3
#read-only = 1
#sync-binlog = 1
#log-slave-updates = 1
#binlog-format = STATEMENT
#auto-increment-offset = 1
#auto-increment-increment = 2
## Logging
#log-output = FILE
#slow-query-log = 1
#slow-query-log-file = /var/log/mysql/slow-log
#log-slow-slave-statements
#long-query-time = 2
[mysqld_safe]
log-error = /var/log/mysqld.log
open-files-limit = 65535
[mysql]
no-auto-rehash
Mohon koreksinya jika ada yang salah..
Terima kasih
-
semakin hari semakin bengkak:
drwx------ 2 mysql mysql 4096 Jan 3 02:26 admin
drwx------ 2 mysql mysql 12288 Jan 11 09:34 afterlogic
-rwx------ 1 mysql mysql 404525940736 Jan 11 10:17 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jan 11 10:18 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jan 11 10:18 ib_logfile1
drwx------ 2 mysql mysql 12288 Jan 11 09:34 kloxo
drwx------ 2 mysql mysql 4096 Jan 11 09:34 mysql
-rw-rw---- 1 mysql mysql 133 Jan 11 09:00 mysql-bin.index
-rw-rw---- 1 mysql mysql 0 Jan 11 09:11 mysqld-bin.index
srwxrwxrwx 1 mysql mysql 0 Jan 11 10:19 mysql.sock
drwx------ 2 mysql mysql 86016 Jan 11 09:34 anu_dtbaseku
drwx------ 2 mysql mysql 4096 Sep 19 06:14 performance_schema
drwx------ 2 mysql mysql 4096 Jan 11 09:34 roundcubemail
drwx------ 2 mysql mysql 4096 Sep 19 06:14 test
drwx------ 2 mysql mysql 4096 Jan 11 09:34 vpopmail
help... help..
A. Kloxo-MR: 6.5.0.f-2014010101
B. OS: CentOS release 5.10 (Final) i686
C. Apps:
1. MySQL: mysql55-5.5.35-1.ius.el5
2. PHP: php54-5.4.23-3.ius.el5
3. Httpd: httpd-2.2.26-1.el5
4. Lighttpd: --uninstalled--
5. Nginx: nginx-1.5.8-1.el5
6. Qmail: qmail-toaster-1.03-1.3.29.mr.el5
- with: courier-imap-toaster-4.1.2-1.3.14.mr.el5
7. Dns: bind-9.9.4-1.P1.el5
D. Php-type (for Httpd/proxy): mod_php_ruid2
E. Memory:
total used free shared buffers cached
Mem: 2469 2368 101 0 72 1669
-/+ buffers/cache: 626 1843
Swap: 11999 0 11999
# The MySQL server
[mysqld]
skip-innodb
default-storage-engine=myisam
#innodb_file_per_table=on
## General
datadir = /var/lib/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
;###123###;###123###;###123###;###123###;###123###;###123###;###123###skip-name-resolve
sql-mode = NO_ENGINE_SUBSTITUTION
#event-scheduler = 1
## Cache
thread-cache-size = 16
table-open-cache = 4096
table-definition-cache = 2048
query-cache-size = 32M
query-cache-limit = 1M
## Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 4M
join-buffer-size = 2M
## Temp Tables
tmp-table-size = 32M
max-heap-table-size = 64M
## Networking
back-log = 100
max-connections = 200
max-connect-errors = 10000
max-allowed-packet = 100M
interactive-timeout = 3600
wait-timeout = 600
### Storage Engines
innodb = FORCE
# binary logging format - mixed recommended
binlog_format=mixed
## MyISAM
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
## InnoDB
#innodb-buffer-pool-size = 128M
#innodb-log-file-size = 100M
#innodb-log-buffer-size = 8M
#innodb-file-per-table = 1
#innodb-open-files = 300
# Uncomment the following if you are using InnoDB tables
#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
#innodb_buffer_pool_size = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
## Replication
server-id = 1
#log-bin = /var/log/mysql/bin-log
#relay-log = /var/log/mysql/relay-log
relay-log-space-limit = 16G
expire-logs-days = 3
#read-only = 1
#sync-binlog = 1
#log-slave-updates = 1
#binlog-format = STATEMENT
#auto-increment-offset = 1
#auto-increment-increment = 2
## Logging
#log-output = FILE
#slow-query-log = 1
#slow-query-log-file = /var/log/mysql/slow-log
#log-slow-slave-statements
#long-query-time = 2
[mysqld_safe]
log-error = /var/log/mysqld.log
open-files-limit = 65535
[mysql]
no-auto-rehash
help..help..
-
Baca http://www.mysqlperformanceblog.com/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/
-
tail /var/log/mysqld.log
140111 23:44:19 InnoDB: Completed initialization of buffer pool
140111 23:44:19 InnoDB: highest supported file format is Barracuda.
140111 23:44:24 InnoDB: Waiting for the background threads to start
140111 23:44:25 InnoDB: 5.5.35 started; log sequence number 345692438464
140111 23:44:25 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140111 23:44:25 [Note] - '0.0.0.0' resolves to '0.0.0.0';
140111 23:44:25 [Note] Server socket created on IP: '0.0.0.0'.
140111 23:44:25 [Note] Event Scheduler: Loaded 0 events
140111 23:44:25 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.35' socket: '/var/lib/mysql/mysql.sock' port: 3306 Distributed by The IUS Community Project
perasaan sudah di convert ke myISAM, bisakah untuk tidak menggunakan inoDB? bisakah untuk tidak ingin menggunakan ibdata.. myISAM only.
Terrima kasih.
-
Ini karena masih ada table yang masih pakai innodb. Pada kondisi tertentu, kadangkala mysql-convert tidak berhasil melakukan conversion. Perlu cara manual (export ke sql, delete table dan import sql) untuk database yang 'bandel'.
-
Ini karena masih ada table yang masih pakai innodb. Pada kondisi tertentu, kadangkala mysql-convert tidak berhasil melakukan conversion. Perlu cara manual (export ke sql, delete table dan import sql) untuk database yang 'bandel'.
kalau boleh minta contoh scema my.cnf yang digunakan forum ini.
atau contoh scema my.cnf yang full tidak menggunakan inoDB. myISAM only untuk ram 2GB
Terima kasih.
-
Pada mysql 5.5, innodb menjadi 1 dengan corenya sehingga menambahkan 'skip-innodb' mungkin menimbulkan masalah.
Secara prinsip, forum ini memakai config dari /usr/share/mysql/my-huge.cnf yang disalinkan ke /etc/my.cnf. Untuk anda mungkin bisa memakai my-medium.cnf atau my-large.cnf.
-
mysql berapa yang terpisah dengan core nya.. saya tidak ingin menggunakan inoDB
Terima kasih.
dibawah ini maksudnya apa dan harus diapakan?
140111 8:55:44 [Note] /usr/libexec/mysqld: Shutdown complete
140111 08:55:44 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140111 08:58:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140111 8:58:41 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
140111 8:58:41 [Note] Plugin 'FEDERATED' is disabled.
140111 8:58:41 [Note] Plugin 'InnoDB' is disabled.
140111 8:58:41 [ERROR] /usr/libexec/mysqld: unknown option '--skip-locking'
140111 8:58:41 [ERROR] Aborting
140111 8:58:41 [Note] /usr/libexec/mysqld: Shutdown complete
140111 08:58:41 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140111 09:00:11 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140111 9:00:11 [Note] Plugin 'FEDERATED' is disabled.
140111 9:00:11 [Note] Plugin 'InnoDB' is disabled.
140111 9:00:11 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140111 9:00:11 [Note] - '0.0.0.0' resolves to '0.0.0.0';
140111 9:00:11 [Note] Server socket created on IP: '0.0.0.0'.
140111 9:00:11 [Note] Event Scheduler: Loaded 0 events
140111 9:00:11 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.35-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Distributed by The IUS Community Project
140111 9:11:47 [Note] /usr/libexec/mysqld: Normal shutdown
140111 9:11:47 [Note] Event Scheduler: Purging the queue. 0 events
140111 9:11:47 [Note] /usr/libexec/mysqld: Shutdown complete
140111 09:11:47 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140111 09:11:48 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140111 9:11:48 [Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=mysqld-bin' to avoid this problem.
140111 9:11:48 [Note] Plugin 'FEDERATED' is disabled.
140111 9:11:48 [Warning] /usr/libexec/mysqld: ignoring option '--innodb-file-per-table' due to invalid value 'on'
140111 9:11:48 [Note] Plugin 'InnoDB' is disabled.
140111 9:11:48 [ERROR] /usr/libexec/mysqld: unknown option '--skip-locking'
140111 9:11:48 [ERROR] Aborting
140111 9:11:48 [Note] /usr/libexec/mysqld: Shutdown complete
140111 09:11:48 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended