One of my early retirement projects was moving my WordPress server off of a 2012 Mac Mini at MacStadium. First I emailed them to ask if they had any more recent but not M2 machines available, no response on that (yet) from sales so I ended up stetting on Digital Ocean with a WordPress Droplet and a managed MySQL. As always with the once 4/5 year server move its a learning experience.
I have a WordPress Multisite install with a ton of travel blog post summaries on http://travel-blogs.publicationaggregator.com ~ 300k posts, and another private site of roughly the same size. Due to the size of the site it can be hard on the database despite not being a highly accessed site.
This is the hardware I ended up setting on to get things working.
Managed SQL Server – 4 GB RAM / 2vCPU / 60 GB Disk / Primary only ~ $60/month
WordPress Droplet 2 GB Memory / 1 AMD vCPU / 50 GB Disk ~$8/month
Here a series of commands needed to get things working. Its not complete but putting it out there incase it helps me or someone else down the line
## if you setup the wordpress droplet with the managed sql server via the same UI window all in one step it will deal with the certificates for the MySQL connection, otherwise you need to read how to link those.
## sql server
## add a trusted connection to the wordpress droplet on the sql server side
# how to connect to the database server
mysql -u user -p -h database_server -P port
#create a new database and user for wordpress use
## on the droplet or on the security tab create a Reserved IP and map it to the droplet. I was fine with my sites being offline so I pointed my domains to that IP and if I messed up a droplet I could delete it and start over and just re-map the Reserved IP to the new droplet.
# On the wordpress droplet login via to command line (they have a popup, or you could upload your certificate and login via ssh from your computer) and do the setup based on your primary domain. Since I'm on multisite I will overwrite that and setup each site manually, to do that I backup 000-default.conf in sites-available and then delete any other .confs in both sites-available and sites-enabled and move 000-default.conf back to sites-available
# reset things
cp /etc/apache2/sites-available/000-default.conf ~/000-default.conf
rm /etc/apache2/sites-available/*
cp ~/000-default.conf /etc/apache2/sites-available/000-default.conf
ls /etc/apache2/sites-enabled/
rm /etc/apache2/sites-enabled/*
# edit and add domain to logs
# I create a set of log file for each
nano /etc/apache2/sites-available/000-default.conf
#update logs to include $domain-, so each domain.com-error.log
ErrorLog ${APACHE_LOG_DIR}/$domain-error.log
CustomLog ${APACHE_LOG_DIR}/$domain-access.log combined
## then for each domain I setup the conf by copying and replacing $domain with the domain name, then enable the site and create the certbot certificate. For subdomains that are their own blogs I do the same.
#site1.com
cp /etc/apache2/sites-available/000-default.conf /etc/apache2/sites-available/site1.com.conf
sed -i "s/\$domain/site1.com/g" /etc/apache2/sites-available/site1.com.conf
sudo a2ensite site1.com.conf
certbot --apache -d site1.com
#subdomain.site1.com
cp /etc/apache2/sites-available/000-default.conf /etc/apache2/sites-available/subdomain.site1.com.conf
sed -i "s/\$domain/subdomain.site1.com/g" /etc/apache2/sites-available/subdomain.site1.com.conf
sudo a2ensite subdomain.site1.com.conf
certbot --apache -d subdomain.site1.com
#site2.com
cp /etc/apache2/sites-available/000-default.conf /etc/apache2/sites-available/site2.com.conf
sed -i "s/\$domain/site2.com/g" /etc/apache2/sites-available/site2.com.conf
sudo a2ensite site2.com.conf
certbot --apache -d site2.com
systemctl reload apache2
## at that point the domains are mapped and if you have updated your DNS it should do something when you hit to domains
#then I download the wordpress backup and MySQL backup I have in S3
# so first install was-cli and then configure with the AWS access data
snap install aws-cli --classic
aws configure
AWS Access Key ID [None]: xxx
AWS Secret Access Key [None]: xxx
Default region name [None]: us-east-1
Default output format [None]: json
aws s3 cp s3://bucket/wordpressarchivefile.tar.gz wordpressarchivefile.tar.gz.tar.gz
tar -xvzf wordpressarchivefile.tar.gz.tar.gz
#copy the themes data from the restored archive path
cp -r Users/user/Sites/wp-content/themes/* /var/www/html/wp-content/themes
#fix permission
chown www-data:www-data /var/www/html/wp-content/themes/*
## download the sql server backup and load
aws s3 cp s3://bucket/wdatabasebackupfile.sql.tar.gz databasebackupfile.sql.tar.gz.tar.gz
tar -xvzf databasebackupfile.sql.tar.gz
# load to the sql on the managed sql server
mysql -u user -p -h database_server -P port database < databasebackupfile.sql
#start setting up wp-config
nano /var/www/html/wp-config.php
## this has the DB data and multisite commands for wp-config
#### START wp-config changes
define( 'DB_NAME', 'database' );
/** Database username */
define( 'DB_USER', 'user' );
/** Database password */
define('DB_PASSWORD', 'password');
/** Database hostname. #### Notice the database:port setting */
define( 'DB_HOST', 'database:port' );
/** Database charset to use in creating database tables. */
define( 'DB_CHARSET', 'utf8' );
/** The database collate type. Don't change this if in doubt. */
define( 'DB_COLLATE', '' );
/** ### note you are using SSL so you need certificate if you setup droplet and MySQL in the same command it will be setup, if not you need to read up on how to add the certificate to the droplet */
define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);
/* Multisite */
define('MULTISITE', true);
define('SUBDOMAIN_INSTALL', true);
define('DOMAIN_CURRENT_SITE', 'domain1.com');
define('PATH_CURRENT_SITE', '/');
define('SITE_ID_CURRENT_SITE', 1);
define('BLOG_ID_CURRENT_SITE', 1);
define('COOKIE_DOMAIN', false );
#### END wp-config changes
#a2disconf block-xmlrpc
#systemctl reload apache2
## I had some issues getting fail2ban to work which is installed via the WordPress droplet, the following steps might work
sudo apt install fail2ban
cp /var/www/html/wp-content/plugins/wp-fail2ban/filters.d/* /etc/fail2ban/filter.d/
#https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-memcached-on-ubuntu-20-04#step-3-—-adding-authorized-users
## add memcached
sudo apt install memcached
sudo apt install libmemcached-tools
sudo systemctl start memcached
sudo apt install sasl2-bin
sudo mkdir -p /etc/sasl2
sudo nano /etc/sasl2/memcached.conf
log_level: 5
mech_list: plain
sasldb_path: /etc/sasl2/memcached-sasldb2
sudo saslpasswd2 -a memcached -c -f /etc/sasl2/memcached-sasldb2 root
sudo chown memcache:memcache /etc/sasl2/memcached-sasldb2
memcstat --servers="127.0.0.1"
sudo nano /etc/memcached.conf
sudo systemctl restart memcached
### 134.0.0.1 IP is your WordPress droplet ipv4
memcstat --servers="134.0.0.1"
sudo ss -plunt
php --version
sudo apt-get install -y php8.0-memcached
systemctl reload apache2
php -m
### 134.0.0.1 IP is your WordPress droplet ipv4
nano /var/www/html/wp-config.php
/** Memcached credentials */
global $memcached_servers;
$memcached_servers = array( array( '134.0.0.1', 11211 ) );
#nano /etc/php/8.3/cli/php.ini
sudo chmod 777 /var/www/html/.htaccess
sudo chmod 777 /var/www/html/wp-content/
#config W3 Total Cache and then revert
sudo chmod 755 /var/www/html/.htaccess
sudo chmod 755 /var/www/html/wp-content/
### 134.0.0.1 IP is your WordPress droplet ipv4
# set Page Cache advance Memcached hostname:port / IP:port: 134.0.0.1:11211
sudo apt install imagemagick
sudo apt install php-imagick
sudo systemctl restart apache2
# disable the mysql installed by the WordPress droplet since using managed SQL
sudo service mysql stop
sudo systemctl disable mysql