User Tools
Writing /app/www/public/data/meta/databaseandnetworkmanagement/replica_restore.meta failed
databaseandnetworkmanagement:replica_restore
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
| databaseandnetworkmanagement:replica_restore [2021/03/17 18:43] – bosowski | databaseandnetworkmanagement:replica_restore [2021/06/25 10:09] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ====== Replica Database Restore ====== | ||
| + | ==== The below steps are confirmed to work and have been performed on EXTDB2, after the server restarted and the replication was in an uncoverable state. ==== | ||
| + | |||
| + | ===== Tmux ===== | ||
| + | To ensure all of the lengthy processes run without the session disconnecting and in result terminating the process, use tmux. After logging into the server simply run: | ||
| + | < | ||
| + | tmux | ||
| + | </ | ||
| + | |||
| + | Now you will be inside of a tmux session, to which you can return even if you disconnect. All of the processes will be bound to that session and will continue running while it is active. | ||
| + | To detach from the session press '' | ||
| + | You can then return to it with | ||
| + | < | ||
| + | tmux attach | ||
| + | </ | ||
| + | |||
| + | ===== Dumping ===== | ||
| + | The below query will give you the total size of all databases, run it on the source DB: | ||
| + | < | ||
| + | select sum(size) | ||
| + | from | ||
| + | (SELECT table_schema AS " | ||
| + | </ | ||
| + | |||
| + | Below command is safe to use on a running primary database. Replace the --size parameter with the output from the above query. | ||
| + | < | ||
| + | mysqldump --all-databases --add-drop-database --allow-keywords --master-data=1 --flush-logs --single-transaction --triggers --routines -u root -p | pv --progress --size < | ||
| + | </ | ||
| + | |||
| + | **Explanation of the --master-data parameter from the docs:** | ||
| + | '' | ||
| + | '' | ||
| + | |||
| + | Backup the mysql database from the replica before restoring the dump, since we tend to have different sets of users on the replica than on primary for customer access. Restoring only the mysql.users table doesn' | ||
| + | < | ||
| + | mysqldump -uroot -p mysql > mysql.dump.sql | ||
| + | </ | ||
| + | |||
| + | Also backup | ||
| + | < | ||
| + | mysqldump -uroot -p watchdog> | ||
| + | </ | ||
| + | |||
| + | ===== Restoring ===== | ||
| + | Move over the dump from primary to replica with: | ||
| + | < | ||
| + | rsync -z scotty@extdb1.ext:/ | ||
| + | </ | ||
| + | |||
| + | Before attempting to load in the dump, ensure that the `max_allowed_packet` is set to 600M or larger, without this you will run into '' | ||
| + | |||
| + | You can do this by running the following: | ||
| + | < | ||
| + | |||
| + | You can set this by adding the following to the `my.cnf` file: | ||
| + | < | ||
| + | |||
| + | Or with the mysql command for the current session: | ||
| + | < | ||
| + | |||
| + | Stop the replica with the following command: | ||
| + | < | ||
| + | |||
| + | To load in the dump into the replica, use the following command: | ||
| + | < | ||
| + | pv ./ | ||
| + | </ | ||
| + | |||
| + | The above will take a considerable amount of time - for a ~500Gb dump file, this process took ~18 hours on EXT DB2. | ||
| + | |||
| + | You can then start the replica and it should start slowly catching up to primary: | ||
| + | < | ||
| + | START SLAVE; | ||
| + | </ | ||
| + | |||
| + | During the incident, I have manually extracted the user inserts from the previously taken mysql.dump.sql and added in the missing users that were missing after the primary dump import, but the below should work also: | ||
| + | Restore the mysq database on the replica with (restoring only the users table didn't seem to work from testing): | ||
| + | < | ||
| + | mysql -uroot -p mysql< mysql.dump.sql | ||
| + | </ | ||
Page Tools
