I have a database for an app, that is currently using MariaDB with about 220 InnoDB tables. The entire database is about 30GB of data. However when attempting to move it to a new server that has the most recent MariaDB version I find that the database is consuming all available disk space (which is currently 800GB). I know that InnoDB can be expected to use 2-3x the database by size but that is like 26x the size of the database. What could be causing this issue? Below is what my config file looks like. This is the only thing I am running on the database server at this point so it has to be this database causing the issue. Any thoughts on what is going on? [mysqld] datadir=/mysqldata tmpdir=/mysqldata/tmp socket=/var/lib/mysql/mysql.sock log-error=/var/log/mariadb/mariadb.log pid-file=/run/mariadb/mariadb.pid [mysqld] bind-address = x.x.x.x skip_name_resolve back_log = 1024 max_connections = 5000 key_buffer_size = 2048M myisam_sort_buffer_size = 512M myisam_max_sort_file_size = 4096M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 2M table_definition_cache = 8000 table_open_cache = 8000 thread_cache_size = 1024 wait_timeout = 60 interactive_timeout = 60 connect_timeout = 10 tmp_table_size = 768M max_heap_table_size = 768M max_allowed_packet=268435456 #max_seeks_for_key = 1000 group_concat_max_len = 1024 max_length_for_sort_data = 1024 net_buffer_length = 16384 max_connect_errors = 100000 concurrent_insert = 2 read_rnd_buffer_size = 768K bulk_insert_buffer_size = 8M query_cache_limit = 1536K query_cache_size = 50M query_cache_type = 0 query_prealloc_size = 262144 query_alloc_block_size = 65536 range_alloc_block_size = 4096 transaction_alloc_block_size = 8192 transaction_prealloc_size = 4096 default-storage-engine = MyISAM innodb_large_prefix=1 innodb_purge_threads = 4 innodb_file_format = Barracuda innodb_file_per_table = ON innodb_open_files = 2000 innodb_data_file_path= ibdata1:10M:autoextend innodb_buffer_pool_size = 20G innodb_buffer_pool_instances = 15 innodb_log_files_in_group = 2 innodb_log_file_size = 768M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT

Do wait_timeout session clear all running session or only sessions which are in SLEEP mode for specified seconds ?

I am trying to restore a MySQL dump of size around 18GB to another MySQL server, which is running inside a container using this command: mysql -h example.com -u user -p matomo ERROR 1114 (HY000) at line 7238: The table 'piwik_log_link_visit_action' is full Many other small tables are copied successfully, but while coping this table it fails with above error. The size of this table is more than 2GB. Based on different suggestions available on Stack Overflow, I tried each one but nothing worked. I tried adding 'autoextend' to the my.cnf file: innodb_data_file_path=ibdata1:10M:autoextend I also tried to increase the tmp_table_size and heap_table_size by adding following parameter to the my.cnf file: tmp_table_size=2G max_heap_table_size=2G Also, I made sure that the server (from where I am running the dump restore command) has enough space (more than 20GB of storage available). But nothing worked. I tried debugging this more and found that, the docker container where MySQL is running has *overlay* filesystem of size 5GB which starts getting filled and as soon as it fills 100%, I get above error. Volume mounted on the container is of more than 30GB size. I am not sure from where this *overlay* file system is coming in docker. Overlay is something coming from docker I guess, but not sure where I can increase its size. I can't even go inside the overlay directory and keep deleting or freeing the space. Can anyone please help me here. ### my.cnf file

[mysqladmin]
user=user1

[mysqld]
skip_name_resolve
explicit_defaults_for_timestamp
basedir=/opt/bitnami/mariadb
port=3306
tmpdir=/opt/bitnami/mariadb/tmp
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
max_allowed_packet=256MB
bind_address=0.0.0.0
log_error=/opt/bitnami/mariadb/logs/mysqld.log
character_set_server=utf8
collation_server=utf8_general_ci
plugin_dir=/opt/bitnami/mariadb/plugin
innodb_data_file_path=ibdata1:10M:autoextend:max:10G
max_heap_table_size=2G
tmp_table_size=2G

[client]
port=3306
socket=/opt/bitnami/mariadb/tmp/mysql.sock
default_character_set=UTF8
plugin_dir=/opt/bitnami/mariadb/plugin

[manager]
port=3306
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
!include /opt/bitnami/mariadb/conf/bitnami/my_custom.cnf

We recently moved to a bigger server (64G, 16core Amazon EC2) and I was hoping some increased performance. However, there are plenty of slow queries (some even 50ms) but CPU usage remains very very low. Same configuration on previous server (32GB, 8 core) used up to 30-40% of CPU and queries were executed a lot faster. Any tips on how to take better advantage of CPU?

## File updated on 25 Jun 2023
##
[mysqld]

datadir=/media/db
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=512M
tmpdir=/dev/shm/
# Disabling symbolic-links is recommended to prevent assorted security risks
local-infile=0
log-warnings=2
skip-external-locking

symbolic-links=0
skip-name-resolve=1

expire_logs_days    = 1
bind-address        = 127.0.0.1
max_binlog_size         = 200M
log-bin=bin.log
log-bin-index=bin-log.index
binlog_format=row
binlog_cache_size = 100M

thread_stack        = 292K
myisam_sort_buffer_size = 64M
thread_cache_size = 200

myisam-recover         = BACKUP
max_connections        = 300
max_user_connections = 200
table_open_cache       = 10240 
table_definition_cache = 4096
thread_concurrency     = 16
join_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 4M
key_buffer_size = 200M

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 1k
query_cache_size = 1024M


innodb_buffer_pool_size= 20G
tmp_table_size=256M
max_heap_table_size=256M
#Disabling timeouts
wait_timeout=50
interactive_timeout=50
connect_timeout=10
open-files-limit               = 65535
innodb_flush_log_at_trx_commit = 0
### Never remove 3 below - must for utf8mb4 charset
innodb_large_prefix=true
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_log_buffer_size= 2G
read_rnd_buffer_size = 8M 
transaction-isolation = READ-COMMITTED
innodb_lock_wait_timeout = 25 
innodb_write_io_threads = 16 
innodb_sync_spin_loops = 200
innodb_spin_wait_delay = 24
innodb_read_io_threads = 16
innodb_flush_method = O_DIRECT 
innodb_buffer_pool_instances = 20
innodb_autoinc_lock_mode = 2
#slow_query_log = 1
#slow-query_log_file = /var/log/mysql-slow.log
#long_query_time = 0.5

# Recommended in standard MySQL setup

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I'm trying to read a csv file into my SQL database, but I'm unable to do this because of security issues with the secure_file_priv. I went through a lot of posts that have told me to edit my.cnf to allow a folder to be accessed by SQL. Currently, my my.cnf looks like this:

[mysqld]
secure_file_priv = /private/var/lib/mysql-files
pid-file = /usr/local/mysql/data/mysqld.local.pid

But, when I run

VARIABLES LIKE 'secure_file_priv';

I still get a value of NULL. I have also reinstalled mysql as well as restarted my database many times. While my changes in the my.cnf file are saved, there is no effect on the output. I was also wondering if this had anything to do with no specifying a configuration file, but when I specified a path to my my.cnf file, my server would automatically stop after I started it. EDIT: Adding content of the error log here sudo tail -f /usr/local/mysql/data/mysqld.local.err:

2024-09-21T20:38:24.709702Z 0 [System] [MY-015016] [Server] MySQL Server - end.
2024-09-21T20:38:29.338249Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-09-21T20:38:29.487803Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.4.2) starting as process 3726
2024-09-21T20:38:29.490033Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2024-09-21T20:38:29.495667Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-21T20:38:29.575501Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-21T20:38:29.700945Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-09-21T20:38:29.700966Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-09-21T20:38:29.712909Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.4.2'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2024-09-21T20:38:29.712908Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
^C
% sudo tail -f /usr/local/mysql/data/mysqld.local.err
2024-09-21T20:38:24.709702Z 0 [System] [MY-015016] [Server] MySQL Server - end.
2024-09-21T20:38:29.338249Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-09-21T20:38:29.487803Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.4.2) starting as process 3726
2024-09-21T20:38:29.490033Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2024-09-21T20:38:29.495667Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-09-21T20:38:29.575501Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-09-21T20:38:29.700945Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-09-21T20:38:29.700966Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-09-21T20:38:29.712909Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.4.2'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2024-09-21T20:38:29.712908Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock

From what I can tell, I'm not seeing any errors getting logged. But, when I run the following SQL script, I get an error.

LOAD DATA INFILE '/private/var/lib/mysql-files/table.csv'
INTO TABLE Patients
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

ERROR: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Some of the MySQL clusters I manage have the following my.cnf settings:

log_bin   = mysql-bin.log
relay_log = mysql-relay-bin.log

For the log_bin setting the .log extension is not needed because MySQL ignores and removes the extension from the file name. According to the binary-log documentation: > If you supply an extension in the log name (for example, --log-bin=base_name.extension), the extension is silently removed and ignored. I noticed that the relay_log setting behaves the same with extensions. MySQL also ignores and removes the extension but it seems this behaviour is (currently) not present in the replication-options documentation. When showing these variables in MySQL there is also a small difference. The log_bin value shows ON (instead of the file name) and the relay_log value shows the actual file name:

mysql> SHOW GLOBAL VARIABLES LIKE 'log_bin%';

| log_bin             | ON
| log_bin_basename    | /var/lib/mysql/mysql-bin

mysql> SHOW GLOBAL VARIABLES LIKE 'relay_log%';

| relay_log           | mysql-relay-bin.log
| relay_log_basename  | /var/lib/mysql/mysql-relay-bin

My questions are: 1. Why are the log_bin and relay_log values different (ON vs File Name)? 2. Will relay_log always behave the same as log_bin with extensions (is this by design)? If this is by design then the .log extensions could be removed in my.cnf for both settings:

log_bin   = mysql-bin
relay_log = mysql-relay-bin

I want to know if there is any way to update server_uuid; I am trying master-slave replication, and I need different server_uuid. FYI - I don't have server_uuid in my my.cnf file, and when I update it like this:

> SET GLOBAL server_uuid = '1';

I get the following error:

ERROR 1238 (HY000): Variable 'server_uuid' is a read only variable

I have two servers which differ in mysql version - Server 1 runs with mysql 5.7 2 cores Intel(R) Xeon(R) Gold 6248R CPU @ 3.00GHz and 6GB RAM - Server 2 runs with mysql 8.0 4cores CPU Intel(R) Xeon(R) Gold 6128 CPU @ 3.40GHz and 24GB RAM, since this is an **mysql only** server. Server 1 runs in production, while server 2 is currently my development server. I use a software, which sends e.g ~11k selects on my products table, which currently holds round about 150k products (so its not that huge). My problem now is that **server 1** is done with the same task (same data) after 2mins, while **server 2** needs 18mins. When I execute a single example select SELECT link FROM products WHERE concat(sku, lieferanten_id) = '130928OR1'; MySQL on **server 1** retuns an answer in 0.05 s, **server 2** in 0.06s If I repeat the same select **server 1** is done after 0.00s, while **server 2** still needs the exact same amount of 0.06s. Below is my /etc/mysql/my.cnf

#Max MysQL memory: 662.8M

[mysqld]
user                           = mysql
pid-file                       = /var/run/mysqld/mysqld.pid
socket                         = /var/run/mysqld/mysqld.sock
port                           = 3306
basedir                        = /usr
#datadir                        = /var/lib/mysql
tmpdir                         = /tmp
lc-messages-dir                = /usr/share/mysql
skip-external-locking

# GENERAL #
default-storage-engine         = InnoDB

# NETWORK #
max_allowed_packet             = 999M
max_connect_errors             = 100000
bind-address                   = 0.0.0.0
skip-name-resolve
skip-external-locking

# DATA STORAGE #
datadir                        = /var/lib/mysql

# CACHES AND LIMITS #
tmp-table-size                 = 1024M
max-heap-table-size            = 1024M
max_connections                = 100
thread-cache-size              = 64
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4000
group_concat_max_len           = 320000
thread_stack                   = 1M

# TIMEOUTS #
interactive_timeout            = 300
wait_timeout                   = 360

# INNODB #
innodb-flush-method            = fsync
innodb-flush-neighbors         = 1
# innodb-log-files-in-group      = 2
innodb_redo_log_capacity       = 67108864
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-undo-log-truncate       = ON
innodb-rollback-segments       = 128
innodb_buffer_pool_size        = 18432M
innodb_thread_concurrency      = 4

# LOGGING #
log-error                      = /var/log/mysql/error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 0
slow-query-log-file            = /var/lib/mysql/mysql-slow.log

# CHARACTER AND COLLATION #
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
explicit_defaults_for_timestamp = 1

Are there any values I totally tuned in the wrong way? Or is my **server 1** just "faster" because of MySQL 5.7, which uses query-caching? If there is something I should also provide, please let me know thanks in advance! Edit: my pastebin link contains output of **mysql-tuner** and SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; from my **server 2** https://pastebin.com/yBScuLLZ

I have changed the location of the socket file in my.cnf, however, when I try to login to mysql, I get the error "Can't connect to local MySQL server through socket 'prev/sock'" (I am using Prev/sock to denote that it is still looking for the previous location/default location). I have added this parameter in my.cnf file socket=/opt/data/sockpid/mysql.sock I am using [mysqld] in my.cnf, not [client] when I try using the command: mysql -u root -p -S /opt/data/sockpid/mysql.sock - this bit is working The parameter is not being read or not working correctly...

**EDIT:** User @mustaccio let me acknowledge that I must present the problem that motivates the need for the tuning: ---------- There are querys running faster in an older server, less powerful, MySQL Version 5.7.42 It's taking **three times** less time. The following is a very simplified example of one of these: QUERY:

INSERT INTO 
	mydatabase.mytable
SET 
	CODMED=56304,
	CODCLOS='82618',
	CONTACTOID=51482,
	TRIM_ALT_GRAL=8,
	TRIM_ANT_GRAL=10,
	TRIM_ACT_GRAL=6,
	TRIM_ACT_RMM=2,
	OS_TO=101,
	OS_PAF=49.5,
	OS_OS=51,
	OS_OS_F=1,
	OS_PA_F=1;

TABLE DEFINITION:

CREATE TABLE mytable (
  CODIGO int NOT NULL,
  CODCLOS varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
  CODMED int NOT NULL,
  CONTACTOID varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL COMMENT 'id de contacto',
  VALOR2 varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  LINEASXMERCADOS_COMPLETO json DEFAULT NULL,
  LINEASXMERCADOS json DEFAULT NULL,
  LINEASXMERCADOS_M5G varchar(5000) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '{}' COMMENT 'VALOR4LM',
  OBRASSOCIALES varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '{}',
  OS_OS int NOT NULL,
  OS_PR int DEFAULT NULL,
  OS_PA int DEFAULT NULL COMMENT 'PAM',
  OS_PAF int NOT NULL,
  OS_X int DEFAULT NULL COMMENT 'varios',
  OS_TO int NOT NULL,
  OS_PR_F int NOT NULL DEFAULT '0',
  OS_OS_F int NOT NULL DEFAULT '0',
  OS_PA_F varchar(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '0',
  TRIM_ALT_GRAL int NOT NULL,
  TRIM_ALT_RMM int DEFAULT NULL,
  TRIM_ANT_GRAL int NOT NULL,
  TRIM_ANT_RMM int DEFAULT NULL,
  TRIM_ACT_GRAL int NOT NULL,
  TRIM_ACT_RMM int NOT NULL,
  EST_GRAL varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  EST_RMM varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  STAMP datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

ALTER TABLE mytable
  ADD PRIMARY KEY (CODIGO),
  ADD UNIQUE KEY CODMED_2 (CODMED),
  ADD KEY TRIM_ACT_GRAL (TRIM_ACT_GRAL),
  ADD KEY CONTACTOID (CONTACTOID),
  ADD KEY CODCLOS (CODCLOS),
  ADD KEY TRIM_ACT_RMM (TRIM_ACT_RMM),
  ADD KEY LINEASXMERCADOS_M5G (LINEASXMERCADOS_M5G(333));

ALTER TABLE mytable
  MODIFY CODIGO int NOT NULL AUTO_INCREMENT;

---------- I'm working with a dedicated server: - **RAM** 32 gb - **CPU** Intel Xeon D-2141I @2.20GHz (it says "Total processors: 16", Speed2200.000 MHz, Cache 16384 KB) 20 Databases in total: - Each one has 250 tables. - From those 250, one as a size of around 700MB and there are about 4 of a size around 300MB. Then the rest is below 80MB - Heavier database is 100 GB - The heavier table from it (as an exception) is 4.5 GB, followed by 10 of about 2.5GB, and around 40 of 1.2GB of size. This is my **my.cnf** configuration:

[mysqld]
disable-log-bin=1
default-authentication-plugin=mysql_native_password
performance-schema=1
innodb_file_per_table=1 # Ensure that each innodb table is it's own binary data block just in case there's corruption.
# query_cache_size=64M
thread_cache_size=4
key_buffer_size=32M
max_allowed_packet=268435456
table_open_cache=512 # max 2048, can be increased if more Opened tables - SHOW STATUS LIKE 'Opened_tables';
wait_timeout=28800
max_user_connections=25
open_files_limit=40000
 
#delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
#delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts
 
myisam_sort_buffer_size=2M # can be increased per sessions if needed for alter tables (indexes, repair)
 
#query_cache_limit=2M # leave at default unless there is a good reason
#join_buffer=2M # leave at default unless there is a good reason
#sort_buffer_size=2M # leave at default unless there is a good reason
#read_rnd_buffer_size=256K # leave at default unless there is a good reason
#read_buffer_size=2M # leave at default unless there is a good reason
 
collation_server=utf8_unicode_ci
character_set_server=utf8
 
#innodb_flush_method=O_DSYNC # Do not enable this without a good reason - it doesn't work well on new dedis
innodb_buffer_pool_size=4G
innodb_thread_concurrency=16 # Number of physical + virtual CPU's, be careful of adding more

tmp_table_size=768M
max_heap_table_size=768M
max_connections=150
 
table_definition_cache=512 # increase by the same factor as table_open_cache
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=64M
 
general_log=0
slow_query_log=0
log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;
long_query_time=20
 
innodb_io_capacity=100
innodb_buffer_pool_instances=4
mysqlx=0
sql_mode="NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES"
innodb_log_file_size=768M

Those are the recommendations from **mysqltuner:**

>>  MySQLTuner 2.1.1
	 * Jean-Marie Renouard 
	 * Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/ 
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
 
[OK] Currently running supported MySQL version 8.0.32
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql.log exists
[--] Log file: /var/log/mysql.log (0B)
[--] Log file /var/log/mysql.log is empty. Assuming log-rotation. Use --server-log={file} for explicit file
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM -ndbcluster -ndbinfo +PERFORMANCE_SCHEMA 
[--] Data in MyISAM tables: 108.0G (Tables: 4194)
[--] Data in InnoDB tables: 5.9G (Tables: 2269)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 4h 29m 19s (13M q [854.998 qps], 20K conn, TX: 8G, RX: 6G)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 29.3G
[--] Max MySQL memory    : 42.8G
[--] Other process memory: 0B
[--] Total buffers: 4.8G global + 257.9M per thread (150 max threads)
[--] Performance_schema Max memory usage: 181M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 7.3G (24.88% of installed RAM)
[!!] Maximum possible memory usage: 42.8G (146.11% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (4/13M)
[OK] Highest usage of available connections: 6% (9/150)
[OK] Aborted connections: 0.10% (20/20208)
[!!] CPanel and Flex system skip-name-resolve should be on
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 4M sorts)
[!!] Joins performed without indexes: 4818
[OK] Temporary tables created on disk: 0% (0 on disk / 60K total)
[OK] Thread cache hit rate: 99% (187 created / 20K connections)
[OK] Table cache hit rate: 99% (23M hits / 23M requests)
[!!] table_definition_cache (512) is less than number of tables (6795) 
[OK] Open file limit used: 1% (423/40K)
[OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 181.6M
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled since MySQL 8.0.
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 16
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 4.0G / 5.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5%): 768.0M * 2 / 4.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 4
[--] Number of InnoDB Buffer Pool Chunk: 32 for 4 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (393143981 hits / 393168510 total)
[!!] InnoDB Write Log efficiency: 58.44% (506487 hits / 866740 total)
[OK] InnoDB log waits: 0.00% (0 waits / 360253 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    name resolution is enabled due to cPanel doesn't support this disabled.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
    Buffer Key MyISAM set to 0, no MyISAM table detected
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU 
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    long_query_time ( 256.0K, or always use indexes with JOINs)
    table_definition_cache (512) > 6795 or -1 (autosizing if supported)
    key_buffer_size=0
    innodb_buffer_pool_size (>= 5.9G) if possible.
    innodb_log_file_size should be (=512M) if possible, so InnoDB total log file size equals 25% of buffer pool size.

These are the variables from PHPMyAdmin that are coloured red, signaling there are alerts (MySQL was started within the last 24 hours according the mysqlturner, because I restarted the MySQL Service):

Handler read rnd: 12 M
Handler read rnd next: 1.7 G
Innodb buffer pool reads: 24.5 k
Key reads: 44 M
Opened tables: 213.1 k
Select full join: 5 k
Select range check: 23
Sort merge passes: 1.5 k
Table locks waited: 1.1 k

Those are tips I read from anothers posts while I was investigating: First one: how-large-should-be-mysql-innodb-buffer-pool-size from user @RolandoMySQLDBA > "This will give you the RIBPS, Recommended InnoDB Buffer Pool Size, > based on all InnoDB Data and Indexes, with an additional 60%.".

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A

**My result is 10** He also said: > This will give you how many actual GB of memory is in use by InnoDB > Data in the InnoDB Buffer Pool at this moment.

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

My result is **12.84** And he also made another formula for the InnoDB Buffer Pool Size:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1.25 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

My result is **9G** There is still another recomendation from the user @max and @RickJames

SELECT
    'Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests' AS Expression,
    'Read requests that had to hit disk' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    ) * 100, 2) AS Val,
    '0-2%' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_pages_read / Innodb_buffer_pool_read_requests' AS Expression,
    'Read requests that had to hit disk' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_pages_read'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    ) * 100, 2) AS Val,
    '0-2%' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_pages_written / Innodb_buffer_pool_write_requests' AS Expression,
    'Write requests that had to hit disk' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_pages_written'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_write_requests'
    ) * 100, 2) AS Val,
    '0-15%' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_buffer_pool_reads / Uptime' AS Expression,
    'Reads' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Uptime'
    )) AS Val,
    '0-100/sec' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    '(Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed)  / Uptime' AS Expression,
    'InnoDB I/O' AS Meaning,
    ROUND(((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    )
    +
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_pages_flushed'
    ))
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Uptime'
    )) AS Val,
    '0-100/sec' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_buffer_pool_pages_flushed / Uptime' AS Expression,
    'Writes (flushes)' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_pages_flushed'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Uptime'
    )) AS Val,
    '0-100/sec' AS Recommend,
    'Increase innodb_buffer_pool_size if you have enough RAM.' AS WhatToDoIfOutOfRange
UNION
SELECT
    'Innodb_buffer_pool_wait_free / Uptime' AS Expression,
    'Counter for when there are no free pages in buffer_pool. That is, all pages are dirty.' AS Meaning,
    ROUND((SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Innodb_buffer_pool_wait_free'
    )
    /
    (SELECT
        VARIABLE_VALUE
    FROM
        performance_schema.global_status
    WHERE
        VARIABLE_NAME = 'Uptime'
    )) AS Val,
    '0-1/sec' AS Recommend,
    'First be sure innodb_buffer_pool_size is set reasonably; if still trouble, decrease innodb_max_dirty_pages_pct' AS WhatToDoIfOutOfRange

Those are my results: And the other post is from user @Abdul Manaf and the answer from @RolandoMySQLDBA what-to-set-innodb-buffer-pool-and-why He said > What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server.

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

**My result is 12.85** ---------- According to what has been posted, it may seems that the way is more or less clear.. but I would like your help, especially since I don't have a development server and I can only do modifications on the production server, I'm worried about causing harm. Thanks!

## Problem ## We have a large database (MariaDB 10.3) and mysqldump takes 2-3 hours to complete a full database dump, and it's also causing downgrade in application performance during the dump process. ## Attempt so far ## After some research, we found out that the [mysqldump] section can be added in the my.cnf file. Also options like quick , max_allowed_packet = 512M can be added. If we can add single-transaction also along with the above settings, then the database will not be locked during the dump and it should speed up the application. So are these entries appropriate for my.cnf?

[mysqldump]
single-transaction
quick
max_allowed_packet = 512M

What would be the most optimized mariadb settings for my server below? E5-2699 V4 CPU(8 Core)- 16GB DDR4 - 100GB NVME SSD Current [mysqld] performance-schema=ON slow_query_log = 1 query_cache_size = 32M innodb_additional_mem_pool_size = 10G innodb_buffer_pool_size = 10G log-error=/var/log/mysqld.log max_allowed_packet=256M open_files_limit=40000 table_open_cache=40000 innodb_use_native_aio = 0 disable-log-bin = 1 datadir = /var/lib/mysql max_connect_errors = 1000000 event_scheduler = off collation-server = utf8_unicode_ci character-set-server = utf8 default_storage_engine = InnoDB innodb_buffer_pool_size = 6G # Use up to 70-80% of RAM innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_log_file_size = 1G innodb_stats_on_metadata = 0 innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # innodb_large_prefix=1 innodb_read_io_threads = 64 innodb_write_io_threads = 64 low_priority_updates = 1 concurrent_insert = 2 back_log = 512 thread_cache_size = 100 thread_stack = 192K innodb_sort_buffer_size = 4M join_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 4M sort_buffer_size = 4M myisam_max_sort_file_size = 2M max_heap_table_size = 2G tmp_table_size = 2G innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" unix_socket=OFF What I want is to be able to capture the most optimized settings for my server. Can you help me?

I am pretty new with Linux, so please be patient :) Ran into database connection problem and decided to reinstall/update mysql Now I get the following error message: Transaction check error: file /etc/my.cnf from install of mysql-community-server-8.0.31-1.el7.x86_64 conflicts with file from package MariaDB-common-10.2.37-1.el7.centos.x86_64 I tried to rename my.cnf to my.cnf.bak, but it does not work.

What's the best way to provide a MySql root password (~/.my.cnf) to cron job without root to have the home folder? the server I'm using is centos

I'm trying to better understand how MariaDB is configured. From mysqld --help --verbose I can see that my mariadb server daemon uses /etc/mysql/my.cnf. But I'm curious about the **/etc/mysql/mariadb.cnf** file, and what it's intended for? It has almost nothing in it compared to my.cnf. ---------- The package **mysql-common** installs **my.cnf** The package **mariadb-common** installs **mariadb.cnf** ---------- On my Debian Buster system /etc/mysql/my.cnf and /etc/mysql/mariadb.cnf are similar in what they 'include'. One minor difference is that because /etc/mysql/conf.d/mariadb.cnf already includes /etc/mysql/mariadb.conf.d, /etc/mysql/mariadb.cnf seems to include /etc/mysql/mariadb.conf.d twice. my.cnf is full of options, and mariadb.cnf has little to no content, so doesn't appear to be a replacement to my.cnf, at least not yet. ---------- /etc/mysql/my.cnf --!includedir--> conf.d /etc/mysql/conf.d/mariadb.cnf --!includedir--> mariadb.conf.d /etc/mysql/mariadb.conf.d/ ... ---------- /etc/mysql/mariadb.cnf --!includedir--> conf.d AND mariadb.conf.d /etc/mysql/conf.d/mariadb.cnf --!includedir--> mariadb.conf.d /etc/mysql/mariadb.conf.d/ /etc/mysql/mariadb.conf.d/ (duplicate load) ... ----------

I am getting error #2006 - MySQL server has gone away while trying to restore a 11.9 mb database using PhpMyAdmin. It worked with a 4.70 mb database, but not with this one I thought that if I put --max-allowed-packet=500M in command it will work, but still getting the same

mysql:
    command: --max-allowed-packet=500M
    container_name: db_mysql
    environment:
      MYSQL_USER: doadmin
      MYSQL_DATABASE: defaultdb
      MYSQL_PASSWORD: mysqlpass3434
      MYSQL_ROOT_PASSWORD: mysqlrootpass3432523
    image: mysql:8
    networks:
      - internal
    ports:
      - 3306:3306
    restart: unless-stopped
    volumes:
      - /root/wordpress/mysql:/var/lib/mysql

Note: I am using a $12 USD premium Intel Droplet on Digital Ocean

i'm running a facebook app which currently has 300 - 600 concurrent users (and growing). To get the hardware ready for growing i changed my i7 / 12gb ram / 2x 80gb intel x25 ssd's (debian 5.0 / mysql 5.0 / 64bit) into a bi-xeon / 24gb ram / 2x 120gb intel 320 ssd (ubuntu 10.10 / mysql 5.1 / 64bit). now i'm facing the problem that the performance is worse than on the "smaller box". On both servers i've been using nginx / php fcgi to serve the content. i'm using innodb only, having Reads / Writes about 65% / 35%. Around 800 - 1000 qps but all Queries are simple and never join more than 1 additional table. All indexes are set and no individual query is logged in the slow log (> 2s). At the moment i have around 400mb of data (around 1gb with indexes) expecting it to double every month. I'd adore everyone who could give me a hint what to change to make it run smoother. The old configuration on the i7 box was like this (mixed myisam / innodb), performed pretty good up to 800+ users. old my.cnf key_buffer = 3000M max_allowed_packet = 128M thread_stack = 192K thread_cache_size = 8 max_connections = 400 table_cache = 8000 thread_concurrency = 16 query_cache_limit = 8M query_cache_size = 128M wait_timeout = 10 interactive_timeout = 10 connect_timeout = 600 low_priority_updates = 1 join_buffer_size = 8M read_buffer_size = 2M sort_buffer_size = 3M myisam_sort_buffer_size = 32M read_rnd_buffer_size = 4M innodb_buffer_pool_size = 3G innodb_log_buffer_size = 8M The new configuration on the bi-xeon box is like this (pure innodb), causing high load with 300+ users. Around 30 mysql processes sitting on the top of the process list. Disk I/O: avg-cpu: %user %nice %system %iowait %steal %idle 36.28 0.00 1.60 0.17 0.00 61.95 my.cnf key_buffer = 64M max_allowed_packet = 1M thread_stack = 192K thread_cache_size = 128 max_connections = 500 table_cache = 512 #thread_concurrency = 10 sort_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K tmp_table_size = 32M max_heap_table_size = 32M query_cache_limit = 1M query_cache_size = 128M query_cache_type = 1 innodb_file_per_table = 1 innodb_data_file_path = ibdata1:1000M:autoextend innodb_buffer_pool_size = 16384M innodb_additional_mem_pool_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_support_xa = 0 innodb_lock_wait_timeout = 50 innodb_flush_method=O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 128M innodb_log_buffer_size = 8M innodb_thread_concurrency = 12

I want enable lowercase table names so i have added to etc/mysql/my.cnf also etc/mysql/mariadb.cnf

lower_case_table_name = 1

restart the service sudo service mysql restart I can acces service from phpmyadmin, but I can't access it from cli

$ mysql -u root -p
mysql: unknown variable 'lower_case_table_names=1'

how to enabled lowercase table name correctly? environment - OS: ubuntu 20.04 - Mariadb: 10.5.9-MariaDB-1:10.5.9+maria~focal **updated:** my.cnf

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/ 

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock
 lower_case_table_names = 1

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

mariadb.cnf

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/ 

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock
 lower_case_table_names = 1

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

I've looked at a lot answers here https://dba.stackexchange.com/questions/83125/mysql-any-way-to-import-a-huge-32-gb-sql-dump-faster and on Server Fault but haven't found a solution to not being able to import a 16 GB MySQL file completely; it silently fails at different parts of the import. Sometimes 2 GB will import before the import stops, sometimes 10 GB. There are no errors in the logs. Sometimes the console returns to #, sometimes not. top shows MySQL running at 100% when importing, but then drops down to normal loads when the import stops. The database is 16 GBs, and has 90% InnoDB tables with a few MyISAM tables. To export the database on the first machine, I'm using mysqldump --single-transaction --lock-tables -u mydatabaseuser -p mydatabase > archive.sql The importing machine has 8 dedicated cores and 150 GB dedicated RAM (server is at Linode) and is running Alma Linux and MySQL 8.0.25. To import, I'm using mysql -u root -p mydatabase ` doesn't help. One table is 12 GB, and I tried importing that table by itself with no luck. Using these in my.cnf throws an error on mysql restart: autocommit=0 unique_checks=0 foreign_key_checks=0 Should I be exporting differently? Is MySQL on the importing machine timing out? What do I need to edit in my.cnf? **my.cnf on the importing machine:** [mysqld] disable-log-bin=1 default-authentication-plugin=mysql_native_password performance-schema=0 port = 3306 socket = /tmp/mysql.sock skip-external-locking skip-name-resolve # uncomment for import bulk_insert_buffer_size = 40G read_buffer_size = 40G # If I uncomment these, MySQL throws the error on restart # Job for mysqld.service failed because the control process exited with error code" # autocommit=0 # unique_checks=0 # foreign_key_checks=0 innodb_buffer_pool_size=50G innodb_buffer_pool_instances=56 innodb_log_file_size = 8G innodb_log_buffer_size=64M innodb_read_io_threads=8 innodb_write_io_threads=8 innodb_io_capacity=300 innodb_doublewrite = 0 max_allowed_packet=268435456 open_files_limit=40000 innodb_file_per_table=1 join_buffer_size=128M sort_buffer_size=2M read_rnd_buffer_size=2M key_buffer_size = 20M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid **Edit 7/28/21** > mysql> SHOW GLOBAL VARIABLES LIKE '%timeout%'; > > connect_timeout 10 > delayed_insert_timeout 300 > have_statement_timeout YES > innodb_flush_log_at_timeout 1 > innodb_lock_wait_timeout 50 > innodb_rollback_on_timeout OFF > interactive_timeout 28800 > lock_wait_timeout 31536000 > mysqlx_connect_timeout 30 > mysqlx_idle_worker_thread_timeout 60 > mysqlx_interactive_timeout 28800 > mysqlx_port_open_timeout 0 > mysqlx_read_timeout 30 > mysqlx_wait_timeout 28800 > mysqlx_write_timeout 60 > net_read_timeout 30 > net_write_timeout 60 > replica_net_timeout 60 > rpl_stop_replica_timeout 31536000 > rpl_stop_slave_timeout 31536000 > slave_net_timeout 60 > wait_timeout 6000 > > mysql> SHOW SESSION VARIABLES LIKE '%timeout%'; > > connect_timeout 10 > delayed_insert_timeout 300 > have_statement_timeout YES > innodb_flush_log_at_timeout 1 > innodb_lock_wait_timeout 50 > innodb_rollback_on_timeout OFF > interactive_timeout 28800 > lock_wait_timeout 31536000 > mysqlx_connect_timeout 30 > mysqlx_idle_worker_thread_timeout 60 > mysqlx_interactive_timeout 28800 > mysqlx_port_open_timeout 0 > mysqlx_read_timeout 30 > mysqlx_wait_timeout 28800 > mysqlx_write_timeout 60 > net_read_timeout 30 > net_write_timeout 60 > replica_net_timeout 60 > rpl_stop_replica_timeout 31536000 > rpl_stop_slave_timeout 31536000 > slave_net_timeout 60 > wait_timeout 28800

I am using mariadb and I to investigate some issue I wanted to check the logs. To my surprise, log file is not generated for mariadb. I suspect this cannot be the case so I am doubting my search skills. MariaDB [(none)]> show variables like 'log_error' -> ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_error | | +---------------+-------+ 1 row in set (0.00 sec) I have added the entry in my.cnf still above field is coming to be empty. [root@cslcodev11-oem ~]# cat /etc/my.cnf [mysqld] !includedir /etc/mysqld/conf.d datadir=/mnt/mgmt/var/lib/mysql socket=/var/lib/mysql/mysql.sock tmpdir=/mnt/mgmt/var/lib/mysql_tmp user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 ### TRT-3229 ##### sync_binlog=1 innodb_flush_method=O_DIRECT innodb_support_xa = 1 myisam_repair_threads = 2 myisam_recover_options = FORCE ################### innodb_file_per_table=1 innodb_log_buffer_size = 8M table_open_cache=256 max_heap_table_size=256M ### TRT-4685 ### max_connections=500 ################ innodb_log_file_size = 512M [mysqld_safe] log-error=/var/log/mariadb/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [root@cslcodev11-oem ~]# So, I want to know do we have any default location where these logs are getting generated, if the path cannot be read from config files. ## Update: ## After some investigation, I found this information, *Reference: https://mariadb.com/kb/en/mariadb/error-log/* ---------- **Activating the Error Log** The error log is active by default. The log-error=filename option determines where the output will be written. If no file name is specified, the log will be written to host-name.err. If no absolute path is specified, the file will be written to the data directory (determined by the value of the datadir system variable). *On Unix systems, if the --log-error option is not used, the errors are written to stderr (usually, the command line).* *On Windows, if the --console option is specified, and --log-error is not used, the errors are written to the console. If --log-error is present, --console is ignored.* So now I understand that due to some reason the configuration **log_error** in my.cnf is not considered. So as per the suggestion, on Unix system, if --log-error is not used, logs are redirected to stdout. ---------- BUT, I am starting the service using service mysqld start command so I want to know what all options are used by default when we start the service using service mysql start? ESP, is --log-error used?