SQL doesn't start up properly from CRON

[queenvictoria]

At 0840 this morning

$ ps aux | grep -i sql
mysql    31365  0.0  5.2 681580 107828 ?       Ssl  02:10   0:10 /usr/sbin/mysqld

$ sudo service mysql restart
$ ps aux | grep -i sql
mysql    18296 10.8  5.3 1338900 109744 ?      Ssl  08:40   0:01 /usr/sbin/mysqld

[ckutay]

Dear Snow

I wen in to start this morning sorry: sudo mysql restart

[sudo] password for ckutay:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

ckutay@mindalgali:~$ sudo mysql start

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Then worked a few minutes later :-(

Cat Kutay ⚽️ (_/) CITIES (='.’=) Mob: 0418 455 089 (")(")

From: Mr Snow notifications@github.com Reply-To: ckutay/Language <reply+0020410237d49c87a092744d166c67e1a0b4d0f86627729b92cf000000011413cdcb9 2a169ce0adaf9a8@reply.github.com> Date: Tuesday, 11 October 2016 8:44 am To: ckutay/Language Language@noreply.github.com Subject: [ckutay/Language] SQL doesn't start up properly from CRON (#5)

At 0840 this morning

$ ps aux | grep -i sql mysql 31365 0.0 5.2 681580 107828 ? Ssl 02:10 0:10 /usr/sbin/mysqld $ sudo service mysql restart $ ps aux | grep -i sql mysql 18296 10.8 5.3 1338900 109744 ? Ssl 08:40 0:01 /usr/sbin/mysqld — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ckutay/Language/issues/5 , or mute the thread https://github.com/notifications/unsubscribe-auth/ACBBAqlOCXxBLJTHyIO4jHL53 CWAswSHks5qyrHLgaJpZM4KTBYf .

{"api_version":"1.0","publisher":{"api_key":"05dde50f1d1a384dd78767c55493e4b b","name":"GitHub"},"entity":{"external_key":"github/ckutay/Language","title ":"ckutay/Language","subtitle":"GitHub repository","main_image_url":"https://cloud.githubusercontent.com/assets/143 418/17495839/a5054eac-5d88-11e6-95fc-7290892c7bb5.png","avatar_image_url":"h ttps://cloud.githubusercontent.com/assets/143418/15842166/7c72db34-2c0b-11e6 -9aed-b52498112777.png","action":{"name":"Open in GitHub","url":"https://github.com/ckutay/Language"}},"updates":{"snippets":[ {"icon":"DESCRIPTION","message":"SQL doesn't start up properly from CRON (#5)"}],"action":{"name":"View Issue","url":"https://github.com/ckutay/Language/issues/5"}}}

[queenvictoria]

Ok thanks Cat. I've looked at the log files from last night. I switch to service mysql stop && service mysql start in Cron yesterday. It looks like the stop command doesn't stop the process as when start is called it is still running. It attempted twice to start at 0210 and then gave up. Perhaps the stop process is backgrounded and takes a few minutes. I'll test that theory this morning.

[queenvictoria]

Just checking. I presume when you say $ sudo mysql start you mean $ sudo service mysql start as the former would try and connect to the database start as user root rather than starting the daemon.

Is that what you did?

[queenvictoria]

I've installed and run mysqltuner to see if there is a good reason that it is not behaving itself. I will implement the 3 performance recommendations mentioned at the end.

root@mindalgali:/home/snow# mysqltuner.pl 
 >>  MySQLTuner 1.7.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.52-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in MEMORY tables: 0B (Tables: 9)
[--] Data in MyISAM tables: 83M (Tables: 657)
[--] Data in InnoDB tables: 53M (Tables: 660)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'adminlanguage@%' hasn't specific host restriction.
[!!] User 'kinship_admin@%' hasn't specific host restriction.
[!!] User 'language_admin@%' hasn't specific host restriction.
[!!] User 'language_control@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5m 29s (2K q [7.386 qps], 725 conn, TX: 631K, RX: 204K)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 2.0G
[--] Max MySQL memory    : 597.8M
[--] Other process memory: 68.0M
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 197.4M (9.86% of installed RAM)
[OK] Maximum possible memory usage: 597.8M (29.86% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/2K)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Aborted connections: 0.00%  (0/725)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23 sorts)
[!!] Joins performed without indexes: 5
[OK] Temporary tables created on disk: 24% (56 on disk / 233 total)
[OK] Thread cache hit rate: 99% (2 created / 725 connections)
[!!] Table cache hit rate: 18% (400 open / 2K opened)
[OK] Open file limit used: 71% (735/1K)
[OK] Table locks acquired immediately: 100% (883 immediate / 883 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.4% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/9.9M
[!!] Read Key buffer hit rate: 64.3% (84 cached / 30 reads)
[!!] Write Key buffer hit rate: 14.3% (14 cached / 12 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/53.0M
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 95.03% (47520 hits/ 50006 total)
[!!] InnoDB Write Log efficiency: 38.46% (5 hits/ 13 total)
[OK] InnoDB log waits: 0.00% (0 waits / 8 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable 
    should be greater than table_open_cache ( 400)
Variables to adjust:
    query_cache_type (=0)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_open_cache (> 400)

[catkutay]

Dear Snow

Sorry this was not what you wanted, and was not there before we met I think :-(

From: Mr Snow notifications@github.com<mailto:notifications@github.com> Reply-To: ckutay/Language reply@reply.github.com<mailto:reply@reply.github.com> Date: Wednesday, 12 October 2016 9:12 am To: ckutay/Language Language@noreply.github.com<mailto:Language@noreply.github.com> Subject: Re: [ckutay/Language] SQL doesn't start up properly from CRON (#5)

I've installed and run mysqltuner to see if there is a good reason that it is not behaving itself. I will implement the 3 performance recommendations mentioned at the end.

root@mindalgali:/home/snow# mysqltuner.pl

MySQLTuner 1.7.0 - Major Hayden major@mhtx.net<mailto:major@mhtx.net> Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script [OK] Logged in using credentials from debian maintenance account. [OK] Currently running supported MySQL version 5.5.52-0ubuntu0.14.04.1 [OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MEMORY tables: 0B (Tables: 9) [--] Data in MyISAM tables: 83M (Tables: 657) [--] Data in InnoDB tables: 53M (Tables: 660) [OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] User 'adminlanguage@%' hasn't specific host restriction. [!!] User 'kinship_admin@%' hasn't specific host restriction. [!!] User 'language_admin@%' hasn't specific host restriction. [!!] User 'language_control@%' hasn't specific host restriction. [!!] There is no basic password file list!

-------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined

-------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 5m 29s (2K q [7.386 qps], 725 conn, TX: 631K, RX: 204K) [--] Reads / Writes: 98% / 2% [--] Binary logging is disabled [--] Physical Memory : 2.0G [--] Max MySQL memory : 597.8M [--] Other process memory: 68.0M [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 197.4M (9.86% of installed RAM) [OK] Maximum possible memory usage: 597.8M (29.86% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/2K) [OK] Highest usage of available connections: 1% (2/151) [OK] Aborted connections: 0.00% (0/725) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [!!] Query cache may be disabled by default due to mutex contention. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23 sorts) [!!] Joins performed without indexes: 5 [OK] Temporary tables created on disk: 24% (56 on disk / 233 total) [OK] Thread cache hit rate: 99% (2 created / 725 connections) [!!] Table cache hit rate: 18% (400 open / 2K opened) [OK] Open file limit used: 71% (735/1K) [OK] Table locks acquired immediately: 100% (883 immediate / 883 locks)

-------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled.

-------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.4% (3M used / 16M cache) [OK] Key buffer size / total MyISAM indexes: 16.0M/9.9M [!!] Read Key buffer hit rate: 64.3% (84 cached / 30 reads) [!!] Write Key buffer hit rate: 14.3% (14 cached / 12 writes)

-------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 128.0M/53.0M [OK] InnoDB buffer pool instances: 1 [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 95.03% (47520 hits/ 50006 total) [!!] InnoDB Write Log efficiency: 38.46% (5 hits/ 13 total) [OK] InnoDB log waits: 0.00% (0 waits / 8 writes)

-------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled.

-------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled.

-------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled.

-------- RocksDB Metrics --------------------------------------------------------------------------- [--] RocksDB is disabled.

-------- Spider Metrics ---------------------------------------------------------------------------- [--] Spider is disabled.

-------- Connect Metrics --------------------------------------------------------------------------- [--] Connect is disabled.

-------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled.

-------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server.

-------- Recommendations --------------------------------------------------------------------------- General recommendations: Restrict Host for user@% to user@SpecificDNSorIp MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Adjust your join queries to always utilize indexes Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Beware that open_files_limit (1024) variable should be greater than table_open_cache ( 400) Variables to adjust: query_cache_type (=0) join_buffer_size (> 128.0K, or always use indexes with joins) table_open_cache (> 400)

You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/ckutay/Language/issues/5#issuecomment-253062469, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AKX-RbMAM_ktcDbYzTGB8uqk1aIFUchsks5qzAm5gaJpZM4KTBYf.

[catkutay]

Dear Snow Ah my error - that's why did not work

From: Mr Snow notifications@github.com<mailto:notifications@github.com> Reply-To: ckutay/Language reply@reply.github.com<mailto:reply@reply.github.com> Date: Wednesday, 12 October 2016 9:01 am To: ckutay/Language Language@noreply.github.com<mailto:Language@noreply.github.com> Subject: Re: [ckutay/Language] SQL doesn't start up properly from CRON (#5)

Just checking. I presume when you say $ sudo mysql start you mean $ sudo service mysql start as the former would try and connect to the database start as user root rather than starting the daemon.

Is that what you did?

You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/ckutay/Language/issues/5#issuecomment-253059823, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AKX-RV4rIFZG2SBJeNnJL_Z2k_sane9tks5qzAcpgaJpZM4KTBYf.

[queenvictoria]

Rerun after changes. Added

Variables to adjust:
    open_files_limit (> 1185)

[queenvictoria]

13/10/2016 0654

$ ps aux | grep sql
mysql    16551  0.0  5.1 681584 105012 ?       Ssl  02:10   0:06 /usr/sbin/mysqld
$ sudo su
# service mysql stop
mysql stop/waiting
# service mysql start
mysql start/running, process 21238
# service mysql start
# ps aux | grep sql
mysql    21238  5.9  4.5 550120 92452 ?        Ssl  06:56   0:00 /usr/sbin/mysqld

[queenvictoria]

# cd /var/lib/mysql
# service mysql stop
mysql stop/waiting
# mv ibdata1 ibdata1-20161013
# mv ib_logfile1 ib_logfile-20161013
# mv ib_logfile0 ib_logfile0-20161013
# mv ib_logfile-20161013 ib_logfile1-20161013
# service mysql start

[catkutay]

Dear Mr Snow Yes read somewhere the ibdata1 may cause problems, but odd Is it working now on cron? Or maybe not even needing restart?

Thank you

From: Mr Snow notifications@github.com<mailto:notifications@github.com> Reply-To: ckutay/Language reply@reply.github.com<mailto:reply@reply.github.com> Date: Thursday, 13 October 2016 7:08 am To: ckutay/Language Language@noreply.github.com<mailto:Language@noreply.github.com> Cc: Cat Kutay ckutay@cse.unsw.edu.au<mailto:ckutay@cse.unsw.edu.au>, Comment comment@noreply.github.com<mailto:comment@noreply.github.com> Subject: Re: [ckutay/Language] SQL doesn't start up properly from CRON (#5)

service mysql stop

mysql stop/waiting root@mindalgali:/var/lib/mysql# mv ibdata1 ibdata1-20161013 root@mindalgali:/var/lib/mysql# mv ib_logfile1 ib_logfile-20161013 root@mindalgali:/var/lib/mysql# mv ib_logfile0 ib_logfile0-20161013 root@mindalgali:/var/lib/mysql# mv ib_logfile-20161013 ib_logfile1-20161013 root@mindalgali:/var/lib/mysql# service mysql start

You are receiving this because you commented. Reply to this email directly, view it on GitHubhttps://github.com/ckutay/Language/issues/5#issuecomment-253323892, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AKX-RZAYXRaTyjRE-1w-CuHR4opmfaaSks5qzT4-gaJpZM4KTBYf.