Multiple DataBases

  • Filter
  • Time
  • Show
Clear All
new posts

  • Multiple DataBases

    Hi All,

    Working in a scenario where we have ~20 databases on a single instance of myql (Percona 5.1), I have 3 servers, a prod, a reporting (replicating from prod) and a test environments. Right now I do dumps of each database and keep a copy of it from the reporting server and keep the dumps for a week. From time to time, I have to copy the data from prod to the test server. so I zcat the compress dump file and load it into the test.

    This process works fine, but I am thinking that using XtraBackup would work better.

    So my questions are these: Can I do something similar to mysqldump on each database and have separate files for each? How can I import just one database into the test server.

    Thank you in advance.


  • #2

    You can take a backup for each database using xtrabackup, that's not a problem, but there are some limitations on the process.

    1- You need to have innodb_file_per_table_enabled.
    2- The restore should be done importing individual tables one by one.
    3- The destination server must be Percona Server.

    I'm going to give you some links to help you decide if XtraBackup is a good solution for your environment:

    How to take partial backups:

    http://www.percona.com/doc/percona-xtrabackup/innobackupex/p artial_backups_innobackupex.html

    How to restore partial backups:

    http://www.percona.com/doc/percona-xtrabackup/innobackupex/i mporting_exporting_tables_ibk.html



    • #3
      Thank you for your reply.. So what I am hearing you say is that the process needs to be done on a table level. that would be a bit problematic, since each database has about 200 tables each.. I will take a look at the links you graciously pointed me to. Thank you.


      • #4
        If the destination server is clean, I mean, with an empty datadir, then you can copy the already prepared backup. Otherwise, you would have to import table by table.