Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to take mysql backup without any downtime but unable to take the backup using Xtrabackup .

Actual Database is almost about 40+ GB in size. I am trying to first satisfy myself. Hence, I have created a sample database on a server with 5000 Tables (all InnoDB tables). As per the blog "there are some cases when you can get table entirely missing from the backup if you use this technique " "--single-transaction are not safe to use on production environment.

[root@localhost mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.5.25a-log MySQL Community Server (GPL) by Remi
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| innodbtest         |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)
mysql> use innodbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show create table T4000;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| T4000 | CREATE TABLE `T4000` (
  `A` int(11) NOT NULL,
  `B` int(11) DEFAULT NULL,
  `C` char(1) DEFAULT NULL,
  PRIMARY KEY (`A`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table T3040;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| T3040 | CREATE TABLE `T3040` (
  `A` int(11) NOT NULL,
  `B` int(11) DEFAULT NULL,
  `C` char(1) DEFAULT NULL,
  PRIMARY KEY (`A`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

I used tool xtrabackup and followed the steps to create a backup

xtrabackup --backup --datadir=/var/lib/mysql --target-dir=/opt/bkp/

After taking the backup I am not getting the ".ibd" files at the backup location.

[root@localhost mysql]# ls -al /opt/bkp/
total 190676
drwxr-xr-x 2 root root      4096 Nov 27 19:02 .
drwxr-xr-x 5 root root      4096 Nov 27 18:08 ..
-rw-r----- 1 root root 195035136 Nov 27 19:02 ibdata1
-rw-r----- 1 root root        79 Nov 27 19:02 xtrabackup_checkpoints
-rw-r----- 1 root root      2560 Nov 27 19:02 xtrabackup_logfile
[root@localhost mysql]#

After restoring the database using xtrabackup, I am not getting the database folder "innodbtest" on the other server too.

Where am I wrong?

share|improve this question
    
Please run SHOW VARIABLES LIKE 'innodb_flile_per_table'; and tell me is it is ON or OFF. –  RolandoMySQLDBA Nov 27 '12 at 21:00
    
Please run SHOW VARIABLES LIKE 'datadir'; and tell me what it is. –  RolandoMySQLDBA Nov 27 '12 at 21:02
    
Another question: Did you run xtrabackup --prepare ( percona.com/doc/percona-xtrabackup/xtrabackup_bin/… ) on the backup after running xtrabackup --backup ? –  RolandoMySQLDBA Nov 27 '12 at 21:08
    
Here is the output mysql> SHOW VARIABLES LIKE 'innodb_flile_per_table'; Empty set (0.00 sec) mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec) Yes I did run xtrabackup --prepare –  PratapSingh Nov 28 '12 at 6:23
    
Here is few more details mysql> show variables like "%innodb"; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | have_innodb | YES | | ignore_builtin_innodb | OFF | +-----------------------+-------+ 2 rows in set (0.01 sec) mysql> show variables like "%engine"; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | | storage_engine | InnoDB | +------------------------+--------+ 2 rows in set (0.00 sec) –  PratapSingh Nov 28 '12 at 8:08
add comment

1 Answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.