Module: Repctl::Commands

Includes:
FileUtils, Config, Servers
Included in:
Helpers
Defined in:
lib/repctl/mysql_admin.rb

Instance Method Summary collapse

Methods included from Servers

#all_instances, #all_live_instances, #all_live_servers, #all_servers, #get_mysqld_pid, #instance_for, #live?, #mysqld_running?, #server_for_instance

Instance Method Details

#do_admin(instance, operation) ⇒ Object

pid = fork

    unless pid
      # We're in the child.
      puts "Starting instance #{instance} with PID #{Process.pid}."
      server = server_for_instance(instance)

      exec(["#{MYSQL_HOME}/bin/mysqld", "mysqld"], 
        "--defaults-file=#{server['defaults-file']}",
        "--datadir=#{server['datadir']}",
        "--port=#{server['port']}",
        "--server-id=#{server['server-id']}",
        "--innodb_data_home_dir=#{server['innodb_data_home_dir']}",
        "--innodb_log_group_home_dir=#{server['innodb_log_group_home_dir']}",
        "--relay-log=#{Socket.gethostname}-relay-bin",
        "--socket=#{server['socket']}",
        "--user=#{server['user']}")
      Process.detach(pid)
    end
end



158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
 
# File 'lib/repctl/mysql_admin.rb', line 158

def do_admin(instance, operation)
  server = server_for_instance(instance)

  cmd = "#{MYSQL_HOME}/bin/mysqladmin " +
    "--defaults-file=#{server['defaults-file']} " +
    "--user=root " +
    "--host=#{server['hostname']} " +
    "--port=#{server['port']} " +
    "--password=#{ROOT_PASSWORD} " +
    operation 
  
  pid = get_mysqld_pid(instance)
  if pid
    puts "Running #{operation} on instance #{instance} with pid #{pid}."
    run_cmd(cmd, false)
  else
    puts "Instance #{instance} is not running." 
  end
end

#do_change_master(master, slave, coordinates, opts = {}) ⇒ Object

From dev.mysql.com/doc/refman/5.0/en/lock-tables.html:

For a filesystem snapshot of innodb, we find that setting innodb_max_dirty_pages_pct to zero; doing a ‘flush tables with readlock’; and then waiting for the innodb state to reach ‘Main thread process no. d+, id d+, state: waiting for server activity’ is sufficient to quiesce innodb.

You will also need to issue a slave stop if you’re backing up a slave whose relay logs are being written to its data directory.

select @@innodb_max_dirty_pages_pct; flush tables with read lock; show master status; …freeze filesystem; do backup… set global innodb_max_dirty_pages_pct = 75;



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
 
# File 'lib/repctl/mysql_admin.rb', line 258

def do_change_master(master, slave, coordinates, opts = {})
  master_server = server_for_instance(master)
  raise "master_server is nil" unless master_server
      
  begin
    slave_connection = Client.open(slave)
    if slave_connection
      
      # Replication on the slave can't be running if we want to
      # execute CHANGE MASTER TO.  
      slave_connection.query("STOP SLAVE") rescue Mysql2::Error
      
      cmd = <<-EOT
CHANGE MASTER TO
  MASTER_HOST = \'#{master_server['hostname']}\',
  MASTER_PORT = #{master_server['port']},
  MASTER_USER = \'#{REPLICATION_USER}\',
  MASTER_PASSWORD = \'#{REPLICATION_PASSWORD}\',
  MASTER_LOG_FILE = \'#{coordinates[:file]}\',
  MASTER_LOG_POS = #{coordinates[:position]}
EOT
      puts "Executing: #{cmd}"
      slave_connection.query(cmd)
    else
      puts "do_change_master: Could not connnect to MySQL server."
    end
  rescue Mysql2::Error => e
      puts e.message
  ensure
    if slave_connection
      slave_connection.query("START SLAVE") if opts[:restart]
      slave_connection.close 
    end
  end
end

#do_cluster_user(instance) ⇒ Object 



393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
 
# File 'lib/repctl/mysql_admin.rb', line 393

def do_cluster_user(instance)
  client = Client.open(instance)
 
  cmd = "DROP USER \'cluster\'@\'localhost\'"
  client.query(cmd) rescue Mysql2::Error
 
  cmd = "DROP USER \'cluster\'@\'%\'"
  client.query(cmd) rescue Mysql2::Error
  
  if client
    cmd = "CREATE USER \'cluster\'@\'localhost\' IDENTIFIED BY \'secret\'"
    puts cmd
    client.query(cmd)
    cmd = "GRANT ALL PRIVILEGES ON *.* TO \'cluster\'@'\localhost\'"
    puts cmd
    client.query(cmd)
    cmd = "CREATE USER \'cluster\'@\'%\' IDENTIFIED BY \'secret\'"
    puts cmd
    client.query(cmd)
    cmd = "GRANT ALL PRIVILEGES ON *.* TO \'cluster\'@\'%\'"
    puts cmd
    client.query(cmd)
  else
    puts "Could not open connection to MySQL instance #{instance}."
  end
rescue Mysql2::Error => e
  puts e.message
  puts e.backtrace
ensure
  client.close if client
end

#do_config(instance) ⇒ Object 



178
179
180
181
182
183
184
185
186
187
188
189
 
# File 'lib/repctl/mysql_admin.rb', line 178

def do_config(instance)
  server = server_for_instance(instance)
  FileUtils.rm_rf(server['datadir'])
  cmd = "./scripts/mysql_install_db " +
    "--defaults-file=#{server['defaults-file']} " +
    "--datadir=#{server['datadir']} " +
    "--server-id=#{server['server-id']} " +
    "--innodb_data_home_dir=#{server['innodb_data_home_dir']} " +
    "--innodb_log_group_home_dir=#{server['innodb_log_group_home_dir']} " +
    "--relay-log=#{Socket.gethostname}-relay-bin" 
 %x( cd #{MYSQL_HOME} && #{cmd} )
end

#do_crash(instance) ⇒ Object 



222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
 
# File 'lib/repctl/mysql_admin.rb', line 222

def do_crash(instance) 
  pid = get_mysqld_pid(instance)
  puts "pid is #{pid}"
  if pid
    puts "Killing mysqld instance #{instance} with PID #{pid}"
    Process.kill("KILL", pid.to_i)
    while get_mysqld_pid(instance)
      puts "in looop"
      sleep 1
    end
    puts "MySQL server instance #{instance.to_i} has been killed."
  else
    puts "MySQL server instance #{instance.to_i} is not running."
  end
end

#do_create_widgets(instance) ⇒ Object

Create the ‘widgets’ database.



426
427
428
429
430
431
432
433
434
435
436
437
438
439
 
# File 'lib/repctl/mysql_admin.rb', line 426

def do_create_widgets(instance)
  client = Client.open(instance)
  if client
    client.query("drop database if exists widgets")
    client.query("create database widgets")
  else
    puts "Could not open connection to MySQL instance #{instance}."
  end
rescue Mysql2::Error => e
  puts e.message
  puts e.backtrace
ensure
  client.close if client
end

#do_dump(instance, dumpfile) ⇒ Object 



294
295
296
297
298
299
300
301
302
303
304
305
306
 
# File 'lib/repctl/mysql_admin.rb', line 294

def do_dump(instance, dumpfile)
  server = server_for_instance(instance)
  coordinates = get_coordinates(instance) do
    cmd = "#{MYSQL_HOME}/bin/mysqldump " +
      "--defaults-file=#{server['defaults-file']} " +
      "--user=root " +
      "--password=#{ROOT_PASSWORD} " +
      "--socket=#{server['socket']} " +
      "--all-databases --lock-all-tables > #{DUMP_DIR}/#{dumpfile}"
    run_cmd(cmd, true)
  end
  coordinates
end

#do_remove_slave(instance) ⇒ Object 



466
467
468
469
470
471
 
# File 'lib/repctl/mysql_admin.rb', line 466

def do_remove_slave(instance)
  stop_slave_io_thread(instance)
  server = server_for_instance(instance)
  datadir = server['datadir']
  %x{ rm -f #{File.join(datadir, 'master.info')} } 
end

#do_repl_user(instance) ⇒ Object 



367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
 
# File 'lib/repctl/mysql_admin.rb', line 367

def do_repl_user(instance)
  hostname = "127.0.0.1"
  client = Client.open(instance)
  cmd = "DROP USER \'#{REPLICATION_USER}\'@\'#{hostname}\'"
  client.query(cmd) rescue Mysql2::Error
  
  if client
    # "CREATE USER \'#{REPLICATION_USER\'@'%.thirdmode.com' IDENTIFIED BY \'#{REPLICATION_PASSWORD}\'"
    # "GRANT REPLICATION SLAVE ON *.* TO \'#{REPLICATON_USER}\'@\'%.#{REPLICATION_DOMAIN}\'"
    cmd = "CREATE USER \'#{REPLICATION_USER}\'@\'#{hostname}\' IDENTIFIED BY \'#{REPLICATION_PASSWORD}\'"
    puts cmd
    client.query(cmd)
    cmd = "GRANT REPLICATION SLAVE ON *.* TO \'#{REPLICATION_USER}\'@\'#{hostname}\'"
    puts cmd
    client.query(cmd)
    client.query("FLUSH PRIVILEGES")
  else
    puts "Could not open connection to MySQL instance #{instance}."
  end
rescue Mysql2::Error => e
  puts e.message
  puts e.backtrace
ensure
  client.close if client
end

#do_restore(instance, dumpfile) ⇒ Object 



308
309
310
311
312
313
314
315
316
317
318
319
 
# File 'lib/repctl/mysql_admin.rb', line 308

def do_restore(instance, dumpfile)
  server = server_for_instance(instance)

  # Assumes that the instance is running, but is not acting as a slave.
  cmd = "#{MYSQL_HOME}/bin/mysql " +
    "--defaults-file=#{server['defaults-file']} " +
    "--user=root " +
    "--password=#{ROOT_PASSWORD} " +
    "--socket=#{server['socket']} " +
    "< #{DUMP_DIR}/#{dumpfile}"
  run_cmd(cmd, true)
end

#do_secure_accounts(instance) ⇒ Object

Public methods are:

do_secure_accounts
do_start
do_admin
do_config
do_crash
do_change_master
do_dump
do_restore
do_repl_user
do_cluster_user
do_create_widgets
do_switch_master
do_stop_slave
get_coordinates
get_mysqld_pid
get_slave_status
run_mysql_query



90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/repctl/mysql_admin.rb', line 90

def do_secure_accounts(instance)
  client = Client.open(instance, :no_password => true)
  q1 = "UPDATE mysql.user SET Password = PASSWORD(\'#{ROOT_PASSWORD}\') where User = \'root\'"
  q2 = "UPDATE mysql.user SET Password = PASSWORD(\'#{ROOT_PASSWORD}\') where User = \'\'"
  # q3 = "CREATE USER \'root\'@\'%.#{REPLICATION_DOMAIN}\' IDENTIFIED BY \'#{ROOT_PASSWORD}\'"
  # For testing with clients whose DHCP assigned IP address is not in DNS.
  q3 = "CREATE USER \'root\'@\'%' IDENTIFIED BY \'#{ROOT_PASSWORD}\'"
  q4 = "GRANT ALL PRIVILEGES ON *.* to \'root\'@\'%\' WITH GRANT OPTION"
  q5 = "FLUSH PRIVILEGES"
  if client
    [q1, q2, q3, q4, q5].each do |query|
      puts query
      client.query(query)
    end
  end
rescue Mysql2::Error => e
  puts e.message
ensure
  client.close if client
end

#do_start(instance) ⇒ Object 



111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
# File 'lib/repctl/mysql_admin.rb', line 111

def do_start(instance)
  pid = get_mysqld_pid(instance)
  if pid
    puts "Instance #{instance} with PID #{pid} is already running."
  else 
    server = server_for_instance(instance)
    puts "Starting instance #{instance} with PID #{Process.pid}."
    pid = spawn(["#{MYSQL_HOME}/bin/mysqld", "mysqld"], 
      "--defaults-file=#{server['defaults-file']}",
      "--datadir=#{server['datadir']}",
      "--port=#{server['port']}",
      "--server-id=#{server['server-id']}",
      "--innodb_data_home_dir=#{server['innodb_data_home_dir']}",
      "--innodb_log_group_home_dir=#{server['innodb_log_group_home_dir']}",
      "--relay-log=#{Socket.gethostname}-relay-bin",
      "--socket=#{server['socket']}",
      "--user=#{server['user']}")
    Process.detach(pid)
    unless live?(instance)
      puts "Waiting for server..."
      sleep(1)
    end
  end
end

#do_switch_master(master, slaves) ⇒ Object

‘master’ is currently a slave that is to be the new master. ‘slaves’ contains the list of slaves, one of these may be the current master.



444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
 
# File 'lib/repctl/mysql_admin.rb', line 444

def do_switch_master(master, slaves)
  master = master.to_i
  slaves = slaves.map(&:to_i)

  # Step 1. Make sure all slaves have completely processed their
  # Relay Log.
  slaves.each do |s|
    # This will also stop the slave threads.
    drain_relay_log(s) if is_slave?(s)
  end
  
  # Step 2. For the slave being promoted to master, issue STOP SLAVE
  # and RESET MASTER.  Get the coordinates of its binlog.
  promote_slave_to_master(master)
  coordinates = get_coordinates(master)

  # Step 3.  Change the master for the other slaves.
  slaves.each do |s|
    do_change_master(master, s, coordinates, :restart => true)
  end
end

#get_coordinates(instance) ⇒ Object

Get the master coordinates from a MySQL instance. Optionally, run a block while holding the READ LOCK.



323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
 
# File 'lib/repctl/mysql_admin.rb', line 323

def get_coordinates(instance)
  instance ||= DEFAULT_MASTER
  locked = false
  client = Client.open(instance)
  if client
    client.query("FLUSH TABLES WITH READ LOCK")
    locked = true
    results = client.query("SHOW MASTER STATUS")
    row = results.first
    coordinates = if row
      {:file => row["File"], :position => row["Position"]}
    else
      {}
    end
    yield coordinates if block_given?
    # You could copy data from the master to the slave at this point
  end
  coordinates
rescue Mysql2::Error => e
  puts e.message
  # puts e.backtrace
ensure
  if client
    client.query("UNLOCK TABLES") if locked
    client.close
  end
  # coordinates
end

#get_slave_status(instance) ⇒ Object

Treat the instance as a slave and process the output of “SHOW SLAVE STATUS”.



195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
 
# File 'lib/repctl/mysql_admin.rb', line 195

def get_slave_status(instance)
  keys = [
    "Instance",
    "Error",
    "Slave_IO_State",
    "Slave_IO_Running",
    "Slave_SQL_Running",
    "Last_IO_Error",
    "Last_SQL_Error",
    "Seconds_Behind_Master",
    "Master_Log_File",
    "Read_Master_Log_Pos",
    "Relay_Master_Log_File",
    "Exec_Master_Log_Pos",
    "Relay_Log_File",
    "Relay_Log_Pos",
    "Master_Host",
    "Master_Port"
  ]
  results = {}
  status = do_slave_status(instance)
  keys.each do |k|
    results.merge!(k => status[k]) if (status[k] and status[k] != "")
  end
  results
end

#run_mysql_query(instance, cmd) ⇒ Object 



352
353
354
355
356
357
358
359
360
361
362
363
364
365
 
# File 'lib/repctl/mysql_admin.rb', line 352

def run_mysql_query(instance, cmd)
  client = Client.open(instance)
  if client 
    results = client.query(cmd)
  else
    puts "Could not open connection to MySQL instance."
  end
  results
rescue Mysql2::Error => e
  puts e.message
  puts e.backtrace
ensure
  client.close if client
end