Want to subscribe to topics you're interested in?
Become a Member

MariaDB mysqlstop and mysqlstart shortcuts doesn't work

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by pamamolf, Jun 15, 2020.

  1. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    • CentOS Version: CentOS 7 64bit
    • Centmin Mod Version Installed: 123.09beta01
    • Nginx Version Installed: 1.19.x
    • PHP Version Installed: 7.3.x
    • MariaDB MySQL Version Installed: 10.3.23-MariaDB
    • When was last time updated Centmin Mod code base ? : just now

    Hello

    I just got a new Ovh dedicated server and install Centminmod using:

    Code:
    yum -y update; curl -O https://centminmod.com/betainstaller73.sh && chmod 0700 betainstaller73.sh && bash betainstaller73.sh
    MariaDB seems to installed fine:

    Code:
    Looking for 'mysql' as: mysql
    Looking for 'mysqlcheck' as: mysqlcheck
    Phase 1/7: Checking and upgrading mysql database
    Processing databases
    mysql
    mysql.column_stats                                 OK
    mysql.columns_priv                                 OK
    mysql.db                                           OK
    mysql.event                                        OK
    mysql.func                                         OK
    mysql.gtid_slave_pos                               OK
    mysql.help_category                                OK
    mysql.help_keyword                                 OK
    mysql.help_relation                                OK
    mysql.help_topic                                   OK
    mysql.host                                         OK
    mysql.index_stats                                  OK
    mysql.innodb_index_stats                           OK
    mysql.innodb_table_stats                           OK
    mysql.plugin                                       OK
    mysql.proc                                         OK
    mysql.procs_priv                                   OK
    mysql.proxies_priv                                 OK
    mysql.roles_mapping                                OK
    mysql.servers                                      OK
    mysql.table_stats                                  OK
    mysql.tables_priv                                  OK
    mysql.time_zone                                    OK
    mysql.time_zone_leap_second                        OK
    mysql.time_zone_name                               OK
    mysql.time_zone_transition                         OK
    mysql.time_zone_transition_type                    OK
    mysql.transaction_registry                         OK
    mysql.user                                         OK
    Phase 2/7: Installing used storage engines... Skipped
    Phase 3/7: Fixing views
    Processing databases
    information_schema
    mysql
    performance_schema
    test
    Phase 4/7: Running 'mysql_fix_privilege_tables'
    Phase 5/7: Fixing table and database names
    Processing databases
    information_schema
    mysql
    performance_schema
    test
    Phase 6/7: Checking and upgrading tables
    Processing databases
    information_schema
    performance_schema
    test
    Phase 7/7: Running 'FLUSH PRIVILEGES'
    OK
    systemctl restart mariadb.service
    systemctl stop mariadb.service
    systemctl start mariadb.service
    All was ok but at the end i tried to run:

    mysqlstop and i got this error:
    Code:
    MariaDB server PID file could not be found!
    I did a check and it was running and my site was working and no issues....
    Then i tried mysqlrestart and i got the same error :(

    As i didn't adjust anything in the config it seems weird why it doesn't work out of the box :(

    Then i checked the /var/lib/mysql/server.mydomain.com.err

    and i found that:
    Code:
    2020-06-14 21:01:29 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
    2020-06-14 21:01:30 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
    2020-06-14 21:01:30 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
    2020-06-14 21:01:31 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
    2020-06-14 21:01:31 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
    2020-06-14 21:01:32 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
    2020-06-14 21:01:32 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
    2020-06-14 21:01:32 0 [Note] InnoDB: Unable to open the first data file
    2020-06-14 21:01:32 0 [ERROR] InnoDB: Operating system error number 11 in a file operation.
    2020-06-14 21:01:32 0 [ERROR] InnoDB: Error number 11 means 'Resource temporarily unavailable'
    2020-06-14 21:01:32 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
    2020-06-14 21:01:32 0 [ERROR] InnoDB: Cannot open datafile './ibdata1'
    2020-06-14 21:01:32 0 [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!
    2020-06-14 21:01:32 0 [ERROR] InnoDB: Plugin initialization aborted with error Cannot open a file
    2020-06-14 21:01:33 0 [Note] InnoDB: Starting shutdown...
    2020-06-14 21:01:33 0 [ERROR] Plugin 'InnoDB' init function returned error.
    2020-06-14 21:01:33 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
    2020-06-14 21:01:33 0 [Note] Plugin 'FEEDBACK' is disabled.
    2020-06-14 21:01:33 0 [ERROR] Unknown/unsupported storage engine: InnoDB
    2020-06-14 21:01:33 0 [ERROR] Aborting
    Then i tried to run:

    Code:
    systemctl stop mysqld
    and
    Code:
    systemctl start mysqld
    Both worked and i didn't get any errors !


    Some checks:
    Code:
    systemctl status mysqld
    Code:
       Active: active (running) since Sun 2020-06-14 21:28:46 UTC; 5min ago
         Docs: man:mysqld(8)
               https://mariadb.com/kb/en/library/systemd/
      Process: 6957 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
      Process: 6649 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
      Process: 6645 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
     Main PID: 6918 (mysqld)
       Status: "Taking your SQL requests now..."
       CGroup: /system.slice/mariadb.service
               └─6918 /usr/sbin/mysqld
    But why Galera is running or trying to start as i didn't adjust anything related?
    Maybe that's the issue?

    Code:
    ps aux | grep mysqld
    Code:
    mysql     6918  120  2.6 9314272 1726476 ?     Ssl  21:28  10:07 /usr/sbin/mysqld
    root      8180  0.0  0.0 112860   968 pts/0    S+   21:37   0:00 grep --color=auto mysqld
    also i have a lot of free space at the disk...

    Code:
    ls -lah /var/lib/mysql/
    Code:
    drwxr-xr-x   7 mysql mysql  4.0K Jun 14 21:58 .
    drwxr-xr-x. 33 root  root   4.0K Jun  9 17:20 ..
    -rw-rw----   1 mysql mysql   16K Jun 14 22:04 aria_log.00000001
    -rw-rw----   1 mysql mysql    52 Jun 14 22:04 aria_log_control
    drwxr-xr-x   2 root  root   4.0K Jun  9 16:52 cmsetiofiotest
    -rw-rw----   1 mysql mysql  564K Jun 14 21:57 ib_buffer_pool
    -rw-rw----   1 mysql mysql   74M Jun 14 22:04 ibdata1
    -rw-rw----   1 mysql mysql  512M Jun 14 22:04 ib_logfile0
    -rw-rw----   1 mysql mysql  1.4M Jun  9 16:51 ib_logfile0.gz
    -rw-rw----   1 mysql mysql  512M Jun 14 20:13 ib_logfile1
    -rw-rw----   1 mysql mysql 1018K Jun  9 16:51 ib_logfile1.gz
    -rw-rw----   1 mysql mysql   12M Jun 14 22:03 ibtmp1
    drwx------   2 mysql mysql   28K Jun 14 20:01 database_blabla
    drwx------   2 mysql mysql   20K Jun 14 20:13 database2_blabla
    -rw-rw----   1 mysql mysql     0 Jun  9 16:51 multi-master.info
    drwx--x--x   2 mysql mysql  4.0K Jun  9 16:51 mysql
    srwxrwxrwx   1 mysql mysql     0 Jun 14 21:58 mysql.sock
    -rw-r--r--   1 mysql mysql    16 Jun  9 16:51 mysql_upgrade_info
    drwx------   2 mysql mysql  4.0K Jun  9 16:51 performance_schema
    -rw-rw----   1 mysql mysql   47K Jun 14 21:01 server.mydomain.com.err
    -rw-rw----   1 mysql mysql     6 Jun 14 21:58 server.pid
    -rw-rw----   1 mysql mysql   24K Jun 14 21:58 tc.log
    
    i found on the net (i don't think that's what Centminmod do):

    Any ideas?

    Thank you
     
    Last edited: Jun 15, 2020
  2. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    Comparing the old server folders/files with the old server i found some differences and this is what i did:

    Stop the MariaDB and changed /var/lib/mysql/mysql folder from 0700 to 0711 and for /var/lib/mysql/mysql_upgrade_info fromm root root to mysql mysql ...

    Then i start MariaDB but again i am getting the same error when i use mysqlstop or mysqlrestart :(

    Weird as i just export the two databases and import them with no errors as i always do....

    Maybe we need to add at mycnf something like:

    Code:
    pid-file=/var/lib/mysql/server.pid
    ?
     
    Last edited: Jun 15, 2020
  3. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    When i set this at mycnf:

    Code:
    pid-file=/var/lib/mysql/server.pid
    i am getting this:

    Code:
    [23:22][root@server.mydomain.com ~]# mysqlstop
    Shutting down MariaDB..                                    [  OK  ]
    [23:22][root@server.mydomain.com ~]# mysqlstart
    Starting MariaDB.200614 23:22:20 mysqld_safe Logging to '/var/lib/mysql/server.mydomain.com.err'.
    200614 23:22:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
                                                               [  OK  ]
    
    I even backup the databases and delete the ib_logfile0 and ib_logfile1 and ibdata1 and re import them with the same results :(

    The shortcut mysqlstop not only it doesn't work but reveals and error that i don't get at all when i use the systemctl stop,start,restart mysqld ....

    :(
     
    Last edited: Jun 15, 2020
  4. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    I just notice at the systemctl status mysqld this:

    Code:
    [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slav
    my.cnf:

    Code:
    [client]
    socket=/var/lib/mysql/mysql.sock
    
    [mysql]
    max_allowed_packet = 80M
    
    [mysqld]
    ignore_db_dirs=cmsetiofiotest
    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
    
    tmpdir=/home/mysqltmp
    
    innodb=ON
    #skip-federated
    ##skip-pbxt
    ##skip-pbxt_statistics
    #skip-archive
    skip-name-resolve
    #old_passwords
    back_log = 1024
    max_connections = 800
    key_buffer_size = 1024M
    myisam_sort_buffer_size = 1024M
    myisam_max_sort_file_size = 10240M
    join_buffer_size = 2M
    read_buffer_size = 2M
    sort_buffer_size = 2M
    table_definition_cache = 10240
    table_open_cache = 10240
    thread_cache_size = 384
    wait_timeout = 1800
    connect_timeout = 10
    tmp_table_size = 1536M
    max_heap_table_size = 1536M
    max_allowed_packet = 80M
    #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+
    query_cache_limit = 1536K
    query_cache_size = 256M
    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 = 4  
    innodb_default_row_format = dynamic
    innodb_file_per_table = 1
    innodb_open_files = 10000
    innodb_data_file_path= ibdata1:10M:autoextend
    innodb_buffer_pool_size = 4G
    
    ## https://mariadb.com/kb/en/mariadb/xtradbinnodb-server-system-variables/#innodb_buffer_pool_instances
    innodb_buffer_pool_instances=4
    
    innodb_log_files_in_group = 2
    innodb_log_file_size = 512M
    innodb_log_buffer_size = 64M
    innodb_flush_log_at_trx_commit = 2
    innodb_thread_concurrency = 28
    innodb_lock_wait_timeout=50
    innodb_flush_method = O_DIRECT
    
    # 200 * # DISKS
    innodb_io_capacity = 2600
    innodb_io_capacity_max = 4333
    innodb_read_io_threads = 6
    innodb_write_io_threads = 6
    innodb_flush_neighbors = 0
    
    # 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 = 1024M
    aria_log_purge_type = immediate
    aria_pagecache_buffer_size = 1024M
    aria_sort_buffer_size = 256M
    
    [mariadb-5.5]
    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 = 80M
    
    [myisamchk]
    tmpdir=/home/mysqltmp
    key_buffer = 1536M
    sort_buffer = 384M
    read_buffer = 384M
    write_buffer = 384M
    
    [mysqlhotcopy]
    interactive-timeout
    
    [mariadb-10.0]
    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
    
    [mariadb-10.3]
    #innodb_file_format = Barracuda
    innodb_default_row_format = dynamic
    innodb_file_per_table = 1
    
    ## wsrep specific
    # wsrep_on=OFF
    # wsrep_provider
    # wsrep_cluster_address
    # binlog_format=ROW
    # default_storage_engine=InnoDB
    # innodb_autoinc_lock_mode=2
    # innodb_doublewrite=1
    # query_cache_size=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
    ## 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
    
    # Defragmenting unused space on InnoDB tablespace
    innodb_defragment=1
    innodb_defragment_n_pages=7
    innodb_defragment_stats_accuracy=0
    innodb_defragment_fill_factor_n_recs=20
    innodb_defragment_fill_factor=0.9
    innodb_defragment_frequency=40
    
    # page compression
    # 10.1+ https://mariadb.com/kb/en/mariadb/compression/
    #innodb_compression_algorithm=zlib
    #innodb_use_fallocate=0
    #innodb_use_mtflush=0
    #innodb_mtflush_threads=4
     
  5. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    Ok i found the issue but i don't know how i end it up with a broken setup :(

    Code:
    mysql_upgrade --force
    Phase 1/7: Checking and upgrading mysql database
    Processing databases
    mysql
    mysql.column_stats                                 OK
    mysql.columns_priv                                 OK
    mysql.db                                           OK
    mysql.event                                        OK
    mysql.func                                         OK
    mysql.gtid_slave_pos
    Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
    status   : Operation failed
    mysql.help_category                                OK
    mysql.help_keyword                                 OK
    mysql.help_relation                                OK
    mysql.help_topic                                   OK
    mysql.host                                         OK
    mysql.index_stats                                  OK
    mysql.innodb_index_stats
    Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
    status   : Operation failed
    mysql.innodb_table_stats
    Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
    status   : Operation failed
    mysql.plugin                                       OK
    mysql.proc                                         OK
    mysql.procs_priv                                   OK
    mysql.proxies_priv                                 OK
    mysql.roles_mapping                                OK
    mysql.servers                                      OK
    mysql.table_stats                                  OK
    mysql.tables_priv                                  OK
    mysql.time_zone                                    OK
    mysql.time_zone_leap_second                        OK
    mysql.time_zone_name                               OK
    mysql.time_zone_transition                         OK
    mysql.time_zone_transition_type                    OK
    mysql.transaction_registry
    Error    : Table 'mysql.transaction_registry' doesn't exist in engine
    status   : Operation failed
    mysql.user                                         OK
    
    Repairing tables
    mysql.gtid_slave_pos
    Error    : Table 'mysql.gtid_slave_pos' doesn't exist in engine
    status   : Operation failed
    mysql.innodb_index_stats
    Error    : Table 'mysql.innodb_index_stats' doesn't exist in engine
    status   : Operation failed
    mysql.innodb_table_stats
    Error    : Table 'mysql.innodb_table_stats' doesn't exist in engine
    status   : Operation failed
    mysql.transaction_registry
    Error    : Table 'mysql.transaction_registry' doesn't exist in engine
    status   : Operation failed
    Phase 2/7: Installing used storage engines... Skipped
    Phase 3/7: Fixing views
    Phase 4/7: Running 'mysql_fix_privilege_tables'
    ERROR 1932 (42S02) at line 635: Table 'mysql.innodb_index_stats' doesn't exist in engine
    ERROR 1243 (HY000) at line 636: Unknown prepared statement handler (stmt) given to EXECUTE
    ERROR 1932 (42S02) at line 638: Table 'mysql.innodb_table_stats' doesn't exist in engine
    ERROR 1243 (HY000) at line 639: Unknown prepared statement handler (stmt) given to EXECUTE
    ERROR 1932 (42S02) at line 643: Table 'mysql.innodb_index_stats' doesn't exist in engine
    ERROR 1932 (42S02) at line 647: Table 'mysql.innodb_table_stats' doesn't exist in engine
    ERROR 1932 (42S02) at line 650: Table 'mysql.innodb_table_stats' doesn't exist in engine
    FATAL ERROR: Upgrade failed
    Is there a way to re create the system tables?
    Or reinstall MariaDB ?
    As i have 2 small databases that i can re import easily .... and i would like to avoid re installing the server :(

    Something like:
    Code:
    yum reinstall MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared --disablerepo=epel --disablerepo=rpmforge --disableplugin=priorities
    or
    Code:
    yum remove MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared --disablerepo=epel --disablerepo=rpmforge --disableplugin=priorities
    and then:
    Code:
    yum install MariaDB-client MariaDB-common MariaDB-compat MariaDB-devel MariaDB-server MariaDB-shared --disablerepo=epel --disablerepo=rpmforge --disableplugin=priorities
    ?

    Thank you
     
    Last edited: Jun 15, 2020
  6. rdan

    rdan Well-Known Member

    4,939
    1,179
    113
    May 25, 2014
    Ratings:
    +1,785
    Local Time:
    9:26 AM
    Mainline
    10.2
  7. eva2000

    eva2000 Administrator Staff Member

    44,417
    10,144
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,692
    Local Time:
    11:26 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    as to why MariaDB failed to stop would be a separate issue to investigate

    That is normal as MariaDB 10 includes Galera by default even if disabled/not used

    see normal status on recently installed 123.09beta01 CentOS 7 KVM VPS
    Code (Text):
    systemctl status mariadb | sed -e "s|$(hostname)|hostname|g"
    ● mariadb.service - MariaDB 10.3.23 database server
       Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
      Drop-In: /etc/systemd/system/mariadb.service.d
               └─migrated-from-my.cnf-settings.conf, openfileslimit.conf, protecthome.conf
       Active: active (running) since Mon 2020-06-15 06:09:38 UTC; 5min ago
         Docs: man:mysqld(8)
               https://mariadb.com/kb/en/library/systemd/
      Process: 43123 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
      Process: 42832 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
      Process: 42830 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
     Main PID: 43098 (mysqld)
       Status: "Taking your SQL requests now..."
       CGroup: /system.slice/mariadb.service
               └─43098 /usr/sbin/mysqld
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Waiting for purge to start
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: 10.3.23 started; log sequence number 1625639; transaction id 21
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] Plugin 'FEEDBACK' is disabled.
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] Server socket created on IP: '::'.
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Buffer pool(s) load completed at 200615  6:09:38
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] /usr/sbin/mysqld: ready for connections.
    Jun 15 06:09:38 hostname mysqld[43098]: Version: '10.3.23-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
    Jun 15 06:09:38 hostname systemd[1]: Started MariaDB 10.3.23 database server.
    


    why you chmodding and changing mysql directory permissions ? you shouldn't need to

    default permissions on /var/lib/mysql are 755 owned by mysql user/group
    Code (Text):
    getfacl -p /var/lib/mysql
    # file: /var/lib/mysql
    # owner: mysql
    # group: mysql
    user::rwx
    group::r-x
    other::r-x
    

    if you are changing them, that is probably why you're running into issues

    how did you restore your databases ?? did they involve changing user/group permissions ?
     
  8. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    Code:
    getfacl -p /var/lib/mysql
    # file: /var/lib/mysql
    # owner: mysql
    # group: mysql
    user::rwx
    group::r-x
    other::r-x

    no

    I am using a simple backup and restore as i always do.....

    I was able to replicate the issue except the MariaDB server PID file could not be found! error at a vps and i fix it by BACKUP the 2 databases first and then:

    Stop MariaDB using: systemctl stop mariadb

    Then i delete the files:

    Code:
    /var/lib/mysql/mysql/gtid_slave_pos.frm
    /var/lib/mysql/mysql/gtid_slave_pos.ibd
    /var/lib/mysql/mysql/innodb_index_stats.frm
    /var/lib/mysql/mysql/innodb_index_stats.ibd
    /var/lib/mysql/mysql/innodb_table_stats.frm
    /var/lib/mysql/mysql/innodb_table_stats.ibd
    /var/lib/mysql/mysql/transaction_registry.frm
    /var/lib/mysql/mysql/transaction_registry.ibd
    Then i start MariaDB: systemctl start mariadb

    Then i run:
    Code:
    mysql_upgrade --force
    And i got OK on all system/mydatabases tables :)

    Now i am able to stop start restart and all working without any errors !

    On the main server i did the same and i am getting all OK without any errors and systemctl status without any errors but i can't stop/start/restart using the mysqlstop, mysqlstart, mysqlrestart :(

    Code:
    MariaDB server PID file could not be found!
    I can use only the systemctl without any issues.

    Don't know why there is that issue with the pid file :(

    The backup was from MariaDB 10.2.x and the new server is 10.3.x ... That's maybe the issue for the mysql system tables..... but it shouldn't give that Pid error :(
     
    Last edited: Jun 15, 2020
  9. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    It can't find the Pid using the:
    Code:
    service mysql stop
    but it's fine with:
    Code:
    systemctl stop mariadb
     
  10. eva2000

    eva2000 Administrator Staff Member

    44,417
    10,144
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,692
    Local Time:
    11:26 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    yeah some aliases for mysql service don't link to mariadb service i.e. service mysql status but service mysqld status does. But should for stop/start commands

    Code (Text):
    service mysql restart
    Restarting mysql (via systemctl):                          [  OK  ]
    
    service mysqld restart
    Redirecting to /bin/systemctl restart mysqld.service
    


    what's output for
    Code (Text):
    cat /usr/bin/mysqlrestart                                          
    

    i.e.
    Code (Text):
    cat /usr/bin/mysqlrestart                                          
    systemctl daemon-reload; service mysql restart
    

    and
    Code (Text):
    cat /usr/lib/systemd/system/mariadb.service
    

    Code (Text):
    # It's not recommended to modify this file in-place, because it will be
    # overwritten during package upgrades.  If you want to customize, the
    # best way is to create a file "/etc/systemd/system/mariadb.service",
    # containing
    #       .include /usr/lib/systemd/system/mariadb.service
    #       ...make your changes here...
    # or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
    # which doesn't need to include ".include" call and which will be parsed
    # after the file mariadb.service itself is parsed.
    #
    # For more info about custom unit files, see systemd.unit(5) or
    # https://mariadb.com/kb/en/mariadb/systemd/
    #
    # Copyright notice:
    #
    # This file is free software; you can redistribute it and/or modify it
    # under the terms of the GNU Lesser General Public License as published by
    # the Free Software Foundation; either version 2.1 of the License, or
    # (at your option) any later version.
    
    [Unit]
    Description=MariaDB 10.3.23 database server
    Documentation=man:mysqld(8)
    Documentation=https://mariadb.com/kb/en/library/systemd/
    After=network.target
    
    [Install]
    WantedBy=multi-user.target
    Alias=mysql.service
    Alias=mysqld.service
    
    
    [Service]
    
    ##############################################################################
    ## Core requirements
    ##
    
    Type=notify
    
    # Setting this to true can break replication and the Type=notify settings
    # See also bind-address mysqld option.
    PrivateNetwork=false
    
    ##############################################################################
    ## Package maintainers
    ##
    
    User=mysql
    Group=mysql
    
    # To allow memlock to be used as non-root user if set in configuration
    CapabilityBoundingSet=CAP_IPC_LOCK
    
    # Prevent writes to /usr, /boot, and /etc
    ProtectSystem=full
    
    # Doesn't yet work properly with SELinux enabled
    # NoNewPrivileges=true
    
    PrivateDevices=true
    
    # Prevent accessing /home, /root and /run/user
    ProtectHome=true
    
    # Execute pre and post scripts as root, otherwise it does it as User=
    PermissionsStartOnly=true
    
    
    
    # Perform automatic wsrep recovery. When server is started without wsrep,
    # galera_recovery simply returns an empty string. In any case, however,
    # the script is not expected to return with a non-zero status.
    # It is always safe to unset _WSREP_START_POSITION environment variable.
    # Do not panic if galera_recovery script is not available. (MDEV-10538)
    ExecStartPre=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
    ExecStartPre=/bin/sh -c "[ ! -e /usr/bin/galera_recovery ] && VAR= || \
     VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] \
     && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1"
    
    # Needed to create system tables etc.
    # ExecStartPre=/usr/bin/mysql_install_db -u mysql
    
    # Start main service
    # MYSQLD_OPTS here is for users to set in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
    # Use the [Service] section and Environment="MYSQLD_OPTS=...".
    # This isn't a replacement for my.cnf.
    # _WSREP_NEW_CLUSTER is for the exclusive use of the script galera_new_cluster
    ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION
    
    # Unset _WSREP_START_POSITION environment variable.
    ExecStartPost=/bin/sh -c "systemctl unset-environment _WSREP_START_POSITION"
    
    
    
    KillSignal=SIGTERM
    
    # Don't want to see an automated SIGKILL ever
    SendSIGKILL=no
    
    # Restart crashed server only, on-failure would also restart, for example, when
    # my.cnf contains unknown option
    Restart=on-abort
    RestartSec=5s
    
    UMask=007
    
    ##############################################################################
    ## USERs can override
    ##
    ##
    ## by creating a file in /etc/systemd/system/mariadb.service.d/MY_SPECIAL.conf
    ## and adding/setting the following under [Service] will override this file's
    ## settings.
    
    # Useful options not previously available in [mysqld_safe]
    
    # Kernels like killing mysqld when out of memory because its big.
    # Lets temper that preference a little.
    # OOMScoreAdjust=-600
    
    # Explicitly start with high IO priority
    # BlockIOWeight=1000
    
    # If you don't use the /tmp directory for SELECT ... OUTFILE and
    # LOAD DATA INFILE you can enable PrivateTmp=true for a little more security.
    PrivateTmp=false
    
    # Set an explicit Start and Stop timeout of 900 seconds (15 minutes!)
    # this is the same value as used in SysV init scripts in the past
    # Galera might need a longer timeout, check the KB if you want to change this:
    # https://mariadb.com/kb/en/library/systemd/#configuring-the-systemd-service-timeout
    TimeoutStartSec=900
    TimeoutStopSec=900
    
    ##
    ## Options previously available to be set via [mysqld_safe]
    ## that now needs to be set by systemd config files as mysqld_safe
    ## isn't executed.
    ##
    
    # Number of files limit. previously [mysqld_safe] open-files-limit
    LimitNOFILE=16364
    
    # Maximium core size. previously [mysqld_safe] core-file-size
    # LimitCore=
    
    # Nice priority. previously [mysqld_safe] nice
    # Nice=-5
    
    # Timezone. previously [mysqld_safe] timezone
    # Environment="TZ=UTC"
    
    # Library substitutions. previously [mysqld_safe] malloc-lib with explicit paths
    # (in LD_LIBRARY_PATH) and library name (in LD_PRELOAD).
    # Environment="LD_LIBRARY_PATH=/path1 /path2" "LD_PRELOAD=
    
    # Flush caches. previously [mysqld_safe] flush-caches=1
    # ExecStartPre=sync
    # ExecStartPre=sysctl -q -w vm.drop_caches=3
    
    # numa-interleave=1 equalivant
    # Change ExecStart=numactl --interleave=all /usr/sbin/mysqld......
    
    # crash-script equalivent
    # FailureAction=
    
     
  11. eva2000

    eva2000 Administrator Staff Member

    44,417
    10,144
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,692
    Local Time:
    11:26 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    from mariadb.service service file there are aliases for mysql and mysqld service calls
    Code (Text):
    [Install]
    WantedBy=multi-user.target
    Alias=mysql.service
    Alias=mysqld.service
    
     
  12. eva2000

    eva2000 Administrator Staff Member

    44,417
    10,144
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,692
    Local Time:
    11:26 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    You can save your full MariaDB log to a file called /root/centminlogs/mariadb-fulllog.log
    Code (Text):
    journalctl -u mariadb --no-pager | sed -e "s|$(hostname)|hostname|g" > /root/centminlogs/mariadb-fulllog.log

    and share it's contents via pastebin.com or gist.github.com
     
  13. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    systemctl daemon-reload; service mysql restart

    contents via pastebin.com:

    -- Logs begin at Mon 2020-06-15 00:24:00 UTC, end at Mon 2020-06-15 08:27:16 UTC - Pastebin.com
     
  14. eva2000

    eva2000 Administrator Staff Member

    44,417
    10,144
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,692
    Local Time:
    11:26 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    One more thing with OVH new VPS use sudo user, did you switch to full root user before installing Centmin Mod i.e. run
    Code (Text):
    sudo -i

    before running centmin mod install
     
  15. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    The pid file problem is related to the Ovh dedicated server so yes i was login and run Centminmod as root as i always do...

    What about Centminmod detects the system and if Centos 6 to use service and if Centos 7 to use systemctl ?

    Or it's not a good idea? Don't know ...
     
  16. eva2000

    eva2000 Administrator Staff Member

    44,417
    10,144
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,692
    Local Time:
    11:26 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    notice from first start up log entries you have references to my.cnf settings which Centmin Mod doesn't use i.e. --skip-name-resolve

    Code:
    Jun 15 00:24:09 hostname mysqld[1978]: 2020-06-15  0:24:09 0 [Warning] 'user' entry 'root@hostname' ignored in --skip-name-resolve mode.
    Jun 15 00:24:09 hostname mysqld[1978]: 2020-06-15  0:24:09 0 [Warning] 'proxies_priv' entry '@% root@hostname' ignored in --skip-name-resolve mode.
    Jun 15 00:24:09 hostname mysqld[1978]: 2020-06-15  0:24:09 6 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
    Jun 15 00:24:09 hostname mysqld[1978]: 2020-06-15  0:24:09 0 [Note] /usr/sbin/mysqld: ready for connections.
    this is what I see on a fresh CentOS 7 install with MariaDB 10.3.23
    Code (Text):
    Jun 15 06:09:38 hostname systemd[1]: Starting MariaDB 10.3.23 database server...
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] /usr/sbin/mysqld (mysqld 10.3.23-MariaDB) starting as process 43098 ...
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Using Linux native AIO
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Uses event mutexes
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Number of pools: 1
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Using SSE2 crc32 instructions
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Completed initialization of buffer pool
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Creating shared tablespace for temporary tables
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Waiting for purge to start
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: 10.3.23 started; log sequence number 1625639; transaction id 21
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] Plugin 'FEEDBACK' is disabled.
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] Server socket created on IP: '::'.
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] InnoDB: Buffer pool(s) load completed at 200615  6:09:38
    Jun 15 06:09:38 hostname mysqld[43098]: 2020-06-15  6:09:38 0 [Note] /usr/sbin/mysqld: ready for connections.
    Jun 15 06:09:38 hostname mysqld[43098]: Version: '10.3.23-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
    Jun 15 06:09:38 hostname systemd[1]: Started MariaDB 10.3.23 database server.
    

    check if /etc/my.cnf or any in /etc have skip-name-resolve set
    Code (Text):
    grep -rin 'skip-name-resolve' /etc

    default Centmin Mod 123.09beta01 installs should return result for 3 files but all have hash # commenting them out/disabling them so they aren't used
    Code (Text):
    grep -rin 'skip-name-resolve' /etc
    /etc/my.cnf:27:#skip-name-resolve
    /etc/my.cnf-newold:26:#skip-name-resolve
    /etc/my.cnf-setiobackup:26:#skip-name-resolve
    
     
  17. eva2000

    eva2000 Administrator Staff Member

    44,417
    10,144
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,692
    Local Time:
    11:26 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
  18. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    Code:
    /etc/my.cnf-newold:26:#skip-name-resolve
    /etc/my.cnf:25:skip-name-resolve
    /etc/my.cnf-setiobackup:26:#skip-name-resolve
    Yes i always enable it to avoid dns resolution and delays.... never had any issues with it...
     
  19. eva2000

    eva2000 Administrator Staff Member

    44,417
    10,144
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,692
    Local Time:
    11:26 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    did you reboot your server since initial Centmin Mod install ? as journalctl -u mariadb log output is only in memory and flushed on server reboot, so your provided log if after reboot of server, wouldn't contain the initial centmin mod install entries so could of been something else
     
  20. pamamolf

    pamamolf Premium Member Premium Member

    3,807
    369
    83
    May 31, 2014
    Ratings:
    +711
    Local Time:
    4:26 AM
    Nginx-1.17.x
    MariaDB 10.3.x
    Yes i rebooted the server...