1
"""Helper for working with a MySQL database"""
9
# from string import upper
11
from charmhelpers.core.host import (
16
from charmhelpers.core.hookenv import (
26
from charmhelpers.fetch import (
29
filter_installed_packages,
31
from charmhelpers.contrib.peerstorage import (
35
from charmhelpers.contrib.network.ip import get_host_ip
40
apt_update(fatal=True)
41
apt_install(filter_installed_packages(['python-mysqldb']), fatal=True)
45
class MySQLHelper(object):
47
def __init__(self, rpasswdf_template, upasswdf_template, host='localhost',
48
migrate_passwd_to_peer_relation=True,
49
delete_ondisk_passwd_file=True):
51
# Password file path templates
52
self.root_passwd_file_template = rpasswdf_template
53
self.user_passwd_file_template = upasswdf_template
55
self.migrate_passwd_to_peer_relation = migrate_passwd_to_peer_relation
56
# If we migrate we have the option to delete local copy of root passwd
57
self.delete_ondisk_passwd_file = delete_ondisk_passwd_file
59
def connect(self, user='root', password=None):
60
log("Opening db connection for %s@%s" % (user, self.host), level=DEBUG)
61
self.connection = MySQLdb.connect(user=user, host=self.host,
64
def database_exists(self, db_name):
65
cursor = self.connection.cursor()
67
cursor.execute("SHOW DATABASES")
68
databases = [i[0] for i in cursor.fetchall()]
72
return db_name in databases
74
def create_database(self, db_name):
75
cursor = self.connection.cursor()
77
cursor.execute("CREATE DATABASE {} CHARACTER SET UTF8"
82
def grant_exists(self, db_name, db_user, remote_ip):
83
cursor = self.connection.cursor()
84
priv_string = "GRANT ALL PRIVILEGES ON `{}`.* " \
85
"TO '{}'@'{}'".format(db_name, db_user, remote_ip)
87
cursor.execute("SHOW GRANTS for '{}'@'{}'".format(db_user,
89
grants = [i[0] for i in cursor.fetchall()]
90
except MySQLdb.OperationalError:
95
# TODO: review for different grants
96
return priv_string in grants
98
def create_grant(self, db_name, db_user, remote_ip, password):
99
cursor = self.connection.cursor()
101
# TODO: review for different grants
102
cursor.execute("GRANT ALL PRIVILEGES ON {}.* TO '{}'@'{}' "
103
"IDENTIFIED BY '{}'".format(db_name,
110
def create_admin_grant(self, db_user, remote_ip, password):
111
cursor = self.connection.cursor()
113
cursor.execute("GRANT ALL PRIVILEGES ON *.* TO '{}'@'{}' "
114
"IDENTIFIED BY '{}'".format(db_user,
120
def cleanup_grant(self, db_user, remote_ip):
121
cursor = self.connection.cursor()
123
cursor.execute("DROP FROM mysql.user WHERE user='{}' "
124
"AND HOST='{}'".format(db_user,
129
def execute(self, sql):
130
"""Execute arbitary SQL against the database."""
131
cursor = self.connection.cursor()
137
def migrate_passwords_to_peer_relation(self, excludes=None):
138
"""Migrate any passwords storage on disk to cluster peer relation."""
139
dirname = os.path.dirname(self.root_passwd_file_template)
140
path = os.path.join(dirname, '*.passwd')
141
for f in glob.glob(path):
142
if excludes and f in excludes:
143
log("Excluding %s from peer migration" % (f), level=DEBUG)
146
key = os.path.basename(f)
147
with open(f, 'r') as passwd:
148
_value = passwd.read().strip()
151
peer_store(key, _value)
153
if self.delete_ondisk_passwd_file:
156
# NOTE cluster relation not yet ready - skip for now
159
def get_mysql_password_on_disk(self, username=None, password=None):
160
"""Retrieve, generate or store a mysql password for the provided
163
template = self.user_passwd_file_template
164
passwd_file = template.format(username)
166
passwd_file = self.root_passwd_file_template
169
if os.path.exists(passwd_file):
170
log("Using existing password file '%s'" % passwd_file, level=DEBUG)
171
with open(passwd_file, 'r') as passwd:
172
_password = passwd.read().strip()
174
log("Generating new password file '%s'" % passwd_file, level=DEBUG)
175
if not os.path.isdir(os.path.dirname(passwd_file)):
176
# NOTE: need to ensure this is not mysql root dir (which needs
177
# to be mysql readable)
178
mkdir(os.path.dirname(passwd_file), owner='root', group='root',
180
# Force permissions - for some reason the chmod in makedirs
182
os.chmod(os.path.dirname(passwd_file), 0o770)
184
_password = password or pwgen(length=32)
185
write_file(passwd_file, _password, owner='root', group='root',
190
def passwd_keys(self, username):
191
"""Generator to return keys used to store passwords in peer store.
193
NOTE: we support both legacy and new format to support mysql
194
charm prior to refactor. This is necessary to avoid LP 1451890.
197
if username == 'mysql':
198
log("Bad username '%s'" % (username), level=WARNING)
201
# IMPORTANT: *newer* format must be returned first
202
keys.append('mysql-%s.passwd' % (username))
203
keys.append('%s.passwd' % (username))
205
keys.append('mysql.passwd')
210
def get_mysql_password(self, username=None, password=None):
211
"""Retrieve, generate or store a mysql password for the provided
212
username using peer relation cluster."""
215
# First check peer relation.
217
for key in self.passwd_keys(username):
218
_password = peer_retrieve(key)
222
# If root password available don't update peer relation from local
223
if _password and not username:
224
excludes.append(self.root_passwd_file_template)
227
# cluster relation is not yet started; use on-disk
230
# If none available, generate new one
232
_password = self.get_mysql_password_on_disk(username, password)
234
# Put on wire if required
235
if self.migrate_passwd_to_peer_relation:
236
self.migrate_passwords_to_peer_relation(excludes=excludes)
240
def get_mysql_root_password(self, password=None):
241
"""Retrieve or generate mysql root password for service units."""
242
return self.get_mysql_password(username=None, password=password)
244
def normalize_address(self, hostname):
245
"""Ensure that address returned is an IP address (i.e. not fqdn)"""
246
if config_get('prefer-ipv6'):
247
# TODO: add support for ipv6 dns
250
if hostname != unit_get('private-address'):
251
return get_host_ip(hostname, fallback=hostname)
253
# Otherwise assume localhost
256
def get_allowed_units(self, database, username, relation_id=None):
257
"""Get list of units with access grants for database with username.
259
This is typically used to provide shared-db relations with a list of
260
which units have been granted access to the given database.
262
self.connect(password=self.get_mysql_root_password())
263
allowed_units = set()
264
for unit in related_units(relation_id):
265
settings = relation_get(rid=relation_id, unit=unit)
266
# First check for setting with prefix, then without
267
for attr in ["%s_hostname" % (database), 'hostname']:
268
hosts = settings.get(attr, None)
273
# hostname can be json-encoded list of hostnames
275
hosts = json.loads(hosts)
279
hosts = [settings['private-address']]
283
host = self.normalize_address(host)
284
if self.grant_exists(database, username, host):
285
log("Grant exists for host '%s' on db '%s'" %
286
(host, database), level=DEBUG)
287
if unit not in allowed_units:
288
allowed_units.add(unit)
290
log("Grant does NOT exist for host '%s' on db '%s'" %
291
(host, database), level=DEBUG)
293
log("No hosts found for grant check", level=INFO)
297
def configure_db(self, hostname, database, username, admin=False):
298
"""Configure access to database for username from hostname."""
299
self.connect(password=self.get_mysql_root_password())
300
if not self.database_exists(database):
301
self.create_database(database)
303
remote_ip = self.normalize_address(hostname)
304
password = self.get_mysql_password(username)
305
if not self.grant_exists(database, username, remote_ip):
307
self.create_grant(database, username, remote_ip, password)
309
self.create_admin_grant(username, remote_ip, password)
314
class PerconaClusterHelper(object):
316
# Going for the biggest page size to avoid wasted bytes.
317
# InnoDB page size is 16MB
319
DEFAULT_PAGE_SIZE = 16 * 1024 * 1024
320
DEFAULT_INNODB_BUFFER_FACTOR = 0.50
322
def human_to_bytes(self, human):
323
"""Convert human readable configuration options to bytes."""
324
num_re = re.compile('^[0-9]+$')
325
if num_re.match(human):
335
if modifier in factors:
336
return int(human[:-1]) * factors[modifier]
339
total_ram = self.human_to_bytes(self.get_mem_total())
340
if self.is_32bit_system() and total_ram > self.sys_mem_limit():
341
total_ram = self.sys_mem_limit()
342
factor = int(human[:-1]) * 0.01
343
pctram = total_ram * factor
344
return int(pctram - (pctram % self.DEFAULT_PAGE_SIZE))
346
raise ValueError("Can only convert K,M,G, or T")
348
def is_32bit_system(self):
349
"""Determine whether system is 32 or 64 bit."""
351
return sys.maxsize < 2 ** 32
352
except OverflowError:
355
def sys_mem_limit(self):
356
"""Determine the default memory limit for the current service unit."""
357
if platform.machine() in ['armv7l']:
358
_mem_limit = self.human_to_bytes('2700M') # experimentally determined
360
# Limit for x86 based 32bit systems
361
_mem_limit = self.human_to_bytes('4G')
365
def get_mem_total(self):
366
"""Calculate the total memory in the current service unit."""
367
with open('/proc/meminfo') as meminfo_file:
368
for line in meminfo_file:
369
key, mem = line.split(':', 2)
370
if key == 'MemTotal':
371
mtot, modifier = mem.strip().split(' ')
372
return '%s%s' % (mtot, modifier[0].upper())
374
def parse_config(self):
375
"""Parse charm configuration and calculate values for config files."""
376
config = config_get()
378
if 'max-connections' in config:
379
mysql_config['max_connections'] = config['max-connections']
381
if 'wait-timeout' in config:
382
mysql_config['wait_timeout'] = config['wait-timeout']
384
if 'innodb-flush-log-at-trx-commit' in config:
385
mysql_config['innodb_flush_log_at_trx_commit'] = config['innodb-flush-log-at-trx-commit']
387
# Set a sane default key_buffer size
388
mysql_config['key_buffer'] = self.human_to_bytes('32M')
389
total_memory = self.human_to_bytes(self.get_mem_total())
391
dataset_bytes = config.get('dataset-size', None)
392
innodb_buffer_pool_size = config.get('innodb-buffer-pool-size', None)
394
if innodb_buffer_pool_size:
395
innodb_buffer_pool_size = self.human_to_bytes(
396
innodb_buffer_pool_size)
398
log("Option 'dataset-size' has been deprecated, please use"
399
"innodb_buffer_pool_size option instead", level="WARN")
400
innodb_buffer_pool_size = self.human_to_bytes(
403
innodb_buffer_pool_size = int(
404
total_memory * self.DEFAULT_INNODB_BUFFER_FACTOR)
406
if innodb_buffer_pool_size > total_memory:
407
log("innodb_buffer_pool_size; {} is greater than system available memory:{}".format(
408
innodb_buffer_pool_size,
409
total_memory), level='WARN')
411
mysql_config['innodb_buffer_pool_size'] = innodb_buffer_pool_size
added
removed
hooks/charmhelpers/contrib/database/mysql.py
