Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Percona Server failed to start using configuration file generated by online wizard

jinchekerjincheker ContributorCurrent User Role Beginner
Hi, I installed percona-server 5.5 on Ubuntu 12.04 using apt repository




It's working by default. But when I try to replace my.conf file with the configuration that generated from the online wizard , and restart the server, it fails with error like below:


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


df: no file systems processed


* /etc/init.d/mysql: ERROR: The partition with /var/lib/mysql/data/ is too full!

How can I solve this problem? Thanks!

The my.cnf file is like this:


[mysql]





# CLIENT #


port = 3306


socket = /var/lib/mysql/data/mysql.sock





[mysqld]





# GENERAL #


user = mysql


default_storage_engine = InnoDB


socket = /var/lib/mysql/data/mysql.sock


pid_file = /var/lib/mysql/data/mysql.pid





character-set-server=utf8


collation-server = utf8_unicode_ci





# Instead of skip-networking the default is now to listen only on


# localhost which is more compatible and is not less secure.


bind-address = 0.0.0.0





# MyISAM #


key_buffer_size = 32M


myisam_recover = FORCE,BACKUP





# SAFETY #


max_allowed_packet = 16M


max_connect_errors = 1000000


skip_name_resolve


sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY


sysdate_is_now = 1


innodb = FORCE


innodb_strict_mode = 1





# DATA STORAGE #


datadir = /var/lib/mysql/data/





# BINARY LOGGING #


log_bin = /var/lib/mysql/data/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 = 64M


innodb_flush_log_at_trx_commit = 1


innodb_file_per_table = 1


innodb_buffer_pool_size = 512M





# LOGGING #


log_error = /var/lib/mysql/data/mysql-error.log


log_queries_not_using_indexes = 1


slow_query_log = 1


slow_query_log_file = /var/lib/mysql/data/mysql-slow.log

Comments

  • miguelangelnietomiguelangelnieto Member Inactive User Role Beginner
    Does the datadir directory exists? /var/lib/mysql/data/

    The my.cnf says that the datadir is /var/lib/mysql/data/ but the startup script says it doesn't exist. There are different solutions:

    - move the datadir from the old folder (you should have the path in the old my.cnf) to the new one
    - change my.cnf to specify where is the datadir actually

    If you attach the old my.cnf I can help you.

    Regards,
  • jinchekerjincheker Contributor Current User Role Beginner
    Does the datadir directory exists? /var/lib/mysql/data/ The my.cnf says that the datadir is /var/lib/mysql/data/ but the startup script says it doesn't exist. There are different solutions: - move the datadir from the old folder (you should have the path in the old my.cnf) to the new one - change my.cnf to specify where is the datadir actually If you attach the old my.cnf I can help you. Regards,

    Hi miguelangelnieto, thanks for your reply. The data folder does not exists.

    Below is my default my.cnf after my first installation. Hope you can help me figure out what should I do.

    By the way, why percona cnf file asks to move data to data folder instead of the default settings, is there any advantage for that or is it just a way to separate percona-server with mysql default server?

    Thanks



    [client]


    port = 3306


    socket = /var/run/mysqld/mysqld.sock





    # Here is entries for some specific programs


    # The following values assume you have at least 32M ram





    # This was formally known as [safe_mysqld]. Both versions are currently parsed.


    [mysqld_safe]


    socket = /var/run/mysqld/mysqld.sock


    nice = 0





    [mysqld]


    #


    # * Basic Settings


    #


    user = mysql


    pid-file = /var/run/mysqld/mysqld.pid


    socket = /var/run/mysqld/mysqld.sock


    port = 3306


    basedir = /usr


    datadir = /var/lib/mysql


    tmpdir = /tmp


    lc-messages-dir = /usr/share/mysql


    skip-external-locking


    #


    # Instead of skip-networking the default is now to listen only on


    # localhost which is more compatible and is not less secure.


    bind-address = 127.0.0.1


    #


    # * Fine Tuning


    #


    key_buffer = 16M


    max_allowed_packet = 16M


    thread_stack = 192K


    thread_cache_size = 8


    # This replaces the startup script and checks MyISAM tables if needed


    # the first time they are touched


    myisam-recover = BACKUP


    #max_connections = 100


    #table_cache = 64


    #thread_concurrency = 10


    #


    # * Query Cache Configuration


    #


    query_cache_limit = 1M


    query_cache_size = 16M


    #


    # * Logging and Replication


    #


    # Both location gets rotated by the cronjob.


    # Be aware that this log type is a performance killer.


    # As of 5.1 you can enable the log at runtime!


    #general_log_file = /var/log/mysql/mysql.log


    #general_log = 1


    #


    # Error log - should be very few entries.


    #


    log_error = /var/log/mysql/error.log


    #


    # Here you can see queries with especially long duration


    #log_slow_queries = /var/log/mysql/mysql-slow.log


    #long_query_time = 2


    #log-queries-not-using-indexes


    #


    # The following can be used as easy to replay backup logs or for replication.


    # note: if you are setting up a replication slave, see README.Debian about


    # other settings you may need to change.


    #server-id = 1


    #log_bin = /var/log/mysql/mysql-bin.log


    expire_logs_days = 10


    max_binlog_size = 100M


    #binlog_do_db = include_database_name


    #binlog_ignore_db = include_database_name


    #


    # * InnoDB


    #


    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.


    # Read the manual for more InnoDB related options. There are many!


    #


    # * Security Features


    #


    # Read the manual, too, if you want chroot!


    # chroot = /var/lib/mysql/


    #


    # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".


    #


    # ssl-ca=/etc/mysql/cacert.pem


    # ssl-cert=/etc/mysql/server-cert.pem


    # ssl-key=/etc/mysql/server-key.pem











    [mysqldump]


    quick


    quote-names


    max_allowed_packet = 16M





    [mysql]


    #no-auto-rehash # faster start of mysql but no tab completition





    [isamchk]


    key_buffer = 16M





    #


    # * IMPORTANT: Additional settings that can override those from this file!


    # The files must end with '.cnf', otherwise they'll be ignored.


    #


    !includedir /etc/mysql/conf.d/
  • jinchekerjincheker Contributor Current User Role Beginner
    After I created the data folder under /var/lib/mysql looks like the errors before are gone. But it still failed to start.

    I look at the mysql-error.log under /var/lib/mysql/data and found it throws an error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

    How can I solve this? Thanks
  • jinchekerjincheker Contributor Current User Role Beginner
    I tried to run mysql_install_db –-user=mysql –-ldata=/var/lib/mysql/data under root. But it failed with the error like:


    /usr/sbin/mysqld: Too many arguments (first extra is '–-user=mysql').


    Use --verbose --help to get a list of available options


    130508 16:30:57 [ERROR] Aborting

    Drives me crazy...
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Is there anything in /var/lib/mysql/data currently? If so (and assuming you never put any data in there you are trying to save), remove whatever is in that folder. Then as root (or with sudo) try to run your command again:
    sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql/data
    

    That should work, as it's pretty straight forward. If it does not, please post the actual output from the script including the command you ran.
  • jinchekerjincheker Contributor Current User Role Beginner
    Is there anything in /var/lib/mysql/data currently? If so (and assuming you never put any data in there you are trying to save), remove whatever is in that folder. Then as root (or with sudo) try to run your command again:
    sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql/data
    
    That should work, as it's pretty straight forward. If it does not, please post the actual output from the script including the command you ran.

    Hi, scott, thanks for your help.

    I can run this command successfully now, after cleaned all the stuff in data folder.

    Then I runed this to set up new root password as it suggested.
    sudo /usr/bin/mysql_secure_installation
    

    But I can not start the server stil. :( it's still failed. There is no error in the log file. Only two warning:
    	130508 17:51:35 [Warning] 'user' entry 'root@find-rent' ignored in --skip-name-resolve mode.
    
    
    	130508 17:51:35 [Warning] 'proxies_priv' entry '@ root@find-rent' ignored in --skip-name-resolve mode.
    
  • jinchekerjincheker Contributor Current User Role Beginner
    I'm confused. When I do ps grep, I do find mysql is running:
    	root     28692  0.0  0.0   4400   680 pts/0    S    17:51   0:00 /bin/sh /usr/bin/mysqld_safe
    
    
    	mysql    29231  0.1  3.8 993928 78416 pts/0    Sl   17:51   0:01  \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql/data/ --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/data/mysql-error.log --open-files-limit=65535 --pid-file=/var/lib/mysql/data/mysql.pid --socket=/var/lib/mysql/data/mysql.sock
    

    I don't know how. Because, when I do
    	/etc/init.d/mysql stop
    
    or
    	service mysql stop
    

    It will never stop. Although it do show this:
    	* Stopping MySQL (Percona Server) mysqld      [ OK ]
    

    I think that's why when I do restart it will fail. .., so the question is how can I stop it, and restart it correctly..?
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    The log bits you posted are just warnings, so those should not affect this.

    Are you attempting to shutdown the server using root/sudo? If not, try again with "sudo /etc/init.d/mysql stop". If that does not work, try "sudo mysqladmin shutdown". If neither of those work, kill the process (kill mysqld_safe one first and then mysqld), and then attempt to start / stop it again. If none of that works, post up the exact commands you ran and your error log (/var/lib/mysql/data/mysql-error.log)
  • jinchekerjincheker Contributor Current User Role Beginner
    The log bits you posted are just warnings, so those should not affect this. Are you attempting to shutdown the server using root/sudo? If not, try again with "sudo /etc/init.d/mysql stop". If that does not work, try "sudo mysqladmin shutdown". If neither of those work, kill the process (kill mysqld_safe one first and then mysqld), and then attempt to start / stop it again. If none of that works, post up the exact commands you ran and your error log (/var/lib/mysql/data/mysql-error.log)

    Thank for your help scott

    I) I tried "sudo /etc/init.d/mysql stop", it's not working. ( although it shows * Stopping MySQL (Percona Server) mysqld [OK] )

    2) "sudo mysqladmin shutdown", it gives this error:
    	mysqladmin: connect to server at 'localhost' failed
    
    
    	error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)'
    
    
    	Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!
    

    3) Then I manually kill these 2 processes. And then run "sudo /etc/init.d/mysql start", it shows " * Starting MySQL (Percona Server) database server mysqld [fail]"
    But it did start the server! Below is the copy from error.log, I didnt see any error. Really don't know why it says fail, but actually start it.
    	
    		130508 18:54:11  InnoDB: Waiting for the background threads to start
    
    	
    		130508 18:54:12 Percona XtraDB (http://www.percona.com) 5.5.30-rel30.2 started; log sequence number 1597945
    
    	
    		130508 18:54:12 [Note] Recovering after a crash using /var/lib/mysql/data/mysql-bin
    
    	
    		130508 18:54:12 [Note] Starting crash recovery...
    
    	
    		130508 18:54:12 [Note] Crash recovery finished.
    
    	
    		130508 18:54:12 [Warning] 'user' entry 'root@find-rent' ignored in --skip-name-resolve mode.
    
    	
    		130508 18:54:12 [Warning] 'proxies_priv' entry '@ root@find-rent' ignored in --skip-name-resolve mode.
    
    	
    		130508 18:54:12 [Note] Event Scheduler: Loaded 0 events
    
    	
    		130508 18:54:12 [Note] /usr/sbin/mysqld: ready for connections.
    
    	
    		Version: '5.5.30-30.2-log'  socket: '/var/lib/mysql/data/mysql.sock'  port: 3306  Percona Server (GPL), Release 30.2
    
    
    
    
    
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Try commenting out the "skip_name_resolve" line in your my.cnf and then kill the processes again and start MySQL back up. I just noticed that it is complaining about your root user, so that actually could be the issue.
  • jinchekerjincheker Contributor Current User Role Beginner
    I just notice one thing. Before the debian-start and debian.cnf are all empty. But now they have data, I dont know if that has something to do with it




    debian-start

    	#!/bin/bash
    
    
    	#
    
    
    	# This script is executed by "/etc/init.d/mysql" on every (re)start.
    
    
    	# 
    
    
    	# Changes to this file will be preserved when updating the Debian package.
    
    
    	#
    
    
    	 
    
    
    	PERCONA_PREFIX=/usr
    
    
    	source "${PERCONA_PREFIX}"/share/mysql/debian-start.inc.sh
    
    
    	 
    
    
    	MYSQL="${PERCONA_PREFIX}/bin/mysql --defaults-file=/etc/mysql/debian.cnf"
    
    
    	MYADMIN="${PERCONA_PREFIX}/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
    
    
    	MYUPGRADE="${PERCONA_PREFIX}/bin/mysql_upgrade --defaults-extra-file=/etc/mysql/debian.cnf"
    
    
    	MYCHECK="${PERCONA_PREFIX}/bin/mysqlcheck --defaults-file=/etc/mysql/debian.cnf"
    
    
    	MYCHECK_SUBJECT="WARNING: mysqlcheck has found corrupt tables"
    
    
    	MYCHECK_PARAMS="--all-databases --fast --silent"
    
    
    	MYCHECK_RCPT="root"
    
    
    	 
    
    
    	# The following commands should be run when the server is up but in background
    
    
    	# where they do not block the server start and in one shell instance so that
    
    
    	# they run sequentially. They are supposed not to echo anything to stdout.
    
    
    	# If you want to disable the check for crashed tables comment
    
    
    	# "check_for_crashed_tables" out.  
    
    
    	# (There may be no output to stdout inside the background process!)
    
    
    	echo "Checking for corrupt, not cleanly closed and upgrade needing tables."
    
    
    	(
    
    
    	  upgrade_system_tables_if_necessary;
    
    
    	  check_root_accounts;
    
    
    	  check_for_crashed_tables;
    
    
    	) >&2 &
    
    
    	 
    
    
    	exit 0
    
    
    








    debian.cnf:

    	# Automatically generated for Debian scripts. DO NOT TOUCH!
    
    
    	[client]
    
    
    	host     = localhost
    
    
    	user     = debian-sys-maint
    
    
    	password = Jfa78OfLJwc8ADvE
    
    
    	socket   = /var/run/mysqld/mysqld.sock
    
    
    	[mysql_upgrade]
    
    
    	host     = localhost
    
    
    	user     = debian-sys-maint
    
    
    	password = Jfa78OfLJwc8ADvE
    
    
    	socket   = /var/run/mysqld/mysqld.sock
    
    
    	basedir  = /usr
    
  • jinchekerjincheker Contributor Current User Role Beginner
    Try commenting out the "skip_name_resolve" line in your my.cnf and then kill the processes again and start MySQL back up. I just noticed that it is complaining about your root user, so that actually could be the issue.

    I commented out this line, and now there is no warning now. But still unable to stop and start mysql :(
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Could just be an issue with authentication due to the "fun" way Debian uses MySQL. Debian uses the user "debian-sys-maint" to control the MySQL instance. When you moved the data directory and re-created the MySQL system tables, that user likely was not created and/or has a different password. So the easiest way to try and fix it would be to copy all of the files / directories from the original MySQL data directory into the new data directory. That will give you the original users that Debian setup to control MySQL, and will likely fix the issue.

    So if you still have the original data directory contents, I'd do the following:

    1) Kill the current MySQL procesesses again (mysqld_safe then mysqld)
    2) Remove the contents of the new data directory (all sub directories as well)
    3) Copy the contents of the original data directory to the new data directory (make sure to use the recursive flag, i.e. cp -r)
    4) Make sure the ownership of the new data dir and contents is still correct
    5) Remove the ib_logfiles (likely ib_logfile0 and ib_logfile1) from the new data directory
    6) If there is a socket file (*.sock) in the new data directory, remove it
    7) Start up MySQL again

    If you do not have the original data directory contents, we can re-create the user, but that is more involved, so easier to avoid that if possible since this is a fresh install.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.