How to copy MySQL database from server

Steps for the local console session.

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 the
docker container ls

Finally, 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.

---

Accessing CoreData from a single background context

The usual scheme when working with Core Data is using one or more of the background contexts for modifying data, and one viewContext for reading and displaying it.

Utilizing many background contexts may seem like a good idea at first, because it allows to modify the data simultaneously from different threads. However, using many background CoreData contexts increases the complexity of a program. It can be tricky to properly set up the CoreData Merge Policy covering all the possible cases, and hence get into the concurrency related issues.

The following unit test recreates the basic concurrency issue on purpose, when the same data entity is being modified from those two background contexts on lines 59 and 75:

The test will pass if the line 78 would be uncommented. This line sets the merge policy that, instead of throwing error, prefers the persisted field version over the newer field change. But, instead of setting a specific merge policy, more viable option would be to have a single background context that executes all the modifications sequentially. It is much easier to manage and reason about.

To employ sequential CoreData writing scheme it's convenient to have a wrapper class that would hold the required single background context. The wrapper class in this sample is named CoreDataInventory and should be used for every CoreData interaction in the application. CoreDataInventory wrapper class in this design is a singleton:

Having this class let's change the unit test, and call the new method CoreDataInventory.instance.perform(...) instead of performBackgroundTask(...). The CoreData merge policy in the test is also the default one (used by CoreData when no specific policy is set), and should throw the error if there is a merge conflict.

This test should pass without merge conflicts:

	 Executed 1 test, with 0 failures (0 unexpected) in 5.023 (5.028) seconds

In conclusion, many background contexts may be used only when it is proven that concrete application performs a high amount of time-consuming data writes, and practically benefits from having multiple background contexts. Otherwise, more practical alternative is to use a single background context for writing to CoreData.