--- 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