Mysql
- Main mysql runs on morpheus (mysql.internal, 192.168.0.76).
- There are mysql slaves on thunder and severus, which used to be backed up by dirvish up nightly.
Moving Mysql
How to move mysql:
- apt-get install mysql-server
- copy /etc/mysql/debian.cnf to the new machine
- copy /var/lib/mysql to the new machine
- set the server id and bin logs in /etc/mysql/my.cnf
- move mysql.internal in DNS
- start the new server
Fixing Replication
Mysql is shit. If replication breaks you'll probably need to re-sync it with the master. This is a pain.
Step 1: Make a snapshot
This step requires you stopping the mysql master server, affecting user websites etc. Obviously, the objective should be to get this step done as quickly as possible - it should take less than 10 minutes on metharme. Don't bother with anything that would slow this step down - compression, writing to nfs mounts, etc. Once this is done you won't have to stop the master again, so you can take your time with all the other steps.
- login to the mysql master as root
mysql> flush tables with read lock;
mysql> show master status\G
- Save the output at the bottom of this page. This means that someone else can
use your tar to setup a slave within in the next
$BINLOG_EXPIRY_TIME
days. Without the file name and position your tar is relatively useless. - CTRL+Z that mysql shell, and
/etc/init.d/mysql stop
cd /var/lib && tar -cvvf mysql-$(date +%Y%m%d).tar mysql
- Plan how your going to hurt robin as his 2GB table holds you up
- Start mysql again.
Alternate Step 1: Make a snapshot from another slave server
Since we have multiple slave servers it's possible to use a snapshot of one to start the other. The process is pretty similar to taking a snapshot of the master.
- Login to the running slave as root
mysql> stop slave;
mysql> flush tables with read lock;
mysql> show slave status\G
- Save the output at the bottom of this page. This means that someone else can
use your tar to setup a slave within in the next
$BINLOG_EXPIRY_TIME
days. Without the file name and position your tar is relatively useless. - CTRL+Z that mysql shell, and
/etc/init.d/mysql stop
cd /var/lib && rm mysql/dumps/*.sql && tar -cvvf mysql-$(date +%Y%m%d).tar mysql
- plan how your going to hurt robin as his 2GB table holds you up
- start mysql again.
Step 2: Move tar to the backup server
- copy the tar to the slave server
- while it's copying check that
/etc/mysql/debian.cnf
on the slave matches the one on the server. The init scripts will break if the passwords in this file on the slave don't match the passwords in the db you're importing. - Stop the mysql slave server
- untar this into
/var/lib/mysql
- Plan how your going to hurt robin as his 2GB table holds you up AGAIN
- If necessary, fix the file ownership
- Ensure that the directory
/var/lib/mysql/dumps/
exists - Start the mysql slave server
Step 3: Fix stuff on the new slave database
- If this wasn't previously a slave server, set server-id in my.cnf, and restart. This server id must be unique.
- Login to the slave mysql as root
mysql> stop slave;
mysql> change master to master_host='mysql.internal', master_user='replication', master_password='passsword', master_log_file='mysql-bin.xxxxxx', master_log_pos=1234567;
mysql> start slave;
mysql> show slave status\G
- Hopefully the status output will look normal. If it doesn't, cry.
Step 4: Backup user
- The backup user needs to exist on all slaves for the dirvish scripts to
operate correctly. The username and password are specified in
/etc/mysql/backup.conf
. If the db was copied from another slave you can probably skip this step, otherwise it needs to be done now. mysql > grant select, reload, lock tables, show databases, show view,
replication client on *.* to 'backup'@'localhost' identified by 'the_password'
Notes
- The
master_log_file
andmaster_log_pos
are the values that you carefully noted from the master at the beginning. If you've lost these you can start all over again ;) - The replication user password is in pwsafe
- The password for the backup user needs to match the password specified in
/etc/mysql/backup.conf
- If this machine wasn't a mysql slave before install
redbrick-mysqlslave,logwatch-mysqlslave
Tar
mysql-20140308.tar
File: mysql-bin.001721 Position: 88417402