MySQL Backup and Restore

From Biowikifarm Metawiki
Revision as of 15:04, 19 August 2013 by Andreas Plank (Talk | contribs) (+reimport)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Dump all, including users and permissions:

Skript used February 2012 by Andreas Plank:

# run script as sudo
date=`date +'%Y%m%d'`

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

# all Databases
# see
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
  • -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:


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.


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!