MySQL Server Install on Ubuntu Server: Difference between revisions
No edit summary |
|||
| (6 intermediate revisions by the same user not shown) | |||
| Line 17: | Line 17: | ||
Log into the SQL server as root by running | Log into the SQL server as root by running | ||
sudo | sudo mysql | ||
Create a new user, where 'password' is your password | Create a new user, where 'password' is your password | ||
CREATE USER 'joey'@'localhost' IDENTIFIED BY 'password'; | CREATE USER 'joey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; | ||
Output should look like <code>Query OK, 0 rows affected (0.01 sec)</code> | Output should look like <code>Query OK, 0 rows affected (0.01 sec)</code> | ||
| Line 27: | Line 27: | ||
Edit the password if needed later | Edit the password if needed later | ||
ALTER USER 'joey'@'localhost' IDENTIFIED | ALTER USER 'joey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password'; | ||
Next, grant privileges as needed (this example grants ALL privileges so use sparingly) | Next, grant privileges as needed (this example grants ALL privileges so use sparingly) | ||
GRANT ALL PRIVILEGES ON *.* TO 'joey'@'localhost' WITH GRANT OPTION; | GRANT ALL PRIVILEGES ON *.* TO 'joey'@'localhost' WITH GRANT OPTION; | ||
After this you will need to log out and log back into any running sessions. | |||
===Remote Access=== | |||
If you are getting "Connection refused" after setting up all the security groups and can't figure out why confirm that the bind address is 0.0.0.0 instead of 127.0.0.1. | |||
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf | |||
Find the section labeled <code>[mysqld]</code> and confirm the settings are as follows. | |||
bind-address = 0.0.0.0 | |||
mysqlx-bind-address = 127.0.0.1 | |||
==Management== | |||
The following apps make it easy to manage your install after setup. | |||
===Mac=== | |||
*[https://apps.apple.com/us/app/sequel-ace/id1518036000 Sequel Ace] | |||
===Web=== | |||
*[[PHPMyAdmin Install on Ubuntu Server|PHPMyAdmin]] | |||
[[Category: Homelab]] | [[Category: Homelab]] | ||
Latest revision as of 19:38, 20 February 2025
This guide shows how to install and set up MySQL server on Ubuntu Server.
Installation
Update/Upgrade any pending packages
sudo apt update
sudo apt upgrade
Install MySQL Server
sudo apt install mysql-server
Configuration
Log into the SQL server as root by running
sudo mysql
Create a new user, where 'password' is your password
CREATE USER 'joey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Output should look like Query OK, 0 rows affected (0.01 sec)
Edit the password if needed later
ALTER USER 'joey'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';
Next, grant privileges as needed (this example grants ALL privileges so use sparingly)
GRANT ALL PRIVILEGES ON *.* TO 'joey'@'localhost' WITH GRANT OPTION;
After this you will need to log out and log back into any running sessions.
Remote Access
If you are getting "Connection refused" after setting up all the security groups and can't figure out why confirm that the bind address is 0.0.0.0 instead of 127.0.0.1.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Find the section labeled [mysqld] and confirm the settings are as follows.
bind-address = 0.0.0.0 mysqlx-bind-address = 127.0.0.1
Management
The following apps make it easy to manage your install after setup.