3

I have had two attempts at dumping all databases but both have failed, one with the mysql database and then with the performance_schema database. The precised errors are:-

11:01:01 Dumping mysql - mysqldump: Got error: 1556: You can't use locks with log tables. when doing LOCK TABLES
11:35:23 Dumping performance_schema () - mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

Is there a way to overcome this? Basically I am trying to follw the procedure here:- https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261

Many thanks...

3

Sounds like you are using an older version of the mysqldump client than the database you're taking the dump from.

Do a "which mysqldump", and see if it is in the same bin directory as your running mysqld. Up to date versions of mysqldump ignore performance_schema.

  • I was trying to back up all databases as to achievable "InnoDB File-Per-Table Tablespaces". My understanding is that you have to dump all databases, stop the server, delete ibdata1 and ib_logfiles, restart server and reload all data. Can I ignore mysql and performance_schema? – MichaelJohn Mar 19 '15 at 14:07
  • You can ignore performance_schema, if you want to recover users, privileges, and any other objects like stored procedures if you have them, you should backup the mysql schema, depending on the version you are running that may also include some InnoDB tables as well, which will need to be re-loaded if the central tablespace is recreated. – Mark Leith Mar 19 '15 at 16:27

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.