MySQL 是一个关系数据库管理系统(Relational Database Management System)( RDBMS )。它处理数据库指令,可以同时管理多个数据库。
例如,如果您想创建一个新数据库(create a new database)或向现有数据库添加数据,您可以向MySQL服务器发送一条消息,将您想要添加的数据提供给它,并告诉它添加到哪里。
无论您的企业规模大小,您的数据都是必不可少的元素。为确保您的宝贵信息免受损坏、盗窃或灾难影响,请备份您的 MySQL(backup your MySQL)数据库。本文将解释几种方法来做到这一点。
使用 phpMyAdmin 备份 MySQL 数据库(Backup MySQL Database Using phpMyAdmin)
(Create)通过从您的网络托管控制面板(如 cPanel)访问 phpMyAdmin 工具来创建MySQL数据库的导出或备份文件。出于本文的目的,我们将使用 cPanel。
- 首先登录到您的 cPanel 并单击 phpMyAdmin。
- 从左侧边栏的导航面板中选择您要备份的MySQL数据库。(MySQL)然后单击顶部导航栏上的导出链接。
- 在导出(Export)页面中,您将看到两个选项:自定义(Custom )和快速(Quick)。选择自定义(Custom)选项。
- 选择Custom后,您将看到数据库列表。您可以选择一个、一些或全部。默认设置是全部备份。
- 在输出选项下,为Compression选择(Compression)gzipped。将其他选项保留为默认值。
- 单击“开始”(Go)按钮开始备份并下载您的文件。您的文件名将是YourDatabaseName.sql.gz 文件(YourDatabaseName.sql.gz file)。
完成备份所需的时间取决于数据库的大小。
使用 mysqldump 备份 MySQL 数据库(Backup MySQL Database With mysqldump)
使用mysqldump命令创建将由MySQL管理的数据库的文本文件转储。文本文件转储是一个文本文件,其中包含从头开始重新创建数据库所需的SQL命令。(SQL)
mysqldump database_name > database_name.sql
此命令将创建备份并将其发送到 .sql 文件。它只会复制您的数据库,不会影响它。
mysqldump –databases database_one database_two > two_databases.sql
(Database_one)database_one是指第一个数据库的名称, database_two是您要备份的第二个数据库的名称。两者都将备份到单个数据库中。
- 如果要备份服务器上的所有MySQL数据库,请使用以下命令创建一个包含所有数据库的 .sql 备份文件。
mysqldump –all-databases > all_databases.sql
使用 Cron 作业备份 MySQL 数据库(Backup MySQL Database Using Cron Jobs)
cron 作业是用于在指定时间自动执行任务的Linux命令。(Linux)我们将解释如何使用此命令自动备份MySQL数据库。
- 首先登录您的 cPanel 并向下滚动到Advanced部分,然后单击Cron jobs。
- 您将看到一条警告,提示您应该熟悉Linux命令才能有效地使用 cron 作业。如果您不这样做,请咨询您的主机管理员。
- 如果您想在每次 cron 作业运行产生输出的命令时收到一封电子邮件,请将您的电子邮件地址放入框中,然后单击更新电子邮件(Update Email)。
- 从下拉菜单中选择一项常用设置以选择运行备份的频率。(Common Settings)
/usr/bin/mysqldump -u dbusername -p’dbpassword’ dbname > /home/username/path/backup.sql
- 将dbusername、dbpassword和dbname替换为您的数据库用户名、数据库密码和数据库名称。
- 路径(Path)是指您希望备份文件所在的文件夹或一系列文件夹。备份文件的名称在命令中表示为backup.sql。您可以更改该名称或保持原样。单击(Click) 添加新的 Cron 作业(Add New Cron Job)。
- 如果您向下滚动一点,您将看到当前 Cron 作业的列表。(Current Cron Jobs.)
- 如果您想更改任何当前的 cron 作业,请单击Edit。如果您不想再运行任何作业或出错,请单击删除(Delete)。
使用 WordPress (WP) 插件备份 MySQL(Use WordPress (WP) Plugins to Backup MySQL)
如果您使用的是 WP,您可以使用插件来备份您的数据库(use a plugin to back up your database)。UpdraftPlus 只需(UpdraftPlus)单击一下即可备份您的数据库文件。它目前有超过 200 万的活跃安装量。
使用 BackWPup(Use BackWPup)备份您的MySQL数据库文件,甚至您的完整 WP 安装。
还有更多WP 插件(WP plugins)可用于备份MySQL。选择一个适用于当前版本的 WP 并且正在积极更新的版本。
为避免丢失关键数据,请定期备份您的MySQL数据库。如果您有备份,您将能够在发生某些事情损坏您的网站时恢复基本或不可替代的数据。
How To Backup a MySQL Database
MySQL is a Relational Database Management System (RDBMS). It handles database instructions and can manage many databases at the same time.
For example, if you want to create a new database or add data to an existing database, you send a message to the MySQL server, give it the data you want to add, and tell it where to add it.
Whether your business is small or large, your data is an essential element. To ensure that your valuable information is secure from damage, theft, or the effects of a disaster, backup your MySQL database. This article will explain several ways to do that.
Backup MySQL Database Using phpMyAdmin
Create an export or backup file of your MySQL database by accessing the phpMyAdmin tool from your web hosting control panel such as cPanel. We will use cPanel for the purposes of this article.
- Start by logging into your cPanel and clicking on phpMyAdmin.
- Choose the MySQL database you want to back up from the navigation panel on the left sidebar. Then click the export link located on the top navigation bar.
- From the Export page, you will see two options: Custom and Quick. Select the Custom option.
- After choosing Custom, you will see a list of your databases. You can select one, some, or all. The default setting is to backup all.
- Under the Output options, choose gzipped for Compression. Leave the other options as the defaults.
- Click the Go button to start the backup and download your file. The name of your file will be YourDatabaseName.sql.gz file.
The time it takes for the backup to complete will depend upon the size of your database.
Backup MySQL Database With mysqldump
Use the mysqldump command to create a text file dump of your database that will be managed by MySQL. A text file dump is a text file that includes the SQL commands you need to recreate your database from scratch.
- To back up a single database, use the following command:
mysqldump database_name > database_name.sql
This command will create the backup and send it to a .sql file. It will only make a copy of your database and won’t affect it.
- Use the following command to backup multiple databases at the same time.
mysqldump –databases database_one database_two > two_databases.sql
Database_one refers to the name of the first database and database_two is the name of the second database you want to back up. Both will be backed up into a single database.
- If you want to backup all your MySQL databases on a server, use the following command to create a single .sql backup file that will contain all your databases.
mysqldump –all-databases > all_databases.sql
Backup MySQL Database Using Cron Jobs
A cron job is a Linux command used to execute a task at a specified time automatically. We will explain how to use this command to automate a backup of MySQL databases.
- Start by logging into your cPanel and scrolling down to the Advanced section and click on Cron jobs.
- You will see a warning that you should have a good knowledge of Linux commands to use cron jobs effectively. If you don’t, check with your hosting administrator.
- If you want to receive an email every time a cron job runs a command that produces output, put your email address in the box and click Update Email.
- Choose one of the Common Settings from the dropdown menu to select how often you want to run the backup.
- You can also tweak the other settings such as time and day of the week.
- Now it’s time to put in the command as follows:
/usr/bin/mysqldump -u dbusername -p’dbpassword’ dbname > /home/username/path/backup.sql
- Replace dbusername, dbpassword, and dbname with your database username, database password, and database name.
- Path refers to a folder or series of folders where you want your backup file to go. The name of your backup file is represented in the command as backup.sql. You can change that name or leave it as is. Click Add New Cron Job.
- If you scroll down a little bit, you will see a list of your Current Cron Jobs.
- If you want to change any of your current cron jobs, click Edit. If you no longer want to run any job or make a mistake, click Delete.
Use WordPress (WP) Plugins to Backup MySQL
If you are using WP, you can use a plugin to back up your database. UpdraftPlus will back up your database file with a single click. It currently has over two million active installs.
Use BackWPup to backup your MySQL database files and even your complete WP installation.
There are many more WP plugins available to back up MySQL. Choose one that works with the current version of WP and is being actively updated.
To avoid losing critical data, regularly backup your MySQL database. If you have a backup, you will be able to restore essential or irreplaceable data should something happen to corrupt your site.