Announcement

Announcement Module
Collapse
No announcement yet.

mysql: unknown variable 'datadir=/var/lib/mysql'

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

  • mysql: unknown variable 'datadir=/var/lib/mysql'

    Hello,

    I get a trouble with the configuration file

    After I generate my.cnf from tools. percona[.] com


    # Generated by Percona Configuration Wizard version REL5-20120208# Configuration name server8.xipat.com generated for dungdt@xipat.com at 2012-11-20 04:55:11[mysql]# CLIENT #port = 3306socket = /var/lib/mysql/data/mysql.sock[mysqld]# GENERAL #user = mysqldefault_storage_engine = InnoDBsocket = /var/lib/mysql/data/mysql.sockpid_file = /var/lib/mysql/data/mysql.pid# MyISAM #key_buffer_size = 32Mmyisam_recover = FORCE,BACKUP# SAFETY #max_allowed_packet = 16Mmax_connect_errors = 1000000skip_name_resolvesql_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_BYsysdate_is_now = 1innodb = FORCEinnodb_strict_mode = 1# DATA STORAGE #datadir = /var/lib/mysql/data/# BINARY LOGGING #log_bin = /var/lib/mysql/data/mysql-binexpire_logs_days = 14sync_binlog = 1# CACHES AND LIMITS #tmp_table_size = 32Mmax_heap_table_size = 32Mquery_cache_type = 0query_cache_size = 0max_connections = 500thread_cache_size = 50open_files_limit = 65535table_definition_cache = 4096table_open_cache = 1# INNODB #innodb_flush_method = O_DIRECTinnodb_log_files_in_group = 2innodb_log_file_size = 128Minnodb_flush_log_at_trx_commit = 1innodb_file_per_table = 1innodb_buffer_pool_size = 2G# LOGGING #log_error = /var/lib/mysql/data/mysql-error.loglog_queries_not_using_indexes = 1slow_query_log = 1slow_query_log_file = /var/lib/mysql/data/mysql-slow.log


    Then I run command: service mysql stop

    I created my.cnf then copy to /etc/my.cnf But after that I couldn't start Mysql anymore, it said could not find the pid file.

    I had to remove [mysqld] and some variables. To get mysql back, here is my my.cnf


    [mysql]## Generalport = 3306datadir = /var/lib/mysqltmpdir = /var/lib/mysqltmpsocket = /var/lib/mysql/mysql.sockskip-name-resolve#event-scheduler = 1## Cachethread-cache-size = 50open_files_limit = 65535table-open-cache = 10240table-definition-cache = 4096query-cache-size = 0query_cache_type = 0## Per-thread Bufferssort-buffer-size = 1Mread-buffer-size = 1Mread-rnd-buffer-size = 1Mjoin-buffer-size = 1Mopen_files_limit = 65535## Temp Tablestmp-table-size = 32Mmax-heap-table-size = 32M## Networkingback-log = 100max-connections = 500max-connect-errors = 10000max-allowed-packet = 16Minteractive-timeout = 3600wait-timeout = 600### Storage Enginesdefault-storage-engine = InnoDB## MyISAMkey-buffer-size = 32Mmyisam-sort-buffer-size = 128Mmyisam_recover = FORCE,BACKUP# SAFETY #max_allowed_packet = 16Mmax_connect_errors = 1000000skip_name_resolvesql_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_BYsysdate_is_now = 1innodb = FORCEinnodb_strict_mode = 1# BINARY LOGGING #log_bin = /var/lib/mysql/data/mysql-binexpire_logs_days = 7sync_binlog = 1## InnoDBinnodb-buffer-pool-size = 2Ginnodb-log-file-size = 128Minnodb-log-buffer-size = 4Minnodb-file-per-table = 1innodb_log_files_in_group = 2innodb_flush_log_at_trx_commit = 1innodb_thread_concurrency=8innodb_flush_method=O_ DIRECT# LOGGING #log_error = /var/lib/mysql/data/mysql-error.loglog_queries_not_using_indexes = 1slow_query_log = 1slow_query_log_file = /var/lib/mysql/data/mysql-slow.log


    The server could start again. But I get another trouble, when I run command: mysql, it returned an error:

    mysql: unknown variable 'datadir=/var/lib/mysql'


    Could you kindly advise how to solve this issue please. Thank you very much.

  • #2
    Hi,

    AFAIK, In my.cnf file, [mysql] is for MySQL client and [mysqld] is for MySQL server. So I would suggest you should add all server related parameters under [mysqld] in my.cnf and try to start mysql. If you are getting any error please update here. Thanks.

    Comment


    • #3
      Hello,

      Thank you for your responding, Once I update my.cnf as suggestion from tools . percona with content below


      [mysql]# CLIENT #port = 3306socket = /var/lib/mysql/data/mysql.sock[mysqld]# GENERAL #user = mysqldefault_storage_engine = InnoDBsocket = /var/lib/mysql/data/mysql.sockpid_file = /var/lib/mysql/data/mysql.pid# MyISAM #key_buffer_size = 32Mmyisam_recover = FORCE,BACKUP# SAFETY #max_allowed_packet = 16Mmax_connect_errors = 1000000skip_name_resolvesql_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_BYsysdate_is_now = 1innodb = FORCEinnodb_strict_mode = 1# DATA STORAGE #datadir = /var/lib/mysql/data/# BINARY LOGGING #log_bin = /var/lib/mysql/data/mysql-binexpire_logs_days = 14sync_binlog = 1# CACHES AND LIMITS #tmp_table_size = 32Mmax_heap_table_size = 32Mquery_cache_type = 0query_cache_size = 0max_connections = 500thread_cache_size = 50open_files_limit = 65535table_definition_cache = 4096table_open_cache = 1# INNODB #innodb_flush_method = O_DIRECTinnodb_log_files_in_group = 2innodb_log_file_size = 128Minnodb_flush_log_at_trx_commit = 1innodb_file_per_table = 1innodb_buffer_pool_size = 2G# LOGGING #log_error = /var/lib/mysql/data/mysql-error.loglog_queries_not_using_indexes = 1slow_query_log = 1slow_query_log_file = /var/lib/mysql/data/mysql-slow.log


      I get the this error


      Starting MySQL (Percona Server)... ERROR! The server quit without updating PID file (/var/lib/mysql/data//[MY_SERVER_NAME].pid).


      Could you advise please?

      Thank you very much/

      Comment


      • #4
        Hi,

        Can you please provide this error log OR last few lines ? (/var/lib/mysql/data/mysql-error.log)

        Regards,

        Comment


        • #5
          Hi,

          This is the content of the mysql-error.log


          130306 14:23:49 InnoDB: Initializing buffer pool, size = 2.0G130306 14:23:49 InnoDB: Completed initialization of buffer pool130306 14:23:49 InnoDB: highest supported file format is Barracuda.130306 14:23:49 InnoDB: Waiting for the background threads to start130306 14:23:50 Percona XtraDB (http [://] www percona com) 1.1.8-rel30.0 started; log sequence number 1597945130306 14:23:50 [Note] Recovering after a crash using /var/lib/mysql/data/mysql-bin130306 14:23:50 [Note] Starting crash recovery...130306 14:23:50 [Note] Crash recovery finished.130306 14:23:50 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist130306 14:23:50 mysqld_safe mysqld from pid file /var/lib/mysql/data//[MY_SERVER_NAME].pid ended

          Comment


          • #6
            Hi,

            Have you upgrade Percona Server? If yes then its result of a bad MySQL upgrade, most likely. If not then some how grants tables are corrupted so you can start MySQL by avoiding the grants and keeping your my.cnf in place.

            # /etc/init.d/mysql start --skip-grant

            and you can fix the missing tables by running mysql_install_db script.

            Please try it and let me know if you face any issue. Thanks.

            Comment


            • #7
              Hello,

              Thank you for your responding. I installed Percona by Yum, it's new clean install. I tried again with the command you suggested


              # /etc/init.d/mysql start --skip-grant


              It's successful. Then I try


              [root@server8 ~]# mysql_install_db


              It shows


              [root@server8 ~]# mysql_install_dbInstalling MySQL system tables...OKFilling help tables...OKTo start mysqld at boot time you have to copysupport-files/mysql.server to the right place for your systemPLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:/usr/bin/mysqladmin -u root password 'new-password'/usr/bin/mysqladmin -u root -h server8.xipat.com password 'new-password'Alternatively you can run:/usr/bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly recommended for production servers.See the manual for more instructions.You can start the MySQL daemon with:cd /usr ; /usr/bin/mysqld_safe &You can test the MySQL daemon with mysql-test-run.plcd /usr/mysql-test ; perl mysql-test-run.plPlease report any problems with the /usr/bin/mysqlbug script!Percona recommends that all production deployments be protected with a supportcontract (http [://] www.percona.com/mysql-suppport/) to ensure the highest uptime,be eligible for hot fixes, and boost your team's productivity.


              But then, my site is down, because it couldn't connect to Mysql.

              I tried


              /usr/bin/mysqladmin -u root password 'new-password'


              But it shows error


              /usr/bin/mysqladmin: connect to server at 'localhost' failederror: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!


              I also tried

              [root@server8 ~]# /usr/bin/mysql_secure_installation


              It require my current root password, I entered, but It not accept with error


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


              So I have to copy the old my.cnf again to /etc/my.cnf then restart mysql. it's back to work again.

              Could you advise please?

              Thank you very much.

              Regards,

              Comment


              • #8
                Hi,

                Can you check if server is going to be started with service mysql start OR /etc/init.d/mysqld start by running
                "ps -ef | grep mysql" ? If server is not started then can you paste the error log here?

                Comment


                • #9
                  Hello,

                  This is the result after I ran command ps -ef | grep mysql with my old my.cnf


                  [root@server8 ~]# ps -ef | grep mysqlroot 2780 1 0 15:50 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/server8.xipat.com.pidmysql 2871 2780 4 15:50 pts/1 00:01:21 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=my sql --log-error=/var/lib/mysql/server8.xipat.com.err --pid-file=/var/lib/mysql/server8.xipat.com.pidroot 10725 30310 0 16:24 pts/1 00:00:00 grep mysql


                  This is the result after I copy new my.cnf from percona tools


                  [root@server8 ~]# ps -ef | grep mysqlroot 11218 1 0 16:26 pts/1 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql/data/ --pid-file=/var/lib/mysql/data//server8.xipat.com.pidmysql 11750 11218 1 16:26 pts/1 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql/data/ --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/data/mysql-error.log --open-files-limit=65535 --pid-file=/var/lib/mysql/data//server8.xipat.com.pid --socket=/var/lib/mysql/data/mysql.sockroot 11826 30310 0 16:27 pts/1 00:00:00 grep mysql


                  This is the log file


                  [root@server8 data]# tail mysql-error.log130306 16:26:55 [Note] /usr/sbin/mysqld: ready for connections.Version: '5.5.29-30.0-log' socket: '/var/lib/mysql/data/mysql.sock' port: 3306 Percona Server (GPL), Release rel30.0, Revision 451130306 16:28:12 [Note] /usr/sbin/mysqld: Normal shutdown130306 16:28:12 [Note] Event Scheduler: Purging the queue. 0 events130306 16:28:12 InnoDB: Starting shutdown...130306 16:28:14 InnoDB: Shutdown completed; log sequence number 1597945130306 16:28:14 [Note] /usr/sbin/mysqld: Shutdown complete


                  Could you kindly advise. Thank you very much.

                  Regards,

                  Comment


                  • #10
                    Hi,

                    If you can notice, looks MySQL is not using your my.cnf while starting. Because if you'll start with your my.cnf it shows error log, --log-error=/var/lib/mysql/server8.xipat.com.err

                    But with new, it shows, --log-error=/var/lib/mysql/data/mysql-error.log

                    Even socket file path is not included, while mysql starting with your my.cnf but when you are starting with new,
                    It shows --socket=/var/lib/mysql/data/mysql.sock

                    So, I would suggest you should look into "/var/lib/mysql/server8.xipat.com.err" error log and find out what can be the issue.

                    Comment


                    • #11
                      Hello,

                      Thank you for your prompt response. This is the /var/lib/mysql/server8.xipat.com.err with old my.cnf


                      [root@server8 mysql]# tail server8.xipat.com.err130306 16:28:27 InnoDB: Compressed tables use zlib 1.2.3130306 16:28:27 InnoDB: Using Linux native AIO130306 16:28:27 InnoDB: Initializing buffer pool, size = 128.0M130306 16:28:27 InnoDB: Completed initialization of buffer pool130306 16:28:27 InnoDB: highest supported file format is Barracuda.130306 16:28:28 InnoDB: Waiting for the background threads to start130306 16:28:29 Percona XtraDB (http [://] www percona com) 1.1.8-rel30.0 started; log sequence number 118869167702130306 16:28:29 [Note] Event Scheduler: Loaded 0 events130306 16:28:29 [Note] /usr/sbin/mysqld: ready for connections.Version: '5.5.29-30.0' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release rel30.0, Revision 451


                      And after I copied new my.cnf


                      [root@server8 ~]# tail /var/lib/mysql/server8.xipat.com.err130306 17:22:20 [Warning] /usr/sbin/mysqld: Forcing close of thread 2527 user: 'jira'130306 17:22:20 [Warning] /usr/sbin/mysqld: Forcing close of thread 2526 user: 'jira'130306 17:22:20 InnoDB: Starting shutdown...130306 17:22:20 InnoDB: Waiting for 16 pages to be flushed130306 17:22:21 InnoDB: Shutdown completed; log sequence number 118892998178130306 17:22:21 [Note] /usr/sbin/mysqld: Shutdown complete130306 17:22:21 mysqld_safe mysqld from pid file /var/lib/mysql/server8.xipat.com.pid ended


                      And


                      [root@server8 data]# tail mysql-error.log130306 17:22:40 InnoDB: Completed initialization of buffer pool130306 17:22:40 InnoDB: highest supported file format is Barracuda.130306 17:22:40 InnoDB: Waiting for the background threads to start130306 17:22:41 Percona XtraDB (http[://]www.percona.com) 1.1.8-rel30.0 started; log sequence number 1597945130306 17:22:42 [Warning] 'user' entry 'root@server8.xipat.com' ignored in --skip-name-resolve mode.130306 17:22:42 [Warning] 'user' entry '@server8.xipat.com' ignored in --skip-name-resolve mode.130306 17:22:42 [Warning] 'proxies_priv' entry '@ root@server8.xipat.com' ignored in --skip-name-resolve mode.130306 17:22:42 [Note] Event Scheduler: Loaded 0 events130306 17:22:42 [Note] /usr/sbin/mysqld: ready for connections.Version: '5.5.29-30.0-log' socket: '/var/lib/mysql/data/mysql.sock' port: 3306 Percona Server (GPL), Release rel30.0, Revision 451


                      Could you kindly advise please.

                      Regards,

                      Comment


                      • #12
                        Thank you very much niljoshi, I figured out why. I think my server's data folder was /var/lib/mysql instead of /var/lib/mysql/data

                        After I changed the path, it's back to work perfectly! Thank you very much!

                        Comment


                        • #13
                          Originally posted by niljoshi View Post
                          Hi,

                          AFAIK, In my.cnf file, [mysql] is for MySQL client and [mysqld] is for MySQL server. So I would suggest you should add all server related parameters under [mysqld] in my.cnf and try to start mysql. If you are getting any error please update here. Thanks.
                          hey i have meet the same problem,and i install percona5.6.10 via compile(i download Percona-Server-5.6.10-alpha60.2.tar.gz),when i finished install, all is well,but after a short time when i use /etc/init.d/mysqld restart i get error like "The server quit without updating PID file ",and how can i fix it,below is /etc/my.cnf
                          [mysqld]

                          # Remove leading # and set to the amount of RAM for the most important data
                          # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
                          # innodb_buffer_pool_size = 128M

                          # Remove leading # to turn on a very important data integrity option: logging
                          # changes to the binary log between backups.
                          # log_bin

                          # These are commonly set, remove the # and set as required.
                          basedir = /usr/local/mysql
                          datadir = /var/mysql/data
                          port = 3306
                          server_id = 1
                          socket = /tmp/mysql.sock
                          sort_buffer_size = 1M
                          join_buffer_size = 1M
                          query_cache_size = 50M
                          query_cache_limit = 2M
                          query_cache_min_res_unit = 2k
                          thread_stack = 192K
                          tmp_table_size = 246M
                          max_heap_table_size = 50M
                          key_buffer_size = 256M
                          read_buffer_size = 1M
                          read_rnd_buffer_size = 10M
                          bulk_insert_buffer_size = 10M
                          slow_query_log = 1
                          log-error = /var/mysql/mysql_error.log
                          slow_query_log_file = /var/mysq/query_slow.log
                          long_query_time = 2
                          log-queries-not-using-indexes = ON

                          # Remove leading # to set options mainly useful for reporting servers.
                          # The server defaults are faster for transactions and fast SELECTs.
                          # Adjust sizes as needed, experiment to find the optimal values.
                          # join_buffer_size = 128M
                          # sort_buffer_size = 2M
                          # read_rnd_buffer_size = 2M

                          sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLE S

                          Comment


                          • #14
                            Hi,

                            Can you please show us last 20 lines of error log to identify the issue.

                            Comment

                            Working...
                            X