MySQL Server
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;