Skip to content

mysql

Common MySQL administration commands, including installation, database management, user creation, privileges, and password policy configuration

Install mysql-server

emerge --ask dev-db/mysql

apt install mysql-server

Show databases

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

Delete database

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

Create user(s)

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

Create users without enter mysql

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

Create another super user

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:

grant all privileges on *.* to '<user>'@'%';
grant all privileges on *.* to 'root'@'%';

Without enter mysql

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

Configure mysql in Gentoo Linux

emerge --config dev-db/mysql

Start mysql-server

/etc/init.d/msysql start

Print status of a database

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

Show databases

show databases;

Print tables

show tables;

Granting Privileges

  • 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;

Create another SuperUser

  • Give <user> the same privileges as the default root account, beware
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
GRANT ALL PRIVILEGES ON *.* TO '<user>'@'%';
FLUSH PRIVILEGES;

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;

Disable 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 ued

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;