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

MySQL & MariaDB Other MySQL® Questions
1

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_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

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

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.

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_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

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/

4

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

5

Hi,

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