SaltStack – Using the Mysql Module


A quick tutorial on setting up access to mysql running on a SaltStack minion so you can access the database from a SaltStack master. In order to issue salt.module.mysql commands from a SaltStack master the minion must have the correct mysql parameters in the /etc/salt/minion file and the MySQl-python package must be installed on the minion. Make sure the package is installed by issuing:

yum install MySQL-python -y

Salt is written in Python and the MySql package is not usually installed by default in most distributions.

Salt Minion File

Using “vi” or your favourite Linux editor, open the /etc/salt/minion file and add the following lines to the end of the file:

mysql.host: ‘localhost’ mysql.port: 3306 mysql.user: ‘root’ mysql.pass: ‘thepasswordhere’ mysql.db: ‘mysql’ mysql.charset: ‘utf8’

Some examples on the internet show a pipe connection method some show both but if your installation is running on a socket then just configure the socket. If you try to configure both pipe and socket, it will not work! Perform a restart of the salt-minion service using:

service salt-minion restart

On the SalStack master issues the following command

root@salt:~# salt ‘dbserver.local.lan’ mysql.version dbserver.local.lan: 5.5.38 root@salt:~#

If all is good then the version should be returned. If you cant get the version then first verify that the account details on the minion are correct. here is the list of mysql modules supported: http://docs.saltstack.com/en/latest/ref/modules/all/salt.modules.mysql.html You can list all DB’s on the minion using mysql.db_list:

root@salt:~#salt ‘dbserver.local.lan’ mysql.db_list dbserver.local.lan: – information_schema – mysql – performance_schema root@salt:~#

We can also create a new Database on the Minion using the command “salt mysql.db_create NewDB” and then list the DB’s using the previous example:

root@salt:~#salt ‘dbserver.local.lan’ mysql.db_list dbserver.local.lan: – information_schema – NewDB – mysql – performance_schema root@salt:~#

JSON Status A very handy command is the mysql.status command with the –out=json command line flag set, the resultant JSON data is very useful for status and monitoring.

root@salt:~#salt ‘dbserver.local.lan’ mysql.status –out=json

{ “dbserver.local.lan”: { “Com_show_create_event”: “0”,  “Com_execute_sql”: “0”,  “Com_show_procedure_status”: “0”,  “Performance_schema_locker_lost”: “0”,  “Ssl_accepts”: “0”,  “Com_xa_rollback”: “0”,  “Performance_schema_table_handles_lost”: “0”,  “Com_replace”: “0”,  “Innodb_pages_read”: “15”,  “Com_create_procedure”: “0”,  “Com_preload_keys”: “0”,  “Com_kill”: “0”,  “Innodb_log_writes”: “3”,  “Com_show_engine_status”: “0”,  “Com_load”: “0”,  “Com_alter_procedure”: “0”,  “Com_ha_open”: “0”,  “Com_alter_db_upgrade”: “0”,  “Compression”: “OFF”,  “Com_drop_trigger”: “0”,  “Com_show_slave_status”: “0”,  “Com_stmt_send_long_data”: “0”,  “Innodb_buffer_pool_pages_free”: “8049”,  “Threads_connected”: “1”,  “Innodb_rows_inserted”: “0”,  “Com_create_udf”: “0”,  “Qcache_lowmem_prunes”: “0”,  “Binlog_cache_use”: “0”,  “Com_lock_tables”: “0”,  “Com_alter_function”: “0”,  “Com_show_authors”: “0”,  “Com_resignal”: “0”,  “Com_stmt_execute”: “0”,  “Com_show_table_status”: “0”,  “Innodb_os_log_written”: “1546752”,  “Ssl_session_cache_mode”: “NONE”,  “Com_commit”: “0”,  “Ssl_cipher_list”: “”,  “Performance_schema_cond_instances_lost”: “0”,  “Com_drop_table”: “0”,  “Com_show_create_proc”: “0”,  “Com_rollback_to_savepoint”: “0”,  “Last_query_cost”: “0.000000”,  “Com_stmt_fetch”: “0”,  “Max_used_connections”: “2”,  “Com_create_view”: “0”,  “Performance_schema_cond_classes_lost”: “0”,  “Sort_rows”: “0”,  “Com_change_master”: “0”,  “Com_do”: “0”,  “Com_show_create_table”: “0”,  “Com_create_trigger”: “0”,  “Innodb_data_writes”: “151”,  “Handler_read_last”: “0”,  “Com_slave_stop”: “0”,  “Com_show_master_status”: “0”,  “Performance_schema_file_handles_lost”: “0”,  “Innodb_pages_written”: “132”,  “Not_flushed_delayed_rows”: “0”,  “Com_install_plugin”: “0”,  “Slave_running”: “OFF”,  “Ssl_session_cache_size”: “0”,  “Innodb_log_write_requests”: “3090”,  “Qcache_inserts”: “0”,  “Com_flush”: “0”,  “Slave_received_heartbeats”: “0”,  “Innodb_buffer_pool_pages_misc”: “0”,  “Binlog_stmt_cache_use”: “0”,  “Ssl_verify_depth”: “0”,  “Innodb_buffer_pool_wait_free”: “0”,  “Com_admin_commands”: “0”,  “Ssl_session_cache_overflows”: “0”,  “Com_begin”: “0”,  “Com_create_table”: “0”,  “Ssl_ctx_verify_mode”: “0”,  “Innodb_row_lock_time_max”: “0”,  “Handler_savepoint_rollback”: “0”,  “Sort_merge_passes”: “0”,  “Ssl_client_connects”: “0”,  “Com_call_procedure”: “0”,  “Com_show_relaylog_events”: “0”,  “Delayed_insert_threads”: “0”,  “Com_show_create_db”: “0”,  “Innodb_rows_read”: “0”,  “Com_drop_index”: “0”,  “Handler_savepoint”: “0”,  “Innodb_row_lock_time_avg”: “0”,  “Innodb_buffer_pool_write_requests”: “1516”,  “Slow_queries”: “0”,  “Performance_schema_table_instances_lost”: “0”,  “Key_blocks_not_flushed”: “0”,  “Ssl_callback_cache_hits”: “0”,  “Innodb_row_lock_current_waits”: “0”,  “Select_range_check”: “0”,  “Com_repair”: “0”,  “Com_show_open_tables”: “0”,  “Com_show_slave_hosts”: “0”,  “Com_revoke_all”: “0”,  “Performance_schema_file_classes_lost”: “0”,  “Aborted_connects”: “2”,  “Handler_read_first”: “0”,  “Innodb_os_log_pending_writes”: “0”,  “Com_show_fields”: “0”,  “Created_tmp_disk_tables”: “0”,  “Select_full_range_join”: “0”,  “Connections”: “75”,  “Key_blocks_unused”: “6676”,  “Slave_heartbeat_period”: “0.000”,  “Com_insert”: “0”,  “Ssl_ctx_verify_depth”: “0”,  “Ssl_used_session_cache_entries”: “0”,  “Innodb_dblwr_pages_written”: “132”,  “Innodb_truncated_status_writes”: “0”,  “Tc_log_page_waits”: “0”,  “Innodb_buffer_pool_pages_dirty”: “0”,  “Com_show_errors”: “0”,  “Com_drop_procedure”: “0”,  “Aborted_clients”: “0”,  “Handler_read_rnd”: “0”,  “Com_signal”: “0”,  “Com_drop_event”: “0”,  “Performance_schema_rwlock_instances_lost”: “0”,  “Handler_delete”: “0”,  “Com_show_tables”: “0”,  “Com_create_event”: “0”,  “Com_alter_table”: “0”,  “Com_delete_multi”: “0”,  “Select_range”: “0”,  “Opened_table_definitions”: “0”,  “Innodb_data_pending_reads”: “0”,  “Innodb_buffer_pool_pages_total”: “8192”,  “Com_show_triggers”: “0”,  “Handler_read_key”: “0”,  “Com_truncate”: “0”,  “Performance_schema_file_instances_lost”: “0”,  “Com_rollback”: “0”,  “Com_replace_select”: “0”,  “Com_show_databases”: “0”,  “Bytes_received”: “145”,  “Tc_log_page_size”: “0”,  “Ssl_session_cache_hits”: “0”,  “Innodb_pages_created”: “128”,  “Com_show_storage_engines”: “0”,  “Threads_cached”: “0”,  “Com_binlog”: “0”,  “Innodb_buffer_pool_pages_flushed”: “132”,  “Com_create_index”: “0”,  “Com_create_user”: “0”,  “Qcache_hits”: “0”,  “Innodb_data_pending_writes”: “0”,  “Com_show_events”: “0”,  “Qcache_queries_in_cache”: “0”,  “Com_check”: “0”,  “Innodb_buffer_pool_read_requests”: “1718”,  “Innodb_os_log_pending_fsyncs”: “0”,  “Com_xa_commit”: “0”,  “Innodb_buffer_pool_bytes_dirty”: “0”,  “Com_create_function”: “0”,  “Slow_launch_threads”: “0”,  “Rpl_status”: “AUTH_MASTER”,  “Delayed_writes”: “0”,  “Bytes_sent”: “122”,  “Table_locks_waited”: “0”,  “Innodb_log_waits”: “0”,  “Com_show_grants”: “0”,  “Com_uninstall_plugin”: “0”,  “Ssl_accept_renegotiates”: “0”,  “Com_savepoint”: “0”,  “Ssl_sessions_reused”: “0”,  “Delayed_errors”: “0”,  “Handler_prepare”: “0”,  “Com_release_savepoint”: “0”,  “Innodb_data_reads”: “26”,  “Ssl_connect_renegotiates”: “0”,  “Select_full_join”: “0”,  “Handler_read_prev”: “0”,  “Flush_commands”: “1”,  “Com_revoke”: “0”,  “Com_xa_end”: “0”,  “Com_show_create_func”: “0”,  “Com_purge”: “0”,  “Com_optimize”: “0”,  “Com_analyze”: “0”,  “Com_slave_start”: “0”,  “Com_stmt_reprepare”: “0”,  “Innodb_dblwr_writes”: “3”,  “Com_show_binlog_events”: “0”,  “Com_xa_recover”: “0”,  “Innodb_buffer_pool_reads”: “16”,  “Performance_schema_mutex_instances_lost”: “0”,  “Innodb_data_read”: “2445312”,  “Com_drop_server”: “0”,  “Innodb_buffer_pool_read_ahead_evicted”: “0”,  “Com_unlock_tables”: “0”,  “Com_assign_to_keycache”: “0”,  “Binlog_stmt_cache_disk_use”: “0”,  “Sort_range”: “0”,  “Ssl_finished_connects”: “0”,  “Com_dealloc_sql”: “0”,  “Questions”: “4”,  “Sort_scan”: “0”,  “Innodb_rows_deleted”: “0”,  “Com_drop_db”: “0”,  “Slave_open_temp_tables”: “0”,  “Qcache_free_blocks”: “0”,  “Queries”: “4394”,  “Com_show_charsets”: “0”,  “Com_show_privileges”: “0”,  “Com_drop_function”: “0”,  “Key_write_requests”: “129”,  “Ssl_verify_mode”: “0”,  “Handler_rollback”: “0”,  “Com_create_server”: “0”,  “Com_stmt_reset”: “0”,  “Select_scan”: “0”,  “Com_update_multi”: “0”,  “Performance_schema_thread_instances_lost”: “0”,  “Com_checksum”: “0”,  “Com_xa_start”: “0”,  “Handler_discover”: “0”,  “Com_alter_tablespace”: “0”,  “Com_grant”: “0”,  “Com_empty_query”: “0”,  “Com_drop_user”: “0”,  “Threads_running”: “1”,  “Com_show_processlist”: “0”,  “Com_show_engine_logs”: “0”,  “Com_drop_view”: “0”,  “Innodb_buffer_pool_pages_data”: “143”,  “Com_show_binlogs”: “0”,  “Com_show_status”: “1”,  “Qcache_not_cached”: “0”,  “Ssl_finished_accepts”: “0”,  “Uptime”: “535340”,  “Com_alter_db”: “0”,  “Ssl_version”: “”,  “Com_ha_read”: “0”,  “Handler_read_rnd_next”: “0”,  “Com_alter_server”: “0”,  “Com_set_option”: “3”,  “Com_alter_event”: “0”,  “Innodb_os_log_fsyncs”: “7”,  “Com_show_function_status”: “0”,  “Com_show_warnings”: “0”,  “Key_blocks_used”: “23”,  “Open_streams”: “0”,  “Ssl_session_cache_misses”: “0”,  “Key_reads”: “24”,  “Ssl_default_timeout”: “0”,  “Com_rename_user”: “0”,  “Performance_schema_rwlock_classes_lost”: “0”,  “Opened_files”: “3466”,  “Handler_read_next”: “0”,  “Com_show_variables”: “0”,  “Performance_schema_mutex_classes_lost”: “0”,  “Created_tmp_tables”: “0”,  “Open_tables”: “81”,  “Com_show_plugins”: “0”,  “Ssl_session_cache_timeouts”: “0”,  “Com_insert_select”: “0”,  “Com_show_profile”: “0”,  “Com_xa_prepare”: “0”,  “Handler_update”: “0”,  “Created_tmp_files”: “6”,  “Innodb_buffer_pool_read_ahead_rnd”: “0”,  “Com_reset”: “0”,  “Handler_commit”: “0”,  “Innodb_buffer_pool_bytes_data”: “2342912”,  “Handler_write”: “0”,  “Com_ha_close”: “0”,  “Innodb_data_pending_fsyncs”: “0”,  “Binlog_cache_disk_use”: “0”,  “Com_show_keys”: “0”,  “Innodb_row_lock_waits”: “0”,  “Com_show_profiles”: “0”,  “Com_show_create_trigger”: “0”,  “Innodb_rows_updated”: “0”,  “Threads_created”: “74”,  “Qcache_total_blocks”: “0”,  “Com_select”: “0”,  “Innodb_buffer_pool_read_ahead”: “0”,  “Qcache_free_memory”: “0”,  “Com_change_db”: “0”,  “Innodb_have_atomic_builtins”: “ON”,  “Opened_tables”: “0”,  “Ssl_cipher”: “”,  “Com_help”: “0”,  “Performance_schema_thread_classes_lost”: “0”,  “Com_prepare_sql”: “0”,  “Com_delete”: “0”,  “Com_purge_before_date”: “0”,  “Open_table_definitions”: “47”,  “Key_writes”: “62”,  “Com_show_engine_mutex”: “0”,  “Com_show_collations”: “0”,  “Innodb_row_lock_time”: “0”,  “Table_locks_immediate”: “562”,  “Tc_log_max_pages_used”: “0”,  “Com_stmt_prepare”: “0”,  “Innodb_data_written”: “5874176”,  “Com_update”: “0”,  “Slave_retried_transactions”: “0”,  “Innodb_data_fsyncs”: “14”,  “Com_stmt_close”: “0”,  “Com_create_db”: “0”,  “Uptime_since_flush_status”: “535340”,  “Key_read_requests”: “490”,  “Open_files”: “128”,  “Com_show_contributors”: “0”,  “Innodb_page_size”: “16384”,  “Com_rename_table”: “0”,  “Prepared_stmt_count”: “0” } } Enjoy!

Advertisements

1 thought on “SaltStack – Using the Mysql Module”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s