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-mariadbWordPress 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)
You can get some issue with your database if your serialization will contain " instead of ' . So just keep this in mind when you deal with serialization objects. It might cause you issues. Read more in the link below.
If you need to replace a string in database but the db file is too large you need to use terminal with regular expression. You may not want to use unix bash as it does not support look-behind and look-ahead functionality. Use perl in your cli instead (alternatively write a script to process the file). You can get pretty advance with regular expressions using perl. See some basic examples below:
perl -pe 's/bar/test/g' simple.txt # replace bar with test# note this is only example - not a solution
perl -pe 's/((?<=\d:)\\")|(\\"(?=;\w:\d))|((?<=0:)\\"(?=\\"))|((?<=0:\\")\\"(?=;))|(\\"(?=;}))|(?<=stdClass)\\"/g' brokendbcopy.sql > fixeddbcopy.sql