Skip to content

MySQL Server

Step-by-step guide to installing, configuring, and managing MySQL Server on Gentoo Linux. Covers initial setup, user and database management, privileges, security policies, and OpenRC service control

Install dev-db/mysql

emerge --ask dev-db/mysql

MySQL-Server Configuration

emerge --config dev-db/mysql

Configuring pkg...

* Creating mysql data directory '/var/lib/mysql' ...

* Detected settings:
* ==================
* MySQL User:                          mysql
* MySQL Group:                         mysql
* MySQL DATA directory:                /var/lib/mysql
* MySQL TMP directory:                 /tmp/mysqld-tmp.162713228
* PID DIR:                             /run/mysqld
* Install db log:                      /tmp/mysqld-tmp.162713228/install_db.214113282.log
* Install server log:                  /tmp/mysqld-tmp.162713228/install_mysqld.111127312.log

* Please select default authentication plugin (enter number or plugin name):
* 1) caching_sha2_password [MySQL 8.0 default]
* 2) mysql_native_password [MySQL 5.7 default]
* 
* For details see:
* https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password
   >1

* Ensuring that default-auth is set to 'caching_sha2_password' in '/etc/mysql/mysql.d/50-distro-client.cnf' ...
* Ensuring that default-authentication-plugin is set to 'caching_sha2_password' in '/etc/mysql/mysql.d/50-distro-server.cnf' ...

* Trying to get password for mysql 'root' user from 'mysql' section ...
* Trying to get password for mysql 'root' user from 'client' section ...

* No password for mysql 'root' user was specified via environment
* variable MYSQL_ROOT_PASSWORD and no password was found in config
* file like '/root/.my.cnf'.
* To continue please provide a password for the mysql 'root' user
* now on console:
* NOTE: Please avoid ["'\_%] characters in the password!
   >
* Retype the password
   >

* Initializing mysql data directory: /usr/sbin/mysqld --loose-skip-host-cache --loose-skip-name-resolve --loose-skip-networking --loose-skip-slave-start --loose-skip-federated --loose-skip-ssl --loose-skip-log-bin --loose-skip-relay-log --loose-skip-slow-query-log --loose-skip-external-locking --loose-skip-log-slave-updates --initialize-insecure --init-file='/tmp/mysqld-tmp.162713228/tz.512161812.sql' --basedir='/usr' --datadir='/var/lib/mysql' --tmpdir='/tmp/mysqld-tmp.162713228' --log-error='/tmp/mysqld-tmp.162713228/install_db.214113282.log' --user=mysql
* Starting mysqld to finalize initialization: /usr/sbin/mysqld --loose-skip-host-cache --loose-skip-name-resolve --loose-skip-networking --loose-skip-slave-start --loose-skip-federated --loose-skip-ssl --loose-skip-log-bin --loose-skip-relay-log --loose-skip-slow-query-log --loose-skip-external-locking --loose-skip-log-slave-updates --basedir='/usr' --datadir='/var/lib/mysql' --tmpdir='/tmp/mysqld-tmp.162713228' --max_allowed_packet=8M --net_buffer_length=16K --socket='/run/mysqld/mysqld17597.sock' --pid-file='/run/mysqld/mysqld17597.pid' --log-error='/tmp/mysqld-tmp.162713228/install_mysqld.111127312.log' --user=mysql
aiting for mysqld to accept connections .....
* Setting root password ...          [ ok ]
topping the server .
* mysql data directory at '/var/lib/mysql' successfully initialized!

Show Databases

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wikidb             |
+--------------------+
5 rows in set (0.00 sec)

Delete A Certain Database

mysql> drop database wikidb;
Query OK, 58 rows affected (0.93 sec)

Add MySQL-User

create user '<user>'@'%' identified by '<password>';

From Shell Without Entering MySQL Enviroment

mysql -e "create user '<user>'@'%' identified by '<password>';"

Create Another SuperUser

While not particularly secure, in some cases you may wish to create another super user, that has ALL privileges across ALL databases on the server. That can be performed similar to above, but by replacing the database_name with the wildcard asterisk:

mysql -e "grant all privileges on *.* to '<user>'@'%';"
mysql -e "grant all privileges on *.* to 'root'@'%';"

Configure MySQL And Create Default Configuration

emerge --config dev-db/mysql

Start MySQL-Server

/etc/init.d/msysql start

Print Status Of A Aatabase

mysql -uroot -pPassword
use database <database>
show status

Dump Database Tables

show tables;

Granting Privileges

To GRANT ALL privileges to a user, allowing that user full control over a specific database, use the following syntax:

GRANT ALL PRIVILEGES ON *.* TO '<user>'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost';
flush privileges;

Security Settings

Set password policy, min length

SET GLOBAL validate_password.length = 4;

Change MySQL Password Validation Policy (use 0 for allow all passwords)

SET GLOBAL validate_password.policy = 0;   
SET GLOBAL validate_password.policy = 1;
SET GLOBAL validate_password.policy = 2;

Turn off the mysql password validation

UNINSTALL COMPONENT 'file://component_validate_password';

Show validate_password level

SHOW VARIABLES LIKE 'validate_password%';

Check validation policy level

SHOW VARIABLES LIKE 'validate_password%';

Allow all passwords to be used

SET GLOBAL validate_password.LENGTH = 4;
SET GLOBAL validate_password.policy = 0;
SET GLOBAL validate_password.mixed_case_count = 0;
SET GLOBAL validate_password.number_count = 0;
SET GLOBAL validate_password.special_char_count = 0;
SET GLOBAL validate_password.check_user_name = 0;
ALTER USER '<user>'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;