DB/Mysql - Revision history

Pio2pio: Pio2pio moved page Mysql to DB/Mysql without leaving a redirect

2022-03-11T15:45:59Z

Pio2pio moved page Mysql to DB/Mysql without leaving a redirect

← Older revision	Revision as of 17:45, 11 March 2022
(No difference)

2021-10-29T09:59:46Z

Setting up wso2 to use SSL when connecting to MySQL DB

← Older revision		Revision as of 11:59, 29 October 2021
Line 326:		Line 326:

	== Setting up wso2 to use SSL when connecting to MySQL DB ==		== Setting up wso2 to use SSL when connecting to MySQL DB ==
			* [https://medium.com/querypie/mysql-ssl-connection-using-jdbc-bee3bcb59029 mysql-ssl-connection-using-jdbc]


	Import client and server certificates to the client-truststore of WSO2 server. In case of using AWS you need to import <tt>rds-ca-2019-root</tt>.		Import client and server certificates to the client-truststore of WSO2 server. In case of using AWS you need to import <tt>rds-ca-2019-root</tt>.
	<source lang=bash>		<source lang=bash>

2021-10-04T13:48:42Z

Watch database size

← Older revision		Revision as of 15:48, 4 October 2021
Line 81:		Line 81:
	</source>		</source>

	= Watch database size =		== Watch database size ==
	This is useful when loading data to a database.		This is useful when loading data to a database.
	<source lang=bash>		<source lang=bash>

2021-10-04T13:48:28Z

Check size of all tables

@@ Line 79: / Line 79: @@
 FROM information_schema.TABLES
 ORDER BY (data_length + index_length) DESC;
 </source>

2021-08-02T10:02:25Z

Grant a user privileges per tables and column

← Older revision		Revision as of 12:02, 2 August 2021
Line 138:		Line 138:
	SELECT * FROM mysql.columns_priv where user like '%praveen%'; -- show columns privileges table		SELECT * FROM mysql.columns_priv where user like '%praveen%'; -- show columns privileges table

	# ~~Limit~~ to a specific table only		# Grant permission to a specific table only
	GRANT select ON mysql.general_log TO 'newuser'@'%';		GRANT select ON mysql.general_log TO 'newuser'@'%';
	GRANT type_of_permission ON db_name.table_name TO 'newuser'@'localhost';		GRANT type_of_permission ON db_name.table_name TO 'newuser'@'localhost';

	# Revoke ~~if needed~~		# Grant permission to a specific column only, eg. grant `UPDATE` permission
			GRANT UPDATE (col) ON `<db_name>`.<table_name> TO '<user>'@'%';

			# Revoke table permission
	REVOKE type_of_permission ON db_name.table_name FROM 'newuser'@'localhost';		REVOKE type_of_permission ON db_name.table_name FROM 'newuser'@'localhost';

2021-08-02T09:01:17Z

Grant a user privileges per tables

← Older revision		Revision as of 11:01, 2 August 2021
Line 127:		Line 127:
	</source>		</source>

	== Grant a user privileges per tables ==		== Grant a user privileges per tables and column ==
	<syntaxhighlightjs lang="sql">		<syntaxhighlightjs lang="sql">
	# MySQL 5.6		# MySQL 5.6
Line 134:		Line 134:
	# MySQL 5.7		# MySQL 5.7
	SHOW GRANTS username;		SHOW GRANTS username;
			SELECT * FROM mysql.user; -- show users table and their privilages
			SELECT * FROM mysql.tables_priv WHERE user LIKE '%prav%'; -- show table privileges table
			SELECT * FROM mysql.columns_priv where user like '%praveen%'; -- show columns privileges table

	# Limit to a specific table only		# Limit to a specific table only

2021-06-17T16:32:12Z

Grant a user privileges per tables

← Older revision		Revision as of 18:32, 17 June 2021
Line 144:		Line 144:
	# Once finalised the permissions for the users, always reload/save all the privileges		# Once finalised the permissions for the users, always reload/save all the privileges
	FLUSH PRIVILEGES;		FLUSH PRIVILEGES;

			-- RO
			GRANT SELECT, SHOW VIEW ON ...
			-- RW
			GRANT SELECT, SHOW VIEW, INSERT, UPDATE, DELETE ON ...
	</syntaxhighlightjs>		</syntaxhighlightjs>

2021-06-17T16:14:09Z

Create or delete a user

← Older revision		Revision as of 18:14, 17 June 2021
Line 99:		Line 99:
	== Create or delete a user ==		== Create or delete a user ==
	{{note\|If you use MySQL Workbench to see effect of the statements below, make sure to close and reopen the tab as the current state is read only on opening the tab.}}		{{note\|If you use MySQL Workbench to see effect of the statements below, make sure to close and reopen the tab as the current state is read only on opening the tab.}}


			Show users
			<source lang="sql">
			SELECT * FROM mysql.user;
			</source>

2021-05-12T08:09:01Z

SSL connections

← Older revision		Revision as of 10:09, 12 May 2021
Line 236:		Line 236:
	</source>		</source>

	= SSL connections =		= SSL/TLS connections =
			{{Note\|MySQL doesn't use raw TLS on the wire for its encrypted connections. Instead, it performs a protocol handshake in the MySQL layer to enable TLS before it starts to be used. That's similar to FTP or SMTP which use AUTH TLS and STARTTLS respectively to enable TLS, and that's what the -starttls argument in the s_client command does. curl doesn't support the MySQL protocol so it can't
			do this. }}


	RDS MySQL can use SSL to encrypt connection.		RDS MySQL can use SSL to encrypt connection.
	More info read here:		More info read here:

2021-04-13T06:36:08Z

Kill a process

← Older revision		Revision as of 08:36, 13 April 2021
Line 32:		Line 32:
	In RDS you don't have access to all commands thus there is specific procedure to be called in:		In RDS you don't have access to all commands thus there is specific procedure to be called in:
	<source lang="sql">		<source lang="sql">
	kill 38 #Mysql server		kill 38 # Mysql server
	CALL mysql.rds_kill(38) #RDS instance		CALL mysql.rds_kill(38) # RDS instance
			CALL mysql.rds_kill_query($PROCESS_ID) # Aurora
	</source>		</source>