Oracle database backup and recovery
# Push contents to a file, then apply the file. Mysqlbinlog mysqld-bin.000001 | mysql -u root -p # Apply contents directly to the database. The following exmaples show how it can be used. If you get this, simply comment out the references while you run the utility and put them back afterwards. Mysqlbinlog: unknown variable 'ssl-ca=/home/mysql/certs/ca-cert.pem' The mysqlbinlog utility doesn't support SSL and will produce the following type of error if the client configuration includes it. You must make sure the "" section of your "/etc/my.cnf" file, or local config file, does not contain any "ssl" references. The mysqlbinlog utility converts the contents of the binary logs to text, which can be replayed against the database. Restart the MySQL service for the change to take effect. # Files stored in "/u01/log_bin" with the prefix "mydb". If you want alter the prefix and path you can do this by specifying an explicit base name. The binary logs will be written to the "datadir" location specified in the "/etc/my.cnf" file, with a default prefix of "mysqld". # changes to the binary log between backups. # Remove leading # to turn on a very important data integrity option: logging To enable the binary logs, edit the "/etc/my.cnf" file, uncommenting the "log_bin" entry. Unless you have a read-only system, it is likely you will need to enable the binary logs. The binary logs allow you to wind forward from that point by applying all the changes that were written to the binary logs. Without the binary logs, you can only recover the database to the point in time of a specific backup. # service mysqld start Binary Logs : Point In Time Recovery (PITR)īinary logs record all changes to the databases, which are important if you need to do a Point In Time Recovery (PITR). To recover the database from a cold backup, stop MySQL, restore the backup files and start MySQL again. # service mysqld start Recovery from Cold Backup
#Oracle database backup and recovery zip#
You can use whichever method you want to copy the files (cp, scp, tar, zip etc.). The basic process of a cold backup involves stopping MySQL, copying the files, the restarting MySQL. Cold BackupsĬold backups are a type of physical backup as you copy the database files while the database is offline.
#Oracle database backup and recovery full#
The full syntax of the MySQL command line tool can be found here. $ mysql -user=root -password=mypassword -database=mydatabase < mydatabase_backup.sql $ mysql -user=root -password=mypassword < all_backup.sql The logical backup created using the mysqldump command can be applied to the database using the MySQL command line tool, as shown below. The full syntax of the command can be found here. $ mysqldump -user=root -password=mypassword mydatabase mytable1 mytable2 mytable3 > mydatabase_mytables_backup.sql $ mysqldump -user=root -password=mypassword mydatabase mytable > mydatabase_mytable_backup.sql $ mysqldump -user=root -password=mypassword mydatabase -force > mydatabase_backup.sql $ # Individual DB (or comma separated list for multiple DBs) $ mysqldump -user=root -password=mypassword -all-databases -force > all_backup.sql Logical Backups Logical Backup (mysqldump)Īmongst other things, the mysqldump command allows you to do logical backups of your database by producing the SQL statements necessary to rebuild all the schema objects.