Jan 1, 2023
Create new Linode.
Login via ssh
Add new admin user.
adduser sammy
sudo usermod -aG sudo sammy
Install LAMP server.
sudo apt install tasksel
sudo tasksel
Open browser athttp://45.79.28.148/
Apache2 Default Page message comes up - it works.
This used to offer install LAMP server, but only web server was offered.
Alternative instructions at
https://www.digitalocean.com/community/tutorials/how-to-install-linux-apache-mysql-php-lamp-stack-on-ubuntu-22-04
Install mySQL server:
sudo apt install mysql-server
sudo mysql
Install PHP
sudo apt install php libapache2-mod-php php-mysql
Install phpmyadmin
https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-22-04
sudo apt install phpmyadmin php-mbstring php-zip php-gd php-json php-curl
Test phpmyadminhttp://45.79.28.148/phpmyadmin
Need a user that for logging in to phpmayadmin
add sammy to allowed users of mySQL:
CREATE USER 'sammy'@'%localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Display how each user is authenticated:
SELECT user,authentication_string,plugin,host FROM mysql.user;
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
Leads to
ERROR 1410 (42000): You are not allowed to create a user with GRANT
Try this:https://lefred.be/content/how-to-grant-privileges-to-users-in-mysql-8-0/
sudo mysql
create user 'user1' identified by 'ChangeMe' password expire;
exit
mysql -u user1 -pChangeMe -h localhost
select now();
set password='MySQL8isGreat';
select now();
show grants;
show privileges;
create user 'user1' identified by 'ChangeMe' password expire;
exit
mysql -u user1 -pChangeMe -h localhost
select now();
set password='MySQL8isGreat';
select now();
show grants;
show privileges;
It is now possible to log into phpmyadmin with userq and its password.
However,under databases is says:
No privileges to create databases
grant alter,create,delete,drop,index,insert,select,update,trigger,alter routine, create routine, execute, create temporary tables on user1.* to 'user1';
Problem:However,under databases is says:
No privileges to create databases
grant alter,create,delete,drop,index,insert,select,update,trigger,alter routine, create routine, execute, create temporary tables on user1.* to 'user1';
Now I can't even start mysql anymore, even with root and its password.
Repair attempt:
Remove mysql completely.
https://askubuntu.com/questions/640899/how-do-i-uninstall-mysql-completely
New attempt: remove mysql
sudo apt install mysql-server
sudo mysql
but now terminal hangs when
SELECT user,authentication_string,plugin,host FROM mysql.user;
New attempt: fresh Ubuntu install
Create new Linode.
Login via ssh
sudo apt update
sudo apt upgrade
sudo reboot
Install Apache web server
sudo apt install apache2
Open browser athttp://45.79.28.148/
Apache2 Default Page message comes up - it works.
Not changing ufw firewall for now because my deno servers will use port 8000.
Install mySQL server
sudo apt install mysql-server
Start mysql:
sudo mysql
Display how each user is authenticated:
SELECT user,authentication_string,plugin,host FROM mysql.user;
works again, shows
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
M2J5j3.SKd.le.AbBBio/lJQPXdr2kuiIlsTkKC03i9 | caching_sha2_password | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root | | auth_socket | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+ 5 rows in set (0.01 sec)
Which user am I?+------------------+------------------------------------------------------------------------+-----------------------+-----------+
| user | authentication_string | plugin | host |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+
M2J5j3.SKd.le.AbBBio/lJQPXdr2kuiIlsTkKC03i9 | caching_sha2_password | localhost |
| mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password | localhost |
| root | | auth_socket | localhost |
+------------------+------------------------------------------------------------------------+-----------------------+-----------+ 5 rows in set (0.01 sec)
SELECT CURRENT_USER();
gives
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
Need a user that for logging in to phpmayadmin+----------------+
| CURRENT_USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
add sammy to mySQL users:
CREATE USER 'sammy'@'%localhost' IDENTIFIED WITH mysql_native_password BY 'password';
The grant directive fails againL
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
Leads to
ERROR 1410 (42000): You are not allowed to create a user with GRANT
what works issudo mysql ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION; but not with a different user.
Jan 2, 2023
New attemptlinuxhint https://linuxhint.com/install-mysql-on-ubuntu-22-04/
try in a virtual machine
sudo apt-get install mysql-server systemctl status mysql sudo mysql_secure_installation press y for yes get error: ... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters. This situation is considered in the tutorial by digitalocean https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-22-04
so next
sudo mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
exit
now run secure install again:
sudo mysql_secure_installation
will ask for password, is now 'password'
sudo mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.
Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : n
... skipping.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n
... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
start mysql
mysql -u root -p
CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.* TO
'sammy'@'localhost' WITH GRANT OPTION;
now works!!
FLUSH PRIVILEGES;
exit
mysql -u sammy -p
list databases:
SHOW DATABASES;
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
create new database:
CREATE DATABASE db_name;
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| db_name |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
