Difference between revisions of "MySQL"

From Wasya Wiki
Jump to: navigation, search
 
(18 intermediate revisions by the same user not shown)
Line 1: Line 1:
  
−
Don't forget to change bind_ip in /etc/mysql/my.cfg to 0.0.0.0!
+
 
 +
 
 +
== 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
 
Allow root to login from anywhere. From: http://stackoverflow.com/questions/11223235/mysql-root-access-from-all-hosts
  
 +
<pre>
 
  CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
 
  CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
 +
 
  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
 
  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
 +
# better:
 +
GRANT ALL PRIVILEGES ON microsites3_test.* TO 'tester'@'localhost';
 +
 
  FLUSH PRIVILEGES;
 
  FLUSH PRIVILEGES;
 +
</pre>
 +
 +
  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';
  
−
=== With Docker ===
+
== kill long quaries ==
  
−
From: https://hub.docker.com/_/mysql/
+
SHOW FULL PROCESSLIST;
  
−
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:tag
+
== Control on a mac ==
  
−
where some-mysql is the name you want to assign to your container, my-secret-pw is the password to be set for the MySQL root user and tag is the tag specifying the MySQL version you want. See the list above for relevant tags.
+
  brew services list
 +
  brew services start/stop mysql

Latest revision as of 23:29, 15 October 2024


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
Retrieved from "https://wiki.wasya.co/index.php?title=MySQL&oldid=3163"