Skip to content

mysql | Cheatsheet

MySQL is a database management system.

A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or the vast amounts of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server. Since computers are very good at handling large amounts of data, database management systems play a central role in computing, as standalone utilities, or as parts of other applications.


emerge --ask dev-db/mysql

Configure mysql

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:

 * 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/' --log-error='/tmp/mysqld-tmp.162713228/install_mysqld.111127312.log' --user=mysql
Waiting for mysqld to accept connections .....
 * Setting root password ...          [ ok ]
Stopping 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 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 and get config file

emerge --config dev-db/mysql

Start mysql-server

/etc/init.d/msysql start
mysql -uroot -pPassword
use database <database>
show status

Show databases

show databases;
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;

Create another SuperUser

Give <user> the same privileges as the default root account, beware

GRANT ALL PRIVILEGES ON *.* TO '<user>'@'%';

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