1. Dump database from remote server (ClearDB DBaaS is used in this post as an example).
mysqldump heroku_2fXXXXXX --host=us-xxxx-xxxx-xx.cleardb.com --user=xxxxxxxxx --password --no-tablespaces --column-statistics=0 > dbcopy.sql
heroku_2fXXXXXX is a database name (can be seen in heroku -> select your app in a list -> Configure add-ons -> click onClearDB MySQL item
--host is an endpoint url (can be seen in App -> Configure add-ons -> click on ClearDB MySQL item -> My Account button -> Enpoint URL)
--username is a db user (can be seen in App -> Configure add-ons -> click on ClearDB MySQL item -> My Account button -> Username)
The --no-tablespaces --column-statistics=0 are needed since MySQL upgrade, more info available here: https://dba.stackexchange.com/a/274460 and https://anothercoffee.net/how-to-fix-the-mysqldump-access-denied-process-privilege-error/
The mysqldump will prompt for a password, and download the database.
The dump file dbcopy.sql shold now be created in a local path. It is not an actual database, but a file with a set of SQL statements to recreate the database. Check it's size and contents.
2. Create new local db to fill it with the dumped data with the next step.
mysqladmin -uroot -p create newdb
3. Transfer the data from the dump file into a newdb file.
Substitute 'root' to the actual local mysql user name, if needed.
mysql -uroot -p newdb < dbcopy.sql
4. Check the newdb local database structure.
Launching local mysql console:
mysql -uroot -p
Now type the following in the console, to check the DB tables are there:
USE newdb;
SHOW TABLES;
Note: If the tables in DB are empty check again the present databases with
SHOW DATABASES. Sometimes, the backup sql would create a database with a specific name. This
name can be seen opening the backup sql file in any text viewer, and checking for the command at the start of the file. The command will look like CREATE DATABASE IF NOT EXISTS `some_database_name`.Checking DB size:
SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024, 1) "Database Size Kb"
FROM information_schema.tables
GROUP BY table_schema;
5. Save newdb database to external file.
Type:
exit;
in mysql console to close it.
Now run mysqldump again.
mysqldump --databases --user=root --password newdb > newdb.sql
Observe the newdb.sql file created in the directory where the mysqldump command was run.
>>> Optional. Below are the steps to perform the dump inside a Docker MySQL container. This is useful when you do not have mysql installed in local environment, and instead prefer to use MySQL in a Docker container.
A. Launching docker with mysql image.
docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -d mysql
-d option to launch the container in detached mode, so it will continue running in background. Check its status with docker -ps command.
-e option to set env variable, MYSQL_ROOT_PASSWORD in this case.
-p option to expose continer on 3306 port if it is needed to connect external tools to our DB, like MySQL Workbench application. The port 3306 should then be specified as the DB connection port in MySQL Workbench application.
Copy dump file to the inside of the container root diretory, using
docker cp command:
docker cp ./dbcopy.sql 6d2b07ed8c00:/.
- 6d2b07ed8c00 should be replaced with your Container ID (find it by running
docker ps command).
B. Launch bash session inside the launched container.
docker exec -it 6d2b07ed8c00 bash
- 6d2b07ed8c00 container id, exact id could be seen using docker ps command.
- it option to run interactivey.
Proceed with the steps 1 - 5 from above description for the local console, but this time inside the docker mysql container.
C. Copy file from docker container to host PC
In the docker container type
pwd to check the path to directory containing db.
Now type exit to exit docker bash.
Let's use docker cp command with docker container id and path to db inside container.
Container id could be found runnig thedocker container lsFinally, the last parameter to
docker cp command is the destination directory, or just . to copy file to current directory on host machine.Example:
docker cp 6d2b07ed8c00:/newdb.sql .
As a result we should be able to see the new MySQL DB file on the host machine.
