Around 2 years ago I set up a vagrant/ubuntu web development environment on my MacBook Pro (Intel). That worked well enough that it had been set & forget until I upgraded to an M2 MBP. It works as documented here, up to installing PHP.
Then I add...
config.vm.provision :shell, path: "provision/scripts/mysql.sh", :args => [MYSQL_VERSION, RT_PASSWORD, DB_USERNAME, DB_PASSWORD, DB_NAME, DB_NAME_TEST]
to my Vagrantfile, which runs...
#!/bin/bash
# MYSQL_VERSION $1
# RT_PASSWORD $2
# DB_USERNAME $3
# DB_PASSWORD $4
# DB_NAME $5
# DB_NAME_TEST $6
# Install MySQL
apt-get update
apt-get -y upgrade
debconf-set-selections <<< "mysql-server mysql-server/root_password password $2"
debconf-set-selections <<< "mysql-server mysql-server/root_password_again password $2"
apt-get -y install mysql-server
# Create the database and grant privileges
CMD="sudo mysql -uroot -p$2 -e"
$CMD "CREATE DATABASE IF NOT EXISTS $5"
$CMD "GRANT ALL PRIVILEGES ON $5.* TO '$3'@'%' IDENTIFIED BY '$4';"
$CMD "CREATE DATABASE IF NOT EXISTS $6"
$CMD "GRANT ALL PRIVILEGES ON $6.* TO '$3'@'%' IDENTIFIED BY '$4';"
$CMD "FLUSH PRIVILEGES;"
sed -i "s/.*bind-address.*/bind-address = 0.0.0.0/" /etc/mysql/mysql.conf.d/mysqld.cnf
grep -q "^sql_mode" /etc/mysql/mysql.conf.d/mysqld.cnf || echo "sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" >> /etc/mysql/mysql.conf.d/mysqld.cnf
service mysql restart
That script is exactly as used both in the earlier version of my GitHub project, & my production environment. It now fails with...
: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'Pa$$w0rdTw0'' at line 1
: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'Pa$$w0rdTw0'' at line 1
: Job for mysql.service failed because the control process exited with error code.
If I reduce mysql.sh to (effectively)...
#!/bin/bash
#RT_PASSWORD = $2
# Install MySQL
apt-get update
debconf-set-selections <<< "mysql-server mysql-server/root_password password $2"
debconf-set-selections <<< "mysql-server mysql-server/root_password_again password $2"
apt-get -y install mysql-server
service mysql restart
It runs without error. Given the lack of error, I take it the intended root password on MySQL was successfully set.
So I try ssh into Vagrant to create the database...
vagrant ssh
sudo mysql -uroot -pPa$$w0rd0ne -e "CREATE DATABASE IF NOT EXISTS 'db'"
It fails with a different error...
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
Running the sed line in ssh produces no error, however running the grep line produces error...
-bash: /etc/mysql/mysql.conf.d/mysqld.cnf: Permission denied
What must I do to make my mysql.sh script work again?
Because implicit user creation in GRANT statements is no longer available in MySQL 8.0, you need to create the user first:
$CMD "CREATE USER '$3'@'%' IDENTIFIED BY '$4';"
$CMD "CREATE DATABASE IF NOT EXISTS $5"
$CMD "GRANT ALL PRIVILEGES ON $5.* TO '$3'@'%'"
$CMD "CREATE DATABASE IF NOT EXISTS $6"
$CMD "GRANT ALL PRIVILEGES ON $6.* TO '$3'@'%'"