Enabling bin-logging with mysql recipe

Chef Infra (archive)
1

Hey everyone,

I’ve been banging my head against the wall all morning with this one
:slight_smile: I’m trying to enable bin-logging in mysql, to eventually set up
replication.

I’m using the most recent version of the mysql cookbook (I just git pulled
the repo), on CentOS 6.3.

I’m setting server-id to 1 and log_bin to “vagrant-bin”, as you can see in
the following node.json:

{ “mysql”: {
“server_root_password”: “”,
“server_repl_password”: “”,
“server_debian_password”: “”,
“tunable”: {
“innodb_buffer_pool_size”: “256M”,
“log_queries_not_using_index”: true,
“max_allowed_packet”: “16M”,
“max_heap_table_size”: “32M”,
“query_cache_size”: 0,
“max_connections”: 500,
“thread_cache_size”: 50,
“innodb_flush_method”: “O_DIRECT”,
“innodb_log_file_size”: “64M”,
“log_error”: “/var/log/mysql-error.log”,
“log_queries_not_using_index”: true,
“log_slow_queries”: “/var/log/mysql-slow.log”,
“old_passwords”: 0,
“log_bin”: “vagrant-bin”,

  •  "server_id": "1"*
    }
    },
    “run_list”:[ “mysql::server” ]
    }

As you can guess, I’m just kicking the tires in Vagrant first :slight_smile:

After running chef-solo, my /etc/my.cnf seems ok, here’s the relevant part,
from the [mysqld] section:
…
server-id = 1
binlog_format = statement
log_bin = vagrant-bin
…

However, after the chef run (or even after a manual service mysqld restart),
bin logging and the server id don’t get picked up at all. From the mysql
console:
SHOW VARIABLES LIKE ‘log_bin’ \G SHOW VARIABLES LIKE ‘server_id’ \G
*************************** 1. row ***************************
Variable_name: log_bin
Value: OFF
1 row in set (0.01 sec)

*************************** 1. row ***************************
Variable_name: server_id
Value: 0
1 row in set (0.00 sec)

So my questions are: has anybody successfully enabled bin-logging with the
mysql cookbook? If so, which version of the cookbook? Any gotchas I should
be aware of? Any related config I should be considering?

Note: upon further inspection, none of the configs I’m setting seems to get
picked up at all. They all appear correctly in /etc/my.cnf, but mysqld
doesn’t seem to take them into account…

Any help is greatly appreciated!

Mathieu

2

A quick question I'd have is:

Has mysqld been restarted post-config file change?

-M

On Thu, Dec 13, 2012 at 11:31 AM, Mathieu Martin webmat@gmail.com wrote:

Hey everyone,

I've been banging my head against the wall all morning with this one :slight_smile: I'm
trying to enable bin-logging in mysql, to eventually set up replication.

I'm using the most recent version of the mysql cookbook (I just git pulled
the repo), on CentOS 6.3.

I'm setting server-id to 1 and log_bin to "vagrant-bin", as you can see in
the following node.json:

{ "mysql": {
"server_root_password": "",
"server_repl_password": "",
"server_debian_password": "",
"tunable": {
"innodb_buffer_pool_size": "256M",
"log_queries_not_using_index": true,
"max_allowed_packet": "16M",
"max_heap_table_size": "32M",
"query_cache_size": 0,
"max_connections": 500,
"thread_cache_size": 50,
"innodb_flush_method": "O_DIRECT",
"innodb_log_file_size": "64M",
"log_error": "/var/log/mysql-error.log",
"log_queries_not_using_index": true,
"log_slow_queries": "/var/log/mysql-slow.log",
"old_passwords": 0,
"log_bin": "vagrant-bin",
"server_id": "1"
}
},
"run_list":[ "mysql::server" ]
}

As you can guess, I'm just kicking the tires in Vagrant first :slight_smile:

After running chef-solo, my /etc/my.cnf seems ok, here's the relevant part,
from the [mysqld] section:
...
server-id = 1
binlog_format = statement
log_bin = vagrant-bin
...

However, after the chef run (or even after a manual service mysqld restart),
bin logging and the server id don't get picked up at all. From the mysql
console:
SHOW VARIABLES LIKE 'log_bin' \G SHOW VARIABLES LIKE 'server_id' \G
*************************** 1. row ***************************
Variable_name: log_bin
Value: OFF
1 row in set (0.01 sec)

*************************** 1. row ***************************
Variable_name: server_id
Value: 0
1 row in set (0.00 sec)

So my questions are: has anybody successfully enabled bin-logging with the
mysql cookbook? If so, which version of the cookbook? Any gotchas I should
be aware of? Any related config I should be considering?

Note: upon further inspection, none of the configs I'm setting seems to get
picked up at all. They all appear correctly in /etc/my.cnf, but mysqld
doesn't seem to take them into account...

Any help is greatly appreciated!

Mathieu

3

Hey Mike,

Yes, chef restarted it properly. I’ve also tried restarting manually it as
well…

Based on further investigation, the problem may have been caused by my
setting innodb_log_file_size.

Changing this after a mysql database is initialized has a tendency to cause:

121213 12:31:16 [ERROR] Plugin ‘InnoDB’ init function returned error.
121213 12:31:16 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE
failed.
121213 12:31:16 [ERROR] Unknown/unsupported table type: InnoDB
121213 12:31:16 [ERROR] Aborting

I’ll try to investigate whether there’s a problem in the mysql cookbook
with this setting when I get a moment, or if I was doing it wrong.

Anyhow, I’ve solved my problem for now.

Thanks,

Mat

4

proper procedure to change the innodb_log_file_size is to shut the
database down, change the size, remove the old log files
(ib_logfile0/ib_logfile1), and then restart the database. if the log
file sizes don't match it won't start back up.

it is a good idea to do this as the default is tiny and you'll reduce
both your write i/o and cpu utilization. as you increase this number
startup times will increase, so you probably don't need to push this
beyond 256M and you should monitor and actively tune it if you think you
need to go higher.

On 12/13/12 10:45 AM, Mathieu Martin wrote:

Hey Mike,

Yes, chef restarted it properly. I've also tried restarting manually
it as well...

Based on further investigation, the problem may have been caused by my
setting innodb_log_file_size.

Changing this after a mysql database is initialized has a tendency to
cause:

121213 12:31:16 [ERROR] Plugin 'InnoDB' init function returned error.
121213 12:31:16 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE
failed.
121213 12:31:16 [ERROR] Unknown/unsupported table type: InnoDB
121213 12:31:16 [ERROR] Aborting

I'll try to investigate whether there's a problem in the mysql
cookbook with this setting when I get a moment, or if I was doing it
wrong.

Anyhow, I've solved my problem for now.

Thanks,

Mat

5

On Thu, Dec 13, 2012 at 2:20 PM, Lamont Granquist lamont@opscode.comwrote:

proper procedure to change the innodb_log_file_size is to shut the
database down, change the size, remove the old log files
(ib_logfile0/ib_logfile1), and then restart the database. if the log file
sizes don't match it won't start back up.

it is a good idea to do this as the default is tiny and you'll reduce both
your write i/o and cpu utilization. as you increase this number startup
times will increase, so you probably don't need to push this beyond 256M
and you should monitor and actively tune it if you think you need to go
higher.

Thanks for the tips, Lamont!

It's a pretty scary thing to do to a live db, though.

I'm still pretty new to all of this. In the past few years I've had it
easy, as I was on Heroku :wink:

Mat