ZFS managing mysql dataset

fred974

fred974

Hi,
I had a server crash this am because I ran out of space..
Code: 
Filesystem                                                                  Size    Used   Avail Capacity  Mounted on
zroot                                                                        12G     12G      0B   100%    /
devfs                                                                       1.0K    1.0K      0B   100%    /dev
zroot/iocage                                                                1.1M    1.1M      0B   100%    /iocage
zroot/iocage/.defaults                                                      288K    288K      0B   100%    /iocage/.defaults
zroot/iocage/base                                                           288K    288K      0B   100%    /iocage/base
zroot/iocage/base/10.2-RELEASE                                              288K    288K      0B   100%    /iocage/base/10.2-RELEASE
zroot/iocage/base/10.2-RELEASE/root                                         251M    251M      0B   100%    /iocage/base/10.2-RELEASE/root
zroot/iocage/base/10.2-RELEASE/root/bin                                     3.4M    3.4M      0B   100%    /iocage/base/10.2-RELEASE/root/bin
zroot/iocage/base/10.2-RELEASE/root/boot                                    4.5M    4.5M      0B   100%    /iocage/base/10.2-RELEASE/root/boot
zroot/iocage/base/10.2-RELEASE/root/lib                                      19M     19M      0B   100%    /iocage/base/10.2-RELEASE/root/lib
zroot/iocage/base/10.2-RELEASE/root/libexec                                 911K    911K      0B   100%    /iocage/base/10.2-RELEASE/root/libexec
zroot/iocage/base/10.2-RELEASE/root/rescue                                   16M     16M      0B   100%    /iocage/base/10.2-RELEASE/root/rescue
zroot/iocage/base/10.2-RELEASE/root/sbin                                     14M     14M      0B   100%    /iocage/base/10.2-RELEASE/root/sbin
zroot/iocage/base/10.2-RELEASE/root/usr                                     312K    312K      0B   100%    /iocage/base/10.2-RELEASE/root/usr
zroot/iocage/base/10.2-RELEASE/root/usr/bin                                 160M    160M      0B   100%    /iocage/base/10.2-RELEASE/root/usr/bin
zroot/iocage/base/10.2-RELEASE/root/usr/include                              58M     58M      0B   100%    /iocage/base/10.2-RELEASE/root/usr/include
zroot/iocage/base/10.2-RELEASE/root/usr/lib                                 139M    139M      0B   100%    /iocage/base/10.2-RELEASE/root/usr/lib
zroot/iocage/base/10.2-RELEASE/root/usr/lib32                               134M    134M      0B   100%    /iocage/base/10.2-RELEASE/root/usr/lib32
zroot/iocage/base/10.2-RELEASE/root/usr/libdata                             767K    767K      0B   100%    /iocage/base/10.2-RELEASE/root/usr/libdata
zroot/iocage/base/10.2-RELEASE/root/usr/libexec                             9.1M    9.1M      0B   100%    /iocage/base/10.2-RELEASE/root/usr/libexec
zroot/iocage/base/10.2-RELEASE/root/usr/sbin                                 31M     31M      0B   100%    /iocage/base/10.2-RELEASE/root/usr/sbin
zroot/iocage/base/10.2-RELEASE/root/usr/share                               232M    232M      0B   100%    /iocage/base/10.2-RELEASE/root/usr/share
zroot/iocage/base/10.2-RELEASE/root/usr/src                                 2.4G    2.4G      0B   100%    /iocage/base/10.2-RELEASE/root/usr/src
zroot/iocage/download                                                       288K    288K      0B   100%    /iocage/download
zroot/iocage/download/10.2-RELEASE                                          603M    603M      0B   100%    /iocage/download/10.2-RELEASE
zroot/iocage/jails                                                          336K    336K      0B   100%    /iocage/jails
zroot/iocage/jails/13b750d7-a116-11e6-9724-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/13b750d7
zroot/iocage/jails/13b750d7-a116-11e6-9724-f04da20055fe/root                7.0G    7.0G      0B   100%    /iocage/jails/13b750d7/root
zroot/iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe
zroot/iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe/root                6.2G    6.2G      0B   100%    /iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe/root
zroot/DATA/mysql                                                            159G    159G      0B   100%    /iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe/root/var/db/mysql
zroot/DATA/mysql/mysql-innodb                                                98M     98M      0B   100%    /iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe/root/var/db/mysql/mysql-innodb
zroot/DATA/mysql/mysql-innodb-logs                                          464M    464M      0B   100%    /iocage/jails/1585e91b-cc07-11e5-9463-f04da20055fe/root/var/db/mysql/mysql-innodb-logs
zroot/iocage/jails/1b92750b-badc-11e6-9724-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/1b92750b
zroot/iocage/jails/1b92750b-badc-11e6-9724-f04da20055fe/root                7.8G    7.8G      0B   100%    /iocage/jails/1b92750b/root
zroot/iocage/jails/2d2162b7-6326-11e6-b2bc-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/2d2162b7
zroot/iocage/jails/2d2162b7-6326-11e6-b2bc-f04da20055fe/root                5.4G    5.4G      0B   100%    /iocage/jails/2d2162b7/root
zroot/iocage/jails/44d18138-fcba-11e5-912b-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/44d18138
zroot/iocage/jails/44d18138-fcba-11e5-912b-f04da20055fe/root                 16G     16G      0B   100%    /iocage/jails/44d18138/root
zroot/iocage/jails/5cf1ee40-1387-11e6-b209-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/5cf1ee40
zroot/iocage/jails/5cf1ee40-1387-11e6-b209-f04da20055fe/root                7.6G    7.6G      0B   100%    /iocage/jails/5cf1ee40/root
zroot/iocage/jails/60823f40-b4f6-11e6-9724-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/60823f40
zroot/iocage/jails/60823f40-b4f6-11e6-9724-f04da20055fe/root                 10G     10G      0B   100%    /iocage/jails/60823f40/root
zroot/iocage/jails/60ddd835-3492-11e6-813c-f04da20055fe                     312K    312K      0B   100%    /iocage/jails/60ddd835
zroot/iocage/jails/60ddd835-3492-11e6-813c-f04da20055fe/root                7.7G    7.7G      0B   100%    /iocage/jails/60ddd835/root
zroot/iocage/jails/6abbbfad-ba15-11e5-b091-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/6abbbfad-ba15-11e5-b091-f04da20055fe
zroot/iocage/jails/6abbbfad-ba15-11e5-b091-f04da20055fe/root                5.0G    5.0G      0B   100%    /iocage/jails/6abbbfad-ba15-11e5-b091-f04da20055fe/root
zroot/iocage/jails/6c2a4470-3f9c-11e6-813c-f04da20055fe                     312K    312K      0B   100%    /iocage/jails/6c2a4470
zroot/iocage/jails/6c2a4470-3f9c-11e6-813c-f04da20055fe/root                5.2G    5.2G      0B   100%    /iocage/jails/6c2a4470/root
zroot/iocage/jails/829611fe-c8dd-11e5-9463-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/829611fe-c8dd-11e5-9463-f04da20055fe
zroot/iocage/jails/829611fe-c8dd-11e5-9463-f04da20055fe/root                4.0G    4.0G      0B   100%    /iocage/jails/829611fe-c8dd-11e5-9463-f04da20055fe/root
zroot/iocage/jails/86950480-0be5-11e6-8dfc-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/86950480
zroot/iocage/jails/86950480-0be5-11e6-8dfc-f04da20055fe/root                5.9G    5.9G      0B   100%    /iocage/jails/86950480/root
zroot/iocage/jails/8a298091-f132-11e5-867e-f04da20055fe                     288K    288K      0B   100%    /iocage/jails/8a298091
zroot/iocage/jails/8a298091-f132-11e5-867e-f04da20055fe/root                4.8G    4.8G      0B   100%    /iocage/jails/8a298091/root
I managed to recover the space running $ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS BEFORE '2017-01-24 10:06:06';"

How can I make sure this never happen again?
I curently have the following in my /usr/local/etc/my.cnf
Code: 
# BINARY LOGGING #
log-bin                        = /var/db/mysql/mysql-bin
expire-logs-days               = 10
sync-binlog                    = 1
binlog_format                  = MIXED
I am using /databases/mariadb101-server
 
Do you really need 10 days worth of binary logs? I would recommend setting it to 3. This will give you 3 days worth of recovery, enough to fix things when the database crashes on Friday and you need to rebuild it on Monday.
 
You must log in or register to reply here.
Back
Top