{"id":2255,"date":"2018-06-11T19:53:01","date_gmt":"2018-06-11T19:53:01","guid":{"rendered":"http:\/\/galhano.com\/blog\/?p=2255"},"modified":"2018-06-11T19:53:01","modified_gmt":"2018-06-11T19:53:01","slug":"mysql-innodb-backup","status":"publish","type":"post","link":"http:\/\/galhano.com\/blog\/?p=2255","title":{"rendered":"Mysql InnoDB Backup"},"content":{"rendered":"

The key to safe database management is making regular backups. Depending on your data volume, number of MySQL servers, and database workload, you can use these backup techniques, alone or in combination: hot backup<\/a> with MySQL Enterprise Backup; cold backup<\/a> by copying files while the MySQL server is shut down; logical backup<\/a> with mysqldump<\/strong><\/span> for smaller data volumes or to record the structure of schema objects. Hot and cold backups are physical backups<\/a> that copy actual data files, which can be used directly by the mysqld<\/strong><\/span> server for faster restore.<\/p>\n

Using MySQL Enterprise Backup<\/em><\/span> is the recommended method for backing up InnoDB<\/code> data.<\/p>\n

\n
Note<\/div>\n

InnoDB<\/code> does not support databases that are restored using third-party backup tools.<\/p>\n<\/div>\n

<\/a>Hot Backups<\/h2>\n

The mysqlbackup<\/strong><\/span> command, part of the MySQL Enterprise Backup component, lets you back up a running MySQL instance, including InnoDB<\/code> tables, with minimal disruption to operations while producing a consistent snapshot of the database. When mysqlbackup<\/strong><\/span> is copying InnoDB<\/code> tables, reads and writes to InnoDB<\/code> can continue. MySQL Enterprise Backup can also create compressed backup files, and back up subsets of tables and databases. In conjunction with the MySQL binary log, users can perform point-in-time recovery. MySQL Enterprise Backup is part of the MySQL Enterprise subscription. For more details, see MySQL Enterprise Backup Overview<\/a>.<\/p>\n

<\/a>Cold Backups<\/h2>\n

If you can shut down the MySQL server, you can make a physical backup that consists of all files used by InnoDB<\/code> to manage its tables. Use the following procedure:<\/p>\n

\n
    \n
  1. Perform a slow shutdown<\/a> of the MySQL server and make sure that it stops without errors.<\/li>\n
  2. Copy all InnoDB<\/code> data files (ibdata<\/code> files and .ibd<\/code> files) into a safe place.<\/li>\n
  3. Copy all the .frm<\/code> files for InnoDB<\/code> tables to a safe place.<\/li>\n
  4. Copy all InnoDB<\/code> log files (ib_logfile<\/code> files) to a safe place.<\/li>\n
  5. Copy your my.cnf<\/code> configuration file or files to a safe place.<\/li>\n<\/ol>\n<\/div>\n

    <\/a>Logical Backups Using mysqldump<\/h2>\n

    In addition to physical backups, it is recommended that you regularly create logical backups by dumping your tables using mysqldump<\/strong><\/span>. A binary file might be corrupted without you noticing it. Dumped tables are stored into text files that are human-readable, so spotting table corruption becomes easier. Also, because the format is simpler, the chance for serious data corruption is smaller. mysqldump<\/strong><\/span> also has a --single-transaction<\/code><\/a> option for making a consistent snapshot without locking out other clients. See Section\u00a01.3.1, \u201cEstablishing a Backup Policy\u201d<\/a>.<\/p>\n

    Replication works with InnoDB<\/code><\/a> tables, so you can use MySQL replication capabilities to keep a copy of your database at database sites requiring high availability. See InnoDB and MySQL Replication<\/a>.<\/p>\n

     <\/p>\n

    https:\/\/dev.mysql.com\/doc\/mysql-backup-excerpt\/5.5\/en\/innodb-backup.html<\/p>\n","protected":false},"excerpt":{"rendered":"

    The key to safe database management is making regular backups. Depending on your data volume, number of MySQL servers, and database workload, you can use these backup techniques, alone or in combination: hot backup with MySQL Enterprise Backup; cold backup by copying files while the MySQL server is shut down; logical backup with mysqldump for […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"close","ping_status":"close","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[172],"class_list":["post-2255","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-mysql","author-admin"],"_links":{"self":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2255"}],"version-history":[{"count":1,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2255\/revisions"}],"predecessor-version":[{"id":2256,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2255\/revisions\/2256"}],"wp:attachment":[{"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2255"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/galhano.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}