Ubuntu 16.04 with Percona (fresh droplet) won't start after custom my.cnf

June 7, 2017 4.4k views
MySQL Ubuntu
rarit
By:
rarit

Hi,

I've just created a new droplet (16gb) with ubuntu 16.04.2. Ive installed percona and after i configured my.cnf (with percona tools) i can't restart the mysql. It will timeout after a while.

In the my.cnf i have the following:

#
# The Percona Server 5.6 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/percona-server.conf.d/

After those i put my percona tools config file.

# Generated by Percona Configuration Wizard (http://tools.percona.com/) version REL5-20120208

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid-file                       = /var/lib/mysql/mysql.pid

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

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

# DATA STORAGE #
datadir                        = /var/lib/mysql/

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

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

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

# LOGGING #
log-error                      = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

But it won't start after this.

Shouldn't i have some settings in this my.cnf? if i go into the folder /etc/mysql/percona-server.conf.d i have a mysqld.cnf where i have some settings. Should i replace these with my custom percona conf?

Appreciate all help i can get on this. Thank you!

Log In to Comment
2 Answers
jtittle1 June 7, 2017

@rarit

Can you post the output of:

tail -20 /var/lib/mysql/mysql-error.log

If that file does not exit, check /var/log for another log file for MySQL, such as:

/var/log/mysql/error.log

... or similar. You can also try running:

systemctl status mysql

... if there's a systemd script handling the service.

Reply
rarit June 8, 2017

Hi @jtittle!

Thanks for replying so fast.

tail -20 /var/lib/mysql/mysql-error.log did not exist.

However /var/log/mysql/error.log did with the following:

root@percona:/etc/mysql# tail -20 /var/log/mysql/error.log
2017-06-08 07:10:01 8915 [Note] Shutting down plugin 'XTRADB_INTERNAL_HASH_TABLES'
2017-06-08 07:10:01 8915 [Note] Shutting down plugin 'XTRADB_READ_VIEW'
2017-06-08 07:10:01 8915 [Note] Shutting down plugin 'InnoDB'
2017-06-08 07:10:01 8915 [Note] InnoDB: FTS optimize thread exiting.
2017-06-08 07:10:01 8915 [Note] InnoDB: Starting shutdown...
2017-06-08 07:10:03 8915 [Note] InnoDB: Shutdown completed; log sequence number 37752745
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'ARCHIVE'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'BLACKHOLE'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'MEMORY'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'MRG_MYISAM'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'MyISAM'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'CSV'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'sha256_password'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'mysql_old_password'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'mysql_native_password'
2017-06-08 07:10:03 8915 [Note] Shutting down plugin 'binlog'
2017-06-08 07:10:03 8915 [Note] /usr/sbin/mysqld: Shutdown complete

systemctl status mysql gives:

root@percona:/etc/mysql# systemctl status mysql
● mysql.service - LSB: Start and stop the mysql (Percona Server) daemon
   Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled)
   Active: failed (Result: exit-code) since Thu 2017-06-08 07:13:38 UTC; 1min 45s ago
     Docs: man:systemd-sysv-generator(8)
  Process: 9026 ExecStop=/etc/init.d/mysql stop (code=exited, status=0/SUCCESS)
  Process: 9143 ExecStart=/etc/init.d/mysql start (code=exited, status=1/FAILURE)

Jun 08 07:13:38 percona systemd[1]: Starting LSB: Start and stop the mysql (Percona Server) daemon...
Jun 08 07:13:38 percona mysql[9143]: df: /var/lib/mysql//.: No such file or directory
Jun 08 07:13:38 percona mysql[9143]:  * /etc/init.d/mysql: ERROR: The partition with /var/lib/mysql/ is too full!
Jun 08 07:13:38 percona systemd[1]: mysql.service: Control process exited, code=exited status=1
Jun 08 07:13:38 percona systemd[1]: Failed to start LSB: Start and stop the mysql (Percona Server) daemon.
Jun 08 07:13:38 percona systemd[1]: mysql.service: Unit entered failed state.
Jun 08 07:13:38 percona systemd[1]: mysql.service: Failed with result 'exit-code'.

When i try to start i get the following:

root@percona:/etc/mysql# service mysql start
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
Reply
  • jtittle1 June 8, 2017

    @rarit

    From the systemctl status mysql command, the issues are:

    df: /var/lib/mysql//.: No such file or directory

    and

    ERROR: The partition with /var/lib/mysql/ is too full!

    ...

    The first issue is most likely stemming from this line:

    datadir                        = /var/lib/mysql/

    You'll need to remove the trailing /.

    ...

    The next issue, where the error is stating that the partition is too full, is most likely due to these three lines:

    log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

    The binary logs can fill up quick and result in 100% disk usage if you're not maintaining them. A setting, max_binlog_size, can be set to define the max size of each log file, though if you're on a busy server or doing quite a bit, they can still fill up quick and result in 100% disk usage.

    I would first comment out those three lines so that they look like:

    #log-bin                        = /var/lib/mysql/mysql-bin
#expire-logs-days               = 14
#sync-binlog                    = 1

    Then cd to /var/lib/mysql/:

    cd /var/lib/mysql/

    Then run:

    du -h

    That'll allow you to see how large the file is in human-readable format. If it's truly taking up all the available disk space, and once you've commented out the above lines and removed the trailing slash from the other line, delete that file, and restart MySQL.

    rm -rf /var/lib/mysql/mysql-bin

    If there are other mysql-bin files, such as mysql-bin.1, mysql-bin.2, ect, remove those as well.

    • rarit June 8, 2017

      Super!

      I did a very noobby mistake and wrote down the wrong path to the datadir. /var/lib/percona-server is the right datadir. It was that simple. But your help was amazing.

      Huge thanks! What a community!

Have another answer? Share your knowledge.

Related Questions