{"id":2728,"date":"2017-08-03T10:57:24","date_gmt":"2017-08-03T10:57:24","guid":{"rendered":"https:\/\/intelligentbee.com\/blog\/?p=2728"},"modified":"2024-12-19T08:51:22","modified_gmt":"2024-12-19T08:51:22","slug":"mysqldump-command-useful-usage-examples","status":"publish","type":"post","link":"https:\/\/intelligentbee.com\/blog\/mysqldump-command-useful-usage-examples\/","title":{"rendered":"Mysqldump Command – Useful Usage Examples"},"content":{"rendered":"

One of the tasks a sysadmin will always have on their list is backing up databases. These backups are also called dump files because, usually, they are generated with mysqldump<\/code> command.<\/p>\n

I am going to share a few tricks on mysqldump that will help when handling servers with many relatively small databases.<\/p>\n

Mysqldump Command<\/h2>\n

The most simple way to backup databases would be using mysqldump<\/code> command with the the\u00a0--all-databases<\/code> attribute. But I find that having each database saved in its own file more convenient to use.<\/p>\n

Lets first suppose that you need to run a script that alters in databases, and that you just need a simple way to have a rollback point, just in case. I used to run something like this before:<\/p>\n

for i in \\\r\n`ls \/var\/lib\/mysql\/`; \\\r\ndo mysqldump -u root -p*** --skip-lock-tables --skip-add-locks --quick --single-transaction $i > $i.sql; done<\/pre>\n
where *** is your root password. The aditional parameters --skip-lock-tables --skip-add-locks --quick --single-transaction<\/code>\u00a0 assure availability and consistency of dump file for\u00a0InnoDB databases\u00a0(the default storage engine as of MySQL 5.5.5).<\/p>\n
Mysql stores databases in folders using same name as database name in \/var\/lib\/mysql<\/code>. The command picks database names from the listing of \/var\/lib\/mysql<\/code> folder and exports to files using same name adding the .sql<\/code>.<\/p>\n
There are 2 issues with the above command:<\/p>\n
    \n
  1. It will try to execute a dump for every file\/folder listed in \/var\/lib\/mysql<\/code>. So if you have error logs or whatever other files it will create .sql<\/code> dumps for them too. This will send just directory names as database names to export:\n
    for i in \\\r\n`find \/var\/lib\/mysql\/ -type d | sed 's\/\\\/var\\\/lib\\\/mysql\\\/\/\/g'`;\\\r\ndo mysqldump -u root -p*** --skip-lock-tables --skip-add-locks --quick --single-transaction $i > $i.sql; done<\/pre>\n
    I find this to be hard to type and prefer to use one I will explain in point 2, since it also covers this.<\/li>\n
  2. When database names have characters like -<\/code> the folder name will have @002<\/code> instead. If that is the case, you can use something like:\n
    for i in \\\r\n`mysql -u root -p*** -e 'show databases'`;\\\r\ndo mysqldump -u root -p*** --skip-lock-tables --skip-add-locks --quick --single-transaction $i > $i.sql;done<\/pre>\n
    This picks database names to export form mysql show databases<\/code> command.<\/li>\n<\/ol>\n
    But, one time I had to export databases with \/<\/code>\u00a0 in their names. And there is no way to export as I showed above, since \/<\/code>\u00a0can’t be used in file names since it is actually a markup for directories. \u00a0So I did this:<\/p>\n
    for i in \\\r\n`mysql -u root -p*** -e 'show databases'`;\\\r\ndo mysqldump -u root -p*** --skip-lock-tables --skip-add-locks --quick --single-transaction $i > `echo $i | sed \"s\/\\\/\/_\/g\"`.sql;done\r\n<\/pre>\n
    This wil replace \/<\/code>\u00a0with _<\/code>\u00a0for the dump file names.<\/p>\n
    For all of the above, we could (for obvious reasons) not use root mysql user. \u00a0We could also run the backing up from a different location. In order to do this, we would need to create a mysql user with the right privileges on the machine we want to back up.<\/p>\n
    create user 'backupuser'@'111.222.333.444' identified by 'backuppassword';\r\n\r\ngrant select, show view, trigger, lock tables, reload, show databases on *.* to 'backupuser'@'111.222.333.444';\r\nflush privileges;<\/pre>\n
    where 111.222.333.444 is the ip of the remote machine.<\/p>\n
    Now you can issue mysqldump command from the other machine like this:<\/p>\n
    for i in \\\r\n`mysql -u backupuser -pbackuppassword -e 'show databases'`;\\\r\ndo mysqldump -u backupuser -pbackuppassword -h 444.333.222.111 --skip-lock-tables --skip-add-locks --quick --single-transaction $i > `echo $i | sed \"s\/\\\/\/_\/g\"`.sql;done<\/pre>\n
    where 444.333.222.111 is the ip of the machine we want to backup.<\/p>\n
    Lets take it to the next step , and put all our knowledge in a shell script.<\/p>\n
    #!\/bin\/bash\r\necho \"Starting the backup script...\"\r\nROOTDIR=\"\/backup\/mysql\/\"\r\nYEAR=`date +%Y`\r\nMONTH=`date +%m`\r\nDAY=`date +%d`\r\nHOUR=`date +%H`\r\nSERVER=\"444.333.222.111\"\r\nBLACKLIST=\"information_schema performance_schema\"\r\nADDITIONAL_MYSQLDUMP_PARAMS=\"--skip-lock-tables --skip-add-locks --quick --single-transaction\"\r\nMYSQL_USER=\"backupuser\"\r\nMYSQL_PASSWORD=\"backuppassword\"\r\n\r\n# Read MySQL password from stdin if empty\r\nif [ -z \"${MYSQL_PASSWORD}\" ]; then\r\n echo -n \"Enter MySQL ${MYSQL_USER} password: \"\r\n read -s MYSQL_PASSWORD\r\n echo\r\nfi\r\n\r\n# Check MySQL credentials\r\necho exit | mysql --user=${MYSQL_USER} --password=${MYSQL_PASSWORD} --host=${SERVER} -B 2>\/dev\/null\r\nif [ \"$?\" -gt 0 ]; then\r\n echo \"MySQL ${MYSQL_USER} - wrong credentials\"\r\n exit 1\r\nelse\r\n echo \"MySQL ${MYSQL_USER} - was able to connect.\"\r\nfi\r\n\r\n#creating backup path\r\nif [ ! -d \"$ROOTDIR\/$YEAR\/$MONTH\/$DAY\/$HOUR\" ]; then\r\n    mkdir -p \"$ROOTDIR\/$YEAR\/$MONTH\/$DAY\/$HOUR\"\r\n    chmod -R 700 $ROOTDIR\r\nfi\r\n\r\necho \"running mysqldump\"\r\ndblist=`mysql -u ${MYSQL_USER} -p${MYSQL_PASSWORD} -h $SERVER -e \"show databases\" | sed -n '2,$ p'`\r\nfor db in $dblist; do\r\n    echo \"Backuping $db\"\r\n    isBl=`echo $BLACKLIST |grep $db`\r\n    if [ $? == 1 ]; then\r\n        mysqldump ${ADDITIONAL_MYSQLDUMP_PARAMS} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} -h $SERVER $db | gzip --best > \"$ROOTDIR\/$YEAR\/$MONTH\/$DAY\/$HOUR\/`echo $db | sed 's\/\\\/\/_\/g'`.sql.gz\"\r\n        echo \"Backup of $db ends with $? exit code\"\r\n    else\r\n        echo \"Database $db is blacklisted, skipped\"\r\n    fi\r\ndone\r\necho \r\necho \"dump completed\"<\/pre>\n
    This will also compress the dump files to save storage.<\/p>\n
    Save the script as backup-mysql.sh<\/code>\u00a0somewhere on the machine you want backups saved, ensure you have the mysql user with the right credentials on the server hosting the mysql. You will also need mysql installed on the backup server. Executesudo chmod 700 backup-mysql.sh<\/code>. Run the script with sudo sh backup-mysql.sh <\/code>. After making sure it works properly, you can also add it to your crontab, so that it runs on a regular schedule.<\/p>\n","protected":false},"excerpt":{"rendered":"
    One of the tasks a sysadmin will always have on their list is backing up databases. These backups are also […]<\/p>\n","protected":false},"author":28,"featured_media":2819,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[73,86],"tags":[],"yst_prominent_words":[1139,1148,1147,1146,1145,1144,1143,1142,1141,1140,567,1138,1137,1136,1135,1134,1057,1053,1014],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/posts\/2728"}],"collection":[{"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/comments?post=2728"}],"version-history":[{"count":3,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/posts\/2728\/revisions"}],"predecessor-version":[{"id":133362,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/posts\/2728\/revisions\/133362"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/media\/2819"}],"wp:attachment":[{"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/media?parent=2728"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/categories?post=2728"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/tags?post=2728"},{"taxonomy":"yst_prominent_words","embeddable":true,"href":"https:\/\/intelligentbee.com\/blog\/wp-json\/wp\/v2\/yst_prominent_words?post=2728"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}