I have a database folder from a crashed server called gain_wordpress. I'm trying to put that database on a new server but I'm not able to do it successfully. I don't have a .sql dump, all I have is the database folder from a past crashed server with ibdata and the 2 log files. I copied the gain_wordpress folder to /var/lib/mysql on the new server but when I do a > show tables on the database I get the following error: ERROR 1018 (HY000): Can't read dir of './gain_wordpress/' (errno: 13) I also changed the permission of the folder and did a chown mysql:mysql gain_wordpress -R and it still didn't take care of the problem. What is the best way to recover gain_wordpress. Do I need to replace any of the ibdata or the logfiles? What is the best way? I have the corresponding .frm, .MYD and .MYI files for each table. Please advise.

We accidentally deleted the directory rm -rf /data/db which was our **MongoDB path**, and thanks to **extundelete**, we recovered it and got the directory /data/db. Here are our files in the directory, and the files was generated under MongoDB **version 3.4**. Folder diagnostic.data: Folder journal: # Step 1: try to run mongod as normal **a)** We ran mongod --port 27017 --dbpath /data/db --bind_ip_all and mongo, and expected there should have a user-defined database wecaXX, **But, it did not show up.**

> show dbs
admin   0.000GB
config  0.000GB
local   0.000GB

In Robo3T **b)** Then I tried to run mongod --port 27017 --dbpath /data/db --bind_ip_all --repair. The result was:

2019-03-25T14:10:02.170+0800 I CONTROL  [main] Automatically disabling TLS 1.0, to force-enable TLS 1.0 specify --sslDisabledProtocols 'none'
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten] MongoDB starting : pid=23018 port=27017 dbpath=/data/db 64-bit host=iZj6c0pipuxk17pb7pbaw0Z
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten] db version v4.0.7
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten] git version: 1b82c812a9c0bbf6dc79d5400de9ea99e6ffa025
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.2g  1 Mar 2016
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten] allocator: tcmalloc
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten] modules: none
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten] build environment:
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten]     distmod: ubuntu1604
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten]     distarch: x86_64
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten]     target_arch: x86_64
2019-03-25T14:10:02.191+0800 I CONTROL  [initandlisten] options: { net: { bindIpAll: true, port: 27017 }, repair: true, storage: { dbPath: "/data/db" } }
2019-03-25T14:10:02.191+0800 W STORAGE  [initandlisten] Detected unclean shutdown - /data/db/mongod.lock is not empty.
2019-03-25T14:10:02.194+0800 I STORAGE  [initandlisten] Detected data files in /data/db created by the 'wiredTiger' storage engine, so setting the active storage engine to 'wiredTiger'.
2019-03-25T14:10:02.194+0800 W STORAGE  [initandlisten] Recovering data from the last clean checkpoint.
2019-03-25T14:10:02.194+0800 I STORAGE  [initandlisten]
2019-03-25T14:10:02.194+0800 I STORAGE  [initandlisten] ** WARNING: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine
2019-03-25T14:10:02.194+0800 I STORAGE  [initandlisten] **          See http://dochub.mongodb.org/core/prodnotes-filesystem 
2019-03-25T14:10:02.194+0800 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=256M,session_max=20000,eviction=(threads_min=4,threads_max=4),config_base=false,statistics=(fast),log=(enabled=true,archive=true,path=journal,compressor=snappy),file_manager=(close_idle_time=100000),statistics_log=(wait=0),verbose=(recovery_progress),
2019-03-25T14:10:02.818+0800 E STORAGE  [initandlisten] WiredTiger error (17) [1553494202:818725][23018:0x7f6119074a40], connection: __posix_open_file, 715: /data/db/WiredTiger.wt: handle-open: open: File exists Raw: [1553494202:818725][23018:0x7f6119074a40], connection: __posix_open_file, 715: /data/db/WiredTiger.wt: handle-open: open: File exists
2019-03-25T14:10:02.818+0800 I STORAGE  [initandlisten] WiredTiger message unexpected file WiredTiger.wt found, renamed to WiredTiger.wt.1
2019-03-25T14:10:03.832+0800 I STORAGE  [initandlisten] WiredTiger message [1553494203:832267][23018:0x7f6119074a40], txn-recover: Main recovery loop: starting at 4/11366912 to 5/256
2019-03-25T14:10:03.832+0800 I STORAGE  [initandlisten] WiredTiger message [1553494203:832674][23018:0x7f6119074a40], txn-recover: Recovering log 4 through 5
2019-03-25T14:10:03.898+0800 I STORAGE  [initandlisten] WiredTiger message [1553494203:898252][23018:0x7f6119074a40], txn-recover: Recovering log 5 through 5
2019-03-25T14:10:03.964+0800 I STORAGE  [initandlisten] WiredTiger message [1553494203:964880][23018:0x7f6119074a40], txn-recover: Set global recovery timestamp: 0
2019-03-25T14:10:03.998+0800 I RECOVERY [initandlisten] WiredTiger recoveryTimestamp. Ts: Timestamp(0, 0)
2019-03-25T14:10:03.999+0800 E STORAGE  [initandlisten] WiredTiger error (17) [1553494203:999855][23018:0x7f6119074a40], WT_SESSION.create: __posix_open_file, 715: /data/db/_mdb_catalog.wt: handle-open: open: File exists Raw: [1553494203:999855][23018:0x7f6119074a40], WT_SESSION.create: __posix_open_file, 715: /data/db/_mdb_catalog.wt: handle-open: open: File exists
2019-03-25T14:10:04.000+0800 I STORAGE  [initandlisten] WiredTiger message unexpected file _mdb_catalog.wt found, renamed to _mdb_catalog.wt.1
2019-03-25T14:10:04.015+0800 I CONTROL  [initandlisten]
2019-03-25T14:10:04.015+0800 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2019-03-25T14:10:04.015+0800 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2019-03-25T14:10:04.015+0800 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2019-03-25T14:10:04.015+0800 I CONTROL  [initandlisten]
2019-03-25T14:10:04.015+0800 I CONTROL  [initandlisten]
2019-03-25T14:10:04.015+0800 I CONTROL  [initandlisten] ** WARNING: soft rlimits too low. rlimits set to 3824 processes, 65535 files. Number of processes should be at least 32767.5 : 0.5 times number of files.
2019-03-25T14:10:04.020+0800 I STORAGE  [initandlisten] createCollection: admin.system.version with provided UUID: 47d8713d-ac61-4081-83bf-60209ad60a7d
2019-03-25T14:10:04.034+0800 W ASIO     [initandlisten] No TransportLayer configured during NetworkInterface startup
2019-03-25T14:10:04.036+0800 I COMMAND  [initandlisten] setting featureCompatibilityVersion to 4.0
2019-03-25T14:10:04.036+0800 I STORAGE  [initandlisten] repairDatabase admin
2019-03-25T14:10:04.037+0800 I STORAGE  [initandlisten] Repairing collection admin.system.version
2019-03-25T14:10:04.040+0800 I STORAGE  [initandlisten] Verify succeeded on uri table:collection-0-4352287918877967674. Not salvaging.
2019-03-25T14:10:04.048+0800 I INDEX    [initandlisten] build index on: admin.system.version properties: { v: 2, key: { _id: 1 }, name: "_id_", ns: "admin.system.version" }
2019-03-25T14:10:04.048+0800 I INDEX    [initandlisten]          building index using bulk method; build may temporarily use up to 500 megabytes of RAM
2019-03-25T14:10:04.055+0800 I STORAGE  [initandlisten] finished checking dbs
2019-03-25T14:10:04.055+0800 I STORAGE  [initandlisten] WiredTigerKVEngine shutting down
2019-03-25T14:10:04.056+0800 I STORAGE  [initandlisten] Shutting down session sweeper thread
2019-03-25T14:10:04.057+0800 I STORAGE  [initandlisten] Finished shutting down session sweeper thread
2019-03-25T14:10:04.140+0800 I STORAGE  [initandlisten] shutdown: removing fs lock...
2019-03-25T14:10:04.140+0800 I CONTROL  [initandlisten] now exiting
2019-03-25T14:10:04.140+0800 I CONTROL  [initandlisten] shutting down with code:0

**c)** After repairing I re-ran mongod --port 27017 --dbpath /data/db --bind_ip_all, it still showed **nothing** (the same result as step a)). # Step 2: Use wiredTiger tool Since that didn't work as I expected, I started to find any other tools or approach that may help me. Here is the link Recovering a WiredTiger collection from a corrupt MongoDB installation which introduces how to use WiredTiger to recover collections. And I decided to have a try. I created a folder db_backup and put all my files into it. And I created a folder wiredtiger-3.0.0 under db_backup.I installed wiredtiger in folder wiredtiger-3.0.0: **a) To salvage the collection**

root@iZj6c0pipuxk17pb7pbaw0Z:/data/db_backup/# cd ./wiredtiger-3.0.0
root@iZj6c0pipuxk17pb7pbaw0Z:/data/db_backup/wiredtiger-3.0.0# ./wt -v -h ../db_for_wt -C "extensions=[./ext/compressors/snappy/.libs/libwiredtiger_snappy.so]" -R salvage collection-23--360946650994865928.wt
        WT_SESSION.salvage 100

Based on the article above, it means that we have successfully salvaged this collection. # Error Occured: **b) To dump the collection**

root@iZj6c0pipuxk17pb7pbaw0Z:/data/db_backup/wiredtiger-3.0.0# ./wt -v -h ../../db_backup -C "extensions=[./ext/compressors/snappy/.libs/libwiredtiger_snappy.so]" -R dump -f ../collection.dump collection-23--360946650994865928
lt-wt: cursor open(table:collection-23--360946650994865928) failed: No such file or directory

Note that the command above was indeed ended without .wt. I have checked my directory argument and I found no fault. In the picture, the file collection-23--360946650994865928.wt **is right Here**. The snapshot of collection-23--360946650994865928.wt just has been salvaged. We could see some English or Chinese characters in it. And that data truly is from one of collection of database wecaXX. # Questions: **1)** Now, I'm stuck by dumping the collection. Does anyone know what's wrong with that? **2)** collection-23--360946650994865928.wt contains our most important data. If we could not restore the whole database, extracting data from that file will be still very useful. However, if we do copy paste manually, not all the characters are well written; they still need to be decrypted. Does anyone know how to do that? **3)** is it possible that we did not recover all the files (or the entire body of all the files) under /data/db? **Any comment or answer will be appreciated!!**

Piecemeal restores make it easier to recover from whoops deletes. Rather than restoring a whole database, I can restore just the primary and the filegroup of the table with the whoops delete [to a new database](https://web.archive.org/web/20150119121213/http://sqlblog.com/blogs/tibor_karaszi/archive/2009/06/25/table-restore-and-filegroups.aspx) and transfer the data over. This has tempted me to make the primary as small as possible by putting each table in its own filegroup. I often hear that everything in SQL Server has a downside. What are the downsides of putting every table in its own filegroup?

In Cassandra, in addition to the main sstable file, which ends with big-Data.db, there appear to be 7 other files, namely: - big-CompressionInfo.db - big-Digest.crc32 - big-TOC.txt - big-Summary.db - big-Index.db - big-Filter.db - big-Statistics.db I'm not sure how important the files are, but while moving files, I lost all files except the big-data.db file. Is there any way I can recreate those files and make the sstable usable? I'm getting the error

java.lang.AssertionError: Primary index component is missing for sstable /home/cassandra/data/gps/module-4792fbc0b82111ed9bb69bcebdf415e4/nb-171026-big
        at org.apache.cassandra.io.sstable.format.SSTableReader.open(SSTableReader.java:467)
        at org.apache.cassandra.io.sstable.format.SSTableReader.open(SSTableReader.java:381)
        at org.apache.cassandra.io.sstable.format.SSTableReader$2.run(SSTableReader.java:551)
        at org.apache.cassandra.concurrent.FutureTask$1.call(FutureTask.java:81)
        at org.apache.cassandra.concurrent.FutureTask.call(FutureTask.java:47)
        at org.apache.cassandra.concurrent.FutureTask.run(FutureTask.java:57)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
        at java.base/java.lang.Thread.run(Thread.java:829)

I have a bigger database with around 1000 tables. Unfortunately after a OS X update everything vanished, but I was able to recover all the file that now consists of only .frm and libd files. Following answers from here https://dba.stackexchange.com/questions/16875/restore-table-from-frm-and-ibd-file I did the following, test only on one table 1.Installed MAMP 2.Created a new database that has the same name as the old (sunflower) 3.Using mysqlfrm I was able to get a query that created me the table structure( First at once I am trying to recover only one table): CREATE TABLE sunflower.USDT@002dNXT_hour ( id int(11) NOT NULL AUTO_INCREMENT, datetime datetime NOT NULL, open text, close text, low text, high text, volume text, btc_volume text, PRIMARY KEY PRIMARY (datetime), UNIQUE KEY id (id) ) ENGINE=InnoDB; Here I encountered with one problem. Running this query it created me a file called : usdt@0040002dnxt_hour.ibd why my old file is usdt@002dnxt_hour.ibd. (Renaming 1 file will be fine, but renaming 1000 is quite complicated) 4. Then I run : ALTER TABLE USDT@002dNXT_hour DISCARD TABLESPACE; 5. Copied the old usdt@002dnxt_hour.ibd file to my database directory. 6. Now I tried to run ALTER TABLE USDT@002dNXT_hour IMPORT TABLESPACE; which complained that such a table does not exits, so I renamed my file to usdt@0040002dnxt_hour.ibd. 7. Running now the same script trows me this error : #1808 - Schema mismatch (Table has ROW_TYPE_COMPACT row format, .ibd file has ROW_TYPE_DYNAMIC row format.) So I changed the row type be running : ALTER TABLE USDT@002dNXT_hour ROW_FORMAT=DYNAMIC;. 8. Now running the import query again throws me this error : Table sunflower.usdt@002dnxt_hour doesn't exist, however the table exits. Is my approach is good ? If yes, where I make the mistake ?

### My Question, Up Front I've inherited some monitoring that hinges on the last_commit_time column in sys.dm_hadr_database_replica_states. My problem is that the documentation for this column is all over the place, bordering on self-contradicting, so what does that column actually mean? Is it RPO or something else? Assume that all of my replicas are synchronous. ### My Research The bulk of [the documentation for the column](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-database-replica-states-transact-sql?view=azuresqldb-current) says this: > On the primary replica, each secondary database row displays the time that the secondary replica that hosts the secondary database reported back to the primary replica. The difference in time between the primary-database row and a given secondary-database row represents approximately the recovery point objective (RPO), assuming that the redo process is caught up and that the progress was reported back to the primary replica by the secondary replica. The final sentence causes me the greatest confusion. What is the "redo process"? That term appears almost nowhere else in the documentation. If "redo process" refers to the redo thread, then this _cannot_ have anything to do with RPO (replaying transaction logs has nothing to do with RPO, only hardening them does). But if last_commit_time refers only to the delay in hardening the log, then how can it differ from secondary_lag_seconds? That column is defined as > On the primary replica, the synchronization delay (lag) for each secondary database is calculated as the number of seconds since the hardening of the the earliest hardened LSN on the primary replica that is yet to be hardened on the secondary replica. The only edge case that I can think of is if a log has hardened on a secondary but the primary somehow doesn't know. But if that's what they meant, then this is an unusual way to say it. This documentation also links to [Calculation of secondary database RPO](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver17&viewFallbackFrom=azuresqldb-current&tabs=new-limits#calculation-of-secondary-database-rpo) which makes **four** claims about what last_commit_time means. [The first](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver17&tabs=new-limits#estimate-potential-data-loss-rpo) claims > A simpler way to estimate Tdata_loss is to use last_commit_time. The DMV on the primary replica reports this value for all replicas. You can calculate the difference between the value for the primary replica and the value for the secondary replica to estimate how fast the log on the secondary replica is catching up to the primary replica. As stated previously, this calculation doesn't tell you the potential data loss based on how fast the log is generated, but it should be a close approximation. which indicates that it's useful for an _approximation_ of RPO. [The second](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver17&viewFallbackFrom=azuresqldb-current&tabs=new-limits#calculation-of-secondary-database-rpo) claims > For the primary database, the last_commit_time is the time when the latest transaction has been committed. For the secondary database, the last_commit_time is the latest commit time for the transaction from the primary database that has been successfully hardened on the secondary database as well. [...] However, a gap between these two values is the duration in which pending transactions haven't been hardened on the secondary database, and might be lost in the event of a failover. which indicates that it can calculate RPO properly. [The third]( is a flowchart **titled calculate-rpo.png**, which says the same as the second. [The fourth](https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver17&tabs=new-limits#create-a-stored-procedure-to-estimate-rpo) is a stored procedure definition that says much the same. So _most_ signs are that RPO is well approximated by doing a calculation using last_commit_time, but then what was the definition of the column talking about?

I did something careless. I deleted a schema. I have backups of the \mysql\...\data\refadmin folder so I (hope) I have all the tables and data. That's backed up daily. When I look at work bench, I still have the connection which works, and I can see the schema refadmin but there are no tables or data. I don't have any recent dumps. The MYSQL server is on Windows 2012 R2. The PC I am using is Windows 10 with Workbench 8. I am using mysql 8.0.0.25 I believe. Is there a way to recover the tables and data? thank you.

For example, I have a level 0 and level 1 RMAN backupset files like inr00_0413_* inr01_0413_1_*
Is it necessary to do this when recover from RMAN level 1 backup: restore controlfile from inr01_0413_1_ctl_file_9jr2v0te_1_1_20160413; Or I just: catalog start with '/u02/rmanbackup/'; recover database; If not restore controlfile, after resotre and recover will get error: >ORA-16004: backup database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA1/rac/datafile/system1.dbf'
Can't do SQL> recover database using backup controlfile until cancel Or RMAN> sql 'alter database open read only'; Backupset files: [oracle@dbsrv01] ll /u02/rmanbackup/ Apr 13 20:53 block_change_tracking.ctf Apr 13 19:44 inr00_0413_arc_8pr2uqq2_1_1_RAC_20160413_42265 Apr 13 19:44 inr00_0413_arc_8qr2uqq2_1_1_RAC_20160413_42266 Apr 13 19:44 inr00_0413_ctl_file_8rr2uqqa_1_1_20160413 Apr 13 19:44 inr00_0413_dbf_8er2uqcc_1_1_RAC_20160413_42254 Apr 13 19:46 inr00_0413_dbf_8fr2uqcc_1_1_RAC_20160413_42255 Apr 13 19:48 inr00_0413_dbf_8gr2uqcc_1_1_RAC_20160413_42256 Apr 13 19:50 inr00_0413_dbf_8hr2uqcc_1_1_RAC_20160413_42257 Apr 13 19:51 inr00_0413_dbf_8ir2uqcc_1_1_RAC_20160413_42258 Apr 13 19:52 inr00_0413_dbf_8jr2uqcc_1_1_RAC_20160413_42259 Apr 13 19:52 inr00_0413_dbf_8kr2uqcc_1_1_RAC_20160413_42260 Apr 13 19:52 inr00_0413_dbf_8lr2uqcc_1_1_RAC_20160413_42261 Apr 13 19:52 inr00_0413_dbf_8mr2uqcc_1_1_RAC_20160413_42262 Apr 13 19:52 inr00_0413_dbf_8nr2uqcc_1_1_RAC_20160413_42263 Apr 13 19:52 inr00_0413_dbf_8or2uqcc_1_1_RAC_20160413_42264 Apr 13 19:52 inr00_0413_spfile_8sr2uqqc_1_1_20160413 Apr 13 20:50 inr01_0413_1_arc_9gr2v0t5_1_1_RAC_20160413_42288 Apr 13 20:50 inr01_0413_1_arc_9hr2v0t5_1_1_RAC_20160413_42289 Apr 13 20:50 inr01_0413_1_arc_9ir2v0tc_1_1_RAC_20160413_42290 Apr 13 20:50 inr01_0413_1_ctl_file_9jr2v0te_1_1_20160413 Apr 13 20:50 inr01_0413_1_dbf_95r2v0sv_1_1_RAC_20160413_42277 Apr 13 20:50 inr01_0413_1_dbf_96r2v0sv_1_1_RAC_20160413_42278 Apr 13 20:50 inr01_0413_1_dbf_97r2v0t0_1_1_RAC_20160413_42279 Apr 13 20:50 inr01_0413_1_dbf_98r2v0t0_1_1_RAC_20160413_42280 Apr 13 20:50 inr01_0413_1_dbf_99r2v0t0_1_1_RAC_20160413_42281 Apr 13 20:50 inr01_0413_1_dbf_9ar2v0t0_1_1_RAC_20160413_42282 Apr 13 20:50 inr01_0413_1_dbf_9br2v0t0_1_1_RAC_20160413_42283 Apr 13 20:50 inr01_0413_1_dbf_9cr2v0t0_1_1_RAC_20160413_42284 Apr 13 20:50 inr01_0413_1_dbf_9dr2v0t0_1_1_RAC_20160413_42285 Apr 13 20:50 inr01_0413_1_dbf_9er2v0t0_1_1_RAC_20160413_42286 Apr 13 21:06 inr01_0413_1_dbf_9fr2v0t0_1_1_RAC_20160413_42287 Apr 13 21:06 inr01_0413_1_spfile_9kr2v0tg_1_1_20160413

I copied a mysql 8.0.26 database from a Linux server to my Windows box but mysqld won't start. Either it's this (with lower_case_table_names=1): [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0'). [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. [ERROR] [MY-010119] [Server] Aborting Or this (with lower_case_table_names=0): [ERROR] [MY-010158] [Server] The server option 'lower_case_table_names' is configured to use case sensitive table names but the data directory is on a case-insensitive file system which is an unsupported combination. Please consider either using a case sensitive file system for your data directory or switching to a case-insensitive table name mode. [ERROR] [MY-010119] [Server] Aborting Is this because the Windows fs is case insensitive? How can I run the database on Windows? Or is this another issue? The database crashed on Linux and I try to repair it on Windows.

I have a Mongo replica set (MongoDB server version: 4.0.3) with primiry, secondary and arbiter. Size of database is about 7 Tb. I add a member to replica set. It stucks in sturtup2 mode for a month! In first day it synhronize 3 Tb and then go slower and slower synchronyzing about 200 Gb each day. Previous member I add to this replica set was added for about a two or three days. I remove all load from replica set, there is nothing else take place - only synchronisation. Maybe there is a way to tell it to synchronyse faster?

I have written a shell script to do an incremental backup of MySQL database. For **point in time recovery** I'm using the binary logs generated in /var/lib/mysql directory. For **point-in-time recovery**, Im creating an sql file using the following statement. mysqlbinlog --start-datetime="$STARTTIME" --stop-datetime="$ENDTIME" $file > $NOW.test.sql The incremental backup happens such that the **$STARTTIME** is going to be the **end time of the Fullbackup** and **$file** is the **list of log files generated since the fullbackup endtime to the time of start of incremental backup**. My question is how can I create a .sql file in /var/lib/mysql folder and move that file to the destination . When I try the above statement, it says permission denied. How can i achieve this? I know the permissions for the file has to be changed. Is there any alternative for this.

I was working on my Ubuntu machine and left it on to grab food. Upon returning, I found it had shut itself down. It was not power failture because I have a home backup system and lights never go off in my house. When I turned it on, it went straight to Busybox because apparently the fsck tool had somehow moved the entire contents of the root partition and everything else to **lost+found** on my laptop's primary drive, which is an SSD. So I backed up the lost+found directory to an external HDD and installed Ubuntu 22, from Ubuntu 20. I did not lose personal data because I had my /home directory on my secondary HDD. However, everything else was on the same partition on the SSD. So, after perusing through the lost+found files, I was able to extract all the files from /lib/postgresql/. Now because Postgresql uses OIDS, unlike mysql which uses names, I had to figure out the databases based on the information on this website here https://www.postgresql.fastware.com/blog/how-postgresql-maps-your-tables-into-physical-files For reference, I was able to recover MySQL tables since they simply use table names. With PostgreSQL however, I'm not sure if just copying the **$PGDATA/base/** files will work given the information mentioned on that website. Is it possible to extract the data (through some tool) from the base files or to re-import them back into an active cluster/instance?

I mange to take backups and recover them to my postgres-server. Now I want to see what PITR is able to do with these steps 1. create a backup (20220111T062908) 1. wait a minute 1. create a new database (at 06:34:11) 1. run a barman recover operation - pg_ctl stop (on postgres-server) - barman recover (on barman server) - pg_ctl start (on postgres-server) - check for my database from 06:34:11 which is not there (on postgres-server) It looks like the recovery is getting me to the point of the backup (06:29:08) but not the the --target-time (06:35:00). Or do I understand something really wrong about the PITR logic? Even though my gut tells me it can't be ... do I need another backup after 06:34 and then be able to do a PITR to a PIT between those 2 backups or am I missing something along the way? --- these are the barman recover details:

:~> barman recover vm-51150-0196 20220111T062908 --remote-ssh-command 'ssh postgres@[postgres-server]' --target-time 20220111T063500 /opt/db/data/postgres/data
        Starting remote restore for server vm-51150-0196 using backup 20220111T062908
        Destination directory: /opt/db/data/postgres/data
        Remote command: ssh postgres@[postgres-server]
        Doing PITR. Recovery target time: '2022-01-11 06:35:00+01:00'
        Using safe horizon time for smart rsync copy: 2022-01-11 06:29:08.521311+01:00
        Copying the base backup.
        Copying required WAL segments.
        Generating recovery configuration
        Identify dangerous settings in destination directory.
        IMPORTANT
        These settings have been modified to prevent data losses
        postgresql.conf line 242: archive_command = false
        postgresql.auto.conf line 5: recovery_target_time = None
        WARNING
        You are required to review the following options as potentially dangerous
        postgresql.conf line 760: include_if_exists = 'postgresql.conf.d/01_postgres_barman.conf' # include file only if it exists
        Recovery completed (start time: 2022-01-11 07:02:20.425453, elapsed time: 7 seconds)
        Your PostgreSQL server has been successfully prepared for recovery!

We have a PostgreSQL 13 replication cluster where one of the standbys is setup with a recovery_min_apply_delay="x hr", which could be useful in the case of data corruption. For a scenario where an accidental delete happened on the *master server*, but not yet applied to the *standby server*. In this case, I am trying to extract the data from standby server by removing the *WAL replay delay* and adding recovery_target_time to a point before delete happened. To this point I am successful, but once I done with pulling data from standby server to master (I am thinking of postgres_fdw), I need to resume replication. But when I run pg_wal_replay_resume(), it is creating a new timeline and is no longer in the replication cluster. Is there any way to replay WAL other than using recovery_target_time? Giving an example of what I am trying to achieve, *Let's say recovery_min_apply_delay=2hr , an accidental delete happened at 03:50 AM, at this time transactions committed at standby is transactions that happened on or before 01:50 AM. If I wait another 2hr and at 05:49 AM if I am able to pause standby WAL replay I will be able see a snapshot of data which is "right before" DELETE. Then I will use postgres_fdw to pull data from standby.* How do I achieve "right before" in a precise systematic way? Also I would be able to resume WAL replay.

So my production db in RDS apparently either crashed or was rebooted during a version update, and upon being restarted mysql couldn't load one of the innodb tables: as evidenced by the following error in the logs. 140324 16:22:23 [ERROR] Cannot find or open table dbname/table_name from the internal data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn't support. See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html how you can resolve the problem. The problem is that no one noticed for three days that the table was missing and therefore my automated snapshots are useless (I only retain them that long) as all the snapshots have the same problem. During my attempts to fix things I have had indications from the db that indicate that the .frm and .ibd files are all inplace. The show tables command lists the table (so frm is there), and I get the error below if I (on a copy) drop the table and try and recreate it. 140324 16:46:05 InnoDB: Error creating file './dbname/table_name.ibd'. 140324 16:46:05 InnoDB: Operating system error number 17 in a file operation. InnoDB: Error number 17 means 'File exists'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html InnoDB: The file already exists though the corresponding table did not InnoDB: exist in the InnoDB data dictionary. Have you moved InnoDB InnoDB: .ibd files around without using the SQL commands InnoDB: DISCARD TABLESPACE and IMPORT TABLESPACE, or did InnoDB: mysqld crash in the middle of CREATE TABLE? You can InnoDB: resolve the problem by removing the file './dbname/table_name.ibd' InnoDB: under the 'datadir' of MySQL. I suspect there was some issue with an index before the instance was restarted, or if it indeed crashed then perhaps that caused a corruption. I managed to recreate the data (probably) from logs though it's only a guess, not a sure thing. So I'm asking what I should do at this point. I'd love to find a solution that allows me to get that table back with the data it had, but if I have to delete it and recreate it that's also ok. Obviously I can't touch any mysql data files as it's an rds instance. Mysql version is 5.5.27.

There are numerous question (in this site and elsewhere on the internet) about how to recover innodb database from *.frm and *.ibd file (the point: when you have the undamaged ibdata1). -------- ##Situation: I assigned to a server where the mysql database is just crashed and can't started. To let them start, i saved the whole /var/lib/mysql folder, then deleted the ibdata1, ib_logfile0, and ib_logfile101, in order to let the mysql server start. That's worked, MyISAM and some InnoDB table got back to online. However there's some databases where some InnoDb table can't back to operation. It turned out, that there's no backup from the databases (and they operating 3+ years). Everybody excepted that plesk makes dayli database backup, but actually not (Schrödinger's backup). After this, it's also turned out that the ibdata1 I saved has been previously overwritten or deleted and a new one created by the mysql (I view the file with hexedit and it's filled fully with zeros). I also looked for the previous file in the lost+found but no result. There are two database which is crutial to restore, one wordpress site and the plesk database. Then i copied the saved mysql directory to my machine and started trying to restore the broken tables. --------- ##Problem: I have a crashed MySQL database where the ibdata1 file has benn lost and no database backup available. On my machine is the same mariadb version installed, so i moved the save mysql directory from the server and started to restore as much as i can from the two databases requested by the owner. I already wrote the innodb_force_recovery = 6 line into /etc/mysql/my.cnf to help recover the broken tables. As described here: https://dba.stackexchange.com/questions/120864/corrupt-innodb-start-mysqld-only-innodb-force-recovery-6 Then i tried to reimport the *.ibd files as described here: https://medium.com/magebit/recover-innodb-database-from-frm-and-ibd-files-99fdb0deccad [1] . An that's worked on smaller tables. On other bigger tables like wp_posts when execute ALTER TABLE wp_posts IMPORT TABLESPACE the mysql server crashes: InnoDB: Trying to access page number 68160 in space 15 space name admin_wordpress/wp_posts, which is outside the tablespace bounds. Byte offset 0, len 16384 i/o type 10. So the Question: How to restore tables with this kind failure? What i found: 1) https://twindb.com/recover-corrupt-mysql-database/ For this i need ibdata1. So it's a dead end. 2) Percona data recovery tool for innodb Theorically this can connect the existing *.ibd to the new ibdata1 However i can't find binary package, the debian package doesn't contains ibdconnect command. I also found the source: https://github.com/jiramot/percona-data-recovery-tool-for-innodb but this can't be compiled. I tried to resolv the compilation an linking errors but this might tacke more time and maybe this solution doesnot work. 3) And the last chance: Rebuild the site. I can extract string from the ibd files strings admin_wordpress/wp_posts.ibd and that way extract the text of the posts on the wordpress page, nut rebiulding a site this way is a Sisyphean task. (And this mathod can't applied to the other database is should restore) __I there any other working method to extract or restore data from *.ibd files? Even extracting the records to eg.: JSON or BSON or CSV is gonna be good enought to move forward in the recovering process.__ ---

I'm trying to list backups in RMAN completed within a certain date range. This works fine when I can connect to the target database and recovery catalog. However, I'm assuming a situation where I am not able to connect to target database, but only have access to the recovery catalog. When I input a query with date specified, it fails, saying it isn't connected to target db.

rman catalog rco/Password;
SET DBID 12345678;

RMAN> LIST BACKUP COMPLETED BETWEEN '01-DEC-21' AND '10-DEC-21';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 12/14/2021 11:54:04
RMAN-06171: not connected to target database

I tried to ALTER a table to add a FOREIGN KEY as: ALTER TABLE artists ADD FOREIGN KEY(country_id) REFERENCES countries(country_id) ON DELETE CASCADE; and I received an error of ERROR 2013 (HY000): Lost connection to MySQL server during query I tried twice and the same error. The third time, my table was gone. How can I recover the dropped table? MySQL server (Ver 14.14 Distrib 5.7.21, for Linux (x86_64)) is on localhost. The entries in other tables which have FK to this table still exist. Thus, I believe the table has not been deleted, but InnoDB map is damaged. I checked the files, table_name.ibd exists but table_name.frm is missing. I cannot CREATE the table as the TABLESPACE already exists. How can I recover or create the table schema to use the available data? My concern is about the FOREIGN KEYS, which still exist in other tables. I had a backup, but it is too old to restore the backup. I have to keep the current version. And it is a huge database. The table has about 1 million rows. The ibd file is over 100MB.

I dropped a large table (80G) Friday morning and got a crash: InnoDB: ###### Diagnostic info printed to the standard error stream 2019-04-19T18:48:55.445507Z 0 [ERROR] [MY-012872] [InnoDB] Semaphore wait has lasted > 37617336 seconds. We intentionally crash the server because it appears to be hung.[FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung. 2019-04-19T18:48:55.445541Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:625 thread 140252955932416 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com . InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 18:48:55 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. ...lots more... The server came back up and the log indicated it was ready for connections: ... 2019-04-22T12:53:51.792574Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.15) starting as process 26956 2019-04-22T12:53:54.631860Z 0 [System] [MY-010229] [Server] Starting crash recovery... 2019-04-22T12:53:54.639196Z 0 [System] [MY-010232] [Server] Crash recovery finished. 2019-04-22T12:53:54.708512Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2019-04-22T12:53:54.754962Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.15' socket: '/data/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. I tried connecting locally and remotely with mysql client, various programs, etc., but cannot complete the connection process - it just hangs. The connection protocol initiates, but then just sits there after negotiation (this is from monitoring with tcpdump): 10:44:02.814237 IP 10.70.250.33.snip-slave > 10.70.250.50.mysql: Flags [S], seq 3534733756, win 14600, options [mss 1460,sackOK,TS val 2565739642 ecr 0,nop,wscale 7], length 0 10:44:02.814273 IP 10.70.250.50.mysql > 10.70.250.33.snip-slave: Flags [S.], seq 4211652446, ack 3534733757, win 28960, options [mss 1460,sackOK,TS val 239668057 ecr 2565739642,nop,wscale 7], length 0 ... 10:44:03.042941 IP 10.70.250.50.mysql > 10.70.250.33.snip-slave: Flags [P.], seq 2784:2819, ack 858, win 252, options [nop,nop,TS val 239668286 ecr 2565739871], length 35 10:44:03.082582 IP 10.70.250.33.snip-slave > 10.70.250.50.mysql: Flags [.], ack 2819, win 160, options [nop,nop,TS val 2565739911 ecr 239668286], length 0 The mysqld process has been running at nearly 100% CPU, for over the weekend even, but nothing seems to change. I can't stop the server with systemd, as it'll just sit there too. I can (and have) killed the mysqld process several times today and started it back up, but again nothing seems to change. Most recently I did that to force innodb recovery, which shows in the log, but is no help since I still can't connect with _anything_. Looking with iostat, it appears to be beating the daylights out of the general log (/dbdata/var/lib/mysql/data/mysql/general_log.CSV), which is quite large - over half a TB. I don't really care about the table that was dropped, I just need the rest of the databases to be accessible, and I'm stuck. Help, please!

If we need to choose a RDMS option to run, not in a server environment, but somewhere (not even a building) where power can go off at any moment a few times a day, what options would be the best? Also if that system would run on a CoW FS like ZFS or BTRFS, would that change anything? The hardware will be running unattended, so my biggest priority is returning to a safe working state after sudden power loss without admin intervention. In the other hand a few seconds or even minutes of data loss before the power loss would be no problem in this application. Of course the best solution would be to use a UPS, but that's not always possible. The reasons for UPS being a problem can be space, weight, costs, regulations...