Difference between revisions of "MySQL Backup and Restore"

Latest revision as of 15:04, 19 August 2013

Dump all, including users and permissions:

Skript used February 2012 by Andreas Plank:

#------------------------
#!/bin/bash
# run script as sudo
password="xxx"
user="me"
date=`date +'%Y%m%d'`
backuppath="/mnt/dump"
backupfile="${date}_alldb.sql.gz"

if [ ! -d "${backuppath}" ]; then #no directory?
  echo "Create ${backuppath} … "
  mkdir -p "${backuppath}" # create also parent folders
fi

# all Databases
# see http://www.velikan.net/import-sql-dump-file-to-mysql-database/
echo "Start backup ("`date`") … "
mysqldump --user=${user} --password=${password} --all-databases --single-transaction | gzip > "${backuppath}/${backupfile}"
echo "End backup ("`date`") … "
echo "Done. Backup see '${backupfile}'  in cd ${backuppath}"
# reimport
# gunzip < dump_alldb.sql.gz | /usr/bin/mysql -u${user} -p${password}
# gunzip < dump_singledatabase.sql.gz | /usr/bin/mysql -u${user} -p${password} singledatabase
#------------------------

For the next iteration a modified dump should be considered, because on inserting back the above the root password or the debian-sys-maint user can be damaged (for our fix see below). To avoid fixing this, it is possible to do:

mysqldump -nt -uroot -p -w"User NOT LIKE 'root' AND User NOT LIKE 'debian%'" mysql user db > xxx.sql

Explanation (from codealpha.net)

• -nt: Do not add "drop table" and "create table".
• -uroot -p: Connect as root and ask for a password
• -w...: Add a "WHERE" condition to each query. We exclude everything related to root and debian-sys-maint.
• mysql user db: Dump the user and db tables from the mysql database.

It is possible that the passwords of the imported users no longer work. In this case the "/etc/init.d/mysql stop" may no longer work either, because it depends on a system mysql user with a system password. We had to create an init-file with:

 UPDATE mysql.user SET Password=PASSWORD('THE_MYSQL_ROOT_PASSWORD_HERE') WHERE User='root';
 FLUSH PRIVILEGES;

and run:

killall -15 mysqld; mysqld_safe --user=root --init-file=/home/xxxxx/mysql-init;

Note that the killall differs from the instruction in the mysql 5.1 documentation (which did not work!). After this, the user mysql: root has a defined password, and the other passwords can be reset.

Finally, we had to run:

 # enter your mysql root password:
 mysql_upgrade -p --force

Else errors about incompatibilities mysql 5.0 to 5.1 were shown at startup. Without --force, mysql_upgrade considered the db to ok, although it was not.

Restore:

sudo mysql -u root -p < "/mnt/dump/alldb.sql"
# Note: for security reasons, never add the password directly on the command line, 
# adding -p without a passwort will cause a prompt!