Tag: restore

postgresql – Restore mysql db from AWS AMI?

We have one EC2 instance running our mysql database server.

due to a security incident we had to delete the current EC2 instance as database was completely messed up with some sort of encryption.

So we had to recover it from Snapshot/AMI that are being taken every 6 hours. we have past 1 months snapshots and AMI’s available for this EC2 instance.

This EC2 instance has only one SSD (Persistent Block Storage) EBS volume, which is part of the Snapshots/AMI’s.

We also have phpmyadmin installed but its not able to connect to DB for obvious reasons!

Below are the details of OS and the mysql Service status.

# cat /etc/release

DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=20.04
DISTRIB_CODENAME=focal
DISTRIB_DESCRIPTION="Ubuntu 20.04.1 LTS"
NAME="Ubuntu"
VERSION="20.04.1 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.1 LTS"
VERSION_ID="20.04"
HOME_URL="https://www.ubuntu.com/"
SUPPORT_URL="https://help.ubuntu.com/"
BUG_REPORT_URL="https://bugs.launchpad.net/ubuntu/"
PRIVACY_POLICY_URL="https://www.ubuntu.com/legal/terms-and-policies/privacy-policy"
VERSION_CODENAME=focal
UBUNTU_CODENAME=focal

# mysqld –version

/usr/sbin/mysqld  Ver 8.0.22-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

# systemctl status mysql.service

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: activating (start) since Mon 2021-03-01 13:19:50 UTC; 241ms ago
    Process: 79728 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 79736 (mysqld)
     Status: "Server startup in progress"
      Tasks: 1 (limit: 1164)
     Memory: 91.3M
     CGroup: /system.slice/mysql.service
             └─79736 /usr/sbin/mysqld

Mar 01 13:19:50 ip-172-31-93-250 systemd(1): Starting MySQL Community Server...
Mar 01 13:19:52 ip-172-31-93-250 systemd(1): mysql.service: Main process exited, code=exited, status=1/FAILURE
Mar 01 13:19:52 ip-172-31-93-250 systemd(1): mysql.service: Failed with result 'exit-code'.
Mar 01 13:19:52 ip-172-31-93-250 systemd(1): Failed to start MySQL Community Server.
Mar 01 13:19:53 ip-172-31-93-250 systemd(1): mysql.service: Scheduled restart job, restart counter is at 328.
Mar 01 13:19:53 ip-172-31-93-250 systemd(1): Stopped MySQL Community Server.
Mar 01 13:19:53 ip-172-31-93-250 systemd(1): Starting MySQL Community Server...

# journalctl -xe

Mar 01 13:14:20 ip-XXX-XXX-XXX-XXX systemd(1): mysql.service: Main process exited, code=exited, status=1/FAILURE
-- Subject: Unit process exited
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- An ExecStart= process belonging to unit mysql.service has exited.
--
-- The process' exit code is 'exited' and its exit status is 1.
Mar 01 13:14:20 ip-XXX-XXX-XXX-XXX systemd(1): mysql.service: Failed with result 'exit-code'.
-- Subject: Unit failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- The unit mysql.service has entered the 'failed' state with result 'exit-code'.
Mar 01 13:14:20 ip-XXX-XXX-XXX-XXX systemd(1): Failed to start MySQL Community Server.
-- Subject: A start job for unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- A start job for unit mysql.service has finished with a failure.
--
-- The job identifier is 105083 and the job result is failed.
Mar 01 13:14:21 ip-XXX-XXX-XXX-XXX systemd(1): mysql.service: Scheduled restart job, restart counter is at 206.
-- Subject: Automatic restarting of a unit has been scheduled
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- Automatic restarting of the unit mysql.service has been scheduled, as the result for
-- the configured Restart= setting for the unit.
Mar 01 13:14:21 ip-XXX-XXX-XXX-XXX systemd(1): Stopped MySQL Community Server.
-- Subject: A stop job for unit mysql.service has finished
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- A stop job for unit mysql.service has finished.
--
-- The job identifier is 105149 and the job result is done.
Mar 01 13:14:21 ip-XXX-XXX-XXX-XXX systemd(1): Starting MySQL Community Server...
-- Subject: A start job for unit mysql.service has begun execution
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
--
-- A start job for unit mysql.service has begun execution.
--
-- The job identifier is 105149.

Can any of you please help me restore the mysql service with the data/databases?

We are not sure what is the right procedure to follow in this scenario to restore the databases.

Thanks in advance!

Mysql Slave restore from slave server

I have master-slave replication topology in MySql. I have taken the database dump from slave server and restore it on new slave. i just want to know that while pointing it to master what could be MASTER_LOG_POS= in below command.

CHANGE MASTER TO MASTER_HOST=”, MASTER_USER=”, MASTER_PASSWORD=”, MASTER_LOG_FILE=”,MASTER_LOG_POS=;

i have two option

  1. SHOW MASTER STATUS where i will get the MASTER_LOG_FILE name and MASTER_LOG_POS=
  2. show slave status G; where also i will get Master_Log_File and Read_Master_Log_Pos

so which one is correct. I have taken the dump from slave server.

sql server – Can you update a database with transaction logs only, without having to do a full restore?

Initial note I am not a DBA, I am learning on the job.

Where I work we have an antiquated third-party system that sends us a .bak file once a week and log files every hour. To get the previous day’s data in the database we have a process that runs a python script which builds the SQL to to do the full restore. The restore takes about 2 hours every night. We have a request from our end users to get the data as “live” as possible, which is theoretically possible as we receive the log file every hour. However I haven’t found a way to restore log files only, as it appears a full restore has to be done before you can restore transaction log files.

I would like to run a process that loads in the latest log file every hour and then run a full restore once a week (when we receive the latest bak file). Is there anyway of doing this? Say by only restoring the latest log file or by running a partial restore so we don’t have to restore all the files every time?

recovery – Restore partially broken PostgreSQL Database

I have managed to break my own server, and undo months of work.

The thing is, I hosted a nextcloud instance on the server, and had it configured to use WasabiS3 as primary storage, which means the data is still intact.

However, the files are stored in a urn:oid format, which means without the database, the data is pretty much useless.

I need to recover the database that contains the filetable, but I cannot connect to pgsql.

After restoring the corrupted folder to /var/lib/postgresql/12/main, I have successfully managed to start the postgresql server.

Trying to log in using the pgsql command, however, this error is encountered.

psql: error: FATAL:  "base/16408" is not a valid data directory
DETAIL:  File "base/16408/PG_VERSION" is missing.

And, the directory /var/lib/postgresql/12/main/base/16408 is indeed empty.

However, I would at least like to restore part of the database if that is possible.
Is this a possibility?

Thanks in advance.

bootcamp – Restore a NTFS volume back up by Disk Utility

I have a Windows installation which I created with Bootcamp. Then I need to expand the volume to add more Software. So I use Disk Utility to create a DMG image for the Bootcamp volume. But after I have deleted the old Bootcamp volume and create a new larger volume, I cannot restore it with Disk Utility:

enter image description here

Nor asr:

 asr restore --source /Users/phuongnd08/Downloads/BOOTCAMP.dmg  --target /dev/disk0s3 --erase --noverify
    Validating target...done
    Validating source...done
    Erase contents of /dev/disk0s3 ()? (ny): y
    Repartitioning target device...
Could not change the partition type for /dev/disk0s3 - Operation not permitted

I guess due to NTFS? But this sounds very illogical because: you can create image out of it, you should be able to restore it from an image? What should I do now to restore that image?

restore – pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column

Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using Docker images.

A dump of the database was taken from the first machine using

pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc --file=/tmp/foo.dump

and then restored on the database on the second machine using

pg_restore --clean --dbname=foo /tmp/foo.dump

When we view a query result using a GUI software TablePlus, we noticed that the column named coordinates of type Geography contains values that look very different after restoring.

Query Result on 1st Machine:

SELECT coordinates FROM locations LIMIT 5;

enter image description here

Query Result on 2nd Machine (after pg_restore):

SELECT coordinates FROM locations LIMIT 5;

enter image description here

However, our app that queries this database for coordinate data appears to be plotting the data correctly on a map.

Question: Why did the Geography data values in the column coordinates changed, and how can we restore from the dump while keeping the original data values?

Update: Tried using -b when performing pg_dump, but the problem persist.

pg_dump --host=db1.foo.com --dbname=foo --username=postgres -Fc -b --file=/tmp/foo.dump

sql server – Can’t restore database with (apparently) same version

The two managed types of SQL Server in Azure (SQL Database and Managed Instance) are version 12.0.2000.8. Don’t be fooled by the low version number, it is always higher than on-prem meaning you can never restore an “Azure SQL” backup to on-prem.
You cannot perform a backup from SQL Database, but you can do a copy only backup from Managed Instance. This would be my guess.

Perhaps the error message is incorrect, and should have said that backup is from 12.00.2000

If it is the 3:rd type of “SQL Server in Azure”, a VM with SQL Server installed, then it is of any version you like. This should be restoreable.

So, find out what type of SQL you have in Azure, then take it from there.

macos – Restore from a Fusion Drive to a standard hard drive

I have an iMac with a built in Fusion Drive. I have switched to using an external Thunderbolt SSD as my boot disk, and I would now like to back up and wipe the internal Fusion Drive so that it can be a Time Machine volume for my new SSD – I’m just backing it up to make sure I won’t miss any files that are accidentally left on there.

I’ve read about using the ‘Restore…’ functionality in Disk Utility to image one disk onto another, so that you basically have an exact clone of it. However, when I go to “Restore” from my Fusion Drive to a regular disk (separate to the ones I’ve mentioned so far), the Fusion Drive shows as two devices in the Source list (the SSD & the HDD part).

Can you image the entire contents of a Fusion Drive onto a single HDD?