Key points.
1. UFW allow specific IPs:
ufw allow from 123.456.789.0/24 to any port 3306
2. MySQL add remote users (e.g. only allow INSERT):
GRANT INSERT ON DB_NAME.TABLE_NAME TO 'REMOTE_USER_NAME'@'%' IDENTIFIED BY 'REMOTE_PASSWORD' WITH GRANT OPTION; FLUSH PRIVILEGES;
3. In PHP, use quotes around variable names;
$sql = "INSERT INTO TABLE_NAME (FIELD1, FIELD2) VALUES ('$VAR_1', '$VAR_2')"
Backup:
mysqldump --add-drop-table -h localhost -u root -p database_name > backup_filename.bak.sql
To restore, first create database database_name in MySQL, then
mysql -h localhost -u root -p database_name < backup_filename.bak.sql
This only works for root@localhost.
sudo /etc/init.d/mysql stop
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
mysql -u root
FLUSH PRIVILEGES;
SET PASSWORD FOR root@'localhost' = PASSWORD('password');
Refer MysqlPasswordReset for more complex cases.