Contact us

For coders TYPO3 Tech Corner

Mysql and Mysqldump - everything you need to know about importing and exporting databases

Mysql and Mysqldump - everything you need to know about importing and exporting databases

When working with TYPO3 on a daily basis, you often have to export and import databases. This works with the help of dump files. We have put together everything that can help you here.

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

# 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.

Back

"Code faster, look at the time" - does this sound familiar to you?

How about time and respect for code quality? Working in a team? Automated tests?

Join us