Migrate MySQL from native installation to docker(ubuntu)

“It worked yesterday”
— Unknown

Please let me know in the comments if this process can be improved, or if I forgot a step. Remember to make proper backups/snapshots of your Server before proceeding with the steps below

Export your Databases

From the command-line, you can use mysqldump to export all your databases

mysqldump -uroot -pPASSWORD database1 > database1.sql

mysqldump -uroot -pPASSWORD database1 > database1.sql

Docker-compose file

Make a docker-compose.yml file for MySQL:

version: '3.1'

services:
  docker-mysql:
    container_name: docker-mysql
    image: mysql:latest
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: YOURPASSWORDHERE
    volumes:
      - /put/your/own/path/here:/var/lib/mysql
    networks:
      frontend:
        ipv4_address: 172.20.0.5
      Database-network:
        ipv4_address: 172.50.0.6

networks:
  frontend:
    ipam:
      config:
        - subnet: 172.20.0.0/24
  Database-network:
    external: true

version: '3.1'

services:
  docker-mysql:
    container_name: docker-mysql
    image: mysql:latest
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: YOURPASSWORDHERE
    volumes:
      - /put/your/own/path/here:/var/lib/mysql
    networks:
      frontend:
        ipv4_address: 172.20.0.5
      Database-network:
        ipv4_address: 172.50.0.6

networks:
  frontend:
    ipam:
      config:
        - subnet: 172.20.0.0/24
  Database-network:
    external: true

Start it up, and you will have a Docker-based MySQL database running on 172.20.0.5:

docker-compose up -d

docker-compose up -d

Import Data into the new Database

Connect to the new database and run the SQL commands to (I used PHPmyAdmin)

Create new databases
Create users
Alter user privileges

-- Create the databases
create database database1

-- Create the database users
CREATE USER 'user1'@'172.17.0.5' IDENTIFIED BY 'PASSWORD';

-- Alter user account privileges
GRANT ALL PRIVILEGES ON `database1`.* TO 'user1'@'172.17.0.5'; 
ALTER USER 'user1'@'172.17.0.5'; 

FLUSH PRIVILEGES;

-- Create the databases
create database database1

-- Create the database users
CREATE USER 'user1'@'172.17.0.5' IDENTIFIED BY 'PASSWORD';

-- Alter user account privileges
GRANT ALL PRIVILEGES ON `database1`.* TO 'user1'@'172.17.0.5'; 
ALTER USER 'user1'@'172.17.0.5'; 

FLUSH PRIVILEGES;

Then run docker commands to import the MySQL dumps

docker exec -i docker-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" database1' < /path/to/sqldump/database1.sql

docker exec -i docker-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD" database1' < /path/to/sqldump/database1.sql

Change configs

Change all references to the old MySQL database in your apps

Clean-up Work

Make sure MySQL is stopped and not running

sudo /etc/init.d/mysql stop
sudo systemctl stop mysql

sudo /etc/init.d/mysql stop
sudo systemctl stop mysql

Purge all MySQL packages

sudo apt purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*

sudo apt purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*

Delete all MySQL files

sudo rm -rf /etc/mysql /var/lib/mysql /var/log/mysql

sudo rm -rf /etc/mysql /var/lib/mysql /var/log/mysql

Finally, clean all packages that are not needed

sudo apt autoremove
sudo apt autoclean

sudo apt autoremove
sudo apt autoclean

Congrats! You’re running MySQL on Docker.