Sign in Subscribe
Engineering👨‍💻

Migrate mysql with minimal downtime

Srujan

• 1 min read
  1. Login to existing mysql server.
  2. Change the configuration /etc/my.cnf, add the following server-id=1, log-bin=mysql-bin
  3. To make sure your configuration is correct, run mysqld --verbose
  4. For the mysql process to pick up these, you need to run
    sudo service mysql restart and verify with mysqladmin variables.
  5. Take a consistent snapshot of mysql. Keep a note of these values -- master_log_file, master_log_pos
  6. Start a new server with this volume (from snapshot).
  7. Edit /etc/my.cnf with server-id=2
  8. Restart mysql
  9. On cli,
 STOP SLAVE;
 CHANGE MASTER TO
 MASTER_HOST='<ip-address>',
 MASTER_USER='<username>',
 MASTER_PASSWORD='<password>',
 MASTER_LOG_FILE='<value of master_log_file>',
 MASTER_LOG_POS= <value of master_log_pos>;
 START SLAVE;
  1. show slave status will show the lag between master and slave. Make sure they're in sync.
  2. Point your application to slave.
  3. Once writes are coming to slave, and master is idle
    On slave cli: stop slave; reset slave;
  4. Terminate your master. New slave is now a standalone mysql server.