MySQL experience: New master from existing master

This is more a reference of what was done than a tutorial or anything else. We had our own quirks and issues that hopefully you won't!

Let's do this!

What we want to accomplish
We want to make MrMaster the new MySQL Master, without interrupting/breaking services that rely on OldMaster.

What we have

Three servers:

  • OldMaster - CentOS Super.Old
  • MrMaster - Ubuntu Server 14.04
  • MrMaster-Slave - Ubuntu Server 14.04

Things in our way

OldMaster is running MySql Server version 5.0.95. MrMaster is running MySql Server Version 5.6.19.

This gives us an incompatible mysql.user table, so it's not as straight forward to import the users and permissions. See the differences and an upgrade script..

Our steps

  • Dump OldMaster without interruption
  • Import OldMaster users/permissions to MrMaster
  • Import OldMaster quickly to MrMaster
  • Get MrMaster up to speed
  • Disable OldMaster
  • Make MrMaster the new Master (not today)
  • Setup MrMaster-Slave (not today)

Dump OldMaster without interruption

I used mysqldump for this so I could get the master data, and use --single-transaction so I get consistent data without overloading/locking the database.

mydumper is very nice and fast, but it locks the tables which effects every user of our services.

[email protected]:/backups/$ mkdir /backups/
[email protected]:/backups/$ /scripts/backup-oldmaster

Import OldMaster users/permissions to MrMaster

Using the script from here - easy peasy (thanks to snoyes/mgriffin)

Import OldMaster quickly to MrMaster

cd $dumpfolder

for SQL in *.sql; do DB=${SQL/\.sql/}; echo importing $DB; mysql -uroot —password=cheeseburger $DB < $SQL & done

echo "change master to master_host='', master_user='replica', master_password='password';" | /scripts/db  
echo "start slave;" | /scripts/db  

Get MrMaster up to speed

echo "change master to master_host='replica-host-replace-this', master_user='replica-user-replace-this', master_password='replace-password-replace-this';" | /scripts/db  
echo "start slave;" | /scripts/db  

Scripts used

Some borrowed, some new, some good, some bad, some ugly


#OldMaster's IP
DATE=`date +%Y-%m-%d-%H-%M-%S`  


# We do this so we can ignore the incompatible databases
DATABASES=`echo "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema');" | mys  
ql -h $HOST -u $USER --password="$PASS" --skip-column-names --skip-secure-auth`

# Get the structure
time mysqldump -h $HOST -u $USER --password="$PASS" --skip-secure-auth --databases $DATABASES --no-data --single-transaction > "$MYSQLDUMP_DIR/db-structure.sql"

# Get the data
time mysqldump -h $HOST -u $USER --password="$PASS" --skip-secure-auth --databases $DATABASES --single-transaction --flush-logs --master-data=1 --quick --trig  
gers > "$MYSQLDUMP_DIR/db-data.sql"  

/scripts/db - this has to have the right permissions so randomers can't execute/read it

mysql -uroot -pcheeseburger $1  
chown root:root /scripts/db  
chmod 100 /scripts/db  

Used while waiting for MrMaster to catch up - I like this one

watch -n 0.5 "echo 'show slave status\G' | /scripts/db | grep -v 'Ignore\|SSL\|Auto_P\|Until\|Last_IO\|Gtid' | tee /tmp/slave-status; grep Behind /tmp/slave-status | awk -F: '{print \$2/3600\" hours behind\"}'"  


Restart anew, something went askew/wrong with the installation/setup/config/data.

apt-get -y remove --purge mysql-server*; rm -rf /var/lib/mysql; apt-get -y install mysql-server-5.6

Get most recent backup directory:

dumpfolder=`ls -Art /backups/ | grep mysqldump | tail -n 1`  
cd $dumpfolder  
time /scripts/ < db-data.sql # this will take around 15 minutes, and will create a .sql file for every DB  

Final thoughts

If you don't have to dump from a live DB, or your data isn't huge, or your'e using MyISAM (not INNODB) then use myloader/mydumper!