Tags

, ,


A quick tutorial on setting up access to mysql running on a SaltStack minion so you can access the database from a SaltStack master. Some additional Tip-n-Tricks are located here.

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: ‘salt’
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