MySQL server without root

frankie -

It's actually very simple. I'll detail the Linux version, but it's identical for Windows.

  1. Download MySQL
  2. Initialize Data Directory
  3. Create a Config File
  4. Start MySQL
  5. Connect to MySQL

1) Download MySQL
Go to MySQL Community and select the "Linux Generic" flavour. Unzip that.

2) Initialize Data Directory

If we try to start MySQL without initializing data-directory, we'll run into errors like:

"Failed to find valid data directory"
"Data Dictionary initialization failed"
# initialize data-dir
mysqld --initialize-insecure --datadir=/home/frankie/mysql/data
this will create a bunch of needed files in /home/frankie/mysql/data

3) Create a Config File

[server]
user=frankie
basedir=/home/frankie/mysql/mysql-8.0.29
datadir=/home/frankie/data

[mysqld]
pid-file=/home/frankie/mysql/mysqld.pid
socket=/home/frankie/mysql/mysqld.sock
port=31666

[client]
socket=/home/frankie/mysql/socket

4) Start MySQL

Now all left to do is fire up the server.

# start mysqld
mysqld --defaults-file=/home/frankie/my.cnf

# or you can start mysqld with watchdog
mysqld_safe --defaults-file=/home/frankie/my.cnf
mysqld_safe checks mysqld exit code and re-starts it on unexpected crashes

5) Connect to MySQL

mysql --host=localhost --socket="/home/frankie/mysql/mysqld.sock" -u root
root user doesn't yet have a password (nor can you connect from the outside)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
Query OK, 0 rows affected (0.03 sec)
Add a password to root!
mysql> UPDATE mysql.user SET host='%' WHERE user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Allow connections to root from the outside world

Now restart MySQL server and that's it, query away using your client of choice.

I'm partial to HeidiSQL and IntelliJ.