On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,620 Users
  • 2,269,768 Discussions
  • 7,916,797 Comments

Discussions

Query re. slave creation from physical backup

Cobert
Cobert Member Posts: 564 Silver Badge
edited May 11, 2020 4:26AM in MySQL Community Space

Hello,

Hoping all are well here on the forum.

I'm familiar with creating a slave database copy using mysqldump from master, including changing of IDs etc.

I'm now trying to create a slave using conventional file copy, NOT using mysql dump (for reasons of volume).

The creation of slaves using physical backups of mysql databases is not extensively covered unless you are using Xtrabackup or another tool. After creating a cold backup of my MYSQL primary database, and then restoring the databases under mysql matador that I wished, I started slave processes, but received an error concerning the file enumeration

table space id and flags are x and y, but in the InnoDB dictionary they are xx and yy

I did not restore the ibdata file, in hesitance that this was directly related to the (already setup, parameterised) slave database.

I will now try to restore ibdata also, but any advise on restores? I do not wish to blanket overwrite the config on the slave database. Sometimes it gets out of sync and we need to restore it.

Thanks in advance,

Cobert.

Dave Stokes-MySQL Community Team-Oracle

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 374 Employee
    edited Apr 7, 2020 10:47AM

    If you are using the transportable tables spaces you should be able to simply copy them from instance to instance. 

    Have you considered using the clone plugin which is extremely quick and designed for such cases?

    Dave Stokes

    MySQL Community Manager

    Cobert
  • Cobert
    Cobert Member Posts: 564 Silver Badge
    edited Apr 17, 2020 10:11AM

    Hello Dave,

    This is using 5.7, so unfortunately does not use the clone process. Transportable tablespaces are not ideal, as there are hundreds of files.


    The database is about 1TB, so takes days for logical import.

    I tried copying IBDATA and all replicated databases, but afterwards started getting errors relating to the innodb_table_stats, innodb_index_stats. And although the relay logs increase, exec_master_log_pos does not increase and no errors are apparent in the logfile.

    I would rather just do a cold copy of the database, but I do not see details on creating a slave from a physical copy of master.

    Thanks,

  • Cobert
    Cobert Member Posts: 564 Silver Badge
    edited May 11, 2020 4:26AM

    So my error here.
    Once I overwrote all data (all the data directory, tmp folders etc), with the exception of the cnf file, this works fine. Xtrabackup is excellent for a fast binary copy of the prod db.

    Dave Stokes-MySQL Community Team-Oracle