MySQL

mysql binlog files grow too large

set expiry of binlogs to 3 days:

 SET GLOBAL  binlog_expire_logs_seconds = (60*60*24*3);
 SET PERSIST binlog_expire_logs_seconds = (60*60*24*3);

list them and purge them:

 SHOW BINARY LOGS;
 PURGE BINARY LOGS TO 'binlog.000142';

Configure External Access

Don't forget to change bind_ip in /etc/mysql/my.cfg to 0.0.0.0 to allow external connections.

Allow root to login from anywhere. From: http://stackoverflow.com/questions/11223235/mysql-root-access-from-all-hosts

 CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
 
 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
 # better:
 GRANT ALL PRIVILEGES ON microsites3_test.* TO 'tester'@'localhost';
 
 FLUSH PRIVILEGES;

 CREATE USER 'bjjc_prod_user'@'%' IDENTIFIED BY '<some-pass>';
 GRANT ALL PRIVILEGES ON bjjc_wordpress.* TO 'bjjc_prod_user'@'%' WITH GRANT OPTION;
 FLUSH PRIVILEGES;

Show privileges of a user

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;

SHOW GRANTS FOR 'jeffrey'@'localhost';

Create a user

# CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password'; ## may or may not work with php ?
CREATE USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER 'sammy'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

GRANT <PRIVILEGE>, <PRIVILEGE> ON database.table TO 'username'@'host';
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

FLUSH PRIVILEGES;

restart

sudo /usr/local/bin/mysql.server restart

dump/restore

mysql -u username -p database_name < file.sql

Troubleshoot

ActiveRecord::StatementInvalid: Mysql2::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

From: https://stackoverflow.com/questions/1008287/illegal-mix-of-collations-mysql-error

 SET collation_connection = 'utf8_general_ci';
 ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;
 ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Unknown collation: utf8mb4_0900_ai_ci

From: https://tecadmin.net/resolved-unknown-collation-utf8mb4_0900_ai_ci/

Replace the below string:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

with:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

also:

ALTER DATABASE moodle_development DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Mysql. The server requested authentication method unknown to the client [caching_sha2_password]

From: https://github.com/laradock/laradock/issues/1390

alter user 'username'@'localhost' identified with mysql_native_password by 'password';

kill long quaries

SHOW FULL PROCESSLIST;

Control on a mac

 brew services list
 brew services start/stop mysql