Tuesday, May 30, 2017

Binary Log recovery using mysqlbinlog tool

Once the full backup and incremental Xtra backup is applied and made ready,
the binary log has to be applied in the sequence.
The last binary log co-ordinates can be found from xtrabackup_binlog_info/ xtrabackup_slave_info in the backup.
mysqlbinlog utility is used to read and apply the binary logs.


Scenario: Recover data upto “2015-05-05 23:30:00”
Binlog co-ordinates:   mysqlbin.002 , 658
Available Binlogs:     mysqlbin.00[2-6]

Full and Incremental backups are applied.

mysqlbinlog  --start-position=658  mysqlbin.002 mysqlbin.003
mysqlbin.004 mysqlbin.005  | mysql –u root –p xxxxx

mysqlbinlog  --stop-datetime=’2015-05-05 23:30:00’ mysqlbin.006 |  mysql –u root –p xxxxx


Note :  To read binary log from remote server --read-from-remote-server can be used in mysqlbinlog utility



  • Restore rest of data from binary log.
    If you have more than one binary log to execute on the MySQL server, the safe method is to process them all using a single connection to the server.
    mysqlbinlog mysql_bin.000001 | mysql -u root -ppassword database_name
mysqlbinlog mysql_bin.000002 | mysql -u root -ppassword database_name
    or:
    mysqlbinlog mysql_bin.000001 mysql_bin.000002 | mysql -u root -ppassword database_name
  • Restore data on basis of time
    mysqlbinlog --start-datetime="2005-04-20 10:01:00" \
            --stop-datetime="2005-04-20 9:59:59" mysql_bin.000001 \
            | mysql -u root -ppassword database_name
  • Restore data on basis of position
    mysqlbinlog --start-position=368315 \
            --stop-position=368312 mysql_bin.000001 \
            | mysql -u root -ppassword database_name
Posted by at No comments:

Ensuring replication consistency


Ensuring replication consistency

 pt-table-checksum http://www.percona.com/doc/percona-toolkit/2.1/pt-table- checksum.html

Checksum master from any node:
pt-table-checksum h=192.168.56.201

Verify no table diffs:
pt-table-checksum h=192.168.56.201 --replicate-check- only 
Create and fix a replication diff
Let’s create some discrepancy in replication.
Run UPDATE query on the slave:
mysql> UPDATE employees.dept_manager SET from_date=CURDATE() WHERE emp_no=110022;

Checksum master again, just this one table:
pt-table-checksum h=192.168.56.201 --tables employees.dept_manager

Fix it by running pt-table-sync on the master:
pt-table-sync --execute --replicate percona.checksums --sync-to- master 192.168.56.202

Re-checksum the table in question:
pt-table-checksum h=192.168.56.201 --tables employees.dept_manager 
Posted by at No comments:

Wednesday, May 24, 2017

Xtrabackup backup / restore

Xtrabackup


What is xtrabackup?

Percona XtraBackup is an open-source hot backup utility for MySQL - based servers that doesn’t lock your database during the backup. It is designed to make backups a seamless procedure without disrupting the performance of the server in a production environment.

Default backup lock is enabled in Percona version MySQL 5.6.16+. It is a lightweight alternative to FLUSH TABLES WITH READ LOCK for both physical and logical backups. Three new statements are now available: LOCK TABLES FOR BACKUP, LOCK BINLOG FOR BACKUP and UNLOCK BINLOG.  Please find detail in this link Backup Locks.

Special cautions

  • Backup folder must be dedicated for backup only.

        In the xtrabackup folders, there should be no any other files or any folders. Because the file or folder time stamps could impact next incremental backup. The reason is
if ($option_incremental_basedir ne '') {
$incremental_basedir = $option_incremental_basedir;
} else {
my @dirs = `ls -t $backup_root`;
my $inc_dir = $dirs[0];
chomp($inc_dir);
$incremental_basedir = File::Spec->catfile($backup_root, $inc_dir);
ls -t is being used to determinate the latest backup folder based modification time. The oldest folder time stamp could be changed due to mistake, in our case is the purging job starts to work and it does not complete since the folder is big.  In this case, this purging folder(the oldest one) could be considered as the latest backup folder and xtrabackup picks up the wrong folder.

  • information_schema is not backed up.

       This means tables create time stamp is lost. The time stamp for all tables are based on when they are restored.


Make backup


Make the backup from master

sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot`
The replication coordinates info is recorded in the file /home/y/var/mysql/data/xtrabackup_binlog_pos_innodb

ROW based replication events format allows to maintain only persistent tables state. Thus master log file and position will be the same even if there are changes in temporary tables, https://dev.mysql.com/doc/refman/5.6/en/replication-rbr-usage.html

Make the backup from slaves (BCP master is considered as slave)

  We are using row based replication, so there is no temporary table opening issue in BCP master.
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot` --slave-info
If you have a big alter table query running, xtrabackup will wait until "alter query" finishes.
mysql>  show processlist;
+------+-----------------+-----------+------+---------+--------+----------------------------------+------------------------------------------------+-----------+---------------+
| Id   | User            | Host      | db   | Command | Time   | State                            | Info                                           | Rows_sent | Rows_examined |
+------+-----------------+-----------+------+---------+--------+----------------------------------+------------------------------------------------+-----------+---------------+
|    1 | event_scheduler | localhost | NULL | Daemon  | 260586 | Waiting on empty queue           | NULL                                           |         0 |             0 |
|  157 | root            | localhost | NULL | Sleep   |      1 |                                  | NULL                                           |         1 |             4 |
|  192 | system user     |           | NULL | Connect | 258744 | Waiting for master to send event | NULL                                           |         0 |             0 |
|  193 | system user     |           | yax  | Connect |   5923 | altering table                   | alter table creative_content drop column stacy |         0 |             0 |
| 5389 | root            | localhost | NULL | Query   |      4 | Waiting for backup lock          | LOCK TABLES FOR BACKUP                         |         0 |             0 |
| 5390 | root            | localhost | NULL | Query   |      0 | init                             | show processlist                               |         0 |             0 |
+------+-----------------+-----------+------+---------+--------+----------------------------------+------------------------------------------------+-----------+---------------+
6 rows in set (0.00 sec)

Make the backup from Multi-Threaded Slave (MTS) without GTID enabled

--slave-info can not be added.
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot` --slave-info
innobackupex: Error: The --slave-info option requires GTID enabled for a multi-threaded slave. at /home/y/bin/innobackupex line 1928.

--safe-slave-backup is optional for MTS without GTID enabled. It leads different mechanism to perform point-time-recovery.
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot` --safe-slave-backup
or
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot`


Make the backup from slaves without lock

Use this option to disable backup lock while the backup is taking. Use it only if ALL your tables are InnoDB and you DO NOT CARE about the binary log position of the backup.
If your backup is used for point-time-recovery, no-lock can not be used at all.
sudo innobackupex --user=root --password=`sudo keydbgetkey mysqlroot` --no-lock

Restore backups


Backup option : --compress --nolock


  • The database backup is compressed.  It could save 2/3 space.
  • no-lock means the xtrabackup does not issue "FLUSH TABLES WITH READ LOCK" to temporarily lock tables to get consistent *frm files.  This means the backup could be inconsistent if some DDL happen during the *frm is being backed up.

Recovery: /mnt/backup/saca/2016-04-07_05-05-01  is full backup   497 GB (compressed)
                /mnt/backup/saca/2016-04-08_05-00-00  is incremental backup  4.7 GB (compressed)

Prepare a empty server, and installed all packages that the original database server owns.

1. copy two backup directories from the backup filer to local server

cp -rp /mnt/backup/saca/2016-04-07_05-05-01 /home/syuan/backup
cp -rp  /mnt/backup/saca/2016-04-08_05-00-00 /home/syuan/backup

2. Decompress the backups first

sudo innobackupex  /home/syuan/backup/2016-04-07_05-05-01  --use-memory=32G  --decompress --parallel=16
sudo innobackupex  /home/syuan/backup/2016-04-08_05-00-00  --use-memory=8G   --decompress --parallel=8

3. Apply logs into backups

sudo innobackupex --apply-log --redo-only  /home/syuan/backup/2016-04-07_05-05-01 --use-memory=32G  --parallel=16
sudo innobackupex --apply-log --redo-only  /home/syuan/backup/2016-04-07_05-05-01  --incremental-dir=/home/syuan/backup/2016-04-08_05-00-00  --use-memory=8G   --parallel=8
sudo innobackupex --apply-log /home/syuan/backup/2016-04-07_05-05-01  --use-memory=32G --parallel=16

4. Move the backup into right database directory

sudo innobackupex --move-back  /home/syuan/backup/2016-04-07_05-05-01

5. Start database

sudo chown -R mysql:mysql /home/y/var/mysql
yinst start mysql_server

6. Post work, change root password

     Go to original database server, get the root password by sudo /home/y/bin/keydbgetkey mysqlroot
$ sudo /home/y/bin/keydbgetkey mysqlroot
Password:
A*********.erC8xQDNnPkxSFI9w--bash-4.1$
Login to database by
mysql -u root -p
paste the password here

\! sudo /home/y/bin/keydbgetkey mysqlroot 
set sql_log_bin=0;
update mysql.user set password=password('abovepassword') where user='root';
flush privileges;

MySQL Point-Time-Recovery


Backup option : --compress --compress-threads=8 --slave-info

The backup is taken in a slave.
slave-info: It prints the binary log position and name of the master server.

The backups
The full backup          /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-12_17-45-01    is 360 GB
Incremental backup  /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-13_16-22-51     is 8.3 GB
Incremental backup  /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-14_09-05-02     is 6.9 GB

Prepare a empty server (bf1-gboothighdb-003.mysql.bf1.yahoo.com), and installed all packages that the original database server owns.

1. Copy three backup directories from the backup filer to local server

cp -rp /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-12_17-45-01   /home/syuan/backup
cp -rp  /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-13_16-22-51  /home/syuan/backup
cp -rp  /mnt/db_backup_nfs/trf_prod/trf_prod_backup/2016-04-14_09-05-02  /home/syuan/backup

2. Decompress the backups first

sudo innobackupex   /home/syuan/backup/2016-04-12_17-45-01  --use-memory=32G  --decompress --parallel=16
sudo innobackupex   /home/syuan/backup/2016-04-13_16-22-51  --use-memory=32G  --decompress --parallel=16
sudo innobackupex   /home/syuan/backup/2016-04-14_09-05-02  --use-memory=32G  --decompress --parallel=16 

3. Stop MySQL if there is mysqld running and remove all related database files.

yinst stop mysql_server
sudo rm -rf /home/y/var/mysql/data/*
sudo rm -rf /home/y/var/mysql/ibdata/*
sudo rm -rf /home/y/var/mysql/iblog/*

4. Apply the logs.

sudo innobackupex --apply-log --redo-only  /home/syuan/backup/2016-04-12_17-45-01 --use-memory=32G  --parallel=16
sudo innobackupex --apply-log --redo-only  /home/syuan/backup/2016-04-12_17-45-01 --incremental-dir=/home/syuan/backup/2016-04-13_16-22-51   --use-memory=32G  --parallel=16
sudo innobackupex --apply-log --redo-only  /home/syuan/backup/2016-04-12_17-45-01 --incremental-dir=/home/syuan/backup/2016-04-14_09-05-02   --use-memory=32G  --parallel=16
sudo innobackupex --apply-log /home/syuan/backup/2016-04-12_17-45-01  --use-memory=32G --parallel=16

5. Move data files back to database folder and start MySQL

sudo innobackupex --move-back  /home/syuan/backup/2016-04-12_17-45-01
sudo chown -R mysql:mysql /home/y/var/mysql
yinst start mysql_server

6. Post work, change root password

     Go to original database server, get the root password by sudo /home/y/bin/keydbgetkey mysqlroot
$ sudo /home/y/bin/keydbgetkey mysqlroot
Password:
A**************w--bash-4.1$

Login to database:

mysql -u root -p
paste the password here
\! sudo /home/y/bin/keydbgetkey mysqlroot 
set sql_log_bin=0;
update mysql.user set password=password('abovepassword') where user='root';
flush privileges;

7. Get its master log file and its position

The xtrabackup_slave_info in /home/y/var/mysql/data  records the master log file name and its position since slave-info option is used in the xtrabackup process.
# cat /home/y/var/mysql/data/xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.004464', MASTER_LOG_POS=49402644

8. Set up the replication if the binary logs are still in the master host.

CHANGE MASTER TO
MASTER_HOST='master-host-name.yahoo.com',
MASTER_USER='replicant',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysqld-bin.004464',
MASTER_LOG_POS=49402644;

START SLAVE;
SHOW SLAVE STATUS\G

9. Set up the replication if the binary logs are NOT in the master host any more.

     1. Get binary logs from the backup filer and unzip them.
     2. Apply all binary logs into database until the last one
   mysqlbinlog --start-position=49402644 mysqld-bin.004464 mysqld-bin.004465 mysqld-bin.004466  .......| mysql -u root -p
    3. For example, the last one mysqld-bin.0044670 is in the master host, so connect to it.
CHANGE MASTER TO
MASTER_HOST='master-host-name.yahoo.com',
MASTER_USER='replicant',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysqld-bin.004470',
MASTER_LOG_POS=120;    #The first pos is always 120

START SLAVE;
SHOW SLAVE STATUS\G

10. Handling events

    It is fine to use slave backup to rebuild new slave. But if this slave is promoted to a master, we need to be careful!
    All events are created in master and they are replicated to all slaves with status SLAVESIDE_DISABLED. If this slave becomes master, we need to enable events.
   Alter event event_name enable;

Backup is taken from BCP master

backup option: slave-info
The reason is xtrabackup implemented backup lock instead of using flush table with read lock to cause the replication stop (safe-slave-backup) or no any changes happened during lock time period.

The replication coordinates info is  in /home/y/var/mysql/data/xtrabackup_slave_info.
For example
[root@bf1-trfdb-001 data]# cat  xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=122719339

In the target slave,
CHANGE MASTER TO
MASTER_HOST='hostname',
MASTER_USER='replicant',
MASTER_PASSWORD='****',
MASTER_LOG_FILE='mysqld-bin.000002',
MASTER_LOG_POS=122719339;

start slave;
Everything is fine.

Backup is taken from Multi-Threaded Slave without GTID enabled

Assume we have full backup( 2016-05-10_21-45-02 ) from MTS without GTID in the new server

1. Apply log

sudo innobackupex --apply-log --use-memory=32G /home/syuan/backup/2016-05-10_21-45-02

2. Move backup file to right database folders.

    Assume mysql running in this server and we do not this old database
  yinst stop mysql_server
  sudo rm -rf /home/y/var/mysql/data/*
  sudo rm -rf /home/y/var/mysql/ibdata/*
  sudo rm -rf /home/y/var/mysql/iblog/*
  sudo innobackupex --move-back /home/syuan/backup/2016-05-10_21-45-02

3. Start mysql instance

  sudo chown -R mysql:mysql /home/y/var/mysql
  yinst start mysql_server
Check mysqld.err to make sure everything is fine.

4. Reset root password.

    The root password is backup server's root password. We need to change it to its own root password.
  • Get password from the backup server
      sudo /home/y/bin/keydbgetkey mysqlroot
            Save the output
  • Change the root password in the new server
       Back to the new server, login to mysql
        mysql -u root -p
       Enter saved password.
mysql> \! sudo /home/y/bin/keydbgetkey mysqlroot    -- Get new password
mysql> set sql_log_bin=0;
mysql> update mysql.user set password=password('new-pasword') where user='root';
mysql> update mysql.user set host='server-host-name' where user='root' and host='backup-server-host';
mysql> flush privileges;

5. Recover database

   Since the backup is taken from MTS without GTID enabled, so we need to do some fix before it connects to the master.

   Assume this backup is taken with --safe-slave-backup enabled.

  • Get all slave worker info.
   select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;
mysql> select id,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;
+----+------------------------------------------------+---------------+-------------------+----------------+
| id | Relay_log_name                                 | Relay_log_pos | Master_log_name   | Master_log_pos |
+----+------------------------------------------------+---------------+-------------------+----------------+
|  1 | /home/y/var/mysql/data/mysqld-relay-bin.000114 |     649715248 | mysqld-bin.000036 |      649715084 |
|  2 | /home/y/var/mysql/data/mysqld-relay-bin.000114 |     651227473 | mysqld-bin.000036 |      651227309 |
|  3 | /home/y/var/mysql/data/mysqld-relay-bin.000114 |     651226653 | mysqld-bin.000036 |      651226489 |
+----+------------------------------------------------+---------------+-------------------+----------------+
3 rows in set (0.00 sec)

  • Fill the holes in the replication server.
       1. If the slave relay log file still exists in the backup server, such as mysqld-relay-bin.000114, copy/paste it into new server /home/y/var/mysql/data
           Add mysqld-relay-bin.000114 in the mysqld-relay-bin.index.
       2. If the slave relay file is purged, then we can get mysql binary log file from its master, such as mysqld-bin.000036,
           1. copy/paste master binary log file to new server /home/y/var/mysql/data
           2. rename this file to mysqld-relay-bin.000001, such as mv mysqld-bin.000036 mysqld-relay-bin.000001
           3. modify the content of mysqld-relay-bin.index, make sure it only contains file mysqld-relay-bin.000001.
           4. update mysql.slave_worker_info set relay_log_name='/home/y/var/mysql/data/mysqld-relay-bin.000001',relay_log_pos=master_log_pos;
mysql> update mysql.slave_worker_info set relay_log_name='/home/y/var/mysql/data/mysqld-relay-bin.000001',relay_log_pos=master_log_pos;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql>  select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;
+----+------------------------------------------------+---------------+-------------------+----------------+
| ID | Relay_log_name                                 | Relay_log_pos | Master_log_name   | Master_log_pos |
+----+------------------------------------------------+---------------+-------------------+----------------+
|  1 | /home/y/var/mysql/data/mysqld-relay-bin.000001 |     649715084 | mysqld-bin.000036 |      649715084 |
|  2 | /home/y/var/mysql/data/mysqld-relay-bin.000001 |     651227309 | mysqld-bin.000036 |      651227309 |
|  3 | /home/y/var/mysql/data/mysqld-relay-bin.000001 |     651226489 | mysqld-bin.000036 |      651226489 |
+----+------------------------------------------------+---------------+-------------------+----------------+
3 rows in set (0.00 sec)

      5. Then start slave to fill all holes.
mysql> START SLAVE UNTIL SQL_AFTER_MTS_GAPS;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>  select id,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;
+----+----------------+---------------+-----------------+----------------+
| id | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos |
+----+----------------+---------------+-----------------+----------------+
|  1 |                |             0 |                 |              0 |
|  2 |                |             0 |                 |              0 |
|  3 |                |             0 |                 |              0 |
+----+----------------+---------------+-----------------+----------------+
3 rows in set (0.00 sec)
  • Get the master log file name and its position
       Show slave status\G
mysql>  show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: gq1-changelogdb-002.mysql.ngd.gq1.yahoo.com
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000036
          Read_Master_Log_Pos: 778970368
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysqld-bin.000036
             Slave_IO_Running: Yes
           .....
             ....
          Exec_Master_Log_Pos: 651227309
              Relay_Log_Space: 127743517
         ...
         ....
         .....
                Auto_Position: 0
1 row in set (0.00 sec)
  • Connect to its master
stop slave;
change master to master_log_file='mysqld-bin.000036',
master_log_pos=651227309;

start slave;

   Assume this backup is taken without --safe-slave-backup enabled.

  • Get all slave worker info.
   select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;
mysql> select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;
+----+------------------------------------------------+---------------+-------------------+----------------+
| ID | Relay_log_name                                 | Relay_log_pos | Master_log_name   | Master_log_pos |
+----+------------------------------------------------+---------------+-------------------+----------------+
|  1 | /home/y/var/mysql/data/mysqld-relay-bin.000187 |     946879583 | mysqld-bin.000059 |      946879419 |
|  2 | /home/y/var/mysql/data/mysqld-relay-bin.000187 |     947931963 | mysqld-bin.000059 |      947931799 |
|  3 | /home/y/var/mysql/data/mysqld-relay-bin.000187 |     947515423 | mysqld-bin.000059 |      947515259 |
+----+------------------------------------------------+---------------+-------------------+----------------+

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: gq1-changelogdb-002.mysql.ngd.gq1.yahoo.com
               ..........
              Master_Log_File: mysqld-bin.000032
          Read_Master_Log_Pos: 64287
               Relay_Log_File: mysqld-relay-bin.000187
                Relay_Log_Pos: 947516238
        Relay_Master_Log_File: mysqld-bin.000059
             Slave_IO_Running: No
            Slave_SQL_Running: No
           ..........
          Exec_Master_Log_Pos: 947516074
              ......
                  Master_UUID: 9c95cb9f-0c9b-11e6-a512-d4ae5276557d
              ......
                Auto_Position: 0
1 row in set (0.00 sec)


Copy mysqld-relay-bin.000187 relay log from the backup server to the new server.
In the new server
cp mysqld-relay-bin.000187 /home/y/var/mysql/data/.
chown mysql:mysql mysqld-relay-bin.000187
chmod 660 mysqld-relay-bin.000187

Edit the file mysqld-relay-bin.index
add /home/y/var/mysql/data/mysqld-relay-bin.000187  into mysqld-relay-bin.index

Make sure relay_log_recovery is set to 0 and slave_parallel_worker=3 (needs to be same as backup server setting)
yinst set mysql_config_multi.relay_log_recovery=0
yinst set mysql_config_multi.slave_parallel_worker=3
yinst restart mysql_server

Login to the MySQL, make sure fill all the holes.
start slave sql_thread until sql_after_mts_gaps;
mysql> start slave sql_thread until sql_after_mts_gaps;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> select ID,Relay_log_name,Relay_log_pos,Master_log_name,Master_log_pos from mysql.slave_worker_info;
+----+----------------+---------------+-----------------+----------------+
| ID | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos |
+----+----------------+---------------+-----------------+----------------+
|  1 |                |             0 |                 |              0 |
|  2 |                |             0 |                 |              0 |
|  3 |                |             0 |                 |              0 |
+----+----------------+---------------+-----------------+----------------+
3 rows in set (0.00 sec)
Connect to its master, it will successfully connect to its master.
start slave;
show slave status\G
Posted by at No comments:
Subscribe to: Posts (Atom)