Backup and restore a MySQL Databases

For backup your database, run next command from linux command prompt (not mysql):

mysqldump -u root -p -f your_db_name | gzip > `date +/real_destination_folder/dump_%Y%m%d.%H%M%S.gz`

For backup database mysql, fot transfering all users, run next command from linux command prompt (not mysql):

mysqldump -u root -p(your_root_password) --quick mysql | gzip > `date +/real_destination_folder/dump.mysql_%Y%m%d.%H%M%S.gz`

For restoring your database to other MySQL, login to mysql comnand prompt and create database:

CREATE DATABASE your_database_name;

After that, logout and run next command from linux command prompt:

gunzip -c /your_path_to_backup/backup_file_name | mysql -u root -p you_database_name

For restoring mysql database with users, do next steps (detailed description you can find here https://dev.mysql.com/doc/refman/5.7/en/copying-databases.html):

  1. Disable check foreign keys option, login to mysql and run command:
    SET GLOBAL foreign_key_checks=0;
  2. Logout from mysql and run command:
    gunzip < mysql.gz | mysql -u root -p(your_root_mysql_password) mysql
  3. Login to mysql for enable fireign keys checking:
    SET GLOBAL foreign_key_checks=1;
  4. Check result:
    show variables where variable_name='foreign_key_checks';
    select user, host from mysql.user;

     

Leave a Comment