Use MySQL without Root

When using Linux as Desktop OS, you should avoid being root as most as you can. So what to do when you want to run MySQL server for Development ? You could just use it by running.

systemctl start mysql

But that requires the root password. Also, ArchLinux package for MariaDB is configured to be run as separate user. You can use docker/podman, but those images are bigger than packages in Arch’s repo.

These process may apply to vanilla MySQL and other distros, but I haven’t tried them.

Install it from repo

❯ sudo pacman -S mariadb

Initialize it like this

❯ mariadb-install-db 
    --user=<Your Unix username> \
    --datadir=/some/path/you/own/.data/mysql

You might get following output

❯ mariadb-install-db --user=smit --datadir=/home/smit/.data/mysql
chown: cannot access '/usr/lib/mysql/plugin/auth_pam_tool_dir/auth_pam_tool': Permission denied
Couldn't set an owner to '/usr/lib/mysql/plugin/auth_pam_tool_dir/auth_pam_tool'.
It must be root, the PAM authentication plugin doesn't work otherwise..

chown: changing ownership of '/usr/lib/mysql/plugin/auth_pam_tool_dir': Operation not permitted
Cannot change ownership of the '/usr/lib/mysql/plugin/auth_pam_tool_dir' directory
to the 'smit' user. Check that you have the necessary permissions and try again.

Installing MariaDB/MySQL system tables in '/home/smit/.data/mysql' ...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system


Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is smit@localhost, it has no password either, but
you need to be the system 'smit' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/home/smit/.data/mysql'

You can test the MariaDB daemon with mysql-test-run.pl
cd '/usr/mysql-test' ; perl mysql-test-run.pl

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.
You can find additional information about the MySQL part at:
https://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

You can ignore those chown and permission errors.

Configuration

Now Create my.cnf file in your home as ~/.my.cnf

❯ cat .my.cnf                                                       

[server]
datadir=/home/smit/.data/mysql
socket=/tmp/mysql.sock

[mysql]
auto-rehash

!includedir /etc/my.cnf.d

remember to replace my username with yours

Running Server

Now start MySQL server

❯ mysqld                                      

Connecting

Now first connect with Unix socket as we don’t have password to login via network

❯ mysql -S /tmp/mysql.sock

Delete these users. They cause a lot of problems when connecting from localhost. I prefer to have once user to connect from everywhere as same user as this is not production server, and I’m mostly the single user connecting from tcp/127.0.0.1 for development purpose.

drop user ''@'localhost';
drop user smit@localhost;

Now your superuser

create user smit;
alter user smit identified by 'rIoawb+eoUwO% Noice pazvord aint it ?';
grant all on *.* to smit;
flush privileges;

DONE!

Now restart mysql server

❯ killall mysqld 
❯ mysqld & disown ; exit       

and connect to your MySQL server

❯ mysql -h 127.0.0.1 -P 3306 -p