tree ways to create back ups
- mysqldump
- filebackup tables, binlog, logs
- relica Server with read only replication for big dbs
when mysqldump is impractical
If db is too big
How to backup raw data from a replica
Stop the db
> mysqladmin shutdown
> tar cf /tmp/dbbackup.tar ./data
Command to set the replication source
“CHANGE REPLICATION SOURCE TO”
What are SOURCE_LOG_FILE
and
SOURCE_LOG_POS for
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.
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
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;
Handling an Unexpected Halt of a Replica
where can we check which transactions are alreade executed
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
Which Parameter
Controls the balance between strict ACID compliance for commit operations and higher performance
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.
)
MySQL Replication
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”)
MySQL Replication
Benefits
- if source db down replica can replace source
- By distributing load improve performance
- makes back up more easy
Which Replication types exist
- asynchronous
- semi-synchronous
- synchronous
Asynchronous replication
describe
- one source
- benefits/useful for scaling read-only operations
- source do not wait for acknowledgment
Asynchronous replication
benefits / problems
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
Synchronous Replication
Pros:
- Data consistency and reliability:
Even in the event of a failure, all servers up-to-date - Fast recovery time:
important for HA
Synchronous Replication
Cons:
- Low performance
Server must wait for ack from replicas - high complexibility
more complex to setup
Semi-synchronous replication
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
Semi-synchronous replication
Pros
- 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
Semi-synchronous replication
Cons
- still be more complex to set up and manage
- Data consistency: If the replica server that acknowledges the receipt of updates experiences a failure
Replication formats
- 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
