Want more timely Centmin Mod News Updates?
Become a Member

Sysadmin Finding the cause of a server crash?

Discussion in 'System Administration' started by RB1, Jan 10, 2017.

  1. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    did you first restart mysql server ?
     
  2. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x
    Yes I did, and I just tried again after restarting. Same error :(
     
  3. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
  4. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x

    mysqladmin Ver 9.1 Distrib 10.1.20-MariaDB, for Linux on x86_64
    Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

    Server version 10.1.20-MariaDB
    Protocol version 10
    Connection Localhost via UNIX socket
    UNIX socket /var/lib/mysql/mysql.sock
    Uptime: 20 min 53 sec

    Threads: 2 Questions: 40 Slow queries: 0 Opens: 17 Flush tables: 1 Open tables: 11 Queries per second avg: 0.031

    Password reset and edited /root/.my.cnf with the new one.
    I can now create user/pass/db via SSH but now my wordpress and hosted software won't connect to their databases with correct info :(

    I will try to figure this out tomorrow morning
     
  5. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    sounds like you didn't backup mysql database itself ? the actual database name = mysql
    Code (Text):
    DBNAME='mysql'
    mysqldump --opt --routines $DBNAME> /home/mysqlbackups/$DBNAME_backup.sql

    then contains all your mysql user/pass details including root user details
    you can use addons/mysqladmin_shell.sh https://community.centminmod.com/threads/mysqladmin_shell-sh-shell-based-addon.543/

    Code (Text):
    ./mysqladmin_shell.sh 
    
    ./mysqladmin_shell.sh {multidb|setglobaluser|setuserdb|setpass|deluser|showgrants}
    
    multidb - multiple mysql databse/user creation mode passing a file name containing db, user, pass 3 column entries
    setglobaluser - create a mysql username with access to all databases on server without SUPER ADMIN privileges (non-root)
    setuserdb - create individual mysql username and databases or assign a new database to an existing mysql username
    setpass - change mysql username password
    deluser - delete a mysql usernames
    showgrants - show existing mysql username granted privileges
    

    and use setglobaluser option to create a single mysql username to access all your databases if you want
    Code (Text):
    ./mysqladmin_shell.sh setglobaluser
    

    Then update your web app's mysql user and pass to the one created by setglobaluser option

    or if you want individual mysql user/pass again, use setuserdb option but specify the mysql user/pass you want but instead when prompted to create a database, just specify a dummy database name i.e. dummydb1, dummydb2 etc
    Code (Text):
    ./mysqladmin_shell.sh setuserdb
    

    then that creates the mysql user/pass then rerun setuserdb but instead of create user/pass select option to assign a new database to existing mysql username/password and specify your actual database names for your web apps
    Code (Text):
    ./mysqladmin_shell.sh setuserdb
    
     
  6. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x
    Even following these steps I still can't connect to the databases.
    Something is either very messed up, or I am incompetent (I'm guessing the latter). :D

    EDIT: Brand new database, mysql user and password and a fresh install of Wordpress as a test and still can't connect to the database. What is happening :( lol
     
  7. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    can you connect via command line ?
    Code (Text):
    mysql -u mysqlusername -p
    

    will prompt you to enter mysqlusername's password and land you in mysql interactive client then just type 'exit' to exit that mode
     
  8. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x
    Yes it lets me connect that way and also create databases from there
     
  9. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    so problem is settings you put into your web app's database config variables. Make sure you set the right mysql username and password and hostname which could be localhost or 127.0.0.1
     
  10. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x
    Only values that changed in the config files are my db name, user/pass. Mysql server still set as local host and I tried everything on a new user and new database (blank and the ones I imported). I will play with things for a little longer but maybe I will just rebuild the server later :sick:
     
  11. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    what errors on web app do you get related to db connection ? tried restart php-fpm + mariadb mysql service too ?
     
  12. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x
    Tried all those steps regarding restart of the services

    Wordpress: Error establishing database connection
    Prosper202: Error establishing a database connection; 202-config.php file is incorrect or we can't contact the database server at localhost
     
  13. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
  14. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x
  15. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    did you setup global mysql user via mysqladmin_shell.sh and try that ?
    Code (Text):
    ./mysqladmin_shell.sh setglobaluser
    
     
  16. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x
    Yes I've done that as well.
    I'm spinning up a new Linode VPS right now :(
    I want to test if I'm going crazy or really setting something up incorrectly

    Should have gone with a more powerful server...I forgot how long initial setup takes on 1 CPU lol
     
  17. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    confirm your mysqlusername has proper grant privileges set via show grant option
    Code (Text):
    ./mysqladmin_shell.sh showgrants
    

    will prompt for mysqlusername you want to check

    example
    Code (Text):
    ./mysqladmin_shell.sh showgrants
    --------------------------------------------------------------
    Basic MySQL Admin - create mysql user & databases 
    --------------------------------------------------------------
    --------------------------------------------------------------
    
    ---------------------------------
    Show Grants for MySQL username:
    ---------------------------------
     Enter MySQL username to Show Grant permissions: MYSQLGLOBALUSERNAME
    Grants for MYSQLGLOBALUSERNAME@localhost
    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'MYSQLGLOBALUSERNAME'@'localhost' IDENTIFIED BY PASSWORD '*2ADCB8D06789002A3A1F92A367E0916A5BA38D70'
    
    Ok: SHOW GRANTS for 'MYSQLGLOBALUSERNAME'@'localhost' was successful
    
     
  18. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x

    Code (Text):
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Grants for supercooladmin@localhost                                                                                                                                                                                       |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'supercooladmin'@'localhost' IDENTIFIED BY PASSWORD '*5G4GA9CFG3A5693B465HF08C5D635F03406F6289' |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     
  19. eva2000

    eva2000 Administrator Staff Member

    44,412
    10,141
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +15,689
    Local Time:
    2:27 AM
    Nginx 1.17.x
    MariaDB 5.5/10.x
    looks right to me unless your password as characters which mysql doesn't like ?
     
  20. RB1

    RB1 Active Member

    285
    75
    28
    Nov 11, 2016
    California
    Ratings:
    +122
    Local Time:
    9:27 AM
    Nginx 1.18.x
    MariaDB 10.1.x
    Hmm...I thought that could maybe be the problem but I tried with a regular plaintext password and it gave an error again. I will post again when I test this on my 2nd server :)