Menu Close

Category: backup

MySQL Online Backup with xtrabackup

My last post about backups was a while ago. Although it is still valid, it’s not advisable to use it as daily backup. For that we can use XtraBackup. With XtraBackup, you can take online backups without interrupt the server workload(it will require a small lock to take binlog coordinates). Today I’ll show how to take a complete backup. Install: To install it, I advise you to use Yum / Apt-get repos: Centos / Redhat: sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm sudo yum install percona-xtrabackup-24 Debian / Ubuntu wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-xtrabackup-24 Backup: To take the backup, we will use innobackupex script: innobackupex –user=root –password=’Passw0rd!’ /backups/ The script will produce some log messages, we need to verify if it ends with . . .

Liked? Help and Share!

MySQL 5.6 Replication with GTID – Global Transaction ID

Hi guys, Early February Oracle released the new version of MySQL named 5.6, one of the enhancements is the GTID (Global Transaction ID) GTID is an unique identifier which will be added at each transaction, and will be very useful on the slave. remember before we needed to set MASTER_LOG_FILE and MASTER_LOG_POS, now we don’t need them anymore. Let’s see some new variables which we need to add to our cnf file: gtid-mode : It will enable GTID, in order to this function work, we need to turn on log-bin and log-slave-updates enforce-gtid-consistency : It will guarantee that only allowed command will be executed ( more information here) Basicly, is only this what we need to enable GTID, for this tutorial I will use 2 virtual machines, black (master – . . .

Liked? Help and Share!

Consistent backup – snapshot

Hi guys, today let’s learn about how to have a consistent backup (snapshot) First of all, in what situations do we use a snapshot? 1. Lets say that your production server now will have a replica, how do you do the first load of data in this slave? what was the master bin log position when you started the backup, during the backup process, does anyone wrote any query to db? 2. In case you want to implement an incremental backup strategy, you can take a snapshot once a week and in case you need to restore you server, you just restore the snapshot and apply the binary logs. Then, let’s start. To grantee this data integrity we will need 2 sessions open on master, first one to lock all . . .

Liked? Help and Share!