· 8 years ago · May 12, 2018, 04:32 PM
1namespace :db do
2 # Read from database.yml to create or drop database user for your project.
3 # At the moment it only works for MySQL database.
4 # SELECT * FROM mysql.user \G statement to find out MySQL users status
5
6
7 desc "Generates a dump of the prod db and stores an obfuscated and unobfuscated copy on the filesystem."
8 task :dump do
9 include DbRakeTasks
10
11 run "mkdir -p #{backup_path}"
12 config = db_config[dump_env]
13
14 # Performing a dump from the replica db is much faster than from the master db
15 # The -c option is required by the obfuscate script.
16 mysql_dump_cmd = "mysqldump -c --add-drop-table -u #{config['username']} -h #{config['host'].gsub("-master", "-replica")} -p#{config["password"]} #{config['database']}"
17
18 raw_dump_file = "#{backup_path}/raw_prod_dump.sql"
19 run "#{mysql_dump_cmd} > #{raw_dump_file}"
20 gzip_command = "gzip < #{raw_dump_file} > #{local_unobfuscated_dump_file_name}"
21 run gzip_command
22
23 raw_obfuscated_dump_file = "#{backup_path}/raw_prod_dump.obfuscated.sql"
24 run "cat #{raw_dump_file} | #{obfuscate_script_cmd} > #{raw_obfuscated_dump_file}"
25 gzip_command = "gzip < #{raw_obfuscated_dump_file} > #{local_obfuscated_dump_file_name}"
26 run gzip_command
27
28 run "rm #{raw_dump_file}"
29 run "rm #{raw_obfuscated_dump_file}"
30 end
31
32
33 desc "Restores the honk database from s3"
34 task :restore_from_local_dumpfile => :environment do
35 include DbRakeTasks
36 raise "Cannot restore DB into the production environment" if RAILS_ENV == "production"
37 puts "Restoring from: #{downloaded_dump_file_path}"
38 if File.exists?(downloaded_dump_file_path)
39 begin
40 ActiveRecord::Base.connection.execute("SET FOREIGN_KEY_CHECKS = 0;")
41 ActiveRecord::Base.connection.execute("SHOW FULL TABLES WHERE TABLE_TYPE='VIEW'").each do |row|
42 ActiveRecord::Base.connection.execute "drop view #{row[0]}"
43 end
44
45 ActiveRecord::Base.connection.tables.each do |table|
46 ActiveRecord::Base.connection.drop_table(table)
47 end
48 restore_cmd = "gunzip -c #{downloaded_dump_file_path} | mysql -u #{dbuser} -p#{dbpass} -h #{dbhost} #{dbname}"
49 run restore_cmd
50 ensure
51 ActiveRecord::Base.connection.execute("SET FOREIGN_KEY_CHECKS = 1;")
52 end
53 else
54 puts "File doesn't exist - the S3 download must have failed or the file must not be on S3!"
55 end
56 end
57end
58
59# (P) means it's a Pivotal addition. (2) means it's new with Rails 2 and thus we will backport it to work in 1.2.
60class DbTasks
61 class DoesNotSupportRollingRestartError < RuntimeError
62 end
63
64 def initialize(rake)
65 @rake = rake
66 end
67
68 # (P) db:init - deprecated
69 def init
70 init_with_environment('development')
71 load_dbs(Honk.test_environments)
72 end
73
74 def init_suite
75 init_with_environment('development')
76 load_dbs(Honk.test_suite_environments)
77 end
78
79 # (P) db:init_with_environment - deprecated
80 def init_with_environment(environment)
81 if environment == "development" && !ENV["IS_CI_BOX"] && !ENV["FORCE_DB_INIT"]
82 raise "This will clear the development database. If you wish to clear the development anyway, set the FORCE_DB_INIT environment variable to true"
83 end
84 connect_to(environment)
85 clear_database
86 migrate_database
87 dump
88 end
89
90 def load_suite_dbs
91 load_dbs(Honk.test_suite_environments)
92 end
93
94 def load_dbs(environments)
95 environments.each do |env|
96 database_name = "honk_#{env}"
97 direct_db_connect(
98 :adapter => "mysql",
99 :host => dbhost,
100 :username => dbuser,
101 :password => dbpass,
102 :database => database_name
103 )
104 clear_database
105 load
106 end
107 end
108
109 # (P) db:clear -> drop and create db for RAILS_ENV
110 def clear
111 clear_database
112 end
113
114 # (P) db:setup -> drop, create, and migrate dbs for test and development environments, and import fixtures into development
115 def setup
116 init
117 connect_to 'development'
118 load_fixtures
119 end
120
121 def dump(file = "#{RAILS_ROOT}/db/#{environment}_dump.sql")
122 puts "Dumping #{database} into #{file}"
123 system "mysqldump #{mysql_command_line_connection_options} --default-character-set=utf8 > #{file}"
124 end
125
126 def load(sql_file = "#{RAILS_ROOT}/db/development_dump.sql")
127 puts "Loading #{sql_file} into #{database}"
128 system "mysql #{mysql_command_line_connection_options} --default-character-set=utf8 < #{sql_file}"
129 end
130
131 # (P) db:delete_data - deprecated
132 def delete_data(environment)
133 connect_to environment
134 puts "Initializing #{environment} database"
135 tables_data = `mysql #{mysql_command_line_connection_options} -e "show tables;"`
136 tables = tables_data.split("\n")[1..-1]
137 tables.each do |table|
138 execute "mysql #{mysql_command_line_connection_options} -e 'TRUNCATE TABLE #{table};'"
139 end
140 end
141
142 def migrate_database
143 puts "Migrating #{environment} database"
144 ActiveRecord::Migration.verbose = false
145 Rake::Task["db:migrate"].invoke
146 end
147
148 def supports_rolling_restart
149 ActiveRecord::Migrator.new(:up, "#{RAILS_ROOT}/db/migrate", nil).pending_migrations.each do |pending_migration|
150 unless pending_migration.supports_rolling_restart?
151 raise(
152 DoesNotSupportRollingRestartError,
153 "Migration #{pending_migration.name} - #{pending_migration.version} in #{pending_migration.filename} does not support rolling restarts. To make it support rolling restarts, call `supports_rolling_restart` in the migration and test it."
154 )
155 end
156 end
157 end
158
159 protected
160
161 def connect_to(environment)
162 ActiveRecord::Base.establish_connection(environment)
163 @environment = environment
164 Object.const_set(:RAILS_ENV, environment)
165 # Note: don't set ENV['RAILS_ENV'] since that gets passed down to invoked tasks (including 'rake test')
166 end
167
168 def environment
169 (@environment ||= RAILS_ENV)
170 end
171
172 def load_fixtures
173 puts "Loading fixtures into #{environment}"
174 Rake::Task["db:fixtures:load"].invoke
175 end
176
177 def clear_database
178 puts "Clearing #{database} database"
179 sql = "SET FOREIGN_KEY_CHECKS = 0; drop database if exists #{database}; create database #{database} character set utf8;"
180 cmd = %Q|mysql #{mysql_command_line_connection_options} -e "#{sql}"|
181 # puts "executing #{cmd.inspect}"
182 system(cmd)
183 end
184
185 def config(env = environment)
186 ActiveRecord::Base.configurations[env]
187 end
188
189 def query(sql)
190 ActiveRecord::Base.connection.execute(sql)
191 end
192
193 def direct_db_connect(db_config)
194 ActiveRecord::Base.establish_connection(db_config)
195 self.database = db_config[:database]
196 end
197
198 def database=(db_name)
199 @db_name = db_name
200 end
201
202 def database
203 @db_name || config["database"]
204 end
205
206 def username
207 config["username"]
208 end
209
210 def password
211 config["password"]
212 end
213
214 def hostname
215 config["host"] || 'localhost'
216 end
217
218 def mysql_command_line_connection_options
219 MysqlCommandLineConnectionConfig.call(config.merge(:database => database))
220 end
221
222 def password_parameter
223 if password.nil? || password.empty?
224 ""
225 else
226 "-p#{password}"
227 end
228 end
229
230 def execute(cmd)
231 puts "\t#{cmd}"
232 unless system(cmd)
233 puts "\tFailed with status #{$?.exitstatus}"
234 end
235 end
236
237 def system(cmd)
238 @rake.send(:system, cmd)
239 end
240end
241
242module DbRakeTasks
243 def ask_root_password
244 require 'highline/import'
245 ask("Please type in MySQL root password:") { |question| question.echo = false}
246 end
247
248 def dbname
249 db_config[RAILS_ENV]["database"]
250 end
251
252 def dbhost
253 db_config[RAILS_ENV]["host"]
254 end
255
256 def dbuser
257 db_config[RAILS_ENV]["username"]
258 end
259
260 def dbpass
261 db_config[RAILS_ENV]["password"]
262 end
263
264 def dump_env
265 ENV['DUMP_ENV'] || "production"
266 end
267
268 def backup_file_name_on_s3
269 ENV["BACKUP_FILE_NAME_ON_S3"] || "#{dump_env}_current_backup.gz"
270 end
271
272 def backup_path
273 "#{Dir.pwd}/../../db_backups"
274 end
275
276 def local_obfuscated_dump_file_name
277 "#{backup_path}/current_obfuscated_backup.sql.gz"
278 end
279
280 def local_unobfuscated_dump_file_name
281 "#{backup_path}/current_unobfuscated_backup.sql.gz"
282 end
283
284 def downloaded_dump_file_path
285 ENV["DOWNLOAD_DUMP_FILE_PATH"] || File.expand_path("~/local_s3_dump_file.gz")
286 end
287
288 def obfuscate_script_cmd
289 "ruby #{Dir.pwd}/script/obfuscate_prod_dump"
290 end
291
292 def db_config
293 YAML.load(ERB.new(File.read("#{Dir.pwd}/config/database.yml")).result)
294 end
295
296 def execute(statements, root_password)
297 if !statements.empty?
298 system "mysql -uroot -p#{root_password} -e \"#{statements}\""
299 if $? == 0
300 puts statements.join("\n")
301 puts "\nDone.\n"
302 end
303 else
304 puts "Nothing to do.\n"
305 end
306 end
307
308 def tasks
309 (@db_tasks ||= DbTasks.new(self))
310 end
311
312end