Backup Confluence wiki database (MySQL)
mysqldump
In addition to the automatic daily backup and manual backup in Administration we can use mysqldump to backup the database.
SSH to the remote host and execute:
Recommended way
mysqldump to stdout, pipe to xz for compression and then pipe to pv redirect to xz file, pv is OPIONTAL, it is for showing the progress ONLY. The - after xz -z is OPTIONAL, by default xz reads from stdin and writes to stdout
mysqldump -u confluenceuser -ppassword confluence | xz -z | pv > wiki_$(date -d "today" +"%Y%m%d").xz
shell script
#! /bin/sh # One liner # mysqldump to stdout, pipe to xz for compression and then pipe to pv redirect to xz file # pv is OPIONTAL, it is for showing the progress ONLY # The - after xz -z is OPTIONAL, by default xz reads from stdin and writes to stdout mysqldump -u confluenceuser -p2wins1023 confluence | xz -z | pv > wiki_$(date -d "today" +"%Y%m%d").xz
man mysqldump
--user=user_name -u user_name
--password=password -ppassword (NO Space)
Old form
mysqldump --opt --user=confluenceuser --password=password --host=localhost confluence > wiki.sql
By default --opt is ON, by default --host is localhost, short forms as below
# Long form mysqldump -user=confluenceuser --password=password confluence > wiki.sql # Short form mysqldump -u confluenceuser -ppassword confluence > wiki.sql
NOTE: If you use the short option form (-p), you cannot have a space between the option and the password.
If you omit the password value following the --password or -p option on the command line, mysqldump prompts for one.
One liner
Idea: mysqldump to stdout, pipe to xz for compression and then pipe to pv redirect to xz file, pv is OPIONTAL, it is for showing the progress ONLY. The - after xz -z is OPTIONAL, by default xz reads from stdin and writes to stdout.
# Form 1 mysqldump -u confluenceuser -ppassword confluence | xz -z | pv > wiki_$(date -d "today" +"%Y%m%d").xz # Form 2 mysqldump -u confluenceuser -ppassword confluence | xz -z | > wiki.xz mysqldump -u confluenceuser -ppassword confluence | xz -z > wiki.xz # Form 3 mysqldump -u confluenceuser -ppassword confluence | xz -z - | pv > wiki.xz
Backup mysqldump file (xz compressed) to remote host via SSH
Use mysqldump to dump the database, compress with xz and then transfer it to remote host via SSH.
# Good mysqldump -u confluenceuser -ppassword confluence | xz -z | ssh terry@terry.im "cat > /backup/wiki_$(date -d "today" +"%Y%m%d").xz" # Alternative mysqldump -u confluenceuser -ppassword confluence | xz -z | ssh terry@terry.im "pv > /backup/wiki_$(date -d "today" +"%Y%m%d").xz"
shell script
#! /bin/sh # One liner # mysqldump to stdout, pipe to xz for compression and then pipe it with a SSH Connection # On the remote SSH Host side, use cat to receive and redirect to a file # You can use pv instead of cat, it works as well # The - after xz -z is OPTIONAL, by default xz reads from stdin and writes to stdout mysqldump -u confluenceuser -ppassword confluence | xz -z | ssh terry@terry.im "cat > ~/backup/support/wiki_$(date -d "today" +"%Y%m%d").xz"
Back up MySQL data files directly
Back up MySQL data files directly, AKA Cold Backup
cd /var/lib tar czf /opt/backup/mysql_$(date -d "today" +"%Y%m%d").tar.gz mysql
Export a table
mysqldump -u user -ppassword database table > table.sql
phpMyAdmin
Or use phpMyAdmin web UI to export the database.
Change MySQL Database storage engine
Change per database table
Change storage engine from MyISAM to InnoDB
alter table table_name ENGINE=InnoDB;
Set it to be global
set GLOBAL storage engine='InnoDB';
Automated
mysql -u [USER_NAME] -p -e "select TABLE_NAME from information_schema.tables where table_schema='DATABASE_NAME' and engine='MyISAM';" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)INNODB/\1MYISAM/g' alter_table.sql
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql