MySQL Forums - Data Recovery

Table Problems on legacy databases (1 reply)

Vito Cavallo — Wed, 08 Oct 2025 07:08:24 +0000

Hello,
I inherited an old CentOS 7 linux server running mysql 5.744
MySQL is running and there are approximately 15 databases.
I am migrating to Rocky 9 running MySQL 8.4.32
Half of the databases work. The other half wont work.
No idea what the previous users did when using and uploading data.

The database folders have idb and frm files. Anywhere from 100 MB to 3 GB folder size, so some data is there.

In MySQL Workbench, I can see databases and tables, but getting the error
"Tables could not be fetched". Everything looks like the good databases.
The tables are shown as folders, inside folders i see Columns and Index and Foreign Keys folders.

Using mysql app on the server, I can list databases and tables.
repair tables prints "Table xxx doesn't exist"

I cant repair, I cant dump, I cant drop.

mysqldump of tables is error 1146: doesn't exist when using LOCK TABLES
mysqlcheck of all database error 2013 lost connection to mysql server when executing 'CHECK TABLE'
I modifed the my.cnf file to allow interactive_timeout - 57600 and wait_timeout

Folders owned by mysql and attributes were 755 and 770.

any ideas how to repair or recreate the bad databases ?

Thank you.

Issue with MySQL 8.4.4 binlog recovery in Docker container (1 reply)

Sergei Peregudov — Thu, 09 Oct 2025 07:05:38 +0000

The official Docker image MySQL 8.4.4 is used. MySQL settings:

server-id=1
log-bin=/var/lib/mysql/binlog
max_binlog_size = 100M
binlog_expire_logs_seconds = 345600
binlog_format=ROW
binlog_row_image=FULL
gtid_mode=ON
enforce-gtid-consistency=ON

The database backup is done using mysqldump with the following parameters: --single-transaction --master-data=2. The image and container settings used for restoring the database are identical to the production one. The database from the backup is restored without errors.

When applying the binlog files using mysqlbinlog, I get the error HY000 mysqlbinlog "can't find record in". I tried running mysqlbinlog with the parameters --start-position and --start-datetime, but always got errors. The binlog files are not corrupted, and there are no DDL statements in the binlog files (no tables are dropped or created). Backup/restore attempts were made multiple times. Restoring from the binlog files always ended with an error.

Index corruption with import tablespace (no replies)

Marcel van Brakel — Tue, 28 May 2024 14:23:42 +0000

Due to some data loss after a misplaced truncate table, I tried to restore an .idb file using the following procedure:

alter table x discard tablespace
replaced the x.idb file with one from our veaam backup
alter table x import tablespace

but this gives me the message:

Error Code: 1817. Index corrupt: Externally stored column(52) has a reference length of 3 in the cluster index PRIMARY

The column 52 is a column of type longtext.

This is innodb running on a Windows server.

Anyone have an idea of how to proceed? Or another method of restoring the content of the .idb file?

My MySQL Database corrupted / hacked by ransom (1 reply)

Prakash H — Thu, 09 May 2024 14:12:12 +0000

MySQL database is corrupted / hacked by ransom and have reduced to 0 bytes.

However, all databases in the form of .ibd still present in the c:/Xampp/mysql/data/ location. But there is only one .frm (readme.frm) file is available.

readme.frm is the hackers file.

Is there any software available for recovery?

Also, we have a back file which we have taken 2 months before.

I have restored the two month backup file and take all the .frm files and copy / paste it to new database. Unfortunately this does not work. MySQL makes errors like "#1932 - Table Database.tablename doesn't exist in engine".

Also, we have taken the ib_logfile0,ib_logfile1 and ibdata1 file from backup folder and data folder from the old (affected) MySQL and replace it into new MySQL. But this is also through the same error.

Could anyone please help on this?

HOW TO RECOVER MYSQL 5.7 (no replies)

Patrick Castro — Thu, 21 Mar 2024 03:16:42 +0000

We have a MySQL 5.7 Server that is now inaccessible. Last task performed was we ran a query that took several hours and was not completed. We resulted to restarting the services and the server itself hoping to terminate the session from the large query.

After restarting the server, we tried to manually start the MySQL server service but it gets "stuck" trying to start running serveral hours without completing.

Unfortunately, our database weekly backup has not started prior to the server going down. We need to recover the current data that is in the DB.

1. What can we do to be able to start the databse successfully?
2. What is the way to recover the data if the database server will not be started?
3. What are our options to salvage this database completely?

Recover database from only .ibd file (1 reply)

vikas lagerweij — Mon, 03 Jun 2024 06:01:19 +0000

I recently lost all my SQL data as my server was forcefully closed. I only managed to recover the .ibd files and nothing else. I'm wondering if it is still possible to clone the tables from the .ibd files into a new database on a new server. I know the table structure from the top of my head yet I can't seem to get anything to work... Are there any available tools out there, solutions? or am I screwed up because its a database containing 1200 customers which need access to my service.

MySQL 5.6 refuses to start after power outage. (no replies)

Cyprian Okpalaku — Fri, 17 Nov 2023 06:17:52 +0000

I have mysql5.1 for years working.
I had FETCH error and browsed I was asked to upgrade to a later version which I upgrade to MySQL 5.6.
I discovered each time power outage,the MySQL service will refuse to start.
I have it in three place and they are same.
I have to use innob_foece_recovery =1-6
to restore it back.
They are troubling me too much and I don't want to go back to mysql5.1

Please what do I do?

server rebooted, now mysql will not start (1 reply)

Troy Rockwood — Sun, 22 Oct 2023 16:31:16 +0000

The server rebooted and mysqld will not start. I have tried adding the following to my.cnf:

innodb_force_recovery=1
log_error_verbosity=3

Here are the messages that I get:

../bin/mysqld --user=
2023-09-22T01:40:09.496787Z 0 [Note] [MY-013667] [Server] Error-log destination "stderr" is not a file. Can not restore error log messages from previous run.
2023-09-22T01:40:09.317264Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2023-09-22T01:40:09.473095Z 0 [Note] [MY-010098] [Server] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2023-09-22T01:40:09.473146Z 0 [Note] [MY-013932] [Server] BuildID[sha1]=9ad00ba9c420608e3d596a40a133fd20d019a80e
2023-09-22T01:40:09.473149Z 0 [Note] [MY-010949] [Server] Basedir set to /home//mysql-8.1.0-linux-glibc2.28-x86_64/.
2023-09-22T01:40:09.473155Z 0 [System] [MY-010116] [Server] /home//mysql-8.1.0-linux-glibc2.28-x86_64/bin/mysqld (mysqld 8.1.0) starting as process 159439
2023-09-22T01:40:09.499152Z 0 [Note] [MY-012366] [InnoDB] Using Linux native AIO
2023-09-22T01:40:09.499330Z 0 [Note] [MY-010747] [Server] Plugin 'FEDERATED' is disabled.
2023-09-22T01:40:09.499393Z 0 [Note] [MY-010747] [Server] Plugin 'ndbcluster' is disabled.
2023-09-22T01:40:09.499408Z 0 [Note] [MY-010747] [Server] Plugin 'ndbinfo' is disabled.
2023-09-22T01:40:09.499415Z 0 [Note] [MY-010747] [Server] Plugin 'ndb_transid_mysql_connection_map' is disabled.
2023-09-22T01:40:09.501184Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-09-22T01:40:09.501271Z 1 [Note] [MY-013546] [InnoDB] Atomic write enabled
2023-09-22T01:40:09.501621Z 1 [Note] [MY-012932] [InnoDB] PUNCH HOLE support available
2023-09-22T01:40:09.501668Z 1 [Note] [MY-012944] [InnoDB] Uses event mutexes
2023-09-22T01:40:09.501682Z 1 [Note] [MY-012945] [InnoDB] GCC builtin __atomic_thread_fence() is used for memory barrier
2023-09-22T01:40:09.501695Z 1 [Note] [MY-012946] [InnoDB] !!! innodb_force_recovery is set to 1 !!!
2023-09-22T01:40:09.501722Z 1 [Note] [MY-012948] [InnoDB] Compressed tables use zlib 1.2.13
2023-09-22T01:40:09.503277Z 1 [Note] [MY-012951] [InnoDB] Using hardware accelerated crc32 and polynomial multiplication.
2023-09-22T01:40:09.503584Z 1 [Note] [MY-012203] [InnoDB] Directories to scan './'
2023-09-22T01:40:09.503656Z 1 [Note] [MY-012204] [InnoDB] Scanning './'
2023-09-22T01:40:09.562913Z 1 [Note] [MY-012208] [InnoDB] Completed space ID check of 7 files.
2023-09-22T01:40:09.563368Z 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 128.000000M, instances = 1, chunk size =128.000000M
2023-09-22T01:40:09.567362Z 1 [Note] [MY-012957] [InnoDB] Completed initialization of buffer pool
2023-09-22T01:40:09.568883Z 0 [Note] [MY-011952] [InnoDB] If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2023-09-22T01:40:09.573091Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2023-09-22T01:40:09.574022Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2023-09-22T01:40:09.582062Z 1 [Note] [MY-013566] [InnoDB] Double write buffer files: 2
2023-09-22T01:40:09.582081Z 1 [Note] [MY-013565] [InnoDB] Double write buffer pages per instance: 4
2023-09-22T01:40:09.582113Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2023-09-22T01:40:09.582147Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2023-09-22T01:40:09.775884Z 1 [ERROR] [MY-013882] [InnoDB] Missing redo log file ./#innodb_redo/#ib_redo1038 (with start_lsn = 3399200768).
2023-09-22T01:40:09.775941Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-09-22T01:40:10.069122Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2023-09-22T01:40:10.069340Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-09-22T01:40:10.069390Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-09-22T01:40:10.069462Z 0 [Note] [MY-010120] [Server] Binlog end
2023-09-22T01:40:10.069613Z 0 [Note] [MY-015019] [Server] MySQL Server: Plugins Shutdown - start.
2023-09-22T01:40:10.069637Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'MyISAM'
2023-09-22T01:40:10.069705Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'InnoDB'
2023-09-22T01:40:10.069763Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'CSV'
2023-09-22T01:40:10.069778Z 0 [Note] [MY-010733] [Server] Shutting down plugin 'daemon_keyring_proxy_plugin'
2023-09-22T01:40:10.069909Z 0 [Note] [MY-015020] [Server] MySQL Server: Plugins Shutdown - end.
2023-09-22T01:40:10.070244Z 0 [System] [MY-010910] [Server] /home//mysql-8.1.0-linux-glibc2.28-x86_64/bin/mysqld: Shutdown complete (mysqld 8.1.0) MySQL Community Server - GPL.
2023-09-22T01:40:10.070352Z 0 [Note] [MY-015021] [Server] MySQL Server: Components Shutdown - start.
2023-09-22T01:40:10.070734Z 0 [Note] [MY-015022] [Server] MySQL Server: Components Shutdown - end (with return value = 0).
2023-09-22T01:40:10.070816Z 0 [System] [MY-015016] [Server] MySQL Server - end.

The redo directory looks like:
ls -alt \#innodb_redo/
total 57601
drwxr-x--- 8 4096 Sep 21 21:40 ..
drwxr-x--- 2 4096 Sep 21 18:36 .
-rw-r----- 1 3276800 Sep 21 05:22 '#ib_redo1085'
-rw-r----- 1 3276800 Sep 21 05:09 '#ib_redo1084'
-rw-r----- 1 3276800 Sep 21 04:43 '#ib_redo1083'
-rw-r----- 1 3276800 Sep 20 10:59 '#ib_redo1037'
-rw-r----- 1 3276800 Sep 20 10:50 '#ib_redo1036'
-rw-r----- 1 3276800 Sep 20 10:33 '#ib_redo1035'
-rw-r----- 1 3276800 Sep 20 10:14 '#ib_redo1034'
-rw-r----- 1 3276800 Sep 20 09:57 '#ib_redo1033'
-rw-r----- 1 3276800 Sep 20 09:39 '#ib_redo1032'
-rw-r----- 1 3276800 Sep 20 09:21 '#ib_redo1031'
-rw-r----- 1 3276800 Sep 20 09:04 '#ib_redo1030'
-rw-r----- 1 3276800 Sep 20 08:47 '#ib_redo1029'
-rw-r----- 1 3276800 Sep 20 08:29 '#ib_redo1028'
-rw-r----- 1 3276800 Sep 20 08:11 '#ib_redo1027'
-rw-r----- 1 3276800 Sep 20 07:53 '#ib_redo1026'
-rw-r----- 1 3276800 Sep 20 07:35 '#ib_redo1025'
-rw-r----- 1 3276800 Sep 20 07:17 '#ib_redo1024'
-rw-r----- 1 3276800 Sep 20 07:00 '#ib_redo1023'

ls -alth
total 2.9G
drwxr-x--- 8 4.0K Sep 21 22:58 .
-rw-r----- 1 6.1K Sep 21 18:45 .err
drwxr-x--- 2 4.0K Sep 21 18:36 '#innodb_redo'
-rw-r----- 1 192K Sep 21 05:22 '#ib_16384_0.dblwr'
-rw-r----- 1 12M Sep 21 05:22 ibdata1
-rw-r----- 1 16M Sep 21 05:22 undo_001
-rw-r----- 1 16M Sep 21 05:22 undo_002
-rw-r----- 1 24M Sep 21 05:22 mysql.ibd
-rw-r----- 1 805M Sep 21 05:22 binlog.000003
-rw-r----- 1 8.2M Sep 21 05:21 '#ib_16384_1.dblwr'
-rw-r----- 1 48 Sep 17 15:31 binlog.index
-rw-r----- 1 1.1G Sep 17 15:31 binlog.000002
-rw-r----- 1 1.1G Sep 14 21:03 binlog.000001
drwxr-x--- 2 4.0K Sep 13 21:06 SIMULATIONS
-rw-r----- 1 12M Sep 13 20:47 ibtmp1
drwxr-x--- 2 4.0K Sep 13 20:47 '#innodb_temp'
-rw-r----- 1 5.5K Sep 13 20:43 ib_buffer_pool
drwxr-x--- 2 4.0K Sep 13 20:43 sys
drwxr-x--- 2 4.0K Sep 13 20:42 mysql
-rw------- 1 1.7K Sep 13 20:42 private_key.pem
-rw-r--r-- 1 452 Sep 13 20:42 public_key.pem
-rw-r--r-- 1 1.1K Sep 13 20:42 client-cert.pem
-rw------- 1 1.7K Sep 13 20:42 client-key.pem
-rw-r--r-- 1 1.1K Sep 13 20:42 server-cert.pem
-rw------- 1 1.7K Sep 13 20:42 server-key.pem
-rw-r--r-- 1 1.1K Sep 13 20:42 ca.pem
-rw------- 1 1.7K Sep 13 20:42 ca-key.pem
drwxr-x--- 2 8.0K Sep 13 20:42 performance_schema
-rw-r----- 1 56 Sep 13 20:42 auto.cnf
drwxrwx--- 10 4.0K Sep 13 20:42 ..

This is a user-space installation which means that I start with:
mysqld --user=

I have been putting research simulation results in it for the past week or so basically nonstop. I had probably 13 different threads adding to the database. I have another copy of the data (the original results from the simulations) but I would prefer not to spend another week waiting for them to be placed in the database. I'm also pretty discouraged that the first hiccup killed the database. Is there anything that I should be doing to make this less likely? Thank you for your help.

Error: MySQL shutdown unexpectedly - ibdata1 is not working (no replies)

Baracs Baracs — Thu, 31 Aug 2023 12:00:45 +0000

Hi,
my problem is that MySQL on XAMPP is not working for me. I'm getting an error like this.

"13:45:43 [mysql] Error: MySQL shutdown unexpectedly.
13:45:43 [mysql] This may be due to a blocked port, missing dependencies,
13:45:43 [mysql] improper privileges, a crash, or a shutdown by another method.
13:45:43 [mysql] Press the Logs button to view error logs and check
13:45:43 [mysql] the Windows Event Viewer for more clues
13:45:43 [mysql] If you need more help, copy and post this
13:45:43 [mysql] entire log window on the forums"

I haven't started my computer for a long time. Today I decided to run it and see my site on XAMPP. Everything was working until I logged into wp-admin and hit the "updates" tab because there was a red circle with the number 11 next to it.

After this incident I lost contact with XAMPP. Since then, I don't want to start MySQL.

I've tried with the Backup folder and so on, it used to help when I had a similar problem, but now it doesn't work.

The problem is with the ibdata1 file. It was probably corrupted, but I don't know how.

I've been working on this page for a long time and I don't want to lose it just like that.

I need help, any advice please.

__

I also tried this.

I copied the contents of the Backup folder to a new data folder. Later, with data_old, I copied the site base, but without the ibdata1.

The database started, but because entering it, the following error appears in phpMyAdmin:

"#1932 - Table 'esklimos.wp_actionscheduler_groups' doesn't exist in engine"

The site is Wordpress.

Rebuild a broken mysql database using .frm or .ibd data. SQL files are 0byte. Resynchronize mysql Software for Windows to restore .ibd data. (1 reply)

Sero Hazr — Tue, 12 Dec 2023 05:07:27 +0000

MySQL files (after MySQL crashed) have reduced to 0 bytes.

However, all databases in the form of .ibd and .frm are still present in the /var/lib/mysql folder.

Is there any software available for recovery?

Free of charge? Open source?

I tried to restore / overwrite these .frm and .ibd into a new database (using a guide). Unfortunately this does not work. MySQL makes errors and can't read the columns anymore.

After trying to recover the corrupted MySQL database or IBD DATA with a new MySQL.For this I created and overwrote the .ibd file and ran the commands:(ALTER TABLE tablename DISCARD TABLESPACE;)(ALTER TABLE tablename IMPORT TABLESPACE;)did not work! :(

Can someone please help me?The platform does not matter, no matter if it is Linux, Mac or Windows. The program should be free of charge.

Thanks a lot!

MySQL data recovery with IBD or FROM based software. (1 reply)

Sero Hazr — Mon, 06 Nov 2023 07:04:18 +0000

MySQL data recovery with IBD or FROM based software.

Hello Community.

After trying to recover the corrupted MySQL database or IBD DATA with a new MySQL.

For this I created and overwrote the .ibd file and ran the commands:
(ALTER TABLE tablename DISCARD TABLESPACE;)
(ALTER TABLE tablename IMPORT TABLESPACE;)

did not work! :(

I am looking for a program similar to "Stellar Repair for MySQL". But it should be free. I don't spend 199 Euro for it! :)

Can someone please help me?
The platform does not matter, no matter if it is Linux, Mac or Windows. The program should be free of charge.

Thanks a lot!

Deleted a table manually and MySQL Server isn't running anymore (no replies)

Othman K — Fri, 31 Mar 2023 11:40:18 +0000

I had a bug in my code which kept writing data to a table for gigabytes, tried ```DELETE FROM TABLENAME``` but it was taking forever, so I went to the Data directory and manually deleted *TABLENAME.ibd*

Now if I start MySQL server, I get this error:

2023-03-31T09:48:13.913658Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 523 for {space: 17339, page_no:861324} could not be restored. File name unknown for tablespace ID 17339
2023-03-31T09:48:13.915373Z 1 [ERROR] [MY-012179] [InnoDB] Could not find any file associated with the tablespace ID: 17339
2023-03-31T09:48:13.916478Z 1 [ERROR] [MY-012964] [InnoDB] Use --innodb-directories to find the tablespace files. If that fails then use --innodb-force-recovery=1 to ignore this and to permanently lose all changes to the missing tablespace(s)
2023-03-31T09:48:14.019872Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-03-31T09:48:14.041689Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2023-03-31T09:48:14.052947Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-03-31T09:48:14.058936Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-03-31T09:48:14.091527Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL.

Then I added: *innodb-force-recovery=1* to *my.ini*, it's getting stuck in "starting" and the log file shows this error:

2023-03-31T10:01:43.226457Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
mysqld: Tablespace is missing for table db/tablename.

Created the table on a PC and copied it to the same folder, but that didn't work either cause I think the tablespace ID doesn't match the one I deleted.

- So, is there a solution? Or is it possible to create the same table on a different PC but change the tablespace ID to 17339?
- Removed *innodb-force-recovery=1* my *my.ini*, now the log doesn't show any error, but the service is stuck on Starting! Neither changing to Running nor Stopping/Stopped.
- Tried this tool: https://mysql.recoverytoolbox.com/ , but when I put the folder: ```C:\ProgramData\MySQL\MySQL Server 8.0\Data``` it shows an error: ```No database found.```
I'm browsing the folder and all the databases are there!
- Is there a way to ignore the entire schema which contains the missing table when starting the server?
- Starting with *innodb-force-recovery=1* gives this error in the log:

> 2023-03-31T11:18:54.572788Z 1 [ERROR] [MY-011971] [InnoDB] Tablespace
> 'innodb_undo_001' Page [page id: space=4294967279, page number=108805]
> log sequence number 905282159231 is in the future! Current system log
> sequence number 674565298710. 2023-03-31T11:18:54.574925Z 1 [ERROR]
> [MY-011972] [InnoDB] Your database may be corrupt or you may have
> copied the InnoDB tablespace but not the InnoDB log files. Please
> refer to
> http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
> for information about forcing recovery.

Help! I'm desperate and I don't know what to do anymore

is there any possibility to commit transaction during mysql recovery (no replies)

Yihua Li — Thu, 23 Feb 2023 01:17:44 +0000

Hello dears,
If I have configured mysql replication by semi-sync with after_sync, after master syncs the binary to disk, but before receipt received from slave, the master exited by accident such as power failure or hardware issue. under this situation, if master restarts, is there any chance that the uncommitted transaction commit?

extract data from an array of json objects (no replies)

TOM BRICOREUR — Sun, 06 Nov 2022 22:02:40 +0000

Hi,

DB : mysql8
server : debian 10
php 7.4

question 1: is it better to have 10000 rows which contains a Json field (3 levels) or 100,000 rows without json field? what is the fastest?

Question 2 : for my understanding

I would like to know what SQL query to perform to extract data from a json object array

For example, extract data where style = Hard

I tried :
SELECT *
FROM myTable
WHERE disc->>"$.Style" = "Hard"

how to get to the lower level ?

MyTable :
id = int
productor = string
disc = json

Here is the structure of my object "disc"

[
{
"Artist": "AC/DC",
"Year" : 2010,
"Album": [
"AlbumName": "Puissance",
"Style" : "Hard"
]
}, {
"Artist": "U2",
"Year" : 2011,
"Album": [
"AlbumName": "Fake",
"Style" : "Pop"
]
}, {
"Artist": "IRON",
"Year" : 2014,
"Album": [
"AlbumName": "New",
"Style" : "Hard"
]
}
]

Thanks for your feedback

Tables got locked when we try to import table data from mysql dump file (no replies)

Nishit kumar — Fri, 30 Sep 2022 10:11:16 +0000

I am trying to import a dump file which consists of a table with its data c_emailnotificationtemplate which was generated by this command :-

mysqldump --host=10.88.129.238 --user=root --password client_1002 c_emailnotificationtemplate --single-transaction --set-gtid-purged=OFF > c_emailnotificationtemplate.sql

But when I am trying to import this c_emailnotificationtemplate.sql to my database , my database gets locked , I am not able perform any query also data is not get inserted on the table.

I tried to add --skip-lock-tables on the command but it doesn't work

so is there any way I can skip the lock operation which is happening when I am trying to import the sql file.

some details database:- client_1002
tablename:- c_emailnotificationtemplate
db instance :- gcp cloud sql (mysql8)

Views not correctly exported to dump file (10 replies)

JK Heydt — Tue, 12 Apr 2022 14:33:00 +0000

When I create a Self-Contained File using Data Export to a dump file, views are defined like this in the dump file insead of the original format below. If

I use a batch file to do nightly backups using mysqldump.exe

--- DUMP FILE SECTION FOR VIEW---

CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `hto_fcd`.`vw_charges` AS
SELECT
1 AS `BillingAcctNo`,
1 AS `CompanyNo`,
1 AS `StandingCd`,
1 AS `CustomerTypeCd`,
1 AS `InvoiceGroupCd`,
1 AS `RecordNo`,
1 AS `ChargeBatchNo`,
1 AS `AcctNo`,
1 AS `HaulCd`,
1 AS `PlanNo`,
1 AS `PrechargeNo`,
1 AS `TicketStopNo`,
1 AS `BillingDesc`,
1 AS `AutoChargeNo`,
1 AS `EffectiveDate`,
1 AS `Units`,
1 AS `UnitRate`,
1 AS `TotalCharge`,
1 AS `ClearedBit`,
1 AS `InvoiceNo`,
1 AS `HideBit`
--- END DUMP FILE ---

--- ORIGINAL VIEW FORMAT ---
CREATE
ALGORITHM = UNDEFINED
DEFINER = `root`@`localhost`
SQL SECURITY DEFINER
VIEW `vw_charges` AS
SELECT
`a`.`BillingAcctNo` AS `BillingAcctNo`,
`account`.`CompanyNo` AS `CompanyNo`,
`account`.`StandingCd` AS `StandingCd`,
`account`.`CustomerTypeCd` AS `CustomerTypeCd`,
`account`.`InvoiceGroupCd` AS `InvoiceGroupCd`,
`charge`.`RecordNo` AS `RecordNo`,
`charge`.`ChargeBatchNo` AS `ChargeBatchNo`,
`charge`.`AcctNo` AS `AcctNo`,
`charge`.`HaulCd` AS `HaulCd`,
`charge`.`PlanNo` AS `PlanNo`,
`charge`.`PrechargeNo` AS `PrechargeNo`,
`charge`.`TicketStopNo` AS `TicketStopNo`,
`charge`.`BillingDesc` AS `BillingDesc`,
`charge`.`AutoChargeNo` AS `AutoChargeNo`,
`charge`.`EffectiveDate` AS `EffectiveDate`,
`charge`.`Units` AS `Units`,
`charge`.`UnitRate` AS `UnitRate`,
`charge`.`TotalCharge` AS `TotalCharge`,
`charge`.`ClearedBit` AS `ClearedBit`,
`charge`.`InvoiceNo` AS `InvoiceNo`,
`charge`.`HideBit` AS `HideBit`
FROM
(((((`charge`
JOIN `pre_charge` ON (((`pre_charge`.`RecordNo` = `charge`.`PrechargeNo`)
AND (`pre_charge`.`HaulCd` = `charge`.`HaulCd`)
AND (`pre_charge`.`HideBit` = 0))))
LEFT JOIN `haul_plan` ON (((`haul_plan`.`RecordNo` = `charge`.`PlanNo`)
AND (`charge`.`HaulCd` = 'HAUL'))))
LEFT JOIN `route_plan` ON (((`route_plan`.`RecordNo` = `charge`.`PlanNo`)
AND (`charge`.`HaulCd` = 'ROUTE'))))
JOIN (SELECT
`account`.`RecordNo` AS `RecordNo`,
(CASE
WHEN
((`account`.`ParentAcctNo` = 0)
OR (`account`.`BillToSelfBit` = 1))
THEN
`account`.`RecordNo`
ELSE `account`.`ParentAcctNo`
END) AS `BillingAcctNo`
FROM
`account`) `a` ON ((`a`.`RecordNo` = `charge`.`AcctNo`)))
LEFT JOIN `account` ON ((`account`.`RecordNo` = `a`.`BillingAcctNo`)))
WHERE
((`charge`.`HideBit` = 0)
AND (IFNULL(`haul_plan`.`HideBit`, 0) = 0)
AND (IFNULL(`route_plan`.`HideBit`, 0) = 0))
--- END ORIGINAL FORMAT --

I can copy and paste between databases but I have dozens of views. Is there a way to export properly in a dump file?

Is it possible to repair this? (1 reply)

Wim Roffel — Thu, 17 Feb 2022 19:22:11 +0000

My Mysql installation crashed with the error "Aria recovery failed. Please run aria_chk -r" (this is MariaDb). There is only one database with the aria format (Mysql) so I did what was asked. Unfortunately it looks like aria_chk truncates all tables. So all the meta information of my installation is gone and I can't even login to this installation anymore as my username/password is no longer recognized.

I made a backup before running aria_chk. But the backup before the crash is long enough ago that would like to recover my data. These are nearly all InnoDb tables.

I had thought that having all the ib*, *.frm and *.ibd files I had all relevant information and there would be some tool available to rebuild the meta database. But I can't find anything on the internet. The only thing I found are things like mysqlfrm that recover in a complicated way a small amount of information. Not useful for recovering hundreds of tables.

So my question is whether there are options to recover this installation.

Recover database from a computer dump? (7 replies)

Hayley Galic — Thu, 10 Feb 2022 03:28:46 +0000

Hi, I recently finished setting up my new computer, and I was just transferring over my MySQL database. However, I never made a proper dump of the database on my MySQl server, I only have the actual server on a backup of everything on my old computer. Is there any way I can salvage my database from this backup?
Many thanks

Restore from binlog (no replies)

Matthieu COUSSI — Fri, 21 Jan 2022 13:14:13 +0000

Hi,

I'am curently testing backup restore scenario of a mysql database.

I tried to recover a record from a binlog.

In the example below :
1 - I switch the binlog
2 - I clean the binlog
3 - insert data in my table
4 - truncate the table

mysql> show binary logs;
+----------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+----------------------+-----------+-----------+
| localhost-bin.000017 | 196 | No |
+----------------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> use tmatt;
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> INSERT INTO test_restauration(message,created_at) VALUES('TEST - RESTAURATION AFTER MYSQLDUMP TOTO',NOW());
Query OK, 1 row affected (0.03 sec)

mysql> select * from test_restauration;
+----+------------------------------------------+---------------------+
| id | message | created_at |
+----+------------------------------------------+---------------------+
| 1 | TEST - RESTAURATION BEFORE MYSQLDUMP | 2022-01-21 10:32:04 |
| 2 | TEST - RESTAURATION AFTER MYSQLDUMP TOTO | 2022-01-21 11:24:44 |
+----+------------------------------------------+---------------------+
2 rows in set (0.00 sec)

mysql> truncate table test_restauration;
Query OK, 0 rows affected (0.08 sec)

mysql> select * from test_restauration;
Empty set (0.00 sec)

New, i backup the binlog and analyse the content to find the position from and to i need to recover to get my delete record (in my example, i have two record, ic onsidere that the first record will be recover the full backup)

[root@tmysql test_backup]# mysqlbinlog -u${MYSQL_USER} -h${MYSQL_HOST} --read-from-remote-server --raw localhost-bin.000017

[root@tmysql test_backup]# mysqlbinlog -v /tmp/test_backup/localhost-bin.000017

[...]

BINLOG '
7InqYRMBAAAASAAAAK8BAAAAANgAAAAAAAEABXRtYXR0ABF0ZXN0X3Jlc3RhdXJhdGlvbgADAw8S
A/8AAAABAQACAQhEJnje
7InqYR4BAAAAVgAAAAUCAAAAANgAAAAAAAEAAgAD/wACAAAAKFRFU1QgLSBSRVNUQVVSQVRJT04g
QUZURVIgTVlTUUxEVU1QIFRPVE+Zq+q2LE/rtW4=
'/*!*/;
### INSERT INTO `tmatt`.`test_restauration`
### SET
### @1=2
### @2='TEST - RESTAURATION AFTER MYSQLDUMP TOTO'
### @3='2022-01-21 11:24:44'
# at 517

[...]

And i restore my data from the binlog.

[root@tmysql test_backup]# mysqlbinlog --start-position=4 --stop-position=625 /tmp/test_backup/localhost-bin.000017 | mysql -u${MYSQL_USER} -h${MYSQL_HOST} tmatt

But my table is empty. I have insert before restore a record to ensure that the truncate in the binlog not apply.

mysql> select * from test_restauration;
+----+--------------------------------------------+---------------------+
| id | message | created_at |
+----+--------------------------------------------+---------------------+
| 1 | TEST - RESTAURATION AFTER MYSQLDUMP TOTO 2 | 2022-01-21 11:38:33 |
+----+--------------------------------------------+---------------------+
1 row in set (0.00 sec)

Howerver, my lost record [TEST - RESTAURATION AFTER MYSQLDUMP TOTO] doen't restore by the binlog apply.

Do you have an idea?

Thanks for your help,

Matt

Restoring database from .IDB files (1 reply)

It Manager — Wed, 05 Jan 2022 18:19:13 +0000

I have a REDCap database (REDCap being an open-source public health database platform developed at Vanderbilt University, which relies on MySQL for storing data) running on Windows Server 2012 R2. The server recently crashed, which required a fresh install of the operating system, and a new installation of REDCap.

I have backup copies of the MySQL data store from the old server (C:\ProgramData\MySQL\MySQL Server 8.0\Data\redcap), and am wondering what the procedure is for incorporating the .IDB files from that folder into the same folder on the new server, which currently only contains some sample tables that come installed by default on a new REDCap instance.

How exactly do I go about getting the .IDB files from the old MySQL database into the new one? Is it as simple as copying the files from the old C:\ProgramData\MySQL\MySQL Server 8.0\Data\redcap folder to the new one? Or do I need to perform the operation in MySQL Workbench or some other utility?

Thank you very much for your advice.

After putting ibdata1 file in data folder, service does not start (2 replies)

Tahira Nasreen — Sat, 11 Dec 2021 09:39:01 +0000

Server is crashed. But we have got MYSQL Data folder. Tn that folder there are following files:

ibdata1,
ib_logfile0,
ib_logfile1
database folder with .frm files and in some databases there are .frm,.MYI, .MYD files.

Anyone have an idea how to recover my databases back.
I am using MYSQL 5.5.
I have installed MYSQL 5.5 on new machine,

I copied the data folder. and renamed data_old.
then I just Copy databases folders from the crashed server mysql/data folder and put it in this data folder.
then i start the mysql service.
in localhost/phpmyadmin, i can see all databses with table names.

ISSUe: when i click any table, it show me an error
"table does not exist in engine"
then i Stop the service

After that i put ibdata1 file into this mysql/data folder. existing was replaced with this one.
then i tried to start service, it was not running.
Do you people have any idea after putting ibdata1 file from one machine mysql/data folder to another machine mysql/data folder. Service does not start . how to start mysql service???

Thanks in Advance

Databases Recovery from ibdata1 and .frm files (5 replies)

Tahira Nasreen — Sun, 12 Dec 2021 17:49:41 +0000

Mysqldump not creating tables in the correct sequence, giving errors. (2 replies)

Michael delos Reyes — Mon, 04 Oct 2021 21:57:15 +0000

I just did a mysqldump.
Very generic mysqldump database > sqlfile.sql
When I run mysql -v database < sqlfile.sql I get
ERROR 1146 (42S02) at line 305: Table doesn't exist when trying to reference a table.

ERROR 1100 (HY000) at line : Table was not locked with LOCK TABLES (6 replies)

Michael delos Reyes — Tue, 05 Oct 2021 16:54:03 +0000

Trying to import from a mysqldump export.
Getting these errors when a table references another table.
Table was not locked with LOCK TABLES
Is there no other choice but to manually add the table like "lock table a WRITE, table b READ'?
There are hundreds of tables I will need to manually add a lock to.

Migrate MyISAM tables from MySQL 5.7 to 8.0 (no replies)

Edwin Desouza — Mon, 02 Aug 2021 16:20:51 +0000

https://lefred.be/content/migrate-myisam-tables-from-mysql-5-7-to-8-0/

extract tables from large mySQL dump with regex or wildcards? (1 reply)

Allen Moore — Fri, 09 Jul 2021 01:34:22 +0000

I need the ability to selectively restore a group of tables from a very large (100+Gb) mysql dump file with table names matching a specific pattern, such as regex "^brn_eraq".

In some cases I may not know all of the possible full table names, hence the need for pattern matching. Seems like I may have used sed in the past; but, only with full table names- no pattern matching. And, is there a way to list the table names contained in a mysql dump file?

Recover InnoDB table (8 replies)

Eldon Down — Wed, 08 Sep 2021 13:29:01 +0000

Windows Server 2016
MySQL 8.0.23
Recent server crash. I have a complete backup of the server from shortly before the crash. There is one specific table I wish to restore, and so far, have tried the following without success:
1) Copy entire database folder containing the table, as well as files from root of Data folder, and the 'sys' db folder, and the my.ini file. Attempting to start the server with this results in service crash with "Multiple files found for the same tablespace ID"
2) with running service, create new schema, and run create statement for the table. Then discard tablespace, copy idb file, and import tablespace. The last command fails with "Error Code: 1034. Incorrect key file for table 'filedata'; try to repair it"
Any other possible fixes I've found seem to revolve around utilities that require compiling, which I do not know how to do. Option 1) would be the preferred route to get working, as there is other less critical data I would like to recover.
Thanks in advance

Replaying binary log? (1 reply)

Jan Andersen — Fri, 19 Mar 2021 16:40:43 +0000

I worked for a while in a company, where they had found a way to replay the binlogs, in order to restore a database up to a given time after the time of the newest backup; however, I can't find any references to how this is done.

In Percona's documentation for innobackupex, they mention the log sequence number - is this the same as the 'end_log_pos' that can be found in the binlog files (using the mysqlbinlog command)?

I don't know how the DBAs in this company did, only that they did it as a matter of routine. Any ideas?

Extract data from .ibd file (2 replies)

Simon Quell — Fri, 19 Mar 2021 08:33:42 +0000

Hi everyone,

we have a special Problem with a mysql database.
Due to a loss of data, we had to restore a database. It is an innoDB database. This worked quite well, unfortunately the associated .frm file for a specific table was missing. Only the .idb file is available. Is there a way to get to the data of the .idb file? We found an old .frm file during the restore, but it doesn't seem to fit. The table is displayed in phpMyAdmin, but the message always appears that the table does not exist.

I don't have much hope

But I would appreciate an opinion from the experts.

Recovery After DROP TABLE, With innodb_file_per_table ON (no replies)

Edwin Desouza — Mon, 08 Mar 2021 02:51:35 +0000

https://twindb.com/recovery-after-drop-table-with-innodb_file_per_table-on/