柳暗花明笔记

    10 Ways to Automatically & Manually Backup MySQL Database

    所属分类:[ linux&mysql ] 猫哲学家 发表于:2012-08-10 22:32 浏览:610

    MySQL is one of the most popular open source database management system for the development of interactive Websites.

    If your site stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (we all have been there).

    There are several ways to backup MySQL data. In this article we’ll look at how to backup your databases using different methods, we will also learn how to achieve an automatic backupsolution to make the process easier. Starting with the mysqldump utility that comes with MySQL, we will review several examples using mysqldump, including the backup of your database to a file, another server, and even a compressed gzip file and send it to your email.

     

    1. Automatically backup mysql database to Amazon S3

    Many of users use Amazon S3 to backup their mysql databases. Here is an automated script which does this task of taking the backup of a mysql database and then moving it to the Amazon S3.

    2. How to Backup MySQL Database automatically (for Linux users)

    1. 15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`data ' %m-%d-%Y'`.sql.gz  

    This post will show you how to backup MySQL Database automatically if you are a linux user. You can use cron to backup your MySQL database automatically.”cron” is a time-based scheduling utility in Unix/Linux
    operating system.

    3. Backup your MySQL databases automatically with AutoMySQLBackup

    AutoMySQLBackup has some great features to: backup a single database, multiple databases, or all the databases on the server; each database is saved in a separate file that can be compressed (with gzip or bzip2); it will rotate the backups and not keep them filling your hard drive (as normal in the daily backup you will have only the last 7 days of backups, the weekly if enabled will have one for each week, etc.).

    4. Backing Up With MySQLDump

    1. mysqldump ---user [user name] ---password=[password]  
    2. [database name] > [dump file]  

    In this article we’ll look at how to backup our databases using the mysqldump utility that comes with MySQL. Several examples will be reviewed using mysqldump, including the backup of your database to a file,
    another server, and even a compressed gzip file.

    5. Backup Your Database into an XML File Using PHP

    1. mysqldump ---user [user name] ---password=[password]  
    2. [database name] > [dump file]  

    Here’s a PHP snippet that outputs your database as XML. XML isn’t the easiest format to restore a table but it can be easier to read.

    6. How to – Using PHP To Backup MySQL Database

    Execute a database backup query from PHP file. Below is an example of using SELECT INTO OUTFILE query for creating table backup:

    1. <?php  
    2. include 'config.php';  
    3. include 'opendb.php';  
    4.   
    5. $tableName  = 'mypet';  
    6. $backupFile = 'backup/mypet.sql';  
    7. $query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";  
    8. $result = mysql_query($query);  
    9.   
    10. include 'closedb.php';  
    11. ?>  

    To restore the backup you just need to run LOAD DATA INFILE query like this :

    1. <?php  
    2. include 'config.php';  
    3. include 'opendb.php';  
    4.   
    5. $tableName  = 'mypet';  
    6. $backupFile = 'mypet.sql';  
    7. $query      = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";  
    8. $result = mysql_query($query);  
    9.   
    10. include 'closedb.php';  
    11. ?>  

    7. Backup MySQL Database Via SSH

    A simple solution to backup your large MySQL databases through SSH. You will need to enable shell access inside your Plesk control panel and use a utility such as PuTTY to log into your server via SSH.

    8. How to e-mail yourself an automatic backup of your MySQL database table with PHP

    This script will send an e-mail to you with an .sql file attached, thus enabling you to back up specific tables easily. You could even set up an e-mail account just to receive these backups…

    9. Ubuntu Linux Backup MySQL server Shell Script

    If you have a dedicated VPS server running Ubuntu Linux. Here is how to backup all your mysql server databases to your ftp server

    10. How to backup MySQL databases, web server files to a FTP server automatically

    This is a simple backup solution for people who run their own web server and MySQL server on a dedicated box or VPS. The main advantage of using FTP or NAS backup is a protection from data loss.First you will need to backup each database with mysqldump command, Automating tasks of backup with tar, Setup a cron job and generate FTP backup script.

    1. $ mysqldump -u root -h localhost -pmypassword faqs | gzip -9 > faqs-db.sql.gz  

    11. MySQL Export: How to backup your MySQL database?

    You can easily create a dump file(export/backup) of a database used by your account. In order to do so you should access the phpMyAdmin tool available in your cPanel.

    Worth Reading

    - 10 things you need to know about backup solutions for MySQL

    Are you using someone else’s backup solution for your MySQL data? Do you care a lot about your data? Are you sure you’re getting a reliable, recoverable backup that’ll work for your business and your application, and won’t impact your critical processes while it runs? Here are ten questions you need to be able to answer.

    http://media.noupe.com/themes/noupe/images/bg-about-head.png); height: 58px;">

    About the Author

    http://media.noupe.com/themes/noupe/images/bg-about.png); width: 600px; line-height: 1.462em;">



    Comments and Discussions

    标签: mysql 
    上一编文章 Adding new users to MySQL Databases 下一编文章 shell empty mysql db
    最近文章
    发表评论