select
user(),
current_user(), -- the account that the server used to authenticate a user
session_user(),
system_user()
set global innodb_file_per_table=1;
If the innodb_file_per_table option is set, a table can be (or is?) created in its own *.ibd* file rather than in a shared ibdata file of the system tablespace.
This option is enabled by default.
An instance is a mysqld daemon in Unix or a service in Windows. The instance listens on its designated TCP port (the default being 3306).
An instance manages a data directory (that in turn consists of one ore more databases).
The default name for the Windows instance service when installing MySQL 8 is MySQL80.
The mybinsqllog command examines the content of bin logs (binary log files).
The local_infile option controls if the server permits local data loading (load data local).
load data statements without character set clause default to using the value of the character_set_database system varible.
Start mysql with the --local-infile option
mysql -u rene -prene --local-infile tq84_db
Character sets and collations
The reportoire of a character set is the collection of characters in the set.
MySQL Server has a server character set and a server collation which are set at startup (options --character-set-server=… and --collation-server=…).
Similarly, a database has a database character set and a database collation.
Similarly, every character string has a character set and a collation
System variables character_set_database, character_set_connection, collation_connection.
utf8 is an alias for the utf8mb3 character set and stores a maximum of three bytes per character. It allows to represent the code points0x0000 through 0xffff, thus, it cannot store all Unicode characters. utf8mb3 is deprecated and will be removed in a future version of MySQL, probably in favor of utf8mb4.
utf8mb4 is what most of the world would call utf-8. It uses up to four bytes per character and thus allows to store supplemental characters (such as emoji).
show character set;
show character set like 'utf8%'
show collation where charset = 'utf8mb4';
select * from information_schema.character_sets order by character_set_name;
select * from information_schema.collations where character_set_name = 'utf8mb4' order by collation_name;
set names specifies the character set of the communication between the server and the client.
set names ascii;
set names utf8;
set names cp1251;
Show variables with character set in their names
show variables like '%character%';
Variable_name Value
---------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
character_set_client utf8
character_set_connection utf8
character_set_database utf8mb4
character_set_filesystem binary
character_set_results
character_set_server utf8
character_set_system utf8
character_sets_dir C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\
8 rows selected.
National character set
nvarchar(10) character set utf8 = national varchar(10) == nvarchar(10) == nchar varchar(10) == national character varying(10) == national char varying(10).
char(10) character set utf8 == national character(10) == nchar(10).
N'foo bar' == n'foo bar' == _utf8'foo bar'.
create database db_xyz
character set utf8mb4
collate utf8mb4_0900_as_cs -- Accent and case sensitive.
;
System variables
show variables;
show variables like '%some_text%';
Changing (global?) system variables. However, it does not seem to survive a bounce.
set global local_infile = on;
Security
Is mysql -u root possible?
show grants;
show grants for 'rene'@'somewhere.net';
In order to change the installation path when installing on Windows, MySQL in their finite wisdom has chosen to let a user do that by clicking an inconspicuous* link named «advanced option».
select * from information_schema.engines where engine = 'PERFORMANCE_SCHEMA';
-- Note the lower case table schema and lower case table names:
select * from information_schema.tables where table_schema = 'performance_schema';
Configure and show *monitoring characteristics with setup tables:
select * from performance_schema.setup_actors;
select * from performance_schema.setup_consumers;
select * from performance_schema.setup_instruments;
select * from performance_schema.setup_objects;
select * from performance_schema.setup_threads;
update performance_schema.setup_… set enabled = 'NO' where name = '…';
select * from performance_schema.events_waits_history where thread_id = 13 order by event_id;
select * from performance_schema.events_waits_summary_global_by_event_name;
Processes
The processlist seems to show connected users and what SQL statement they're currently executing (column info):
show processlist;
select * from information_schema.processlist;
select
id,
user,
db,
time,
state,
info as sql_stmt
from
information_schema.processlist
where
command = 'Query';
Parallel execution
show variables like '%innodb_parallel_read_threads%';
set local innodb_parallel_read_threads=32;
Spatial data
An SRSID identifies a spatial reference system.
select
srs_id,
srs_name,
organization,
organization_coordsys_id,
definition,
description
from
information_schema.st_spatial_reference_systems;
select * from information_schema.st_spatial_reference_systems where srs_id in (
0 , -- (cartesian projection)
4326, -- WGS 84 (= GPS coordinates)
3857 -- WGS 84 / Pseudo-Mercator (map projection used on Google Maps, OpenStreetMap etc., but not in paper maps)
);