The following command will use mysqldump to create a dump of all available databases in the HOST that are available for the user USER.
The results will be found in a file that begins with the current date and will contain the hostname as part of the name.
The user must have the LOCK TABLES privilege for the above command to work or else you will get the following error.
mysqldump: Got error: 1044: "Access denied for user 'USER'@'HOST' to database 'DBNAME'" when using LOCK TABLES
In case you cannot give the privilege to the user, then use the parameter --single-transaction to mitigate the problem. The command changes as follows.
The following command will use mysqldump to create a dump of all available databases in the OLD_HOST that are available for the user OLD_USER. The results will be imported to another server via the mysql pipe. OLD_USER="myUser"; OLD_PASS="myPASS"; OLD_HOST="myHost"; NEW_USER="myUserNEW"; NEW_PASS="myPASSNEW"; NEW_HOST="myHostNEW"; mysqldump -u "$OLD_USER" -p"$OLD_PASS" -h "$OLD_HOST" --all-databases |…
When working with MySQL databases, it's common to create backups of the database using the mysqldump utility. However, these backups can often take up a significant amount of disk space, especially for large databases. One way to reduce the size of these backups is to compress them using a compression…
When trying to issue the command mysqldump -h "mysql.example.com" -u "username" -p"password" db table_A table_B to get the MySQL dump for two tables (table_A and table_B), you will get the error mysqldump: Got error: 1044: Access denied for user 'username'@'ip' to database 'db' when doing LOCK TABLES when your account…
