Backing up MySQL Database Hosted in Docker Container - Revision history

AdminKB42 at 15:30, 14 February 2024

2024-02-14T15:30:20Z

← Older revision		Revision as of 15:30, 14 February 2024
Line 71:		Line 71:

	{{article summary		{{article summary
	\| image = ~~Category NWO.png~~		\| image =
	\| title = {{%PAGENAME%}}		\| title = {{%PAGENAME%}}
	\| summary = We created a MySQL image with Dockerfile, and used this image to run MySQL container.		\| summary = We created a MySQL image with Dockerfile, and used this image to run MySQL container.

AdminKB42 at 15:26, 14 February 2024

2024-02-14T15:26:37Z

← Older revision		Revision as of 15:26, 14 February 2024
Line 69:		Line 69:
	$ cat backup.sql \| docker exec -i f91d97a62086 /usr/bin/mysql -u root --password=jsppassword jsptutorial		$ cat backup.sql \| docker exec -i f91d97a62086 /usr/bin/mysql -u root --password=jsppassword jsptutorial
	</syntaxhighlight>		</syntaxhighlight>

			{{article summary
			\| image = Category NWO.png
			\| title = {{%PAGENAME%}}
			\| summary = We created a MySQL image with Dockerfile, and used this image to run MySQL container.
			}}

	[[Category:Technology]]		[[Category:Technology]]
	[[Category:Software]]		[[Category:Software]]
	[[Category:Computers]]		[[Category:Computers]]
	[[Category:Privacy]]		[[Category:Privacy]]

AdminKB42 at 00:36, 23 November 2023

2023-11-23T00:36:13Z

← Older revision		Revision as of 00:36, 23 November 2023
Line 1:		Line 1:



	== Introduction==		== Introduction==
Line 71:		Line 69:
	$ cat backup.sql \| docker exec -i f91d97a62086 /usr/bin/mysql -u root --password=jsppassword jsptutorial		$ cat backup.sql \| docker exec -i f91d97a62086 /usr/bin/mysql -u root --password=jsppassword jsptutorial
	</syntaxhighlight>		</syntaxhighlight>
			[[Category:Technology]]
	~~## 4. Source Files~~		[[Category:Software]]
			[[Category:Computers]]
			[[Category:Privacy]]

AdminKB42 at 00:33, 23 November 2023

2023-11-23T00:33:38Z

← Older revision		Revision as of 00:33, 23 November 2023
Line 24:		Line 24:
	Dump all databases and save to file.		Dump all databases and save to file.

	~~```raw~~		<syntaxhighlight lang="bash">
	$ mysqldump --all-databases > dump.sql		$ mysqldump --all-databases > dump.sql
	~~```~~		</syntaxhighlight>

	Dump only specific databases and save to file.		Dump only specific databases and save to file.

	~~```raw~~		<syntaxhighlight lang="bash">
	$ mysqldump --databases db1 db2 db3 > dump.sql		$ mysqldump --databases db1 db2 db3 > dump.sql
	~~```~~		</syntaxhighlight>
	=== Using mysqldump for MySQL Container===		=== Using mysqldump for MySQL Container===

	Dump specific database in container. The format looks as follows.		Dump specific database in container. The format looks as follows.

	~~```raw~~		<syntaxhighlight lang="bash">
	$ docker exec CONTAINER /usr/bin/mysqldump -u root --password=PASSWORD DATABASE > backup.sql		$ docker exec CONTAINER /usr/bin/mysqldump -u root --password=PASSWORD DATABASE > backup.sql
	~~```~~		</syntaxhighlight>

	== Backing up and Restoring==		== Backing up and Restoring==
Line 46:		Line 46:
	Check the container id.		Check the container id.

	~~```raw~~		<syntaxhighlight lang="bash">
	$ docker ps		$ docker ps
	CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES		CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
	f91d97a62086 jspmysql:0.1 "docker-entrypoint..." 28 minutes ago Up 28 minutes 0.0.0.0:6603->3306/tcp jspmysql		f91d97a62086 jspmysql:0.1 "docker-entrypoint..." 28 minutes ago Up 28 minutes 0.0.0.0:6603->3306/tcp jspmysql
	~~```~~		</syntaxhighlight>

	Execute `mysqldump` in docker terminal.		Execute `mysqldump` in docker terminal.

	~~```raw~~		<syntaxhighlight lang="bash">
	$ docker exec f91d97a62086 /usr/bin/mysqldump -u root --password=jsppassword jsptutorial > backup.sql		$ docker exec f91d97a62086 /usr/bin/mysqldump -u root --password=jsppassword jsptutorial > backup.sql
	~~```~~		</syntaxhighlight>

	Notice `f91d97a62086` is the container id, `jsptutorial` is the database and `backup.sql` is the output file. After running the above command, a new backup.sql file is created. ![image](https://jojozhuang.github.io/assets/images/architecture/3513/mysqldump.png)		Notice `f91d97a62086` is the container id, `jsptutorial` is the database and `backup.sql` is the output file. After running the above command, a new backup.sql file is created. ![image](https://jojozhuang.github.io/assets/images/architecture/3513/mysqldump.png)
Line 68:		Line 68:
	You can also restore the data directly to the container. In docker terminal, run the following command.		You can also restore the data directly to the container. In docker terminal, run the following command.

	~~```raw~~		<syntaxhighlight lang="bash">
	$ cat backup.sql \| docker exec -i f91d97a62086 /usr/bin/mysql -u root --password=jsppassword jsptutorial		$ cat backup.sql \| docker exec -i f91d97a62086 /usr/bin/mysql -u root --password=jsppassword jsptutorial
	~~```~~		</syntaxhighlight>

	## 4. Source Files		## 4. Source Files

AdminKB42: Created page with " == Introduction== In posting [Creating MySQL Image with Docker File](https://jojozhuang.github.io/architecture/creating-mysql-image-with-docker-file/), we created MySQL image `jojozhuang/jspmysql` with Dockerfile, and used this image to run MySQL container. The issue is MySQL image doesn’t persist data. Initially, in this MySQL container, there are three rows in table `Product`. If you make some changes(eg. add new product), and create new image based on this conta..."

2023-11-23T00:28:20Z

Created page with " == Introduction== In posting [Creating MySQL Image with Docker File](https://jojozhuang.github.io/architecture/creating-mysql-image-with-docker-file/), we created MySQL image `jojozhuang/jspmysql` with Dockerfile, and used this image to run MySQL container. The issue is MySQL image doesn’t persist data. Initially, in this MySQL container, there are three rows in table `Product`. If you make some changes(eg. add new product), and create new image based on this conta..."

New page

== Introduction==

In posting [Creating MySQL Image with Docker File](https://jojozhuang.github.io/architecture/creating-mysql-image-with-docker-file/), we created MySQL image `jojozhuang/jspmysql` with Dockerfile, and used this image to run MySQL container. The issue is MySQL image doesn’t persist data. Initially, in this MySQL container, there are three rows in table `Product`. If you make some changes(eg. add new product), and create new image based on this container, these changes won’t be restored to the new image, they are lost. So each time before creating a new image for MySQL container, we need to backup the database first.

== MySQL Database Backup==

=== General Backup Approaches in MySQL===

Some general methods for making backups in MySQL.

- Making Backups with mysqldump
- Making Backups by Copying Table Files
- Making a Hot Backup with MySQL Enterprise Backup
- Making Delimited-Text File Backups
- …

We will focus on `mysqldump`.

=== Dumping Data in SQL Format with mysqldump===

Dump all databases and save to file.

```raw
$ mysqldump --all-databases > dump.sql
```

Dump only specific databases and save to file.

```raw
$ mysqldump --databases db1 db2 db3 > dump.sql
```
=== Using mysqldump for MySQL Container===

Dump specific database in container. The format looks as follows.

```raw
$ docker exec CONTAINER /usr/bin/mysqldump -u root --password=PASSWORD DATABASE > backup.sql
```

== Backing up and Restoring==
=== Dump Database ===

Check the container id.

```raw
$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
f91d97a62086 jspmysql:0.1 "docker-entrypoint..." 28 minutes ago Up 28 minutes 0.0.0.0:6603->3306/tcp jspmysql
```

Execute `mysqldump` in docker terminal.

```raw
$ docker exec f91d97a62086 /usr/bin/mysqldump -u root --password=jsppassword jsptutorial > backup.sql
```

Notice `f91d97a62086` is the container id, `jsptutorial` is the database and `backup.sql` is the output file. After running the above command, a new backup.sql file is created. ![image](https://jojozhuang.github.io/assets/images/architecture/3513/mysqldump.png)

=== Checking the Output File===

Notice that new product `(4,'iPad',399)` is added to the sql for restoring table `Product`. ![image](https://jojozhuang.github.io/assets/images/architecture/3513/newsql.png) Now you can rename this file to jsp_backup.sql or use it directly in Dockerfile to create MySQL image.

=== Restoring Data to MySQL Container===

You can also restore the data directly to the container. In docker terminal, run the following command.

```raw
$ cat backup.sql | docker exec -i f91d97a62086 /usr/bin/mysql -u root --password=jsppassword jsptutorial
```

## 4. Source Files