Restoring MySQLDump
Restoring a mysqldump
- ensure clean Db
- ensure a mydmp is available
- log into mysql
- type source </PATH_TO_DIR/<name of mysqldump>.mydmp>
- consistent up to the time of the mysqldump
- check --flush-logs has been used for the mysqldump
- in order to roll forward use the bin logs
- check header of mydmp file for next binlog and that the binlog and subsequent ones are available
- shell >MYSQLBINLOG <absolute_path_to_binlog_directory/mysql-bin.nnnnnn> | mysql -uroot -p ------ will replay all of the transactions individually (repeating for all bin logs)
- This can be dangerous to do as individual transactions, it is better to do them as one transaction
- shell >MYSQLBINLOG <absolute_path_to_binlog_directory/mysql-bin.nnnnnn> <absolute_path_to_binlog_directory/mysql-bin.nnnnnn> etc etc | mysql -uroot -p
OR AND THIS IS THE BEST METHOD
- shell >MYSQLBINLOG <absolute_path_to_binlog_directory/mysql-bin.nnnnnn> > /tmp/statements.sql
- subsequently shell > MYSQLBINLOG <absolute_path_to_binlog_directory> >> /tmp/statements.sql (appends statements to previous command and statements.txt file)
- TO REPLAY THE STATEMENTS - shell> mysql -uroot -p -e "source /tmp/statements.sql" (This will run it as an OS command) or mysql> source /tmp/statements.sql (from a mysql prompt)
- ensure clean Db
- ensure a mydmp is available
- log into mysql
- type source </PATH_TO_DIR/<name of mysqldump>.mydmp>
- consistent up to the time of the mysqldump
- check --flush-logs has been used for the mysqldump
- in order to roll forward use the bin logs
- check header of mydmp file for next binlog and that the binlog and subsequent ones are available
- shell >MYSQLBINLOG <absolute_path_to_binlog_directory/mysql-bin.nnnnnn> | mysql -uroot -p ------ will replay all of the transactions individually (repeating for all bin logs)
- This can be dangerous to do as individual transactions, it is better to do them as one transaction
- shell >MYSQLBINLOG <absolute_path_to_binlog_directory/mysql-bin.nnnnnn> <absolute_path_to_binlog_directory/mysql-bin.nnnnnn> etc etc | mysql -uroot -p
OR AND THIS IS THE BEST METHOD
- shell >MYSQLBINLOG <absolute_path_to_binlog_directory/mysql-bin.nnnnnn> > /tmp/statements.sql
- subsequently shell > MYSQLBINLOG <absolute_path_to_binlog_directory> >> /tmp/statements.sql (appends statements to previous command and statements.txt file)
- TO REPLAY THE STATEMENTS - shell> mysql -uroot -p -e "source /tmp/statements.sql" (This will run it as an OS command) or mysql> source /tmp/statements.sql (from a mysql prompt)
Comments