Join the community today
Become a Member

MariaDB my.cnf

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by KeVo, Jun 7, 2015.

  1. KeVo

    KeVo Active Member

    180
    71
    28
    May 28, 2014
    Ratings:
    +101
    Local Time:
    9:44 PM
    1.11.x
    10.1.18
    Hey guys, I'm just wondering if anyone has a solid, basic my.cnf file setting that would serve well on a 1GB Linode VPS with 256MB Swap. @eva2000, I know you don't give help for free for this kind of thing, so it's all good. :)


    Just hoping someone else has a similar VPS setup that they'd be willing to share or help me out with. I've never gotten this far into server setups, so I am looking at a lot that I don't understand yet. I'd rather ask on here than blindly copying and pasting some random my.cnf on the web. lol
     
  2. robbyq92

    robbyq92 New Member

    8
    0
    1
    Jul 9, 2015
    Ratings:
    +1
    Local Time:
    4:44 AM
    1.9.2
    Maria DB 10
    Hi, i used that config:

    Code:
    [mysqld]
    set-variable=local-infile=0
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    
    # nueva configuracion
    # este parámetro es fundamental, hay que registrar las consultas lentas, todas las que duren mas de 2 segundos.
    # asi se puede ver las consultas que se hacen y que tienen que ser optimizadas
    log-slow-queries=/var/log/mysql-slow-queries.log
    long_query_time = 2
    log-queries-not-using-indexes
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_connections=500
    interactive_timeout=100
    # Reducido wait_timeout para prevenir que clientes inactivos retengan una conexion.
    wait_timeout=30
    connect_timeout=10
    # establecido a 128 para que no use mas memoria
    thread_cache_size=128
    key_buffer=64M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=512
    record_buffer=1M
    sort_buffer_size=2M
    read_buffer_size=2M
    max_connect_errors=10
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    # nueva configuracion
    open_files_limit=8192
    
    # nueva configuracion
    [mysql]
    no-auto-rehash
    #safe-updates
    
    # nueva configuracion
    [isamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M
    
    [mysqlhotcopy]
    interactive-timeout
    
    
     
  3. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    Also, but on sever with 4 gb (Digital Ocean) VPS with 2gb swap, MariaDB 10.0.22
     
  4. rdan

    rdan Well-Known Member

    5,446
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    11:44 AM
    Mainline
    10.2
  5. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    Nice, but what to do? Should I manually change my.cnf with my.cnf from your link or there is a way to "download" that?
     
  6. rdan

    rdan Well-Known Member

    5,446
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    11:44 AM
    Mainline
    10.2
    Just replace all it's content.
     
  7. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    Content from which file exactly? Last one my.cnf?
    Copy content of centminmod/my.cnf at 123.09beta01 · centminmod/centminmod · GitHub to my.cnf file (/etc/my.cnf)?

    my.cnf file (/etc/my.cnf)

    Code:
    [mysqld]
    local-infile=0
    ignore-db-dir=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
    # vbtechsupport.com
    
    tmpdir=/home/mysqltmp
    
    innodb=ON
    #skip-federated
    #skip-pbxt
    #skip-pbxt_statistics
    #skip-archive
    #skip-name-resolve
    #old_passwords
    back_log = 75
    max_connections = 300
    key_buffer_size = 32M
    myisam_sort_buffer_size = 32M
    myisam_max_sort_file_size = 2048M
    join_buffer_size = 128K
    read_buffer_size = 128K
    sort_buffer_size = 256K
    table_definition_cache = 4096
    table_open_cache = 2048
    thread_cache_size = 64
    wait_timeout = 1800
    connect_timeout = 10
    tmp_table_size = 32M
    max_heap_table_size = 32M
    max_allowed_packet = 32M
    max_seeks_for_key = 1000
    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 = 256K
    bulk_insert_buffer_size = 8M
    query_cache_limit = 512K
    query_cache_size = 16M
    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 = 1000
    innodb_data_file_path= ibdata1:10M:autoextend
    innodb_buffer_pool_size = 48M
    
    ## 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 = 128M
    innodb_log_buffer_size = 8M
    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 = 100
    innodb_read_io_threads = 2
    innodb_write_io_threads = 2
    
    # 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 = 32M
    aria_log_purge_type = immediate
    aria_pagecache_buffer_size = 8M
    aria_sort_buffer_size = 8M
    
    [mariadb-5.5]
    #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 = 32M
    
    [myisamchk]
    tmpdir=/home/mysqltmp
    key_buffer = 32M
    sort_buffer = 16M
    read_buffer = 16M
    write_buffer = 16M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mariadb-10.0]
    # 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
     
  8. rdan

    rdan Well-Known Member

    5,446
    1,408
    113
    May 25, 2014
    Ratings:
    +2,201
    Local Time:
    11:44 AM
    Mainline
    10.2
  9. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    Done! ;)

    Why is this line commented out in new version?
    #character-set-server=utf8
     
  10. eva2000

    eva2000 Administrator Staff Member

    54,546
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    1:44 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    you have to decide if you want it enabled... best if starting with new dataabases

    if importing data from other servers it's left disabled as other databases on other servers may default to latin1 and potentially mess with your data if you have importing server set it utf8
     
  11. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    I will than leave it as it is.
    But, my forum database (tables) are all in utf8, so I think that it will not be a problem even if I uncommented it out.

    Also, I have setup MariaDb to utf8 by default (for every new database).
     
  12. eva2000

    eva2000 Administrator Staff Member

    54,546
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    1:44 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    then uncomment and enable utf8 should be fine
     
  13. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    After edited my.conf as wrote in this post.
    There is not row default-storage-engine = InnoDB
    Should I add it below innodb=ON ?

    this is my.cnf
    Code:
    [mysqld]
    local-infile=0
    ignore-db-dir=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
    # vbtechsupport.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 = 500
    key_buffer_size = 256M
    myisam_sort_buffer_size = 256M
    myisam_max_sort_file_size = 2048M
    join_buffer_size = 256K
    read_buffer_size = 256K
    sort_buffer_size = 256K
    table_definition_cache = 8192
    table_open_cache = 4096
    thread_cache_size = 256
    wait_timeout = 1800
    connect_timeout = 10
    tmp_table_size = 256M
    max_heap_table_size = 256M
    max_allowed_packet = 64M
    max_seeks_for_key = 1000
    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_limit = 512K
    query_cache_size = 64M
    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 = 1000
    innodb_data_file_path= ibdata1:10M:autoextend
    innodb_buffer_pool_size = 512M
    
    ## 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 = 256M
    innodb_log_buffer_size = 8M
    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 = 300
    innodb_read_io_threads = 4
    innodb_write_io_threads = 2
    
    # 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]
    #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 = 64M
    
    [myisamchk]
    tmpdir=/home/mysqltmp
    key_buffer = 256M
    sort_buffer = 32M
    read_buffer = 32M
    write_buffer = 32M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mariadb-10.0]
    # 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

    Also, when I restart mysql, it is restarted but some error show up to:
    # mysqlrestart
    ERROR! MySQL server PID file could not be found!
    Starting MySQL SUCCESS!
     
  14. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    Hmmmm...
    When I try - lsof | grep '/var/lib/mysql/aria_log_control'
    this is result:
    Code:
    mysqld     1663         mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1711   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1712   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1713   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1714   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1715   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1716   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1717   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1718   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1915   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1916   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1917   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1918   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1919   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1959   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1960   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1961   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1962   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1963   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  1999   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  2007   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    mysqld     1663  2347   mysql   10uW     REG              253,1        52     524787 /var/lib/mysql/aria_log_control
    Also, when try to mysqladmin flush-tables
    it throw error:
    mysqladmin: connect to server at 'localhost' failed
    error: 'Access denied for user 'root'@'localhost' (using password: YES)'

    I have password, but there is no moment where to write it. I am logged like root user
     
  15. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    I log in to Mariadb with mysql -u root -p
    When I see status, I see that Server characterset and Db characterset are set to latin not to utf8. How to change that?
    Code:
    mysql  Ver 15.1 Distrib 10.0.22-MariaDB, for Linux (x86_64) using readline 5.1
    
    Connection id:          13
    Current database:
    Current user:           root@localhost
    SSL:                    Not in use
    Current pager:          stdout
    Using outfile:          ''
    Using delimiter:        ;
    Server:                 MariaDB
    Server version:         10.0.22-MariaDB MariaDB Server
    Protocol version:       10
    Connection:             Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:            /var/lib/mysql/mysql.sock
    Uptime:                 21 hours 57 min 11 sec
    
    Threads: 1  Questions: 4  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 63  Queries per second avg: 0.000
     
  16. eva2000

    eva2000 Administrator Staff Member

    54,546
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    1:44 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    it's there.. check again after line transaction_prealloc_size = 4096

    You can check your mysql error log which is listed along with all other log file locations as per How to troubleshoot Centmin Mod initial install issues (/var/log/mysqld.log) for clues.

    For example, it can be related to different sized innodb_log_file_size compared to the initialised innodb instance on initial Centmin Mod install which defaults to 48M size.

    In /etc/my.cnf you can change the value of it
    Code:
    innodb_log_file_size = 48M
    
    restart mysql server and see

    if so, then to change from 48M to desired value and read official MySQL documentation for Changing Size of InnoDB Log Files. Only move/delete the log files ib_logfile0 and ib_logfile1

    is mysql root password populated in /root/.my.cnf too should be by default so no need to pass -u USER -p pass on mysql client command line

    setting under [mysqld] group character-set-server=utf8 option as per MariaDB MySQL - CentminMod.com LEMP Nginx web stack for CentOS should of done it if you had a proper mysql restart

    you should see
    Code:
    mysqladmin var | grep -E 'character|collation' | tr -s ' '
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8 |
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | utf8 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    | collation_connection | utf8_general_ci |
    | collation_database | utf8_general_ci |
    | collation_server | utf8_general_ci |
    Code:
    MariaDB [(none)]> status
    --------------
    mysql  Ver 15.1 Distrib 10.1.8-MariaDB, for Linux (x86_64) using readline 5.1
    
    Connection id:          4
    Current database:
    Current user:           root@localhost
    SSL:                    Not in use
    Current pager:          stdout
    Using outfile:          ''
    Using delimiter:        ;
    Server:                 MariaDB
    Server version:         10.1.8-MariaDB MariaDB Server
    Protocol version:       10
    Connection:             Localhost via UNIX socket
    Server characterset:    utf8
    Db     characterset:    utf8
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:            /var/lib/mysql/mysql.sock
    Uptime:                 57 sec
    
    Threads: 1  Questions: 6  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 11  Queries per second avg: 0.105
     
  17. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    After I reboot server, this error is gone.
    This is part of error log:
    Code:
    151103 18:28:52 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
    151103 18:28:52 [Note] InnoDB: Unable to open the first data file
    2015-11-03 18:28:52 7fc3dd188880  InnoDB: Operating system error number 11 in a file operation.
    InnoDB: Error number 11 means 'Resource temporarily unavailable'.
    InnoDB: Some operating system error numbers are described at
    InnoDB: http://dev.mysql.com/doc/refman/5.6/en/operating-system-error-codes.html
    151103 18:28:52 [ERROR] InnoDB: Can't open './ibdata1'
    151103 18:28:52 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!
    151103 18:28:52 [ERROR] Plugin 'InnoDB' init function returned error.
    151103 18:28:52 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    151103 18:28:52 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds
    151103 18:29:23 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file '/var/lib/mysql/aria_log_control'
    151103 18:29:23 [ERROR] Plugin 'Aria' init function returned error.
    151103 18:29:23 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
    151103 18:29:23 [Note] Plugin 'FEEDBACK' is disabled.
    151103 18:29:23 [ERROR] Unknown/unsupported storage engine: InnoDB
    151103 18:29:23 [ERROR] Aborting
    
    151103 18:29:23 [Note] /usr/sbin/mysqld: Shutdown complete
    
    151103 18:29:23 mysqld_safe mysqld from pid file /var/lib/mysql/hostname.pijanitvor.com.pid ended
    151103 19:01:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    151103 19:01:58 [Note] /usr/sbin/mysqld (mysqld 10.0.22-MariaDB) starting as process 14415 ...
    151103 19:01:58 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    151103 19:01:58 [Note] InnoDB: The InnoDB memory heap is disabled
    151103 19:01:58 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    151103 19:01:58 [Note] InnoDB: Memory barrier is not used
    151103 19:01:58 [Note] InnoDB: Compressed tables use zlib 1.2.7
    151103 19:01:58 [Note] InnoDB: Using Linux native AIO
    151103 19:01:58 [Note] InnoDB: Using CPU crc32 instructions
    151103 19:01:58 [Note] InnoDB: Initializing buffer pool, size = 512.0M
    151103 19:01:58 [Note] InnoDB: Completed initialization of buffer pool
    151103 19:01:58 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
    151103 19:01:58 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
    151103 19:01:58 [Note] InnoDB: Retrying to lock the first data file
    151103 19:01:59 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11


    There is a password, but default one. I have changed password yesterday, but it is not changed in .my.cnf automatically. Should I edit /root/.my.cnf end against old/default password put my new password?
    Edit - I changed, and still it is asked for password but accepted him when I wrote :)

    and there I see:
    Code:
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8 |
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | utf8 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    | collation_connection | utf8_general_ci |
    | collation_database | utf8_general_ci |
    | collation_server | utf8_general_ci |
    And MariaDB status:
    Code:
    MariaDB [(none)]> \s
    --------------
    mysql  Ver 15.1 Distrib 10.0.22-MariaDB, for Linux (x86_64) using readline 5.1
    
    Connection id:          4
    Current database:
    Current user:           root@localhost
    SSL:                    Not in use
    Current pager:          stdout
    Using outfile:          ''
    Using delimiter:        ;
    Server:                 MariaDB
    Server version:         10.0.22-MariaDB MariaDB Server
    Protocol version:       10
    Connection:             Localhost via UNIX socket
    Server characterset:    utf8
    Db     characterset:    utf8
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:            /var/lib/mysql/mysql.sock
    Uptime:                 2 min 8 sec
    
    Threads: 1  Questions: 6  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 63  Queries per second avg: 0.046
    --------------
    Now it is seems that is OK, maybe because mysql is properly restarted?
     
  18. eva2000

    eva2000 Administrator Staff Member

    54,546
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    1:44 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    i see the problem

    if you stop mysql and check process list, do any mysql processes still linger
    Code:
    mysqlstop
    ps aufx
    then mysqlstart again and check mysql error log latest lines
     
  19. Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:44 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    this is output:
    Code:
    USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
    root         2  0.0  0.0      0     0 ?        S    Nov03   0:00 [kthreadd]
    root         3  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [ksoftirqd/0
    root         5  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [kworker/0:0
    root         7  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [migration/0
    root         8  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [rcu_bh]
    root         9  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [rcuob/0]
    root        10  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [rcuob/1]
    root        11  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [rcu_sched]
    root        12  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [rcuos/0]
    root        13  0.0  0.0      0     0 ?        R    Nov03   0:00  \_ [rcuos/1]
    root        14  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [watchdog/0]
    root        15  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [watchdog/1]
    root        16  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [migration/1
    root        17  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [ksoftirqd/1
    root        18  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [kworker/1:0
    root        19  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [kworker/1:0
    root        20  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [khelper]
    root        21  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [kdevtmpfs]
    root        22  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [netns]
    root        23  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [writeback]
    root        24  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [kintegrityd
    root        25  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [bioset]
    root        26  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [kblockd]
    root        27  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [khubd]
    root        28  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [md]
    root        29  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [kworker/0:1
    root        32  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [khungtaskd]
    root        33  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [kswapd0]
    root        34  0.0  0.0      0     0 ?        SN   Nov03   0:00  \_ [ksmd]
    root        35  0.0  0.0      0     0 ?        SN   Nov03   0:00  \_ [khugepaged]
    root        36  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [fsnotify_ma
    root        37  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [crypto]
    root        45  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [kthrotld]
    root        46  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [kworker/u4:
    root        47  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [kmpath_rdac
    root        48  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [kpsmoused]
    root        67  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [deferwq]
    root        93  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [kauditd]
    root       103  0.0  0.0      0     0 ?        S    Nov03   0:05  \_ [kworker/1:2
    root       220  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [kworker/u4:
    root       224  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [ata_sff]
    root       228  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [scsi_eh_0]
    root       230  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [scsi_tmf_0]
    root       232  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [scsi_eh_1]
    root       233  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [scsi_tmf_1]
    root       243  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [ttm_swap]
    root       259  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [jbd2/vda1-8
    root       260  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [ext4-rsv-co
    root       261  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [ext4-unrsv-
    root       361  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [loop0]
    root       362  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [jbd2/loop0-
    root       363  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [ext4-rsv-co
    root       364  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [ext4-unrsv-
    root       402  0.0  0.0      0     0 ?        S    Nov03   0:00  \_ [vballoon]
    root       458  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [kvm-irqfd-c
    root      2738  0.0  0.0      0     0 ?        S<   Nov03   0:00  \_ [kworker/1:1
    root      5470  0.0  0.0      0     0 ?        S    00:00   0:00  \_ [kworker/0:0
    root         1  0.0  0.0  54212  3944 ?        Ss   Nov03   0:01 /usr/lib/systemd
    root       343  0.0  0.2  43016  8232 ?        Ss   Nov03   0:06 /usr/lib/systemd
    root       349  0.0  0.0  42328  1756 ?        Ss   Nov03   0:00 /usr/lib/systemd
    root       386  0.0  0.0  51148  1612 ?        S<sl Nov03   0:00 /sbin/auditd -n
    root       463  0.0  0.2 446824  8412 ?        Ssl  Nov03   0:03 /usr/sbin/Networ
    root       464  0.0  0.0   4372   596 ?        Ss   Nov03   0:00 /sbin/rngd -f
    root       465  0.0  0.4 550152 18144 ?        Ssl  Nov03   0:03 /usr/bin/python
    root       476  0.0  0.1 208016  7288 ?        Ssl  Nov03   0:01 /usr/sbin/rsyslo
    root       479  0.0  0.0  19152  1160 ?        Ss   Nov03   0:01 /usr/sbin/irqbal
    root       480  0.0  0.0  12136  3900 ?        Ss   Nov03   0:01 /usr/sbin/havege
    avahi      485  0.0  0.0  28068  1672 ?        Ss   Nov03   0:00 avahi-daemon: ru
    avahi      495  0.0  0.0  27944   448 ?        S    Nov03   0:00  \_ avahi-daemon
    dbus       487  0.0  0.0  26700  1668 ?        Ss   Nov03   0:00 /bin/dbus-daemon
    root       489  0.0  0.0  34684  1724 ?        Ss   Nov03   0:00 /usr/lib/systemd
    root       500  0.0  0.0 126300  1568 ?        Ss   Nov03   0:00 /usr/sbin/crond
    ntp        504  0.0  0.0  31432  2080 ?        Ss   Nov03   0:00 /usr/sbin/ntpd -
    polkitd    530  0.0  0.2 513984 11612 ?        Ssl  Nov03   0:00 /usr/lib/polkit-
    root       575  0.0  0.0 110004   832 tty1     Ss+  Nov03   0:00 /sbin/agetty --n
    root       769  0.0  0.0  82508  3612 ?        Ss   Nov03   0:00 /usr/sbin/sshd -
    root      6011  0.4  0.1 133772  5308 ?        Ss   00:19   0:00  \_ sshd: root@p
    root      6013  0.2  0.0 116408  3116 pts/0    Ss   00:19   0:00      \_ -bash
    root      6074  0.0  0.0 123508  1456 pts/0    R+   00:19   0:00          \_ ps a
    root       811  0.0  0.0 202504  2552 ?        Ss   Nov03   0:00 pure-ftpd (SERVE
    nsd        945  0.0  0.0  15340   944 ?        Ss   Nov03   0:00 /usr/local/sbin/
    nsd        947  0.0  0.1  22204  4804 ?        S    Nov03   0:00  \_ /usr/local/s
    nsd        948  0.0  0.0  15472   572 ?        S    Nov03   0:00  \_ /usr/local/s
    root      1052  0.0  0.2 628684  9240 ?        Ss   Nov03   0:01 php-fpm: master
    root      1335  0.0  0.0  91512  2212 ?        Ss   Nov03   0:00 /usr/libexec/pos
    postfix   1342  0.0  0.1  91796  4184 ?        S    Nov03   0:00  \_ qmgr -l -t u
    postfix   5451  0.0  0.0  91616  3968 ?        S    Nov03   0:00  \_ pickup -l -t
    postfix   6071  0.0  0.0  91764  4028 ?        S    00:19   0:00  \_ cleanup -z -
    postfix   6072  0.0  0.0  91620  3980 ?        S    00:19   0:00  \_ trivial-rewr
    postfix   6073  0.0  0.1  91720  4616 ?        S    00:19   0:00  \_ local -t uni
    root      1546  0.0  0.5 171080 23180 ?        Ss   Nov03   0:03 lfd - sleeping
    nobody    2392  0.0  0.0 342008  1120 ?        Ssl  Nov03   0:02 /usr/local/bin/m
    root      2407  0.0  0.7 184592 29672 ?        Ss   Nov03   0:00 nginx: master pr
    nginx     2408  0.0  0.9 196884 37792 ?        S<l  Nov03   0:10  \_ nginx: worke
    nginx     2410  0.0  0.9 196884 37796 ?        S<l  Nov03   0:14  \_ nginx: worke
    
    This is latest few lines:
    Code:
    151104 00:21:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
    151104  0:21:27 [Note] /usr/sbin/mysqld (mysqld 10.0.22-MariaDB) starting as process 6436 ...
    151104  0:21:27 [Note] InnoDB: Using mutexes to ref count buffer pool pages
    151104  0:21:27 [Note] InnoDB: The InnoDB memory heap is disabled
    151104  0:21:27 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    151104  0:21:27 [Note] InnoDB: Memory barrier is not used
    151104  0:21:27 [Note] InnoDB: Compressed tables use zlib 1.2.7
    151104  0:21:27 [Note] InnoDB: Using Linux native AIO
    151104  0:21:27 [Note] InnoDB: Using CPU crc32 instructions
    151104  0:21:27 [Note] InnoDB: Initializing buffer pool, size = 512.0M
    151104  0:21:27 [Note] InnoDB: Completed initialization of buffer pool
    151104  0:21:27 [Note] InnoDB: Highest supported file format is Barracuda.
    151104  0:21:27 [Note] InnoDB: 128 rollback segment(s) are active.
    151104  0:21:27 [Note] InnoDB: Waiting for purge to start
    151104  0:21:28 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.26-74.0 started; log sequence number 1617992
    2015-11-04 00:21:28 7f00373f8700 InnoDB: Loading buffer pool(s) from .//ib_buffer_pool
    2015-11-04 00:21:28 7f00373f8700 InnoDB: Buffer pool(s) load completed at 151104  0:21:28
    151104  0:21:28 [Note] Plugin 'FEEDBACK' is disabled.
    151104  0:21:28 [Note] Server socket created on IP: '::'.
    151104  0:21:28 [Note] /usr/sbin/mysqld: ready for connections.
    Version: '10.0.22-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
     
  20. eva2000

    eva2000 Administrator Staff Member

    54,546
    12,221
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,790
    Local Time:
    1:44 PM
    Nginx 1.27.x
    MariaDB 10.x/11.4+
    no lingering mysql processes on mysql stop + error log looks clean so good to go :D