Questions tagged [my.cnf]

my.cnf is the configuration file for MySQL server, client programs, and utilities.

Filter by
Sorted by
Tagged with
38
votes
3answers
44k views

How do you calculate mysql max_connections variable?

How do you calculate mysql max_connections ? What do you take into consideration ?
18
votes
2answers
31k views

MySql - changing innodb_file_per_table for a live db

I have a large MySql DB (150GB) and only now I've noticed that the innodb_file_per_table is set to off which causes the entire DB to be hosted on one single file (ibdata1). I want to activate ...
12
votes
2answers
69k views

How to configure global charset on MySQL

I tried to change the charset for our MySQL via my.cnf and it failed. I added the charset settings listed below: # The MySQL server [mysqld] port = 3306 socket = /var/lib/mysql/...
11
votes
1answer
90k views

MySQL: bind-address 0.0.0.0 in my.cnf does not work?

I'm new to mysql. I want to connect to mysql server using whatever ip I want. I read that all I have to do is to add to my.cnf file the line bind-address = 0.0.0.0. That's what I did. I restarted ...
10
votes
7answers
15k views

How can I make MySQL client read password from mylogin.cnf?

I'm trying to make the mysql client connect to a mysql server without requiring the password to be given interactively. Steps taken: 1) First create a mylogin.cnf file $ mysql_config_editor set --...
10
votes
2answers
15k views

MySQL 5.1 InnoDB Configuration / 24GB RAM - bi-xeon high load

i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql ...
9
votes
1answer
49k views

Enabling LOAD DATA LOCAL INFILE in mysql

LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian ...
9
votes
1answer
19k views

How do I make binlog-format=ROW the start up default in the /etc/my.cnf file?

I can run the query SET GLOBAL binlog_format = 'ROW' and it works for that session. How do I make this setting stick after stopping and restarting MySQL? NOTE: I am on version 5.5.19 of MySQL (...
7
votes
1answer
15k views

Reloading MySQL my.cnf changes

Do you have to restart MySQL to edit changes? Or can you edit them on the fly and restart MySQL or make the changes inside MySQL? This server runs 100s of website that are active and I don't really ...
6
votes
1answer
2k views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
6
votes
2answers
19k views

What happened to my-*.cnf in MySQL 5.6.8?

Up to MySQL 5.6.7, the official RPM was shipping with these files: /usr/share/mysql/my-innodb-heavy-4G.cnf /usr/share/mysql/my-medium.cnf /usr/share/mysql/my-huge.cnf /usr/share/mysql/my-large.cnf /...
5
votes
1answer
9k views

Mysql fulltext search my.cnf optimization

I have open a question on https://serverfault.com/questions/353888/mysql-full-text-search-cause-high-usage-cpu Some user recommended asking here. We built a news site. Every day we will input tens of ...
5
votes
1answer
585 views

Tools for cnf management, generation? [closed]

Does anyone know of any good tools for cnf configuration management? It's often the case I have a group of machines that has a very similar config except for a few differences such as server id or ...
5
votes
2answers
3k views

How to optimize the my.cnf of a MySQL server that mixes InnoDB and MyISAM tablespaces?

I have a database that, because of some issues, I needed to convert some tables from MyISAM to InnoDB. I basically did this: set sql_log_bin = 0; set sql_mode = 'STRICT_ALL_TABLES'; ALTER TABLE `...
5
votes
1answer
1k views

Installing and enabling both master and slave semisynchronous plugins on same server

I want to run a semisynchronous replication setup, and for simplicity I'd like to not have to INSTALL and UNINSTALL plugins when a slave is promoted to a master. So I'm wondering if I'll experience ...
4
votes
2answers
15k views

MySQL difference between wait_timeout and connect_timeout

I have a my.cnf file. In it I see wait_timeout as well as connect_timeout. What's the difference between these 2?
4
votes
1answer
5k views

How can I set a default session sql_mode for a given user?

I want to have a default session sql_mode that is different from the global sql_mode. I can set it manually, but I'd like to do it automatically. I thought I could add it to .my.cnf, but that doesn'...
4
votes
1answer
11k views

How to set slow query log in MariaDB my.cnf?

It should be easy, but when I put these lines: log_slow_queries = 1 long_query_time = 1 log-slow-queries = /var/log/mysql/slow_query.log log_queries_not_using_indexes in [mysqld], MariaDB fails to ...
4
votes
3answers
11k views

Confirm that my.cnf file has loaded OK

Does anyone know if there is a way to confirm if any my.cnf file is being read by MySQL? I only have the one my.cnf on the machine (and there are no my.ini files) which I have in /etc/my.cnf. When I ...
4
votes
2answers
2k views

Read mysql config (my.cnf) using mysql console

I have a mysql server that is not under my control (I don't have ssh on that machine). I only have access to one database using mysql client. How can I have access to config options that are declared ...
4
votes
2answers
8k views

Creating and editing my.cnf by MySQL Workbench?

I just installed MySQL Server and MySQL Workbench on a clean Snow Leopard MacBook. I hooked up Workbench with the server and was able to start the MySQL Server from within the Workbench. I enter the ...
4
votes
2answers
13k views

Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?

My Hardware Spec Technology: Ivy Bridge CPU: Intel Xeon E3 1245v2 Intel Smart Cache: 8MB Cores / Threads: 4 / 8 Frequency: 3.4GHz+ / 3.8GHz Turbo Boost RAM: 32 GB DDR3 Hard disk: 2x 2TB SATA3 Raid 1 ...
4
votes
1answer
18k views

MySQL my.cnf vs my-huge.cnf

I've recently installed MySQL server and client 5.5.28 through rpm packages on an openSUSE machine, and I was not able to locate my.cnf file. I look into all directories mysql --verbose --help | grep -...
3
votes
3answers
31k views

Where does my.cnf get stored in Linux?

I am new to linux, and i just installed MySQL-server-5.1.59-1.glibc23.i386.rpm in my linux machine. I wanted to know where my.cnf file gets stored..? Will my.cnf gets created with the installation ...
3
votes
1answer
16k views

What is wrong with table_cache hit rate?

In my.cnf I have: table_cache = 524288 open_files_limit = 65535 Both are at max allowed value for mysql config. Both are less than max open file limit: # cat /proc/sys/fs/file-max ...
3
votes
3answers
11k views

innodb_buffer_pool_size not changed

I use MySQL 5.5.23-1~dotdeb.0 (Debian) and here is the relevant part of my.cnf default-storage-engine = innodb innodb_buffer_pool_size = 3G innodb_log_file_size = 256M innodb_thread_concurrency = 8 ...
3
votes
1answer
3k views

MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1

I've set ft_min_word_len = 1 to my.cnf file, also SHOW VARIABLES LIKE 'ft_min_word%' displays ft_min_word_len as 1. Then I've reloaded all database tables and it still doesn't search for one character ...
3
votes
1answer
2k views

unknown variable 'log_slow_verbosity=query_plan'

What is the use of log_slow_verbosity = query_plan parameter in my.cnf ? I got an error when installing MySQL in my machine because of this parameter.
3
votes
2answers
7k views

MySQL 5.6 OS X 10.8: how tell if my.cnf read?

With mysqld running, I expect to see that /etc/my.cnf has been used by at least one of the commands ps aux | grep mysql | grep -v grep mysqladmin variables | grep defaults-file showing the value of ...
3
votes
2answers
324 views

optimal size of innodb buffer pool

I read a lot of optimal number of innodb_buffer_pool_size and people say it must be larger with 10% than the innodb tables size, what is 60M. Accordyngly this the optimal size of the buffer is about ...
3
votes
1answer
1k views

How to set datadir to different values based on the hostname in my.cnf?

How to set datadir to different values based on the hostname in my.cnf? I want to use the same my.cnf file on a Linux machine and a Mac OS X machine where the datadir should have different values.
3
votes
3answers
9k views

MySQL “server has gone away” using SQLyog

I'm trying to copy a database from a server to my local machine, I've installed Wamp 2.2, mysql 5.5.16, but during copying db I m getting "mysql server has gone" 2006, I've tried to fix it by changing ...
3
votes
4answers
12k views

Mariadb on Centos 7 not changing max connections based on my.cnf

The following configuration in my.cnf is not being followed. Max_connections go back to 151. please help as i have 16gb of dedicated ram that i would like to use properly. [mysqld] datadir=/var/...
3
votes
1answer
10k views

MySQL wait_timeout setting ignored

I have a MySQL 5.5 instance running on a RedHat distro. I have edited my.cnf with the following setting: wait_timeout=30 However, client connections are sleeping and never getting killed. The ...
3
votes
1answer
8k views

MySQL: Can't find .ibd files after turning on innodb_file_per_table

Question: This a question about MySQL. Let me give the specifics of my setup: Mac OSX 10.6.8 MySQL 5.5.28 Workbench 5.2.44 for all administration InnoDB engine exclusively In order to manage ...
3
votes
0answers
341 views

MySQL RDS Incompatible Restore while creating read replica

We have multiple m5 generation MySQL RDS instance. The problem we are facing is that while creating read replica for these instances we are repeatedly getting 'incompatible-restore' error. In the ...
3
votes
1answer
5k views

MariaDB is ignoring /etc/my.cnf

The variable I am trying to change is innodb_buffer_pool_size. As you can see below the settings is 512M. But SELECT @@innodb_buffer_pool_size; returns 134217728. When I execute: /usr/libexec/mysqld -...
3
votes
3answers
18k views

How to initiate MySQL on Mac OS X 10.6.8?

I followed this tutorial to install MySQL on Mac OS X 10.6.8. Now I get he following errors: $ mysqladmin version /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed error: 'Can'...
2
votes
2answers
29k views

My.cnf with 64GB RAM

May the following my.cnf file be fine for a dedicated server with 64GB of RAM (on Debian)? I'm running just one website on the server and on high load data insert in some table takes centuries...it ...
2
votes
1answer
7k views

16 Cores 12 GB RAM server MySql Configuration - my.cnf

The server takes 20+ seconds (wait time/slow IO response time) to response to a HTTP request even with memcached and APC installed. I believe this has something to do with MYSQL since the site as lots ...
2
votes
1answer
811 views

Passwordless mysqldump via shell script in /etc/cron.daily

I'm aware that there are dozens of questions similar to this, but it seems none has a definitive answer to my problem, so that's why I'm posting this... I hope in the right place. The problem: I ...
2
votes
2answers
636 views

Galera: any drawback of enabling wsrep_log_conflicts and cert.log_conflicts?

We have a HAProxy which balances reads and writes to only one (of three) Galera nodes. If this balancing works fine, the parameters wsrep_log_conflicts and cert.log_conflicts should never log any ...
2
votes
1answer
4k views

Can I reduce MySQL memory usage, or do I need to add more RAM?

I have a VPS with 2.25GB of RAM. MySQL is using 1.4GB with only four users on my site right now. It seems to use this amount even when no users are online. The available memory of my server regularly ...
2
votes
2answers
11k views

MySQL Server consuming too much memory

Currently I have the following happening : And : When i run the query i get about 68 entries with most Time set at 0 show full processlist; What I'm doing wrong ? How do i free some memory ? Is it ...
2
votes
1answer
8k views

Mysql Optimization suggessions

I have an InnoDB database table with more than 2 million rows. I am running efficienty indexed queries to reduce server load, but site becomes slower day by day. I ran Mysqltuner.pl to identify any ...
2
votes
2answers
932 views

Errors while creating multiple mysql-5.5 instances

I have installed 3rd mysql instance on my testing server. 2 instances already running without any issues. When I installed 3rd instance by mysql-5.5.30 zip source, it installed successfully but when ...
2
votes
1answer
1k views

“set-variable = innodb_thread_concurrency=4” or “innodb_thread_concurrency=4”

Based on this question, the asker wrote: set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = ...
2
votes
1answer
385 views

Override database option in a user-specific .my.cnf config file

Using MariaDB 10.2.15 (Linux Debian Stretch) I have created a ~/.my.cnf config file in my home directory in order to set a default database at connection time for all local client tools: [client] ...
2
votes
1answer
840 views

Upgrading to new cPanel Server with mySQL on SSD — What should I change in my.cnf?

I'm upgrading to a faster server with whm/cpanel and the mysql databases will be on a SSD. I've read a bit about not wanting any query cache and stuff like that, but as a novice, I've learned that ...
2
votes
1answer
653 views

What does this mean - ~/.my.cnf

When I run $ mysqld --verbose --help | less I got information like Default options are read from the following files in the given order: /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf As per my ...