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).
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».
MySQL daemon / service
Linux / Debian
The following commands worked with a MariaDB installation. I assume it works equally with mysql.
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 variable.
Start mysql with the --local-infile option
mysql -u rene -prene --local-infile tq84_db
Creating TSV files
A tsv file can be created locally from the command like so:
mariadb -u rene -p db -B -N -e 'select ‥ from ‥' > /mnt/exp/dat.tsv
-B is --batch
-N (--skip-column-names) prevents adding a header line in the tsv.
On the server, a tsv file can be created with
select col_1, col_2 into outfile '/tmp/mydata.tsv' fields terminated by '\t' lines terminated by '\n' from theTable;
See also mariadb-dump.
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_result
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.
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;
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)