Taking Cold Database backup

To restore a MySQL database from a cold backup, follow these steps:

1. Shutdown the MySQL server:

• • Open a terminal or command prompt.
  • Enter the command: mysqladmin -u root -p shutdown
  • Provide the root password when prompted.

1. Copy the backup files to the appropriate location:

• • Locate the backup files that were created during the cold backup process.
  • If you backed up the shared tablespace (InnoDB) or MyISAM tables, copy the corresponding data files (ibdata*, *.ibd) to the MySQL data directory. The default locations are:

• • • Unix: /var/lib/mysql/
    • Windows: C:\mysql\data\

• • If you backed up individual InnoDB tablespaces, copy the respective .ibd files to their original location within the data directory.
  • Copy the format files (*.frm) to their original location within the data directory.
  • If applicable, copy the log files (ib_logfile*) to their original location within the data directory.
  • Copy the option file (my.ini or my.cnf) to the MySQL configuration directory. The default locations are:

• • • Unix: /etc/mysql/
    • Windows: C:\mysql\mysql-X.X\

1. Start the MySQL server:

• • Open a terminal or command prompt.
  • Start the MySQL server using the appropriate command:

• • • Unix: sudo service mysql start
    • Windows: net start mysql or mysqld

1. Verify the restored database:

• • Open a terminal or command prompt.
  • Enter the command: mysql -u root -p
  • Provide the root password when prompted.
  • Once logged in to the MySQL command-line interface, you can list the databases using the command: SHOW DATABASES;
  • Verify that the restored database is present and accessible.

Note: It's crucial to ensure that the backup files are from a consistent point in time and are not corrupted. Additionally, if you encounter any issues during the restoration process, consult the MySQL documentation or seek assistance from a database administrator.

Remember to adjust the file paths and commands mentioned above to match your specific server environment and file locations.

Physical Files backup using mysqlhotcopy (MyISAM tables)

Benefits of mysqlhotcopy

•     Faster than mysqldump by using direct file copy
•     Internal lock done automatically
•     Flush binary log automaticatlly

But:

•     Can only run on the same server as the DB instance
•     Only for MyISAM and ARCHIVE tables
•     Only on Unix and NetWare.

$ mysqlhotcopy db_name /path/copy_to/

Physical InnoDB Files backup

    Shut down the MySQL instance gracefully

    $ mysqladmin -u root -p shutdown

    Backup the following file: