發新話題
打印

[Mysql] Back up a Database

本主題被作者加入到個人文集中

Back up a Database

引用:
mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql
https://www.linode.com/docs/data ... p-mysql-or-mariadb/ 


引用:

  • mysqldump prompts for a password before it starts the backup process.
  • Depending on the size of the database, it could take a while to complete.
  • The database backup will be created in the directory the command is run.
  • -$(date +%F) adds a timestamp to the filename.

Example use cases include:

TOP

Automate Backups with cron

Automate Backups with cronPermalink

Entries can be added to /etc/crontab to regularly schedule database backups.

  1. Create a file to hold the login credentials of the MySQL root user which will be performing the backup. Note that the system user whose home directory this file is stored in can be unrelated to any MySQL users.

    /home/example_user/.mylogin.cnf
    1
    2
    3
    
    [client]
    user = root
    password = MySQL root user's password
  2. Restrict permissions of the credentials file:

    chmod 600 /home/example_user/.mylogin.cnf
    
  3. Create the cron job file. Below is an example cron job to back up the entire database management system every day at 1am:

    /etc/cron.daily/mysqldump
    1
    
    0 1 * * * /usr/bin/mysqldump --defaults-extra-file=/home/example_user/.my.cnf -u root --single-transaction --quick --lock-tables=false --all-databases > full-backup-$(date +\%F).sql

    For more information on cron, see the cron(8) and cron(5) manual pages.


TOP

Restore a BackupPermalink

The restoration command’s general syntax is:

mysql -u [username] -p [databaseName] < [filename].sql
  • Restore an entire DBMS backup. You will be prompted for the MySQL root user’s password:
    This will overwrite all current data in the MySQL database system

    mysql -u root -p < full-backup.sql
    
  • Restore a single database dump. An empty or old destination database must already exist to import the data into, and the MySQL user you’re running the command as must have write access to that database:

    mysql -u [username] -p db1 < db1-backup.sql
    
  • Restore a single table, you must have a destination database ready to receive the data:

    mysql -u dbadmin -p db1 < db1-table1.sql
    

More InformationPermalink

You may wish to consult the following resources for additional information on this topic. While these are provided in the hope that they will be useful, please note that we cannot vouch for the accuracy or timeliness of externally hosted materials.

TOP

發新話題