¿Cómo hacer COPIAS DE SEGURIDAD AUTOMATICAS 📥en MySQL? | WINDOWS | Fácil 2024

How to Automate MySQL Backups on Windows

Introduction to Automated Backups

• The speaker introduces the process of creating automatic backups for a MySQL database on Windows, using "mini agenda" as an example.

• Emphasizes the importance of scheduling backups at specific times based on administrative needs.

Using MySQL Dump for Backups

• Introduces mysqldump, a command-line tool included with MySQL installations, which facilitates database backup creation.

• Discusses the syntax for using mysqldump, including user credentials and file path specifications for saving backups.

Backup Process Overview

• Explains that the backup will include both the schema and data from the database, allowing restoration to its original state when needed.

• Describes creating a .bat file in Windows to automate the execution of backup commands.

Creating and Configuring the Batch File

• Provides details about writing instructions in the batch file, including necessary parameters like username and password.

• Warns about security risks associated with storing passwords in plain text within the batch file.

Finalizing and Testing Backup Automation

• Instructs on specifying database names and paths where backups will be stored, highlighting how to append timestamps to filenames.

• Mentions sharing instructions in video descriptions for viewers' reference while personalizing their own batch files.

• Guides through saving changes in Notepad and ensuring proper naming conventions for batch files (e.g., .bat extension).

• Advises testing the created batch file by executing it directly from Windows Explorer to confirm functionality.

Creating and Automating MySQL Backups

Verifying the Backup File Creation

• The speaker opens a generated SQL file to confirm its correct creation, which contains all necessary SQL instructions for establishing the database structure.

• Emphasizes that while this is a small dataset, the backup process applies similarly to larger databases; however, execution time will vary based on database size.

Setting Up Environment Variables

• Discusses how to verify if the MySQL installation path is included in the system's environment variables by navigating through system settings.

• The speaker confirms their MySQL installation path is correctly set in environment variables, highlighting its importance for executing scripts without issues.

Configuring Windows Task Scheduler

• After creating the script, configuring an environment variable is essential before setting up a scheduled task in Windows for automatic execution of backups.

• Accessing Windows Task Scheduler allows users to create automated tasks; they opt for a basic task setup.

Scheduling Backup Tasks

• The speaker names the task "mysql BX" and describes it as a scheduled task for generating MySQL backups.

• Users can choose frequency options (daily, weekly, etc.), with daily backups being recommended for regular data protection.

Finalizing and Testing Scheduled Tasks

• The configuration includes specifying start dates and intervals; users can set specific times (e.g., midnight).

• The next step involves selecting the program or script to execute during each scheduled run by browsing to find the saved .bat file.

Verifying Backup Execution

• After finalizing settings, details are reviewed confirming daily execution of the specified script.

• Users can test their newly created scheduled task directly from Task Scheduler options like "Run" or "Disable."

Checking Backup Integrity

• Upon running the backup task, users check if new files appear in their designated folder sorted by date.

• Opening generated files verifies that data integrity is maintained across backups; successful automation means daily backups occur as planned.

Manual Restoration Process Overview

• Although typically not automated, manual restoration processes are discussed. This may be necessary for specific reviews or checks on databases.

• A new database named "mini agenda v2" is created to demonstrate proper information generation during restoration.

How to Execute a Script in MySQL Workbench

Copying and Executing the Script

• The process begins with copying a script from an existing query. The user selects the last generated script, copies it, and prepares to paste it into MySQL Workbench.

• After pasting the script into MySQL Workbench, the user executes it. A confirmation message appears at the bottom of the interface indicating that all operations were completed successfully.

Verifying Database Structure and Data

• Once executed, refreshing the database reveals that both structure and data are now visible. The tables created during this process can be seen in the database view.

• The user navigates to display data from a specific table named "contact." They perform a SELECT operation to confirm that the information matches what is expected from both tables involved.

Conclusion

• The video concludes with an assurance that this tutorial will be useful for viewers looking to understand how to execute scripts within MySQL Workbench effectively.