How do you backup magento Database through command line

Question

I'm a beginner to these mysql. i used the command

"mysqldump -u root -p magento > magentobackup.sql"

but returned a

"Got error: 1036: Table 'catalogsearch_fulltext' is read only when using LOCK TABLES"

. whats the safe command to back up?

Answer 1

I've never had a lock tables issue. But the problem is that the search table is trying to be updated whilst you are Dumping.

Generally speaking you should consider putting the store into maintenance mode during a dump to prevent data loss or other issues as MySQLDump does attempt to lock the tables so it can see how much data it needs to load from the whole table. (It doesn't want to miss newly added data so it locks the table but something else has locked it.

You can use the --lock-tables=false option on the MySQLDump command line. But I'd go with Maintenance mode myself.

--lock-tables, -l

Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all.

• Man Page for MySQLDump
• Sourced from this StackOverflow Answer

Answer 2

Please use below command :

php bin/magento setup:backup --db

& after this check in var folder. you will get backup.

Answer 3

You can try this in Linux.

Run the following command:

   ..../bin/php /bin/magento setup:backup --db   

This will create the DB backup in Magento2/var/backups

Answer 4

You can backup db through Magento CLI. Try Following cmd into CLI.

php bin/magento setup:backup --db

Also you can backup codebase, media like following ways

php bin/magento setup:backup --code

php bin/magento setup:backup --media

All the backup comes under var/backups directory.