Class: Jetpants::DB
- Includes:
- CallbackHandler
- Defined in:
- lib/jetpants/db.rb,
lib/jetpants/db/state.rb,
lib/jetpants/db/client.rb,
lib/jetpants/db/server.rb,
lib/jetpants/db/privileges.rb,
lib/jetpants/db/replication.rb,
lib/jetpants/db/import_export.rb
Overview
– Import, export, and data set methods ####################################### ++
Constant Summary collapse
- @@all_dbs =
We keep track of DB instances to prevent DB.new from every returning duplicates.
{}
- @@all_dbs_mutex =
Mutex.new
Instance Attribute Summary collapse
-
#host ⇒ Object
readonly
Jetpants::Host object that this MySQL instance runs on.
-
#ip ⇒ Object
readonly
IP address (as a string) of the MySQL instance.
-
#port ⇒ Object
readonly
Port number of the MySQL instance.
Class Method Summary collapse
- .clear ⇒ Object
-
.new(ip, port = 3306) ⇒ Object
We override DB.new so that attempting to create a duplicate DB object (that is, one with the same IP and port as an existing DB object) returns the original object.
Instance Method Summary collapse
-
#<=>(other) ⇒ Object
DB objects are sorted as strings, ie, by calling to_s.
-
#alter_schemata ⇒ Object
Has no built-in effect.
-
#app_credentials ⇒ Object
Returns a hash containing :user and :pass indicating how the application connects to this database instance.
-
#app_schema ⇒ Object
Returns the schema name (“database name” in MySQL parlance) to use for connections.
-
#binlog_coordinates(display_info = true) ⇒ Object
Returns a two-element array containing [log file name, position] for this database.
-
#catch_up_to_master(timeout = 3600, threshold = 3, poll_frequency = 5) ⇒ Object
Waits for this instance’s SECONDS_BEHIND_MASTER to reach 0 and stay at 0 after repeated polls (based on threshold and poll_frequency).
-
#change_master_to(new_master, option_hash = {}) ⇒ Object
Changes the master for this instance.
-
#clone_to!(*targets) ⇒ Object
Copies mysql db files from self to one or more additional DBs.
-
#confirm_listening(timeout = 10) ⇒ Object
Confirms that a process is listening on the DB’s port.
-
#connect(options = {}) ⇒ Object
Returns a Sequel database object for use in sending queries to the DB remotely.
-
#connection ⇒ Object
(also: #mysql)
Returns a Sequel database object representing the current connection.
-
#create_user(username = false, password = false, skip_binlog = false) ⇒ Object
Create a MySQL user.
-
#data_set_size(in_gb = false) ⇒ Object
Returns the data set size in bytes (if in_gb is false or omitted) or in gigabytes (if in_gb is true).
-
#delete_table_data_outside_range(table, keep_min_id, keep_max_id, direction) ⇒ Object
Helper method used by prune_data_to_range.
-
#disable_binary_logging ⇒ Object
Disables binary logging in my.cnf.
-
#disable_monitoring(*services) ⇒ Object
Has no built-in effect.
-
#disable_read_only! ⇒ Object
Disables global read-only mode on the database.
-
#disable_replication! ⇒ Object
(also: #reset_replication!)
Permanently disables replication.
-
#disconnect ⇒ Object
Closes the database connection(s) in the connection pool.
-
#drop_user(username = false, skip_binlog = false) ⇒ Object
Drops a user.
-
#enable_binary_logging ⇒ Object
Re-enables binary logging in my.cnf after a prior call to disable_bin_log.
-
#enable_monitoring(*services) ⇒ Object
Has no built-in effect.
-
#enable_read_only! ⇒ Object
Enables global read-only mode on the database.
-
#enslave!(targets, repl_user = false, repl_pass = false) ⇒ Object
Wipes out the target instances and turns them into slaves of self.
-
#enslave_sibling!(target) ⇒ Object
Shortcut to call DB#enslave_siblings! on a single target.
-
#enslave_siblings!(targets) ⇒ Object
Wipes out the target instances and turns them into slaves of self’s master.
-
#export_data(tables, min_id = false, max_id = false) ⇒ Object
Exports data for the supplied tables.
-
#export_schemata(tables) ⇒ Object
Exports the DROP TABLE + CREATE TABLE statements for the given tables via mysqldump.
-
#export_table_data(table, min_id = false, max_id = false) ⇒ Object
Exports data for a table.
-
#for_backups? ⇒ Boolean
Jetpants supports a notion of dedicated backup machines, containing one or more MySQL instances that are considered “backup slaves”, which will never be promoted to serve production queries.
-
#global_status ⇒ Object
Returns a hash mapping global MySQL status fields (as symbols) to their values (as strings).
-
#global_variables ⇒ Object
Returns a hash mapping global MySQL variables (as symbols) to their values (as strings).
-
#grant_or_revoke_privileges(statement, username, database, privileges) ⇒ Object
Helper method that can do grants or revokes.
-
#grant_privileges(username = false, database = false, *privileges) ⇒ Object
Grants privileges to the given username for the specified database.
-
#has_slaves? ⇒ Boolean
Returns true if this instance had at least one slave when it was last probed, false otherwise.
-
#import_data(tables, min_id = false, max_id = false) ⇒ Object
Imports data for a table that was previously exported using export_data.
-
#import_schemata! ⇒ Object
Executes a .sql file previously created via export_schemata.
-
#import_table_data(table, min_id = false, max_id = false) ⇒ Object
Imports the data subset previously dumped thorugh export_data.
-
#initialize(ip, port = 3306) ⇒ DB
constructor
A new instance of DB.
-
#is_slave? ⇒ Boolean
Returns true if this instance has a master, false otherwise.
-
#is_standby? ⇒ Boolean
Returns true if this instance appears to be a standby slave, false otherwise.
-
#master ⇒ Object
Returns the Jetpants::DB instance that is the master of this instance, or false if there isn’t one, or nil if we can’t tell because this instance isn’t running.
-
#method_missing(name, *args, &block) ⇒ Object
Jetpants::DB delegates missing methods to its Jetpants::Host.
-
#mysql_config_file ⇒ Object
Returns the MySQL server configuration file for this instance.
-
#mysql_directory ⇒ Object
Returns the MySQL data directory for this instance.
-
#mysql_root_cmd(cmd, options = {}) ⇒ Object
Runs the provided SQL statement as root, locally via an SSH command line, and returns the response as a single string.
-
#output(str, table = nil) ⇒ Object
Displays the provided output, along with information about the current time, self, and optionally a Jetpants::Table name.
-
#parse_vertical_result(text) ⇒ Object
Parses the result of a MySQL query run with a G terminator.
-
#pause_replication ⇒ Object
(also: #stop_replication)
Pauses replication.
-
#pool(create_if_missing = false) ⇒ Object
Returns the Jetpants::Pool that this instance belongs to, if any.
-
#probe(force = false) ⇒ Object
Probes this instance to discover its status, master, and slaves.
-
#probe! ⇒ Object
Alias for probe(true).
-
#probed? ⇒ Boolean
Returns true if we’ve probed this MySQL instance already.
-
#prune_data_to_range(tables, keep_min_id, keep_max_id) ⇒ Object
Cleans up all rows that should no longer be on this db.
-
#query(sql, *binds) ⇒ Object
Execute a write (INSERT, UPDATE, DELETE, REPLACE, etc) query.
-
#query_return_array(sql, *binds) ⇒ Object
Execute a read (SELECT) query.
-
#query_return_first(sql, *binds) ⇒ Object
Execute a read (SELECT) query.
-
#query_return_first_value(sql, *binds) ⇒ Object
Execute a read (SELECT) query.
-
#read_only? ⇒ Boolean
Returns true if the global READ_ONLY variable is set, false otherwise.
-
#rebuild!(tables = false, min_id = false, max_id = false) ⇒ Object
Exports and re-imports data for the specified tables, optionally bounded by the given range.
-
#reconnect(options = {}) ⇒ Object
Disconnects and reconnects to the database.
-
#repl_binlog_coordinates(display_info = true) ⇒ Object
Use this on a slave to return [master log file name, position] for how far this slave has executed (in terms of its master’s binlogs) in its SQL replication thread.
-
#repl_paused? ⇒ Boolean
Returns true if replication is paused on this instance, false if it isn’t, or nil if this instance isn’t a slave (or if we can’t tell because the instance isn’t running).
-
#replicating? ⇒ Boolean
Returns true if the MySQL slave I/O thread and slave SQL thread are both running, false otherwise.
-
#replication_credentials ⇒ Object
Reads an existing master.info file on this db or one of its slaves, propagates the info back to the Jetpants singleton, and returns it as a hash containing :user and :pass.
-
#respond_to?(name, include_private = false) ⇒ Boolean
Alters respond_to? logic to account for delegation of missing methods to the instance’s Host.
-
#restart_mysql ⇒ Object
Restarts MySQL.
-
#resume_replication ⇒ Object
(also: #start_replication)
Starts replication, or restarts replication after a pause.
-
#revoke_all_access! ⇒ Object
Disables access to a DB by the application user, and sets the DB to read-only.
-
#revoke_privileges(username = false, database = false, *privileges) ⇒ Object
Revokes privileges from the given username for the specified database.
-
#role ⇒ Object
Determines the DB’s role in its pool.
-
#row_counts(tables, min_id, max_id) ⇒ Object
Counts rows falling between min_id and max_id for the supplied tables.
-
#running? ⇒ Boolean
Returns true if MySQL is running for this instance, false otherwise.
-
#same_host_as?(db) ⇒ Boolean
Returns true if the supplied Jetpants::DB is on the same Jetpants::Host as self.
-
#seconds_behind_master ⇒ Object
Returns the number of seconds beind the master the replication execution is, as reported by SHOW SLAVE STATUS.
-
#slave_status ⇒ Object
Returns a hash containing the information from SHOW SLAVE STATUS.
-
#slaves ⇒ Object
Returns an Array of Jetpants::DB instances that are slaving from this instance, or nil if we can’t tell because this instance isn’t running.
-
#start_mysql ⇒ Object
Starts MySQL, and confirms that something is now listening on the port.
-
#start_query_killer ⇒ Object
Has no built-in effect.
-
#stop_mysql ⇒ Object
Shuts down MySQL, and confirms that it is no longer listening.
-
#stop_query_killer ⇒ Object
Has no built-in effect.
-
#taking_connections?(max = 4, interval = 2.0, threshold = 1) ⇒ Boolean
Confirms instance has no more than [max] connections currently (AS VISIBLE TO THE APP USER), and in [interval] seconds hasn’t received more than [threshold] additional connections.
-
#to_db ⇒ Object
Returns self, since self is already a Jetpants::DB.
-
#to_host ⇒ Object
Returns the instance’s Jetpants::Host.
-
#to_s ⇒ Object
Returns a string in the form “ip:port”.
-
#version_tuple ⇒ Object
Returns an array of integers representing the version of the MySQL server.
Methods included from CallbackHandler
Constructor Details
#initialize(ip, port = 3306) ⇒ DB
Returns a new instance of DB.
56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
# File 'lib/jetpants/db.rb', line 56 def initialize(ip, port=3306) @ip, @port = ip, port.to_i @host = Host.new(ip) # These get set upon DB#probe being run @master = nil @slaves = nil @repl_paused = nil @running = nil # These get set upon DB#connect being run @user = nil @schema = nil end |
Dynamic Method Handling
This class handles dynamic methods through the method_missing method
#method_missing(name, *args, &block) ⇒ Object
Jetpants::DB delegates missing methods to its Jetpants::Host.
74 75 76 77 78 79 80 |
# File 'lib/jetpants/db.rb', line 74 def method_missing(name, *args, &block) if @host.respond_to? name @host.send name, *args, &block else super end end |
Instance Attribute Details
#host ⇒ Object (readonly)
Jetpants::Host object that this MySQL instance runs on.
27 28 29 |
# File 'lib/jetpants/db.rb', line 27 def host @host end |
#ip ⇒ Object (readonly)
IP address (as a string) of the MySQL instance
17 18 19 |
# File 'lib/jetpants/db.rb', line 17 def ip @ip end |
#port ⇒ Object (readonly)
Port number of the MySQL instance. The base Jetpants implementation only supports port 3306, since this is necessary to crawl a replication hierarchy using SHOW PROCESSLIST, which does not include slave port numbers. However, plugins may override this behavior to support nonstandard ports and multi-instance-per-host topologies.
24 25 26 |
# File 'lib/jetpants/db.rb', line 24 def port @port end |
Class Method Details
.clear ⇒ Object
34 35 36 |
# File 'lib/jetpants/db.rb', line 34 def self.clear @@all_dbs_mutex.synchronize {@@all_dbs = {}} end |
.new(ip, port = 3306) ⇒ Object
We override DB.new so that attempting to create a duplicate DB object (that is, one with the same IP and port as an existing DB object) returns the original object.
46 47 48 49 50 51 52 53 54 |
# File 'lib/jetpants/db.rb', line 46 def self.new(ip, port=3306) ip, = ip.split(':', 2) port = .to_i if addr = "#{ip}:#{port}" @@all_dbs_mutex.synchronize do @@all_dbs[addr] = nil unless @@all_dbs[addr].is_a? self @@all_dbs[addr] ||= super end end |
Instance Method Details
#<=>(other) ⇒ Object
DB objects are sorted as strings, ie, by calling to_s
110 111 112 |
# File 'lib/jetpants/db.rb', line 110 def <=> other to_s <=> other.to_s end |
#alter_schemata ⇒ Object
Has no built-in effect. Plugins can override this and/or use before_alter_schemata and after_alter_schemata callbacks to provide an implementation. Also sometimes useful to override this as a singleton method on specific DB objects in a migration script.
34 35 |
# File 'lib/jetpants/db/import_export.rb', line 34 def alter_schemata end |
#app_credentials ⇒ Object
Returns a hash containing :user and :pass indicating how the application connects to this database instance. By default this just delegates to Jetpants.application_credentials, which obtains credentials from the Jetpants config file. Plugins may override this to use different credentials for particular hosts or in certain situations.
94 95 96 |
# File 'lib/jetpants/db/client.rb', line 94 def app_credentials Jetpants.app_credentials end |
#app_schema ⇒ Object
Returns the schema name (“database name” in MySQL parlance) to use for connections. Defaults to just calling Jetpants.mysql_schema, but plugins may override.
100 101 102 |
# File 'lib/jetpants/db/client.rb', line 100 def app_schema Jetpants.mysql_schema end |
#binlog_coordinates(display_info = true) ⇒ Object
Returns a two-element array containing [log file name, position] for this database. Only useful when called on a master. This is the current instance’s own binlog coordinates, NOT the coordinates of replication progress on a slave!
161 162 163 164 165 166 |
# File 'lib/jetpants/db/replication.rb', line 161 def binlog_coordinates(display_info=true) hash = mysql_root_cmd('SHOW MASTER STATUS', :parse=>true) raise "Cannot obtain binlog coordinates of this master becaues binary logging is not enabled" unless hash[:file] output "Own binlog coordinates are (#{hash[:file]}, #{hash[:position].to_i})." if display_info [hash[:file], hash[:position].to_i] end |
#catch_up_to_master(timeout = 3600, threshold = 3, poll_frequency = 5) ⇒ Object
Waits for this instance’s SECONDS_BEHIND_MASTER to reach 0 and stay at 0 after repeated polls (based on threshold and poll_frequency). Will raise an exception if this has not happened within the timeout period, in seconds. In other words, with default settings: checks slave lag every 5+ sec, and returns true if slave lag is zero 3 times in a row. Gives up if this does not occur within a one-hour period. If a large amount of slave lag is reported, this method will automatically reduce its polling frequency.
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 |
# File 'lib/jetpants/db/replication.rb', line 183 def catch_up_to_master(timeout=3600, threshold=3, poll_frequency=5) raise "This instance is not a slave" unless master resume_replication if @repl_paused times_at_zero = 0 start = Time.now.to_i output "Waiting to catch up to master" while (Time.now.to_i - start) < timeout lag = seconds_behind_master if lag == 0 times_at_zero += 1 if times_at_zero >= threshold output "Caught up to master." return true end sleep poll_frequency elsif lag.nil? resume_replication sleep 1 raise "Unable to restart replication" if seconds_behind_master.nil? else output "Currently #{lag} seconds behind master." times_at_zero = 0 extra_sleep_time = (lag > 30000 ? 300 : (seconds_behind_master / 100).ceil) sleep poll_frequency + extra_sleep_time end end raise "This instance did not catch up to its master within #{timeout} seconds" end |
#change_master_to(new_master, option_hash = {}) ⇒ Object
Changes the master for this instance. Supply a Jetpants::DB indicating the new master, along with options :log_pos, :log_file, :user, :password. Does NOT automatically start replication afterwards on self!
If you omit :log_pos or :log_file, uses the current position/file from new_master, though this is only safe if new_master is not receiving writes!
If you omit :user and :password, tries obtaining replication credentials from the current node (assuming it is already a slave) or if that fails then from the global settings.
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
# File 'lib/jetpants/db/replication.rb', line 18 def change_master_to(new_master, option_hash={}) return disable_replication! unless new_master # change_master_to(nil) alias for disable_replication! return if new_master == master # no change logfile = option_hash[:log_file] pos = option_hash[:log_pos] if !(logfile && pos) raise "Cannot use coordinates of a new master that is receiving updates" if new_master.master && ! new_master.repl_paused logfile, pos = new_master.binlog_coordinates end repl_user = option_hash[:user] || replication_credentials[:user] repl_pass = option_hash[:password] || replication_credentials[:pass] pause_replication if @master && !@repl_paused result = mysql_root_cmd "CHANGE MASTER TO " + "MASTER_HOST='#{new_master.ip}', " + "MASTER_PORT=#{new_master.port}, " + "MASTER_LOG_FILE='#{logfile}', " + "MASTER_LOG_POS=#{pos}, " + "MASTER_USER='#{repl_user}', " + "MASTER_PASSWORD='#{repl_pass}'" output "Changing master to #{new_master} with coordinates (#{logfile}, #{pos}). #{result}" @master.slaves.delete(self) if @master rescue nil @master = new_master @repl_paused = true new_master.slaves << self end |
#clone_to!(*targets) ⇒ Object
Copies mysql db files from self to one or more additional DBs. WARNING: temporarily shuts down mysql on self, and WILL OVERWRITE CONTENTS OF MYSQL DIRECTORY ON TARGETS. Confirms first that none of the targets have over 100MB of data in the schema directory or in ibdata1. MySQL is restarted on source and targets afterwards.
300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 |
# File 'lib/jetpants/db/import_export.rb', line 300 def clone_to!(*targets) targets.flatten! raise "Cannot clone an instance onto its master" if master && targets.include?(master) destinations = {} targets.each do |t| destinations[t] = t.mysql_directory existing_size = t.data_set_size + t.dir_size("#{t.mysql_directory}/ibdata1") raise "Over 100 MB of existing MySQL data on target #{t}, aborting copy!" if existing_size > 100000000 end [self, targets].flatten.concurrent_each {|t| t.stop_query_killer; t.stop_mysql} targets.concurrent_each {|t| t.ssh_cmd "rm -rf #{t.mysql_directory}/ib_logfile*"} fast_copy_chain(mysql_directory, destinations, port: 3306, files: ['ibdata1', 'mysql', 'test', app_schema], overwrite: true) [self, targets].flatten.concurrent_each {|t| t.start_mysql; t.start_query_killer} end |
#confirm_listening(timeout = 10) ⇒ Object
Confirms that a process is listening on the DB’s port
62 63 64 |
# File 'lib/jetpants/db/server.rb', line 62 def confirm_listening(timeout=10) confirm_listening_on_port(@port, timeout) end |
#connect(options = {}) ⇒ Object
Returns a Sequel database object for use in sending queries to the DB remotely. Initializes (or re-initializes) the connection pool upon first use or upon requesting a different user or schema. Note that we only maintain one connection pool per DB. Valid options include :user, :pass, :schema, :max_conns or omit these to use defaults.
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
# File 'lib/jetpants/db/client.rb', line 46 def connect(={}) [:user] ||= app_credentials[:user] [:schema] ||= app_schema return @db if @db && @user == [:user] && @schema == [:schema] disconnect if @db @db = Sequel.connect( :adapter => 'mysql2', :host => @ip, :port => @port, :user => [:user], :password => [:pass] || app_credentials[:pass], :database => [:schema], :max_connections => [:max_conns] || Jetpants.max_concurrency) @user = [:user] @schema = [:schema] @db end |
#connection ⇒ Object Also known as: mysql
Returns a Sequel database object representing the current connection. If no current connection, this will automatically connect with default options.
85 86 87 |
# File 'lib/jetpants/db/client.rb', line 85 def connection @db || connect end |
#create_user(username = false, password = false, skip_binlog = false) ⇒ Object
Create a MySQL user. If you omit parameters, the defaults from Jetpants’ configuration will be used instead. Does not automatically grant any privileges; use DB#grant_privileges for that.
11 12 13 14 15 16 17 18 19 20 21 22 |
# File 'lib/jetpants/db/privileges.rb', line 11 def create_user(username=false, password=false, skip_binlog=false) username ||= app_credentials[:user] password ||= app_credentials[:pass] commands = [] commands << 'SET sql_log_bin = 0' if skip_binlog Jetpants.mysql_grant_ips.each do |ip| commands << "CREATE USER '#{username}'@'#{ip}' IDENTIFIED BY '#{password}'" end commands << "FLUSH PRIVILEGES" commands = commands.join '; ' mysql_root_cmd commands, schema: true end |
#data_set_size(in_gb = false) ⇒ Object
Returns the data set size in bytes (if in_gb is false or omitted) or in gigabytes (if in_gb is true). Note that this is actually in gibibytes (2^30) rather than a metric gigabyte. This puts it on the same scale as the output to tools like “du -h” and “df -h”.
188 189 190 191 |
# File 'lib/jetpants/db/state.rb', line 188 def data_set_size(in_gb=false) bytes = dir_size("#{mysql_directory}/#{app_schema}") in_gb ? (bytes / 1073741824.0).round : bytes end |
#delete_table_data_outside_range(table, keep_min_id, keep_max_id, direction) ⇒ Object
Helper method used by prune_data_to_range. Deletes data for the given table that falls either below the supplied keep_min_id (if direction is :desc) or falls above the supplied keep_max_id (if direction is :asc).
209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 |
# File 'lib/jetpants/db/import_export.rb', line 209 def delete_table_data_outside_range(table, keep_min_id, keep_max_id, direction) rows_deleted = 0 if direction == :asc dir_english = "Ascending" boundary = keep_max_id output "Removing rows with ID > #{boundary}", table elsif direction == :desc dir_english = "Descending" boundary = keep_min_id output "Removing rows with ID < #{boundary}", table else raise "Unknown order parameter #{order}" end table.sharding_keys.each do |col| deleter_sql = table.sql_cleanup_delete(col, keep_min_id, keep_max_id) id = boundary iter = 0 while id do finder_sql = table.sql_cleanup_next_id(col, id, direction) id = query_return_first_value(finder_sql) break unless id rows_deleted += query(deleter_sql, id) iter += 1 output("#{dir_english} deletion progress: through #{col} #{id}, deleted #{rows_deleted} rows so far", table) if iter % 50000 == 0 end end rows_deleted end |
#disable_binary_logging ⇒ Object
Disables binary logging in my.cnf. Does not take effect until you restart mysql.
245 246 247 248 |
# File 'lib/jetpants/db/replication.rb', line 245 def disable_binary_logging output "Disabling binary logging in MySQL configuration; will take effect at next restart" comment_out_ini(mysql_config_file, 'log-bin', 'log-slave-updates') end |
#disable_monitoring(*services) ⇒ Object
Has no built-in effect. Plugins can override it, and/or implement before_disable_monitoring and after_disable_monitoring callbacks.
86 87 |
# File 'lib/jetpants/db/server.rb', line 86 def disable_monitoring(*services) end |
#disable_read_only! ⇒ Object
Disables global read-only mode on the database.
87 88 89 90 91 |
# File 'lib/jetpants/db/privileges.rb', line 87 def disable_read_only! output "Disabling global read_only mode" mysql_root_cmd 'SET GLOBAL read_only = 0' if read_only? not read_only? end |
#disable_replication! ⇒ Object Also known as: reset_replication!
Permanently disables replication. Clears out the SHOW SLAVE STATUS output entirely in MySQL versions that permit this.
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
# File 'lib/jetpants/db/replication.rb', line 69 def disable_replication! raise "This DB object has no master" unless master output "Disabling replication; this db is no longer a slave." ver = version_tuple # MySQL < 5.5: allows master_host='', which clears out SHOW SLAVE STATUS if ver[0] == 5 && ver[1] < 5 output mysql_root_cmd "STOP SLAVE; CHANGE MASTER TO master_host=''; RESET SLAVE" # MySQL 5.5.16+: allows RESET SLAVE ALL, which clears out SHOW SLAVE STATUS elsif ver[0] >= 5 && (ver[0] > 5 || ver[1] >= 5) && (ver[0] > 5 || ver[1] > 5 || ver[2] >= 16) output mysql_root_cmd "STOP SLAVE; CHANGE MASTER TO master_user='test'; RESET SLAVE ALL" # Other versions: no safe way to clear out SHOW SLAVE STATUS. Still set master_user to 'test' # so that we know to ignore the slave status output. else output mysql_root_cmd "STOP SLAVE; CHANGE MASTER TO master_user='test'; RESET SLAVE" end @master.slaves.delete(self) rescue nil @master = nil @repl_paused = nil end |
#disconnect ⇒ Object
Closes the database connection(s) in the connection pool.
68 69 70 71 72 73 74 75 |
# File 'lib/jetpants/db/client.rb', line 68 def disconnect if @db @db.disconnect rescue nil @db = nil end @user = nil @schema = nil end |
#drop_user(username = false, skip_binlog = false) ⇒ Object
Drops a user. Can optionally make this statement skip replication, if you want to drop a user on master and not on its slaves.
26 27 28 29 30 31 32 33 34 35 36 |
# File 'lib/jetpants/db/privileges.rb', line 26 def drop_user(username=false, skip_binlog=false) username ||= app_credentials[:user] commands = [] commands << 'SET sql_log_bin = 0' if skip_binlog Jetpants.mysql_grant_ips.each do |ip| commands << "DROP USER '#{username}'@'#{ip}'" end commands << "FLUSH PRIVILEGES" commands = commands.join '; ' mysql_root_cmd commands, schema: true end |
#enable_binary_logging ⇒ Object
Re-enables binary logging in my.cnf after a prior call to disable_bin_log. Does not take effect until you restart mysql.
252 253 254 255 |
# File 'lib/jetpants/db/replication.rb', line 252 def enable_binary_logging output "Re-enabling binary logging in MySQL configuration; will take effect at next restart" uncomment_out_ini(mysql_config_file, 'log-bin', 'log-slave-updates') end |
#enable_monitoring(*services) ⇒ Object
Has no built-in effect. Plugins can override it, and/or implement before_enable_monitoring and after_enable_monitoring callbacks.
81 82 |
# File 'lib/jetpants/db/server.rb', line 81 def enable_monitoring(*services) end |
#enable_read_only! ⇒ Object
Enables global read-only mode on the database.
80 81 82 83 84 |
# File 'lib/jetpants/db/privileges.rb', line 80 def enable_read_only! output "Enabling global read_only mode" mysql_root_cmd 'SET GLOBAL read_only = 1' unless read_only? read_only? end |
#enslave!(targets, repl_user = false, repl_pass = false) ⇒ Object
Wipes out the target instances and turns them into slaves of self. Resumes replication on self afterwards, but does NOT automatically start replication on the targets. You can omit passing in the replication user/pass if this machine is itself a slave OR already has at least one slave OR the global setting is fine to use here. Warning: takes self offline during the process, so don’t use on a master that is actively in use by your application!
102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
# File 'lib/jetpants/db/replication.rb', line 102 def enslave!(targets, repl_user=false, repl_pass=false) repl_user ||= replication_credentials[:user] repl_pass ||= replication_credentials[:pass] disable_monitoring pause_replication if master && ! @repl_paused file, pos = binlog_coordinates clone_to!(targets) targets.each do |t| t.change_master_to( self, log_file: file, log_pos: pos, user: repl_user, password: repl_pass ) end resume_replication if @master # should already have happened from the clone_to! restart anyway, but just to be explicit enable_monitoring end |
#enslave_sibling!(target) ⇒ Object
Shortcut to call DB#enslave_siblings! on a single target
143 144 145 |
# File 'lib/jetpants/db/replication.rb', line 143 def enslave_sibling!(target) enslave_siblings!([target]) end |
#enslave_siblings!(targets) ⇒ Object
Wipes out the target instances and turns them into slaves of self’s master. Resumes replication on self afterwards, but does NOT automatically start replication on the targets. Warning: takes self offline during the process, so don’t use on an active slave!
124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 |
# File 'lib/jetpants/db/replication.rb', line 124 def enslave_siblings!(targets) raise "Can only call enslave_siblings! on a slave instance" unless master disable_monitoring pause_replication unless @repl_paused file, pos = repl_binlog_coordinates clone_to!(targets) targets.each do |t| t.change_master_to( master, log_file: file, log_pos: pos, user: replication_credentials[:user], password: replication_credentials[:pass] ) end resume_replication # should already have happened from the clone_to! restart anyway, but just to be explicit catch_up_to_master enable_monitoring end |
#export_data(tables, min_id = false, max_id = false) ⇒ Object
Exports data for the supplied tables. If min/max ID supplied, only exports data where at least one of the table’s sharding keys falls within this range. Creates a ‘jetpants’ db user with FILE permissions for the duration of the export.
41 42 43 44 45 46 47 48 49 50 51 52 53 |
# File 'lib/jetpants/db/import_export.rb', line 41 def export_data(tables, min_id=false, max_id=false) pause_replication if @master && ! @repl_paused import_export_user = 'jetpants' create_user(import_export_user) grant_privileges(import_export_user) # standard privs grant_privileges(import_export_user, '*', 'FILE') # FILE global privs reconnect(user: import_export_user) @counts ||= {} tables.each {|t| @counts[t.name] = export_table_data t, min_id, max_id} ensure reconnect(user: app_credentials[:user]) drop_user import_export_user end |
#export_schemata(tables) ⇒ Object
Exports the DROP TABLE + CREATE TABLE statements for the given tables via mysqldump
9 10 11 12 13 14 15 16 17 |
# File 'lib/jetpants/db/import_export.rb', line 9 def export_schemata(tables) output 'Exporting table definitions' supply_root_pw = (Jetpants.mysql_root_password ? "-p#{Jetpants.mysql_root_password}" : '') supply_port = (@port == 3306 ? '' : "-h 127.0.0.1 -P #{@port}") cmd = "mysqldump #{supply_root_pw} #{supply_port} -d #{app_schema} " + tables.join(' ') + " >#{Jetpants.export_location}/create_tables_#{@port}.sql" cmd.untaint result = ssh_cmd(cmd) output result end |
#export_table_data(table, min_id = false, max_id = false) ⇒ Object
Exports data for a table. Only includes the data subset that falls within min_id and max_id. The export files will be located according to the export_location configuration setting. Returns the number of rows exported.
59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
# File 'lib/jetpants/db/import_export.rb', line 59 def export_table_data(table, min_id=false, max_id=false) unless min_id && max_id && table.chunks > 0 output "Exporting all data", table rows_exported = query(table.sql_export_all) output "#{rows_exported} rows exported", table return rows_exported end output "Exporting data for ID range #{min_id}..#{max_id}", table lock = Mutex.new rows_exported = 0 chunks_completed = 0 (min_id..max_id).in_chunks(table.chunks) do |min, max| attempts = 0 begin sql = table.sql_export_range(min, max) result = query sql lock.synchronize do rows_exported += result chunks_completed += 1 percent_finished = 100 * chunks_completed / table.chunks output("Export #{percent_finished}% complete.", table) if table.chunks >= 40 && chunks_completed % 20 == 0 end rescue => ex if attempts >= 10 output "EXPORT ERROR: #{ex.}, chunk #{min}-#{max}, giving up", table raise end attempts += 1 output "EXPORT ERROR: #{ex.}, chunk #{min}-#{max}, attempt #{attempts}, re-trying after delay", table ssh_cmd("rm -f " + table.export_file_path(min, max)) sleep(1.0 * attempts) retry end end output "#{rows_exported} rows exported", table rows_exported end |
#for_backups? ⇒ Boolean
Jetpants supports a notion of dedicated backup machines, containing one or more MySQL instances that are considered “backup slaves”, which will never be promoted to serve production queries. The default implementation identifies these by a hostname beginning with “backup”. You may want to override this with a plugin to use a different scheme if your architecture contains a similar type of node.
125 126 127 |
# File 'lib/jetpants/db/state.rb', line 125 def for_backups? @host.hostname.start_with? 'backup' end |
#global_status ⇒ Object
Returns a hash mapping global MySQL status fields (as symbols) to their values (as strings).
140 141 142 143 144 145 |
# File 'lib/jetpants/db/state.rb', line 140 def global_status query_return_array('show global status').reduce do |variables, variable| variables[variable[:Variable_name].to_sym] = variable[:Value] variables end end |
#global_variables ⇒ Object
Returns a hash mapping global MySQL variables (as symbols) to their values (as strings).
131 132 133 134 135 136 |
# File 'lib/jetpants/db/state.rb', line 131 def global_variables query_return_array('show global variables').reduce do |variables, variable| variables[variable[:Variable_name].to_sym] = variable[:Value] variables end end |
#grant_or_revoke_privileges(statement, username, database, privileges) ⇒ Object
Helper method that can do grants or revokes.
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
# File 'lib/jetpants/db/privileges.rb', line 53 def grant_or_revoke_privileges(statement, username, database, privileges) preposition = (statement.downcase == 'revoke' ? 'FROM' : 'TO') username ||= app_credentials[:user] database ||= app_schema privileges = Jetpants.mysql_grant_privs if privileges.empty? privileges = privileges.join(',') commands = [] Jetpants.mysql_grant_ips.each do |ip| commands << "#{statement} #{privileges} ON #{database}.* #{preposition} '#{username}'@'#{ip}'" end commands << "FLUSH PRIVILEGES" commands = commands.join '; ' mysql_root_cmd commands, schema: true end |
#grant_privileges(username = false, database = false, *privileges) ⇒ Object
Grants privileges to the given username for the specified database. Pass in privileges as additional params, each as strings. You may omit parameters to use the defaults in the Jetpants config file.
41 42 43 |
# File 'lib/jetpants/db/privileges.rb', line 41 def grant_privileges(username=false, database=false, *privileges) grant_or_revoke_privileges('GRANT', username, database, privileges) end |
#has_slaves? ⇒ Boolean
Returns true if this instance had at least one slave when it was last probed, false otherwise. (This method will indirectly force a probe if the instance hasn’t been probed before.)
87 88 89 |
# File 'lib/jetpants/db/state.rb', line 87 def has_slaves? slaves.count > 0 end |
#import_data(tables, min_id = false, max_id = false) ⇒ Object
Imports data for a table that was previously exported using export_data. Only includes the data subset that falls within min_id and max_id. If run after export_data (in the same process), import_data will automatically confirm that the import counts match the previous export counts. Creates a ‘jetpants’ db user with FILE permissions for the duration of the import.
106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 |
# File 'lib/jetpants/db/import_export.rb', line 106 def import_data(tables, min_id=false, max_id=false) import_export_user = 'jetpants' create_user(import_export_user) grant_privileges(import_export_user) # standard privs grant_privileges(import_export_user, '*', 'FILE') # FILE global privs reconnect(user: import_export_user) import_counts = {} tables.each {|t| import_counts[t.name] = import_table_data t, min_id, max_id} # Verify counts @counts ||= {} @counts.each do |name, exported| if exported == import_counts[name] output "Verified import count matches export count for table #{name}" else raise "Import count (#{import_counts[name]}) does not match export count (#{exported}) for table #{name}" end end ensure reconnect(user: app_credentials[:user]) drop_user(import_export_user) end |
#import_schemata! ⇒ Object
Executes a .sql file previously created via export_schemata. Warning: this will DESTROY AND RECREATE any tables contained in the file. DO NOT USE ON A DATABASE THAT CONTAINS REAL DATA!!! This method doesn’t check first! The statements will replicate to any slaves! PROCEED WITH CAUTION IF RUNNING THIS MANUALLY!
24 25 26 27 28 |
# File 'lib/jetpants/db/import_export.rb', line 24 def import_schemata! output 'Dropping and re-creating table definitions' result = mysql_root_cmd "source #{Jetpants.export_location}/create_tables_#{@port}.sql", terminator: '', schema: true output result end |
#import_table_data(table, min_id = false, max_id = false) ⇒ Object
Imports the data subset previously dumped thorugh export_data. Returns number of rows imported.
133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 |
# File 'lib/jetpants/db/import_export.rb', line 133 def import_table_data(table, min_id=false, max_id=false) unless min_id && max_id && table.chunks > 0 output "Importing all data", table rows_imported = query(table.sql_import_all) output "#{rows_imported} rows imported", table return rows_imported end output "Importing data for ID range #{min_id}..#{max_id}", table lock = Mutex.new rows_imported = 0 chunks_completed = 0 (min_id..max_id).in_chunks(table.chunks) do |min, max| attempts = 0 begin sql = table.sql_import_range(min, max) result = query sql lock.synchronize do rows_imported += result chunks_completed += 1 percent_finished = 100 * chunks_completed / table.chunks output("Import #{percent_finished}% complete.", table) if table.chunks >= 40 && chunks_completed % 20 == 0 chunk_file_name = table.export_file_path(min, max) ssh_cmd "rm -f #{chunk_file_name}" end rescue => ex if attempts >= 10 output "IMPORT ERROR: #{ex.}, chunk #{min}-#{max}, giving up", table raise end attempts += 1 output "IMPORT ERROR: #{ex.}, chunk #{min}-#{max}, attempt #{attempts}, re-trying after delay", table sleep(3.0 * attempts) retry end end output "#{rows_imported} rows imported", table rows_imported end |
#is_slave? ⇒ Boolean
Returns true if this instance has a master, false otherwise.
80 81 82 |
# File 'lib/jetpants/db/state.rb', line 80 def is_slave? !!master end |
#is_standby? ⇒ Boolean
Returns true if this instance appears to be a standby slave, false otherwise. Note that “standby” in this case is based on whether the slave is actively receiving connections, not based on any Pool’s understanding of the slave’s state. An asset- tracker plugin may want to override this to determine standby status differently.
115 116 117 |
# File 'lib/jetpants/db/state.rb', line 115 def is_standby? !(running?) || (is_slave? && !taking_connections?) end |
#master ⇒ Object
Returns the Jetpants::DB instance that is the master of this instance, or false if there isn’t one, or nil if we can’t tell because this instance isn’t running.
10 11 12 13 14 |
# File 'lib/jetpants/db/state.rb', line 10 def master return nil unless running? || @master probe if @master.nil? @master end |
#mysql_config_file ⇒ Object
Returns the MySQL server configuration file for this instance. A plugin can override this if needed, especially if running multiple MySQL instances on the same host.
75 76 77 |
# File 'lib/jetpants/db/server.rb', line 75 def mysql_config_file '/etc/my.cnf' end |
#mysql_directory ⇒ Object
Returns the MySQL data directory for this instance. A plugin can override this if needed, especially if running multiple MySQL instances on the same host.
68 69 70 |
# File 'lib/jetpants/db/server.rb', line 68 def mysql_directory '/var/lib/mysql' end |
#mysql_root_cmd(cmd, options = {}) ⇒ Object
Runs the provided SQL statement as root, locally via an SSH command line, and returns the response as a single string. Available options:
- :terminator
-
how to terminate the query, such as ‘G’ or ‘;’. (default: ‘G’)
- :parse
-
parse a single-row, vertical-format result (:terminator must be ‘G’) and return it as a hash
- :schema
-
name of schema to use, or true to use this DB’s default. This may have implications when used with filtered replication! (default: nil, meaning no schema)
- :attempts
-
by default, queries will be attempted up to 3 times. set this to 0 or false for non-idempotent queries.
15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
# File 'lib/jetpants/db/client.rb', line 15 def mysql_root_cmd(cmd, ={}) terminator = [:terminator] || '\G' attempts = ([:attempts].nil? ? 3 : ([:attempts].to_i || 1)) schema = ([:schema] == true ? app_schema : [:schema]) failures = 0 begin raise "MySQL is not running" unless running? supply_root_pw = (Jetpants.mysql_root_password ? "-p#{Jetpants.mysql_root_password}" : '') supply_port = (@port == 3306 ? '' : "-h 127.0.0.1 -P #{@port}") real_cmd = %Q{mysql #{supply_root_pw} #{supply_port} -ss -e "#{cmd}#{terminator}" #{schema}} real_cmd.untaint result = ssh_cmd!(real_cmd) raise result if result && result.downcase.start_with?('error ') result = parse_vertical_result(result) if [:parse] && terminator == '\G' return result rescue => ex failures += 1 raise if failures >= attempts output "Root query \"#{cmd}\" failed: #{ex.}, re-trying after delay" sleep 3 * failures retry end end |
#output(str, table = nil) ⇒ Object
Displays the provided output, along with information about the current time, self, and optionally a Jetpants::Table name.
98 99 100 101 102 103 104 105 106 107 |
# File 'lib/jetpants/db.rb', line 98 def output(str, table=nil) str = str.to_s.strip str = nil if str && str.length == 0 str ||= "Completed (no output)" output = Time.now.strftime("%H:%M:%S") + " [#{self}] " output << table.name << ': ' if table output << str print output + "\n" output end |
#parse_vertical_result(text) ⇒ Object
Parses the result of a MySQL query run with a G terminator. Useful when interacting with MySQL via the command-line client (for secure access to the root user) instead of via the MySQL protocol.
130 131 132 133 134 135 136 137 138 139 140 141 |
# File 'lib/jetpants/db/client.rb', line 130 def parse_vertical_result(text) results = {} return results unless text raise text.chomp if text =~ /^ERROR/ lines = text.split("\n") lines.each do |line| col, val = line.split ':' next unless val results[col.strip.downcase.to_sym] = val.strip end results end |
#pause_replication ⇒ Object Also known as: stop_replication
Pauses replication
49 50 51 52 53 54 55 |
# File 'lib/jetpants/db/replication.rb', line 49 def pause_replication raise "This DB object has no master" unless master return if @repl_paused output "Pausing replication from #{@master}." output mysql_root_cmd "STOP SLAVE" @repl_paused = true end |
#pool(create_if_missing = false) ⇒ Object
Returns the Jetpants::Pool that this instance belongs to, if any. Can optionally create an anonymous pool if no pool was found. This anonymous pool intentionally has a blank sync_configuration implementation.
157 158 159 160 161 162 163 164 165 |
# File 'lib/jetpants/db/state.rb', line 157 def pool(create_if_missing=false) result = Jetpants.topology.pool(self) || Jetpants.topology.pool(master) if !result && create_if_missing pool_master = master || self result = Pool.new('anon_pool_' + pool_master.ip.tr('.', ''), pool_master) def result.sync_configuration; end end return result end |
#probe(force = false) ⇒ Object
Probes this instance to discover its status, master, and slaves. Several other methods trigger a probe automatically, including master, slaves, repl_paused?, and running?. Ordinarily this method won’t re-probe an instance that has already been probed, unless you pass force=true. This can be useful if something external to Jetpants has changed a DB’s state while Jetpants is running. For example, if you’re using jetpants console and, for whatever reason, you stop replication on a slave manually outside of Jetpants. In this case you will need to force a probe so that Jetpants learns about the change.
58 59 60 61 62 63 64 65 |
# File 'lib/jetpants/db/state.rb', line 58 def probe(force=false) return if probed? && !force output "Probing MySQL installation" probe_running probe_master probe_slaves self end |
#probe! ⇒ Object
Alias for probe(true)
68 |
# File 'lib/jetpants/db/state.rb', line 68 def probe!() probe(true) end |
#probed? ⇒ Boolean
Returns true if we’ve probed this MySQL instance already. Several methods trigger a probe, including master, slaves, repl_paused?, and running?.
44 45 46 |
# File 'lib/jetpants/db/state.rb', line 44 def probed? [@master, @slaves, @running].compact.count >= 3 end |
#prune_data_to_range(tables, keep_min_id, keep_max_id) ⇒ Object
Cleans up all rows that should no longer be on this db. Supply the ID range (in terms of the table’s sharding key) of rows to KEEP.
196 197 198 199 200 201 202 203 204 |
# File 'lib/jetpants/db/import_export.rb', line 196 def prune_data_to_range(tables, keep_min_id, keep_max_id) reconnect(user: app_credentials[:user]) tables.each do |t| output "Cleaning up data, pruning to only keep range #{keep_min_id}-#{keep_max_id}", t rows_deleted = 0 [:asc, :desc].each {|direction| rows_deleted += delete_table_data_outside_range(t, keep_min_id, keep_max_id, direction)} output "Done cleanup; #{rows_deleted} rows deleted", t end end |
#query(sql, *binds) ⇒ Object
Execute a write (INSERT, UPDATE, DELETE, REPLACE, etc) query. If the query is an INSERT, returns the last insert ID (if an auto_increment column is involved). Otherwise returns the number of affected rows.
107 108 109 110 |
# File 'lib/jetpants/db/client.rb', line 107 def query(sql, *binds) ds = connection.fetch(sql, *binds) connection.execute_dui(ds.update_sql) {|c| return c.last_id > 0 ? c.last_id : c.affected_rows} end |
#query_return_array(sql, *binds) ⇒ Object
Execute a read (SELECT) query. Returns an array of hashes.
113 114 115 |
# File 'lib/jetpants/db/client.rb', line 113 def query_return_array(sql, *binds) connection.fetch(sql, *binds).all end |
#query_return_first(sql, *binds) ⇒ Object
Execute a read (SELECT) query. Returns a hash of the first row only.
118 119 120 |
# File 'lib/jetpants/db/client.rb', line 118 def query_return_first(sql, *binds) connection.fetch(sql, *binds).first end |
#query_return_first_value(sql, *binds) ⇒ Object
Execute a read (SELECT) query. Returns the value of the first column of the first row only.
123 124 125 |
# File 'lib/jetpants/db/client.rb', line 123 def query_return_first_value(sql, *binds) connection.fetch(sql, *binds).single_value end |
#read_only? ⇒ Boolean
Returns true if the global READ_ONLY variable is set, false otherwise.
92 93 94 |
# File 'lib/jetpants/db/state.rb', line 92 def read_only? global_variables[:read_only].downcase == 'on' end |
#rebuild!(tables = false, min_id = false, max_id = false) ⇒ Object
Exports and re-imports data for the specified tables, optionally bounded by the given range. Useful for defragmenting a node. Also useful for doing fast schema alterations, if alter_schemata (or its callbacks) has been implemented.
You can omit all params for a shard, in which case the method will use the list of sharded tables in the Jetpants config file, and will use the shard’s min and max ID.
248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 |
# File 'lib/jetpants/db/import_export.rb', line 248 def rebuild!(tables=false, min_id=false, max_id=false) raise "Cannot rebuild an active node" unless is_standby? || for_backups? p = pool if p.is_a?(Shard) tables ||= Table.from_config 'sharded_tables' min_id ||= p.min_id max_id ||= p.max_id if p.max_id != 'INFINITY' end raise "No tables supplied" unless tables && tables.count > 0 disable_monitoring stop_query_killer disable_binary_logging restart_mysql pause_replication if is_slave? # Automatically detect missing min/max. Assumes that all tables' primary keys # are on the same scale, so this may be non-ideal, but better than just erroring. unless min_id tables.each do |t| my_min = query_return_first_value "SELECT MIN(#{t.sharding_keys[0]}) FROM #{t.name}" min_id = my_min if !min_id || my_min < min_id end end unless max_id @found_max_ids = {} # we store the detected maxes in case DB#alter_schemata needs them later tables.each do |t| my_max = @found_max_ids[t.name] = query_return_first_value("SELECT MAX(#{t.sharding_keys[0]}) FROM #{t.name}") max_id = my_max if !max_id || my_max > max_id end end export_schemata tables export_data tables, min_id, max_id import_schemata! alter_schemata if respond_to? :alter_schemata import_data tables, min_id, max_id resume_replication if is_slave? enable_binary_logging restart_mysql catch_up_to_master start_query_killer enable_monitoring end |
#reconnect(options = {}) ⇒ Object
Disconnects and reconnects to the database.
78 79 80 81 |
# File 'lib/jetpants/db/client.rb', line 78 def reconnect(={}) disconnect # force disconnection even if we're not changing user or schema connect() end |
#repl_binlog_coordinates(display_info = true) ⇒ Object
Use this on a slave to return [master log file name, position] for how far this slave has executed (in terms of its master’s binlogs) in its SQL replication thread.
149 150 151 152 153 154 155 |
# File 'lib/jetpants/db/replication.rb', line 149 def repl_binlog_coordinates(display_info=true) raise "This instance is not a slave" unless master status = slave_status file, pos = status[:relay_master_log_file], status[:exec_master_log_pos].to_i output "Has executed through master's binlog coordinates of (#{file}, #{pos})." if display_info [file, pos] end |
#repl_paused? ⇒ Boolean
Returns true if replication is paused on this instance, false if it isn’t, or nil if this instance isn’t a slave (or if we can’t tell because the instance isn’t running)
27 28 29 30 31 |
# File 'lib/jetpants/db/state.rb', line 27 def repl_paused? return nil unless master probe if @repl_paused.nil? @repl_paused end |
#replicating? ⇒ Boolean
Returns true if the MySQL slave I/O thread and slave SQL thread are both running, false otherwise. Note that this always checks the current actual state of the instance, as opposed to DB#repl_paused? which just remembers the state from the previous probe and any actions since then.
74 75 76 77 |
# File 'lib/jetpants/db/state.rb', line 74 def replicating? status = slave_status [status[:slave_io_running], status[:slave_sql_running]].all? {|s| s && s.downcase == 'yes'} end |
#replication_credentials ⇒ Object
Reads an existing master.info file on this db or one of its slaves, propagates the info back to the Jetpants singleton, and returns it as a hash containing :user and :pass. If the node is not a slave and has no slaves, will use the global Jetpants config instead.
230 231 232 233 234 235 236 237 238 239 240 241 |
# File 'lib/jetpants/db/replication.rb', line 230 def replication_credentials user = false pass = false if master || slaves.count > 0 target = (@master ? self : @slaves[0]) results = target.ssh_cmd("cat #{mysql_directory}/master.info | head -6 | tail -2").split if results.count == 2 && results[0] != 'test' user, pass = results end end user && pass ? {user: user, pass: pass} : Jetpants.replication_credentials end |
#respond_to?(name, include_private = false) ⇒ Boolean
Alters respond_to? logic to account for delegation of missing methods to the instance’s Host.
84 85 86 |
# File 'lib/jetpants/db.rb', line 84 def respond_to?(name, include_private=false) super || @host.respond_to?(name) end |
#restart_mysql ⇒ Object
Restarts MySQL.
32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
# File 'lib/jetpants/db/server.rb', line 32 def restart_mysql @repl_paused = false if @master # Disconnect if we were previously connected user, schema = false, false if @db user, schema = @user, @schema disconnect end output "Attempting to restart MySQL" output service(:restart, 'mysql') confirm_listening @running = true # Reconnect if we were previously connected connect(user: user, schema: schema) if user || schema end |
#resume_replication ⇒ Object Also known as: start_replication
Starts replication, or restarts replication after a pause
59 60 61 62 63 64 |
# File 'lib/jetpants/db/replication.rb', line 59 def resume_replication raise "This DB object has no master" unless master output "Resuming replication from #{@master}." output mysql_root_cmd "START SLAVE" @repl_paused = false end |
#revoke_all_access! ⇒ Object
Disables access to a DB by the application user, and sets the DB to read-only. Useful when decommissioning instances from a shard that’s been split.
72 73 74 75 76 77 |
# File 'lib/jetpants/db/privileges.rb', line 72 def revoke_all_access! user_name = app_credentials[:user] enable_read_only! output "Revoking access for user #{user_name}." output(drop_user(user_name, true)) # drop the user without replicating the drop statement to slaves end |
#revoke_privileges(username = false, database = false, *privileges) ⇒ Object
Revokes privileges from the given username for the specified database. Pass in privileges as additional params, each as strings. You may omit parameters to use the defaults in the Jetpants config file.
48 49 50 |
# File 'lib/jetpants/db/privileges.rb', line 48 def revoke_privileges(username=false, database=false, *privileges) grant_or_revoke_privileges('REVOKE', username, database, privileges) end |
#role ⇒ Object
Determines the DB’s role in its pool. Returns either :master, :active_slave, :standby_slave, or :backup_slave.
Note that we consider a node with no master and no slaves to be a :master, since we can’t determine if it had slaves but they’re just offline/dead, vs it being an orphaned machine.
173 174 175 176 177 178 179 180 181 182 |
# File 'lib/jetpants/db/state.rb', line 173 def role p = pool case when !@master then :master when for_backups? then :backup_slave when p && p.active_slave_weights[self] then :active_slave # if pool in topology, determine based on expected/ideal state when !p && !is_standby? then :active_slave # if pool missing from topology, determine based on actual state else :standby_slave end end |
#row_counts(tables, min_id, max_id) ⇒ Object
Counts rows falling between min_id and max_id for the supplied tables. Returns a hash mapping table names to counts. Note: runs 10 concurrent queries to perform the count quickly. This is MUCH faster than doing a single count, but far more I/O intensive, so don’t use this on a master or active slave.
179 180 181 182 183 184 185 186 187 188 189 190 191 |
# File 'lib/jetpants/db/import_export.rb', line 179 def row_counts(tables, min_id, max_id) lock = Mutex.new row_count = {} tables.each do |t| row_count[t.name] = 0 (min_id..max_id).in_chunks(t.chunks, 10) do |min, max| result = query_return_first_value(t.sql_count_rows(min, max)) lock.synchronize {row_count[t.name] += result} end output "#{row_count[t.name]} rows counted", t end row_count end |
#running? ⇒ Boolean
Returns true if MySQL is running for this instance, false otherwise. Note that if the host isn’t available/online/reachable, we consider MySQL to not be running.
36 37 38 39 |
# File 'lib/jetpants/db/state.rb', line 36 def running? probe if @running.nil? @running end |
#same_host_as?(db) ⇒ Boolean
Returns true if the supplied Jetpants::DB is on the same Jetpants::Host as self.
90 91 92 |
# File 'lib/jetpants/db.rb', line 90 def same_host_as?(db) @ip == db.ip end |
#seconds_behind_master ⇒ Object
Returns the number of seconds beind the master the replication execution is, as reported by SHOW SLAVE STATUS.
170 171 172 173 174 |
# File 'lib/jetpants/db/replication.rb', line 170 def seconds_behind_master raise "This instance is not a slave" unless master lag = slave_status[:seconds_behind_master] lag == 'NULL' ? nil : lag.to_i end |
#slave_status ⇒ Object
Returns a hash containing the information from SHOW SLAVE STATUS
214 215 216 217 218 219 220 221 222 223 |
# File 'lib/jetpants/db/replication.rb', line 214 def slave_status hash = mysql_root_cmd('SHOW SLAVE STATUS', :parse=>true) hash = {} if hash[:master_user] == 'test' if @master && hash.count < 1 = "should be a slave of #{@master}, but SHOW SLAVE STATUS indicates otherwise" raise "#{self}: #{}" if Jetpants.verify_replication output end hash end |
#slaves ⇒ Object
Returns an Array of Jetpants::DB instances that are slaving from this instance, or nil if we can’t tell because this instance isn’t running.
18 19 20 21 22 |
# File 'lib/jetpants/db/state.rb', line 18 def slaves return nil unless running? || @slaves probe if @slaves.nil? @slaves end |
#start_mysql ⇒ Object
Starts MySQL, and confirms that something is now listening on the port. Raises an exception if MySQL is already running or if something else is already running on its port.
21 22 23 24 25 26 27 28 29 |
# File 'lib/jetpants/db/server.rb', line 21 def start_mysql @repl_paused = false if @master running = ssh_cmd "netstat -ln | grep #{@port} | wc -l" raise "[#{@ip}] Failed to start MySQL: Something is already listening on port #{@port}" unless running.chomp == '0' output "Attempting to start MySQL" output service(:start, 'mysql') confirm_listening @running = true end |
#start_query_killer ⇒ Object
Has no built-in effect. Plugins can override it, and/or implement before_start_query_killer and after_start_query_killer callbacks.
58 59 |
# File 'lib/jetpants/db/server.rb', line 58 def start_query_killer end |
#stop_mysql ⇒ Object
Shuts down MySQL, and confirms that it is no longer listening. OK to use this if MySQL is already stopped; it’s a no-op then.
10 11 12 13 14 15 16 |
# File 'lib/jetpants/db/server.rb', line 10 def stop_mysql output "Attempting to shutdown MySQL" output service(:stop, 'mysql') running = ssh_cmd "netstat -ln | grep #{@port} | wc -l" raise "[#{@ip}] Failed to shut down MySQL: Something is still listening on port #{@port}" unless running.chomp == '0' @running = false end |
#stop_query_killer ⇒ Object
Has no built-in effect. Plugins can override it, and/or implement before_stop_query_killer and after_stop_query_killer callbacks.
53 54 |
# File 'lib/jetpants/db/server.rb', line 53 def stop_query_killer end |
#taking_connections?(max = 4, interval = 2.0, threshold = 1) ⇒ Boolean
Confirms instance has no more than [max] connections currently (AS VISIBLE TO THE APP USER), and in [interval] seconds hasn’t received more than [threshold] additional connections. You may need to adjust max if running multiple query killers, monitoring agents, etc.
101 102 103 104 105 106 107 |
# File 'lib/jetpants/db/state.rb', line 101 def taking_connections?(max=4, interval=2.0, threshold=1) current_conns = query_return_array('show processlist').count return true if current_conns > max conn_counter = global_status[:Connections].to_i sleep(interval) global_status[:Connections].to_i - conn_counter > threshold end |
#to_db ⇒ Object
Returns self, since self is already a Jetpants::DB.
120 121 122 |
# File 'lib/jetpants/db.rb', line 120 def to_db self end |
#to_host ⇒ Object
Returns the instance’s Jetpants::Host.
125 126 127 |
# File 'lib/jetpants/db.rb', line 125 def to_host @host end |
#to_s ⇒ Object
Returns a string in the form “ip:port”
115 116 117 |
# File 'lib/jetpants/db.rb', line 115 def to_s "#{@ip}:#{@port}" end |
#version_tuple ⇒ Object
Returns an array of integers representing the version of the MySQL server. For example, Percona Server 5.5.27-rel28.1-log would return [5, 5, 27]
149 150 151 152 |
# File 'lib/jetpants/db/state.rb', line 149 def version_tuple raise "Cannot determine version of a stopped MySQL instance" unless running? global_variables[:version].split('.', 3).map &:to_i end |