1
"""Helper for working with a MySQL database"""
10
from string import upper
12
from charmhelpers.core.host import (
17
from charmhelpers.core.hookenv import (
25
from charmhelpers.core.hookenv import config as config_get
26
from charmhelpers.fetch import (
29
filter_installed_packages,
31
from charmhelpers.contrib.peerstorage import (
39
apt_update(fatal=True)
40
apt_install(filter_installed_packages(['python-mysqldb']), fatal=True)
44
class MySQLHelper(object):
46
def __init__(self, rpasswdf_template, upasswdf_template, host='localhost'):
48
# Password file path templates
49
self.root_passwd_file_template = rpasswdf_template
50
self.user_passwd_file_template = upasswdf_template
52
def connect(self, user='root', password=None):
53
self.connection = MySQLdb.connect(user=user, host=self.host,
56
def database_exists(self, db_name):
57
cursor = self.connection.cursor()
59
cursor.execute("SHOW DATABASES")
60
databases = [i[0] for i in cursor.fetchall()]
64
return db_name in databases
66
def create_database(self, db_name):
67
cursor = self.connection.cursor()
69
cursor.execute("CREATE DATABASE {} CHARACTER SET UTF8"
74
def grant_exists(self, db_name, db_user, remote_ip):
75
cursor = self.connection.cursor()
76
priv_string = "GRANT ALL PRIVILEGES ON `{}`.* " \
77
"TO '{}'@'{}'".format(db_name, db_user, remote_ip)
79
cursor.execute("SHOW GRANTS for '{}'@'{}'".format(db_user,
81
grants = [i[0] for i in cursor.fetchall()]
82
except MySQLdb.OperationalError:
87
# TODO: review for different grants
88
return priv_string in grants
90
def create_grant(self, db_name, db_user, remote_ip, password):
91
cursor = self.connection.cursor()
93
# TODO: review for different grants
94
cursor.execute("GRANT ALL PRIVILEGES ON {}.* TO '{}'@'{}' "
95
"IDENTIFIED BY '{}'".format(db_name,
102
def create_admin_grant(self, db_user, remote_ip, password):
103
cursor = self.connection.cursor()
105
cursor.execute("GRANT ALL PRIVILEGES ON *.* TO '{}'@'{}' "
106
"IDENTIFIED BY '{}'".format(db_user,
112
def cleanup_grant(self, db_user, remote_ip):
113
cursor = self.connection.cursor()
115
cursor.execute("DROP FROM mysql.user WHERE user='{}' "
116
"AND HOST='{}'".format(db_user,
121
def execute(self, sql):
122
"""Execute arbitary SQL against the database."""
123
cursor = self.connection.cursor()
129
def migrate_passwords_to_peer_relation(self):
130
"""Migrate any passwords storage on disk to cluster peer relation."""
131
dirname = os.path.dirname(self.root_passwd_file_template)
132
path = os.path.join(dirname, '*.passwd')
133
for f in glob.glob(path):
134
_key = os.path.basename(f)
135
with open(f, 'r') as passwd:
136
_value = passwd.read().strip()
139
peer_store(_key, _value)
142
# NOTE cluster relation not yet ready - skip for now
145
def get_mysql_password_on_disk(self, username=None, password=None):
146
"""Retrieve, generate or store a mysql password for the provided
149
template = self.user_passwd_file_template
150
passwd_file = template.format(username)
152
passwd_file = self.root_passwd_file_template
155
if os.path.exists(passwd_file):
156
with open(passwd_file, 'r') as passwd:
157
_password = passwd.read().strip()
159
mkdir(os.path.dirname(passwd_file), owner='root', group='root',
161
# Force permissions - for some reason the chmod in makedirs fails
162
os.chmod(os.path.dirname(passwd_file), 0o770)
163
_password = password or pwgen(length=32)
164
write_file(passwd_file, _password, owner='root', group='root',
169
def get_mysql_password(self, username=None, password=None):
170
"""Retrieve, generate or store a mysql password for the provided
171
username using peer relation cluster."""
172
self.migrate_passwords_to_peer_relation()
174
_key = 'mysql-{}.passwd'.format(username)
176
_key = 'mysql.passwd'
179
_password = peer_retrieve(_key)
180
if _password is None:
181
_password = password or pwgen(length=32)
182
peer_store(_key, _password)
184
# cluster relation is not yet started; use on-disk
185
_password = self.get_mysql_password_on_disk(username, password)
189
def get_mysql_root_password(self, password=None):
190
"""Retrieve or generate mysql root password for service units."""
191
return self.get_mysql_password(username=None, password=password)
193
def get_allowed_units(self, database, username, relation_id=None):
194
"""Get list of units with access grants for database with username.
196
This is typically used to provide shared-db relations with a list of
197
which units have been granted access to the given database.
199
self.connect(password=self.get_mysql_root_password())
200
allowed_units = set()
201
for unit in related_units(relation_id):
202
settings = relation_get(rid=relation_id, unit=unit)
203
# First check for setting with prefix, then without
204
for attr in ["%s_hostname" % (database), 'hostname']:
205
hosts = settings.get(attr, None)
210
# hostname can be json-encoded list of hostnames
212
hosts = json.loads(hosts)
216
hosts = [settings['private-address']]
220
if self.grant_exists(database, username, host):
221
log("Grant exists for host '%s' on db '%s'" %
222
(host, database), level=DEBUG)
223
if unit not in allowed_units:
224
allowed_units.add(unit)
226
log("Grant does NOT exist for host '%s' on db '%s'" %
227
(host, database), level=DEBUG)
229
log("No hosts found for grant check", level=INFO)
233
def configure_db(self, hostname, database, username, admin=False):
234
"""Configure access to database for username from hostname."""
235
if config_get('prefer-ipv6'):
237
elif hostname != unit_get('private-address'):
239
remote_ip = socket.gethostbyname(hostname)
241
# socket.gethostbyname doesn't support ipv6
244
remote_ip = '127.0.0.1'
246
self.connect(password=self.get_mysql_root_password())
247
if not self.database_exists(database):
248
self.create_database(database)
250
password = self.get_mysql_password(username)
251
if not self.grant_exists(database, username, remote_ip):
253
self.create_grant(database, username, remote_ip, password)
255
self.create_admin_grant(username, remote_ip, password)
260
class PerconaClusterHelper(object):
262
# Going for the biggest page size to avoid wasted bytes. InnoDB page size is
264
DEFAULT_PAGE_SIZE = 16 * 1024 * 1024
266
def human_to_bytes(self, human):
267
"""Convert human readable configuration options to bytes."""
268
num_re = re.compile('^[0-9]+$')
269
if num_re.match(human):
279
if modifier in factors:
280
return int(human[:-1]) * factors[modifier]
283
total_ram = self.human_to_bytes(self.get_mem_total())
284
if self.is_32bit_system() and total_ram > self.sys_mem_limit():
285
total_ram = self.sys_mem_limit()
286
factor = int(human[:-1]) * 0.01
287
pctram = total_ram * factor
288
return int(pctram - (pctram % self.DEFAULT_PAGE_SIZE))
290
raise ValueError("Can only convert K,M,G, or T")
292
def is_32bit_system(self):
293
"""Determine whether system is 32 or 64 bit."""
295
return sys.maxsize < 2 ** 32
296
except OverflowError:
299
def sys_mem_limit(self):
300
"""Determine the default memory limit for the current service unit."""
301
if platform.machine() in ['armv7l']:
302
_mem_limit = self.human_to_bytes('2700M') # experimentally determined
304
# Limit for x86 based 32bit systems
305
_mem_limit = self.human_to_bytes('4G')
309
def get_mem_total(self):
310
"""Calculate the total memory in the current service unit."""
311
with open('/proc/meminfo') as meminfo_file:
312
for line in meminfo_file:
313
key, mem = line.split(':', 2)
314
if key == 'MemTotal':
315
mtot, modifier = mem.strip().split(' ')
316
return '%s%s' % (mtot, upper(modifier[0]))
318
def parse_config(self):
319
"""Parse charm configuration and calculate values for config files."""
320
config = config_get()
322
if 'max-connections' in config:
323
mysql_config['max_connections'] = config['max-connections']
325
# Total memory available for dataset
326
dataset_bytes = self.human_to_bytes(config['dataset-size'])
327
mysql_config['dataset_bytes'] = dataset_bytes
329
if 'query-cache-type' in config:
330
# Query Cache Configuration
331
mysql_config['query_cache_size'] = config['query-cache-size']
332
if (config['query-cache-size'] == -1 and
333
config['query-cache-type'] in ['ON', 'DEMAND']):
334
# Calculate the query cache size automatically
335
qcache_bytes = (dataset_bytes * 0.20)
336
qcache_bytes = int(qcache_bytes -
337
(qcache_bytes % self.DEFAULT_PAGE_SIZE))
338
mysql_config['query_cache_size'] = qcache_bytes
339
dataset_bytes -= qcache_bytes
341
# 5.5 allows the words, but not 5.1
342
if config['query-cache-type'] == 'ON':
343
mysql_config['query_cache_type'] = 1
344
elif config['query-cache-type'] == 'DEMAND':
345
mysql_config['query_cache_type'] = 2
347
mysql_config['query_cache_type'] = 0
349
# Set a sane default key_buffer size
350
mysql_config['key_buffer'] = self.human_to_bytes('32M')
352
if 'preferred-storage-engine' in config:
353
# Storage engine configuration
354
preferred_engines = config['preferred-storage-engine'].split(',')
355
chunk_size = int(dataset_bytes / len(preferred_engines))
356
mysql_config['innodb_flush_log_at_trx_commit'] = 1
357
mysql_config['sync_binlog'] = 1
358
if 'InnoDB' in preferred_engines:
359
mysql_config['innodb_buffer_pool_size'] = chunk_size
360
if config['tuning-level'] == 'fast':
361
mysql_config['innodb_flush_log_at_trx_commit'] = 2
363
mysql_config['innodb_buffer_pool_size'] = 0
365
mysql_config['default_storage_engine'] = preferred_engines[0]
366
if 'MyISAM' in preferred_engines:
367
mysql_config['key_buffer'] = chunk_size
369
if config['tuning-level'] == 'fast':
370
mysql_config['sync_binlog'] = 0
added
removed
hooks/charmhelpers/contrib/database/mysql.py
