Breaking News: Grepper is joining You.com. Read the official announcement!

recovery with transaction log mysql

Innocent Iguana answered on March 7, 2023 Popularity 3/10 Helpfulness 7/10

answer recovery with transaction log mysql

related Recovery with transaction log sql server

recovery with transaction log mysql

Comment

# Create a database and a table
mysql -u root -p
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE example_table (id INT, name VARCHAR(50));


# Insert some data into the table
INSERT INTO example_table VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');

# Create a backup of the database using the mysqldump utility
mysqldump -u root -p example_db > backup.sql

# Modify the table by deleting a row
DELETE FROM example_table WHERE id=3;

# Create a binary log file to record the changes
FLUSH LOGS;

# Modify the table again by inserting a new row
INSERT INTO example_table VALUES (4, 'Alice');

# Create another binary log file
FLUSH LOGS;

# Simulate a crash by deleting the database
DROP DATABASE example_db;

# Restore the backup
mysql -u root -p example_db < backup.sql

# Apply the binary log files to roll the database forward to the current state
mysqlbinlog binlog.000001 | mysql -u root -p
mysqlbinlog binlog.000002 | mysql -u root -p

# Verify that the database is now in the correct state
mysql -u root -p
USE example_db;
SELECT * FROM example_table;

Popularity 3/10 Helpfulness 7/10 Language sql

Source: Grepper

Tags: mysql recovery sql transaction-log

Link to this answer
Share Copy Link

Contributed on Mar 07 2023

Innocent Iguana

0 Answers Avg Quality 2/10

Closely Related Answers

Recovery with transaction log sql server

Comment

# Create a database and a table
sqlcmd -S localhost -U sa -P yourpassword
CREATE DATABASE example_db;
USE example_db;
CREATE TABLE example_table (id INT, name VARCHAR(50));

# Insert some data into the table
INSERT INTO example_table VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');

# Create a backup of the database using the BACKUP statement
BACKUP DATABASE example_db TO DISK='backup.bak';

# Modify the table by deleting a row
DELETE FROM example_table WHERE id=3;

# Create a transaction log backup to record the changes
BACKUP LOG example_db TO DISK='log_backup.trn';

# Modify the table again by inserting a new row
INSERT INTO example_table VALUES (4, 'Alice');

# Create another transaction log backup
BACKUP LOG example_db TO DISK='log_backup2.trn';

# Simulate a crash by stopping the SQL Server service
sudo systemctl stop mssql-server.service

# Restore the database and the last transaction log backup
RESTORE DATABASE example_db FROM DISK='backup.bak' WITH NORECOVERY;
RESTORE LOG example_db FROM DISK='log_backup2.trn' WITH NORECOVERY;

# Apply the previous transaction log backup to roll the database forward to the state before the crash
RESTORE LOG example_db FROM DISK='log_backup.trn' WITH NORECOVERY;

# Verify that the database is now in the correct state
SELECT * FROM example_table;

Popularity 3/10 Helpfulness 2/10 Language sql

Source: Grepper

Tags: sql-server transaction-log sql recovery

Link to this answer
Share Copy Link

Contributed on Mar 07 2023

Innocent Iguana

0 Answers Avg Quality 2/10

recovery with transaction log mysql

Contents

More Related Answers

recovery with transaction log mysql

Closely Related Answers

Recovery with transaction log sql server

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.