Missing .frm files

MySQL & MariaDB Other MySQL® Questions
1

Let me first explain my scenario,

I have two instance on my local linux box for mysql running on 3306 and 3307 port respectively. I want to get incremental backup
of 3 DB from 3306 and want to restore it on 3307.

Problem is that when i complete the backup & restoring process (using innobackupex) the data directory didn’t contains any .frm files.

Below are the steps which i followed

Taking, Preparing and restoring multiple databases:-

Complete backup:-
innobackupex --defaults-file=/opt/mysql/etc/my.cnf --user=root --password=xxxxx --port=3306 --host=127.0.0.1 --include=test,test_1 /root/sunil/jimit/Percona/data/backups/multiple_db_backups

First Incremental backup:-
→ Create some new tables and add data in those tables:-
innobackupex --defaults-file=/opt/mysql/etc/my.cnf --user=root --password=xxxxx --port=3306 --host=127.0.0.1 --include=test,test_1 --incremental /root/sunil/jimit/Percona/data/backups/multiple_db_incr_backups/first_incremental/ --incremental-basedir=/root/sunil/jimit/Percona/data/backups/multiple_db_backups/2015-03-26_06-27-14

Second Incremental Backup:-
→ Create some new tables and add data in those tables:-
innobackupex --defaults-file=/opt/mysql/etc/my.cnf --user=root --password=xxxxx --port=3306 --host=127.0.0.1 --include=test,test_1 --incremental /root/sunil/jimit/Percona/data/backups/multiple_db_incr_backups/second_incremental/ --incremental-basedir=/root/sunil/jimit/Percona/data/backups/multiple_db_incr_backups/first_incremental/2015-03-20_07-44-43

Preparing multiple backups in order to restore:-

→ Applying logs to main compelete backup:-
innobackupex --defaults-file=/opt/mysql/etc/my.cnf --user=root --password=xxxxx --port=3306 --host=127.0.0.1 --include=test,test_1 --apply-log --redo-only /root/sunil/jimit/Percona/data/backups/multiple_db_backups/2015-03-20_07-30-31

→ Applying logs of first incremental backup to main compelete backup:-
Applying first incremental backup:-
innobackupex --defaults-file=/opt/mysql/etc/my.cnf --user=root --password=xxxxx --port=3306 --host=127.0.0.1 --include=test,test_1 --apply-log /root/sunil/jimit/Percona/data/backups/multiple_db_backups/2015-03-20_07-30-31 --incremental-dir=/root/sunil/jimit/Percona/data/backups/multiple_db_incr_backups/first_incremental/2015-03-20_07-44-43

→ Applying logs of second incremental backup to main compelete backup:-
Applying second incremental backup:-
innobackupex --defaults-file=/opt/mysql/etc/my.cnf --user=root --password=xxxxx --port=3306 --host=127.0.0.1 --include=test,test_1 --apply-log /root/sunil/jimit/Percona/data/backups/multiple_db_backups/2015-03-20_07-30-31 --incremental-dir=/root/sunil/jimit/Percona/data/backups/multiple_db_incr_backups/second_incremental/2015-03-20_07-55-05

Restoring Complete backups for multiple databases prepared with first and second incremental ones:-
→ Stop Mysql service
/etc/init.d/mysqld-5.6.20 stop

→ Make data directory changes in my.cnf file by keeping path of empty data directory folder at 3 places.
→ Make changes wherever there is data directory path in my.cnf file.

Path is /data/multiple_db_datadir/

innobackupex --copy-back --defaults-file=/opt/mysql/etc/my.cnf /root/sunil/jimit/Percona/data/backups/multiple_db_backups/2015-03-20_07-30-31

→ Give permission to mysql user with chmod
chown -R mysql:mysql /data/multiple_db_datadir/mysql/

→ Copy mysql database to new data directory folder where test database resides.
cp -r /data/mysql-5.6-16/mysql /data/multiple_db_datadir/

Again give permission:-
chown -R mysql:mysql /data/multiple_db_datadir/mysql/

→ Start Mysql service and cross check tables of test database.
/etc/init.d/mysqld-5.6.20 start
/opt/mysql/bin/mysql -uroot -pxxxxx -P3307

2

Hi Dharmang;

Have you ever had the MySQL instance on 3307 running? Running two MySQL instances on the same host can be tricky in itself sometimes.

That aside, why are you copying the “mysql” directory into your fresh backup (cp -r /data/mysql-5.6-16/mysql /data/multiple_db_datadir/)? Your backup will already have the “mysql” database. All you should need to do is run --copy-back in your new data directory and set ownership for MySQL, and then you are good to go as long as your overall configuration is correct (i.e. you have your my.cnf(s) and init.d scripts setup to handle the two MySQL instances).

-Scott

3

Dear Scott,

Thank you very much for the reply.

Yes, I have two my.cnf files and mysql config file(init.d) respectively for 3306 and 3307 instance.

Let me explain my whole scenario,

my 3306 instance contains four databases:test,test_1,dharmang,tom
my 3307 instance contains four databases: product, reseller, hsm,wallet.

I simply want to take incremental backup for test and test_1 database from 3306 instance and want to restore it on 3307 instance.

When I complete with innobackupex process(for 3306 instance), instead of taking backup of this two databases, it contains all the database backups which is there on 3306 instance.

My problem is that if i restore this to 3307 instance, will it create dharmang and tom database also(which I don’t want to be there on 3307)??

4

Hi Dharmang;

Ah okay. I misread your original post, and thought you were trying to do something more simple.

So you are combining incremental backups with partial backups, and wanting to do a partial restore into an existing MySQL instance. This is a fairly complex use case, but it should be doable.

  1. For your backups, replace --include=test,test_1 with --databases=“test test1”
  2. For your prepares, remove the --include=test,test_1 option (you can also remove the user/password/connection related options)
  3. Your first incremental prepare should have the --redo-only option still (the second incremental prepare can go without it)
  4. Do one final prepare on the base backup with --export:

innobackupex --defaults-file=/opt/mysql/etc/my.cnf --apply-log --export /root/sunil/jimit/Percona/data/backups/multiple_db_backups/2015-03-20_07-30-31

Now if you look in the base backup directory (2015-03-20_07-30-31 in this example), you should see directories for the test and test_1 databases. If you look in those directories, you should see your tables with extensions of .cfg/.exp/.frm/.ibd.

Now if you want to import those databases into your existing 3307 instance, you must do so one table at a time:

[URL=“Restoring Individual Tables”]http://www.percona.com/doc/percona-x...porting-tables[/URL]

Note when you do this process again, just make sure to get the right base/incremental backup directories in the commands since those will change and they are easy to get mixed up since they all look similar.

-Scott