All examples below refer to console commands, because we believe that no program works as well and smoothly as working on bash.
1. mysqldump / Database Export
# Default dump
mysqldump -uusername -p -h127.0.0.1 database > dump.sql
# Resource-saving dump (to prevent website freezing while dumping)
mysqldump -uusername -p -h127.0.0.1 --single-transaction --quick --lock-tables=false database > dump.sql
# Compress dump into a .sql.gz file
mysqldump -uusername -p -h127.0.0.1 --single-transaction --quick --lock-tables=false database | gzip > dump.sql.gz
# Exclude unneeded tables in dump fule
mysqldump -uusername -p -h127.0.0.1 --single-transaction --quick --lock-tables=false --ignore-table=database.sys_log --ignore-table=database.sys_history database | gzip > dump.sql.gz
# Dump with TYPO3 Console (after installation of package helhum/typo3-console)
./vendor/bin/typo3cms database:export > dump.sql
# Dump with TYPO3 Console without unneeded tables (after installation of package helhum/typo3-console)
./vendor/bin/typo3cms database:export -c Default -e 'cf_*' -e 'cache_*' -e '[bf]e_sessions' -e sys_log > dump.sql
You can find more information about exporting using the TYPO3 Console at https://docs.typo3.org/p/helhum/typo3-console/main/en-us/CommandReference/DatabaseExport.html
2. mysql / Database Import
# Default import of a dump file
mysql -uusername -p -h127.0.0.1 database < dump.sql
# Import of a compressed file .sql.gz
zcat dump.sql.gz | mysql -uuser -p -h127.0.0.1 database
# Import with TYPO3 Console (After installation of package helhum/typo3-console)
cat dump.sql | ./vendor/bin/typo3cms database:import More information about importing using the TYPO3 Console https://docs.typo3.org/p/helhum/typo3-console/main/en-us/CommandReference/DatabaseImport.html
3. Tips And Tricks
# Transferring a database to another database by combining mysqldump and mysql
mysqldump -uusername -ppassword database1 | mysql -uusername -ppassword database2
# Dump with current date
mysqldump -uusername -p -h127.0.0.1 database > dump_$(date +%Y-%m-%d).sql
# You can also replace one or both sides with an SSH command to pull or import dumps from another server
ssh user@server 'mysqldump -uuser -ppassword databaseforeign' | mysql -uuser -ppassword databaselocal Tip A: If you stumple over "tablespaces" failures while dumping just add a --no-tablespaces
Tip B: Do you often make dumps on the server, but don't want to keep typing the command? Just use STRG-R and search for mysql or mysqldump - the last used command is immediately visible and can be used again.
