Replication Flashcards

1
Q

tree ways to create back ups

A
  • mysqldump
  • filebackup tables, binlog, logs
  • relica Server with read only replication for big dbs
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

when mysqldump is impractical

A

If db is too big

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

How to backup raw data from a replica

A

Stop the db
> mysqladmin shutdown
> tar cf /tmp/dbbackup.tar ./data

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Command to set the replication source

A

“CHANGE REPLICATION SOURCE TO”

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are SOURCE_LOG_FILE
and
SOURCE_LOG_POS for

A

SOURCE_LOG_FILE and SOURCE_LOG_POS options to tell the replica to re-read the binary logs from that point. This requires that the binary logs still exist on the source server.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Two Commands to before backup a read-only source. To stop replication

  • A source server S1
    *A replica server R1 that has S1 as its source
A

Go to R1

mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH TABLES WITH READ LOCK;

Restart replication

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Handling an Unexpected Halt of a Replica

where can we check which transactions are alreade executed

A

mysql.slave_relay_log_info

By using this transactional storage engine the information is always recoverable, meaning that the replica’s progress information recorded in that repository is always consistent with what has been applied to the database, even in the event of an unexpected server halt

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Which Parameter

Controls the balance between strict ACID compliance for commit operations and higher performance

A

innodb_flush_log_at_trx_commit

(
* The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.

  • With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
  • With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
    )
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

MySQL Replication

A

MySQL Replication is:
* a process
* where data from one MySQL database known as the source (formerly called “master”) is copied over to one or more other databases called replicas (formerly called “slaves”)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

MySQL Replication
Benefits

A
  • if source db down replica can replace source
  • By distributing load improve performance
  • makes back up more easy
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Which Replication types exist

A
  • asynchronous
  • semi-synchronous
  • synchronous
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Asynchronous replication

describe

A
  • one source
  • benefits/useful for scaling read-only operations
  • source do not wait for acknowledgment
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Asynchronous replication

benefits / problems

A

Benefits

  • Scalabillity
  • Performance
  • Flexibility

Problems
* Data consistency
In case of failure in source. source server does not wait for the replica servers to catch
* Lagging
no realtime sync, may result in outdated data on the replica servers
* Recovery Time
Maybe high because replica servers may have lagged

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Synchronous Replication
Pros:

A
  • Data consistency and reliability:
    Even in the event of a failure, all servers up-to-date
  • Fast recovery time:
    important for HA
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Synchronous Replication
Cons:

A
  • Low performance
    Server must wait for ack from replicas
  • high complexibility
    more complex to setup
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Semi-synchronous replication

A

offers a compromise between the high data consistency of synchronous replication and the improved performance of asynchronous replication

the source server waits for at least one replica server to acknowledge

17
Q

Semi-synchronous replication

Pros

A
  • Data consistency and performance:
  • source server only needs to wait for one replica server to acknowledge
  • Reduced data loss
  • With this method, the replica server acts as a backup of the data
18
Q

Semi-synchronous replication

Cons

A
  • still be more complex to set up and manage
  • Data consistency: If the replica server that acknowledges the receipt of updates experiences a failure
19
Q

Replication formats

A
  • Statement-based replication
  • Maybe much faster
  • bad in some types of non-deterministic updates
  • Row-based replication
  • This type of replication is much more flexible and robust than SBR
  • needs maybe much more ressources