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.

Installation¶

Gentoo

emerge --ask dev-db/mysql

Configure mysql

Gentoo

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

Print status of a database¶

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

Show databases¶

show databases;

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

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;

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