Generally you can try to drop database without any extra parameters however the output of the SQL file might not be what u expected. So always test your backup on development environment.

What I like to do is to create a rdbbc.sh (bash) file to save a backup command. Place this file in your directory where you want to store backups (something like “user_backup_db”) and run it every time you need to create a quick database backup. with bash rdbbc.sh

rdbbc.sh file:

#!/bin/bash

# set variables
uDate=$(date +"%y-%m-%d")

siteName="somesitehere.org"
dbPass="secretstringhere" # or load from external file
dbUser="usernamehere"
dbName="dbnamehere"
dbHost="localhost" # or your db host name here if not on same machine

# dump database
mysqldump -u $dbUser -h $dbHost -p"$dbPass" --skip-opt --skip-comments $dbName > ${uDate}-${siteName}.sql

# clear varibles
uDate=""
siteName=""
dbPass=""
dbUser=""
dbName=""
dbHost=""

# https://mariadb.com/kb/en/mysqldump/
# https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb

WordPress note!

Note that if dump WordPress database with mysqldump command, all quotes in sterilised object get escaped with backward slash like here: \" Because of that original string count will not represent a true length of a escaped string which brakes the data. To fix this issue you need to remove all extra characters that where added to serialised data.

The best way to deal with wp string manipulation is through tools that will make a notice and rewrite s:\d to the proper value when changing for example site URL which is shorter or longer from original URL.

How to fix WordPress dump?
Open source project for developers: https://github.com/interconnectit/Search-Replace-DB
https://stackoverflow.com/questions/15138893/fix-serialized-data-broken-due-to-editing-mysql-database-in-a-text-editor
https://interconnectit.com/search-and-replace-for-wordpress-databases/
https://interconnectit.com/news/2009/10/07/migrating-a-wordpresswpmubuddypress-website/

DIY FIX PHP (CAUTION)

https://stackoverflow.com/questions/10152904/how-to-repair-a-serialized-string-which-has-been-corrupted-by-an-incorrect-byte

DIY FIX REGEX (CAUTION)

This method is not fully proved and might not work yet. Be cautious.

Replace in sublime text regex pattern with look behind and lookahead using Sublime Text Ctrl+H:

# search for regex:
((?<=\d:)\\")|(\\"(?=;\w:\d))|((?<=0:)\\"(?=\\"))|((?<=0:\\")\\"(?=;))|(\\"(?=;}))
# replace with:
"

If the file is large you need to use terminal to remove escape backslash based on regular expression. You can not do it in unix as it does not support look-behind and look-ahead so use perl cli instead:

perl -pe 's/bar/test/g' simple.txt # replace bar with test
perl -pe 's/((?<=\d:)\\")|(\\"(?=;\w:\d))|((?<=0:)\\"(?=\\"))|((?<=0:\\")\\"(?=;))|(\\"(?=;}))|(?<=stdClass)\\"/g' brokendbcopy.sql > fixeddbcopy.sql
0:"";|"(;s:\d+:)|"(;s:|;i:|;a:)|(0:")|(\d+:)"|"(;\}\})|";\}
(?<=0:)\\"\\";
"";

\\"(;s:\d+:)
"$1

\\"(;s:|;i:|;a:|;N;)
"$1

(:\d+:)\\"
$1"

\\"(;\}+(i|s|'))
"$1

\\"(;b:\d;(i|a|s|\}))
"$1

\\"(:\d+:\{)
"$1


((;s:|;i:|;a:|;N;)\d+:"[a-zA-Z-_0-9]+)\\";
$1"

(\{s:\d+:"[a-zA-Z0-9_-]+)\\"
$1"
0
Would love your thoughts, please comment.x
()
x