Announcement

Announcement Module
Collapse
No announcement yet.

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

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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_S UBSTITUTION,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







  • #2
    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,

    Comment


    • #3
      Originally posted by miguelangelnieto View Post
      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/

      Comment


      • #4
        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

        Comment


        • #5
          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...

          Comment


          • #6
            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:

            Code:
            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.

            Comment


            • #7
              Originally posted by scott.nemes View Post
              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:
              Code:
              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.

              Code:
              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:

              Code:
              	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.




              Comment


              • #8
                I'm confused. When I do ps grep, I do find mysql is running:

                Code:
                	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
                Code:
                	/etc/init.d/mysql stop
                or
                Code:
                	service mysql stop
                It will never stop. Although it do show this:
                Code:
                	* 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..?

                Comment


                • #9
                  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).

                  Comment


                  • #10
                    Originally posted by scott.nemes View Post
                    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:
                    Code:
                    	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.

                    Code:
                    	
                    		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

                    Comment


                    • #11
                      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.

                      Comment


                      • #12
                        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


                        Code:
                        
                        	#!/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:


                        Code:
                        
                        	# 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

                        Comment


                        • #13
                          Originally posted by scott.nemes View Post
                          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

                          Comment


                          • #14
                            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.
                            Last edited by scott.nemes; 05-09-2013, 10:32 AM.

                            Comment

                            Working...
                            X