Not the answer you need?
Register and ask your own question!

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

dungdtdungdt EntrantCurrent User Role Beginner
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 [email protected] 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_SUBSTITUTION,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</pre>


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_SUBSTITUTION,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</pre>


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.

Comments

  • niljoshiniljoshi MySQL Sage Inactive User Role Beginner
    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.
  • dungdtdungdt Entrant Current User Role Beginner
    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_SUBSTITUTION,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</pre>


    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).</pre>


    Could you advise please?

    Thank you very much/
  • hellogxphellogxp Entrant Current User Role Beginner
    niljoshi wrote: »
    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_TABLES
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    Hi,

    Can you please show us last 20 lines of error log to identify the issue.
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.