Ad Widget

Collapse

how to move my database /var/lib/mysql to anther filesystem?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • charlie
    Junior Member
    • Nov 2021
    • 3

    #1

    how to move my database /var/lib/mysql to anther filesystem?

    currently my zabbix 5.0 database sits on /var/lib folder which is on root filesystem. now because of mysql, my root fs is full:
    [root@bp2-zabbix-d001 data]# df -h
    Filesystem Size Used Avail Use% Mounted on
    devtmpfs 7.8G 0 7.8G 0% /dev
    tmpfs 7.8G 0 7.8G 0% /dev/shm
    tmpfs 7.8G 66M 7.7G 1% /run
    tmpfs 7.8G 0 7.8G 0% /sys/fs/cgroup
    /dev/mapper/cl-root 14G 14G 20K 100% /
    /dev/sda1 1014M 180M 835M 18% /boot
    /dev/mapper/vg_data-lv_data 94G 692M 94G 1% /data
    tmpfs 1.6G 0 1.6G 0% /run/user/0

    but my /data fs has plenty of space, how could I move to /data? thx

  • charlie
    Junior Member
    • Nov 2021
    • 3

    #2
    here is my.cnf:
    [root@bp2-zabbix-d001 data]# more /etc/my.cnf
    [mysql]

    # CLIENT #
    port = 3306
    socket = /data/data/mysql/mysql.sock
    default-character-set = utf8

    [mysqld]

    # GENERAL #
    user = mysql
    default-storage-engine = InnoDB
    socket = /data/data/mysql/mysql.sock
    pid-file = /var/run/mysqld/mysqld.pid
    bind-address = 127.0.0.1
    report-host = 127.0.0.1
    collation-server = utf8_unicode_ci
    init-connect = 'SET NAMES utf8'
    character-set-server = utf8
    tmpdir = /data/tmp
    read-only = 0
    sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZER O_DA TE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_A UTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    performance_schema = 1


    # MyISAM #
    key-buffer-size = 32M
    myisam-recover = FORCE,BACKUP

    # SAFETY #
    max-allowed-packet = 16M
    max-connect-errors = 1000000
    skip-name-resolve

    # DATA STORAGE #
    [root@bp2-zabbix-d001 data]# pwd
    /data/data
    [root@bp2-zabbix-d001 data]# cat /etc/my.cnf
    [mysql]

    # CLIENT #
    port = 3306
    socket = /data/data/mysql/mysql.sock
    default-character-set = utf8

    [mysqld]

    # GENERAL #
    user = mysql
    default-storage-engine = InnoDB
    socket = /data/data/mysql/mysql.sock
    pid-file = /var/run/mysqld/mysqld.pid
    bind-address = 127.0.0.1
    report-host = 127.0.0.1
    collation-server = utf8_unicode_ci
    init-connect = 'SET NAMES utf8'
    character-set-server = utf8
    tmpdir = /data/tmp
    read-only = 0
    sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZER O_DA TE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_A UTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    performance_schema = 1


    # MyISAM #
    key-buffer-size = 32M
    myisam-recover = FORCE,BACKUP

    # SAFETY #
    max-allowed-packet = 16M
    max-connect-errors = 1000000
    skip-name-resolve

    # DATA STORAGE #
    datadir = /var/lib/mysql/
    symbolic-links = 1

    # BINARY LOGGING #
    log-bin = /var/lib/mysql/mysql-bin
    expire-logs-days = 7
    sync-binlog = 1
    binlog_format = row

    # CACHES AND LIMITS #
    tmp-table-size = 48M
    max-heap-table-size = 48M
    query-cache-type = 0
    query-cache-size = 0
    max-connections = 4000
    thread-cache-size = 500
    open-files-limit = 65535
    table-definition-cache = 1024
    table-open-cache = 2048

    # INNODB #
    innodb-flush-method = O_DIRECT
    innodb-log-files-in-group = 2
    innodb-log-file-size = 1G
    innodb-flush-log-at-trx-commit = 1
    innodb-file-per-table = 1
    innodb-buffer-pool-size = 6144M
    innodb_buffer_pool_instances = 4

    # LOGGING #
    log-error = /data/logs/mysql/mysqld.log
    log-queries-not-using-indexes = 0
    long_query_time = 2
    slow-query-log = 1
    slow-query-log-file = /data/logs/mysql/mysql-slow.log

    [mysqld_safe]
    log-error = /data/logs/mysql/mysqld.log
    pid-file = /var/run/mysqld/mysqld.pid

    Comment

    Working...