---
# System prepended metadata

title: Linux Database Migration
tags: [ccdc]

---

---
tags: ccdc
---

# Linux Database Migration

Note: most values/variables with \_ (underscores) are not literal and will need to be changed to an appropriate literal value
## Determine relevent database in DBMS
mysql:
```sql=
SHOW DATABASES;
```

## Dump database to file db.sql (example, use a meaningful name)
For SQL databases:
```bash=
mysqldump -u root --triggers --events --routines name_of_database > db.sql
```

## Transfer db.sql to new database server w/ scp

### Enable outbound SSH (server to transfer FROM)

#### ufw
TODO

#### iptables
TODO

### Enable inbound SSH (server to transfer TO)

#### ufw
TODO

#### iptables
TODO

### scp on source system
```bash=
scp src_file_path remote_username:dst_file_path
```

### scp on destination system
```bash=
scp remote_username:src_file_path dst_file_path 
```

## Load database from db.sql on new server
mysql:
```sql=
source db.sql;
```

## Set up new user on database server to grant access to web server
mysql:
```sql=
GRANT ALL ON name_of_database.* TO 'web_server_username'@'web_server_ip' IDENTIFIED BY 'some_password';
```

## Change configuration on database server to allow remote connections
Example:
change the bind-address variable to be 0.0.0.0 in /etc/mysql/mariadb.conf.d/50-server.cnf

## Change configuration on web server to connect as the new user to the new database server
Example:
update the variables $host, $user, $password and $dbprefix in /var/www/html/shop/configuration.php

## Allow outbound db/sql traffic on web server
### ufw
```bash=
ufw allow out from 0.0.0.0/0 to db_server_ip port 3306 proto tcp
```
### iptables
TODO

### verify web server can connect to database server
quick & dirty
```bash=
telnet database_ip 3306
```
better
```bash=
mysql -h database_ip -u database_user
```

## Verify on website that everything works as expected 
e.g. items pulled from database appear on appropriate pages