MySQL

notes:

things I tend to forget :)

#find config file load order
mysql --help | grep cnf
sudo vi .my.cnf
    [mysqd]
    character-set-server=utfmb4
    collation-server=utfmb4_unicode_ci

20/03/29 migrating databases

Migrating Tables to InnoDB

some tables I'm dealing with were created with MySQL v5.1 [ v5.7.29 zeke/woozer & v8.0.19 -macs ]

SET @DATABASE_NAME = 'name_of_db';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html
In MySQL 8.0, the default authentication plugin has changed from mysql_native_password to caching_sha2_password, and the 'root'@'localhost' administrative account uses caching_sha2_password by default. If you prefer that the root account use the previous default authentication plugin (mysql_native_password), see caching_sha2_password and the root Administrative Account.

cd /usr/local/mysql
mkdir mysql-files
chown mysql:mysql mysql-files
chmod 750 mysql-files
bin/mysqld --initialize --user=mysql

#######  caching_sha2_password -> mysql_native_password in v8 #######

mysql> ALTER USER 'pma'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ERROR 1726 (HY000): Storage engine 'MyISAM' does not support system tables. [mysql.db]

mysql> alter table mysql.db ENGINE=InnoDB

mysql> REPAIR TABLE mysql.db;
+----------+--------+----------+---------------------------------------------------------+
| Table    | Op     | Msg_type | Msg_text                                                |
+----------+--------+----------+---------------------------------------------------------+
| mysql.db | repair | note     | The storage engine for the table doesnt support repair

also had to grab v5.7 via homebrew so that I could switch between versions.

If you need to have mysql@5.7 first in your PATH run:
  echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.zshrc

For compilers to find mysql@5.7 you may need to set:
  export LDFLAGS="-L/usr/local/opt/mysql@5.7/lib"
  export CPPFLAGS="-I/usr/local/opt/mysql@5.7/include"

For pkg-config to find mysql@5.7 you may need to set:
  export PKG_CONFIG_PATH="/usr/local/opt/mysql@5.7/lib/pkgconfig"

To have launchd start mysql@5.7 now and restart at login:
  brew services start mysql@5.7
Or, if you don't want/need a background service you can just run:
  /usr/local/opt/mysql@5.7/bin/mysql.server start

needed a new pmauser on 5.7

GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'pmapass';
GRANT SELECT (
    Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
    Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
    File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
    Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
    Execute_priv, Repl_slave_priv, Repl_client_priv
    ) ON mysql.user TO 'pma'@'localhost';
GRANT SELECT ON mysql.db TO 'pma'@'localhost';
GRANT SELECT ON mysql.host TO 'pma'@'localhost';
GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
    ON mysql.tables_priv TO 'pma'@'localhost';

January 2020 Notes:
Migrated all of my local machines to MySQL v.8.0.19
Still debating MariaDB and I've chosen to stick with MySQL for the time being due to existing documentation for the various software. Had to briefly install multiple versions using Homebrew and DBengin in order to rectify some issues between versions by defining datadir=. As of MySQL 8.0.16, the server performs the tasks previously handled by mysql_upgrade. After installation of a new MySQL version, the server now automatically performs all necessary upgrade tasks at the next startup and is not dependent on the DBA invoking mysql_upgrade. All of my existing working local and remote databases are backed up by folder date on my two external drives.

What's New is MySQL 8
MySQL 8 Release Notes

commands

mysql > status
mysql > exhibit

Access monitor: mysql -u [username] -p; (will prompt for password)

Show all databases: show databases;

Access database: mysql -u [username] -p [database] (will prompt for password)

Create new database: create database [database];

Select database: use [database];

Determine what database is in use: select database();

Show all tables: show tables;

Show table structure: describe [table];

List all indexes on a table: show index from [table];

Create new table with columns: CREATE TABLE [table] ([column] VARCHAR(120), [another-column] DATETIME);

Adding a column: ALTER TABLE [table] ADD COLUMN [column] VARCHAR(120);

Adding a column with an unique, auto-incrementing ID: ALTER TABLE [table] ADD COLUMN [column] int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Inserting a record: INSERT INTO [table] ([column], [column]) VALUES ('[value]', [value]');

MySQL function for datetime input: NOW()

Selecting records: SELECT * FROM [table];

Explain records: EXPLAIN SELECT * FROM [table];

Selecting parts of records: SELECT [column], [another-column] FROM [table];

Counting records: SELECT COUNT([column]) FROM [table];

Counting and selecting grouped records: SELECT *, (SELECT COUNT([column]) FROM [table]) AS count FROM [table] GROUP BY [column];

Selecting specific records: SELECT * FROM [table] WHERE [column] = [value]; (Selectors: <, >, !=; combine multiple selectors with AND, OR)

Select records containing [value]: SELECT * FROM [table] WHERE [column] LIKE '%[value]%';

Select records starting with [value]: SELECT * FROM [table] WHERE [column] LIKE '[value]%';

Select records starting with val and ending with ue: SELECT * FROM [table] WHERE [column] LIKE '[val_ue]';

Select a range: SELECT * FROM [table] WHERE [column] BETWEEN [value1] and [value2];

Select with custom order and only limit: SELECT * FROM [table] WHERE [column] ORDER BY [column] ASC LIMIT [value]; (Order: DESC, ASC)

Updating records: UPDATE [table] SET [column] = '[updated-value]' WHERE [column] = [value];

Deleting records: DELETE FROM [table] WHERE [column] = [value];

Delete all records from a table (without dropping the table itself): DELETE FROM [table]; (This also resets the incrementing counter for auto generated columns like an id column.)

Delete all records in a table: truncate table [table];

Removing table columns: ALTER TABLE [table] DROP COLUMN [column];

Deleting tables: DROP TABLE [table];

Deleting databases: DROP DATABASE [database];

Custom column output names: SELECT [column] AS [custom-column] FROM [table];

Export a database dump (more info here): mysqldump -u [username] -p [database] > db_backup.sql

Use --lock-tables=false option for locked tables (more info here).

Import a database dump (more info here): mysql -u [username] -p -h localhost [database] < db_backup.sql

Logout: exit;

Aggregate functions

Select but without duplicates: SELECT distinct name, email, acception FROM owners WHERE acception = 1 AND date >= 2015-01-01 00:00:00

Calculate total number of records: SELECT SUM([column]) FROM [table];

Count total number of [column] and group by [category-column]: SELECT [category-column], SUM([column]) FROM [table] GROUP BY [category-column];

Get largest value in [column]: SELECT MAX([column]) FROM [table];

Get smallest value: SELECT MIN([column]) FROM [table];

Get average value: SELECT AVG([column]) FROM [table];

Get rounded average value and group by [category-column]: SELECT [category-column], ROUND(AVG([column]), 2) FROM [table] GROUP BY [category-column];

Multiple tables

Select from multiple tables: SELECT [table1].[column], [table1].[another-column], [table2].[column] FROM [table1], [table2];

Combine rows from different tables: SELECT * FROM [table1] INNER JOIN [table2] ON [table1].[column] = [table2].[column];

Combine rows from different tables but do not require the join condition: SELECT * FROM [table1] LEFT OUTER JOIN [table2] ON [table1].[column] = [table2].[column]; (The left table is the first table that appears in the statement.)

Rename column or table using an alias: SELECT [table1].[column] AS '[value]', [table2].[column] AS '[value]' FROM [table1], [table2];

Users functions

List all users: SELECT User,Host FROM mysql.user;

Create new user: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant ALL access to user for * tables: GRANT ALL ON database.* TO 'user'@'localhost';

Remote database Server

sudo apt-get install mysql-server systemctl status mysql mysql_secure_installation sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] bind-address = [database server IP address] [add] require_secure_transport = on

setup ssl

sudo mysql_ssl_rsa_setup --uid=mysql sudo systemctl restart mysql

check that it's listening for external connection

sudo netstat -plunt | grep mysqld

make sure the port is open on the servers

sudo ufw allow mysql

sudo iptables -L -nv --line-numbers sudo iptables -I INPUT 7 -p tcp --dport 3306 -m state

###Performance Tuning

https://github.com/major/MySQLTuner-perl

wget http://mysqltuner.pl/ -O mysqltuner.pl perl mysqltuner.pl --host targetDNS_IP --user admin_user --pass admin_password

avoid swapping

https://mariadb.com/kb/en/mariadb/configuring-swappiness/

notes:​

Migrating Tables to InnoDB​

commands​

Aggregate functions​

Multiple tables​

Users functions​

Remote database Server​

avoid swapping​

MariaDB​