2

Can any one please explain what is happening with our DB dumps?

We are executing dump as below

mysqldump -v --single-transaction --quick --lock-tables=false --skip-triggers -uXXX -pXXX $EXCLUDETABLES dbname -h xxx.xxx.xxx > $FLNM

$EXCLUDETABLES contains multiple --ignore-table statements

Instance details : AWS RDS MYSQL MULTIAZ 5.6.21 us-east-1c 40GB

Warning: Using a password on the command line interface can be insecure.
-- Connecting to xxx.xxx.xxx...
-- Starting transaction...
-- Setting savepoint...
-- Retrieving table structure for table t_XXX_XXX...
-- Sending SELECT query...
-- Retrieving rows...
-- Rolling back to savepoint sp...
-- Retrieving table structure for table t_XXX...
-- Sending SELECT query...
-- Retrieving rows...
-- Rolling back to savepoint sp...
-- Retrieving table structure for table t_XXX...
-- Sending SELECT query...
-- Retrieving rows...
-- Rolling back to savepoint sp...
-- Retrieving table structure for table t_XXX_XXX...
-- Sending SELECT query...
-- Retrieving rows...
-- Rolling back to savepoint sp...
-- Retrieving table structure for table t_XXX...
-- Sending SELECT query...
-- Retrieving rows...
-- Rolling back to savepoint sp...
mysqldump: Couldn't execute 'ROLLBACK TO SAVEPOINT sp': SAVEPOINT sp does not exist (1305)

RDS error log at the same time

2014-11-18 12:59:03 28522 [Note] Plugin 'FEDERATED' is disabled.
2014-11-18 12:59:03 28522 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-11-18 12:59:03 28522 [Note] InnoDB: The InnoDB memory heap is disabled
2014-11-18 12:59:03 28522 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-11-18 12:59:03 28522 [Note] InnoDB: Memory barrier is not used
2014-11-18 12:59:03 28522 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-11-18 12:59:03 28522 [Note] InnoDB: Using Linux native AIO
2014-11-18 12:59:03 28522 [Note] InnoDB: Using CPU crc32 instructions
2014-11-18 12:59:03 28522 [Note] InnoDB: Initializing buffer pool, size = 5.3G
2014-11-18 12:59:04 28522 [Note] InnoDB: Completed initialization of buffer pool
2014-11-18 12:59:04 28522 [Note] InnoDB: Highest supported file format is Barracuda.
2014-11-18 12:59:04 28522 [Note] InnoDB: The log sequence numbers 32416243155 and 32416243155 in ibdata files do not match the log sequence number 42069849641 in the ib_logfiles!
2014-11-18 12:59:04 28522 [Note] InnoDB: Database was not shutdown normally!
2014-11-18 12:59:04 28522 [Note] InnoDB: Starting crash recovery.
2014-11-18 12:59:04 28522 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-11-18 12:59:05 28522 [Note] InnoDB: Restoring possible half-written data pages 
2014-11-18 12:59:05 28522 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 184648, file name mysql-bin-changelog.001861
2014-11-18 12:59:06 28522 [Note] InnoDB: 128 rollback segment(s) are active.
2014-11-18 12:59:06 28522 [Note] InnoDB: Waiting for purge to start
2014-11-18 12:59:06 28522 [Note] InnoDB: 5.6.21 started; log sequence number 42069849641
2014-11-18 12:59:06 28522 [Note] Recovering after a crash using /rdsdbdata/log/binlog/mysql-bin-changelog
2014-11-18 12:59:06 28522 [Note] Starting crash recovery...
2014-11-18 12:59:06 28522 [Note] Crash recovery finished.
2014-11-18 12:59:06 28522 [Note] Server hostname (bind-address): '*'; port: 3306
2014-11-18 12:59:06 28522 [Note] IPv6 is available.
2014-11-18 12:59:06 28522 [Note] - '::' resolves to '::';
2014-11-18 12:59:06 28522 [Note] Server socket created on IP: '::'.
2014-11-18 12:59:06 28522 [Note] Event Scheduler: Loaded 0 events
2014-11-18 12:59:06 28522 [Note] /rdsdbbin/mysql/bin/mysqld: ready for connections.
Version: '5.6.21-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)

--Edit--

Would like to know if there are any relation with --skip-triggers. We added a trigger to a table a week back. Trigger basically updates a shadow table. We do not require shadow table in daily backups, so we are ignoring that table also.

Is there any know issues while backing up mysql databases with triggers, any other item to consider?

3

Sounds like a bug from a year ago (mysqldump creates useless metadata locks)

This bug was fixed for MySQL 5.5 and I see you are using MySQL 5.6.21

SUGGESTIONS

  • Don't use --lock-tables=false with --single-transaction because MySQL Documentation says "The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly."
  • Don't do any DDL from other DB Session while the mysqldump is in progress

GIVE IT A TRY !!!

1
  • Same command worked one day back. Not sure what went wrong. Let me try you suggestion.
    – titogeo
    Nov 19 '14 at 5:06
2

I have about 10 clients with same database configuration (but different versions of mysql/mariadb), and one has the same issue (mysqldump: Couldn't execute 'ROLLBACK TO SAVEPOINT sp': SAVEPOINT sp does not exist (1305)) while performing mysqldump.

I am using : mysql Ver 15.1 Distrib 10.0.13-MariaDB, for Linux (x86_64) using readline 5.1

Looks like a MySQL bug.

Removing the --single-transaction solved the problem. I now added --lock-all-tables to make sure backup is consistent. Since the affected cliente is not working during the night (when backup is done), locking all tables will not affect the users.

my.cnf:
[mysqldump]
#quick
#single-transaction
lock-all-tables
routines
max_allowed_packet = 64M
1
  • Thanks, I used the --max_allowed_packet=64M in mysqldump command and it worked
    – Lanklaas
    Feb 26 '19 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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