If you’re looking for more than 4gb of ram configuration just divide the value with 4 and multiply the result with the amount of RAM, for example : 256/4 = 64 and you want it for 8 gb of ram just multiply it with the result i.e. 64 x 8 = 512

For MariaDB :
Edit the MariaDB configuration file /etc/my.cnf.d/server.cnf using your favorite text editor:

nano /etc/my.cnf.d/server.cnf
Add the following lines under the [mysqld] section:

default-storage-engine = InnoDB
innodb_flush_method = O_DIRECT
innodb_log_file_size = 128M
innodb_buffer_pool_size = 128M
max_allowed_packet = 128M
max_connections = 200
key_buffer_size = 256M
tmp_table_size = 64M
max_heap_table_size = 64M

For MySQL :
Edit the MySQL configuration file /etc/my.cnf using your favorite text editor:

nano /etc/my.cnf

Add the following lines under the [mysqld] section:

default-storage-engine = InnoDB
innodb_flush_method = O_DIRECT
innodb_log_file_size = 128M
innodb_buffer_pool_size = 128M
max_allowed_packet = 128M
max_connections = 200
key_buffer_size = 256M
tmp_table_size = 64M
max_heap_table_size = 64M

After you’ve added this values restart MariaDB/Mysql server :

MariaDB server restart :

systemctl restart mariadb

MySQL server restart :

systemctl restart mysql mysqld

Note: I used this command : mysql -u alemaren_gild -p alemaren_statute < alemaren_english.sql but it's not working and I getting access dined error.

waiting for you help

thanks.

I have a problem uploading a file through phpmyadmin

the sql file size 10mb zip

shows the following error "504 gateway time-out"

I would like to know if you can help me.

thank you

If you’re getting below error then it seems root password is not working or something bad happens to MySQL root user :

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Lets get started with MySQL/mariadb root password reset :-

First stop the MySQL/Mariadb service :

systemctl stop mysqld mariadb mysql
service mysqld stop / service mariadb stop

Then you need to run the below command to start mysql with skip-grant-tables option :

mysqld --skip-grant-tables --user=root &

if upper user with root not work you can use below with mysql

mysqld --skip-grant-tables --user=mysql &

After running the above command press “Enter” Key or "ctrl+c" > to get the shell again

Then you need to run mysql command and then run flush privileges command under MySQL CLI:

mysql
FLUSH PRIVILEGES;

Then at last change the MySQL/Mariadb root password :

ALTER USER 'root'@'localhost' IDENTIFIED BY 'root_passowrd';

Replace “root_password” with some password.

then exit from MySQL cli by typing “quit“

Then run this command to kill all mysql processes :

killall -u mysql

or

pkill -U mysql

Ensure by running this command to unset environments if you accidentally set any :

systemctl unset-environment MYSQLD_OPTS

Now Stop and restart mysql

systemctl stop mysql mysqld mariadb && systemctl start mysql mysqld mariadb
service mysqld stop && service mysqld restart

some time mysql/mariadb process are still running you need to kill them manually you can find the process via top command : top -u mysql

if it is running then you need to kill the pid with kill -9 "pid no." and restart mysql/mariadb

Thats it you’re done with root password reset for MySQL

Check by logging into mysql root.

CWP and for other control panels only :

On control panel like cwp and other panel having /root/.my.cnf ensure you’ve updated the file with new root password which you changed it via upper command

For CWP only update the new password in this files:

Quote

/root/.my.cnf
/usr/local/cwpsrv/htdocs/resources/admin/include/db_conn.php

No worry today I’ll show you easy way to do it via CLI with single command each for repairing and optimizing databases.

To Repair ALL Database you need to simply run this command :

mysqlcheck -r --all-databases

If you want to Repair single database then use this :

mysqlcheck -r user_db

*user_db is the db name, replace with your one.

To Optimize ALL Database you need to simply run this command :

mysqlcheck -o --all-databases

If you want to Optimize single database then use this :

mysqlcheck -o user_db

*user_db is the db name, replace with your one.

Extras :
If you need to specify username to do the repair and optimize task you can add this pipes :

mysqlcheck -r  -u root -p --all-databases
mysqlcheck -o  -u root -p --all-databases

of for single database :

mysqlcheck -r user_db -u root -p
mysqlcheck -o user_db -u root -p

*this will ask the password for mysql root user

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. 

To Disable MySQL strict mode simply add below config in my.cnf usually in /etc/my.cnf ensure you added it under [mysqld] section see example below in this tutorial :

sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

How to find the my.cnf ? mostly my.cnf is located in this locations :

Quote

/etc/my.cnf 
/etc/mysql/my.cnf 

if there is no my.cnf files then create one in this location /etc/my.cnf with below config :

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

[mysqld]
sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d

**please note you need to change the permission if you’ve newly created the my.cnf to user:group i.e. : mysql:mysql

This is extremely useful in high-read, low-write environments (such as most websites). It does not scale well in environments with high throughput on multi-core machines, so it is disabled by default.

Here are the steps to enable query cache in MariaDB:

Check if query cache is enabled:

Before enabling query cache, you should check if it’s already enabled or not. You can do this by logging into your MariaDB server and executing the following command:

SHOW VARIABLES LIKE 'query_cache_type';

example :

type mysql in command line and hit enter it will bring the mysql console and then execute show variable command

[root@server ]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.11.2-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

1 row in set (0.001 sec)

If the value of query_cache_type is OFF, it means that query cache is not enabled.

Enable query cache:

To enable query cache, you need to modify the mariadb configuration file and add the below configs under [mysqld] section.

For CWP/centos file is located in : /etc/my.cnf or /etc/my.cnf.d/server.cnf

For ubuntu/Debian file is located in : /etc/mysql/my.cnf

Open the file in a text editor and add the following lines:

query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M

The query_cache_type variable is set to 1 to enable query cache, query_cache_size specifies the size of the cache in megabytes, and query_cache_limit specifies the maximum size of a single query that can be cached.

Restart MariaDB server:

After modifying the my.cnf configuration file, you need to restart the MariaDB server to apply the changes. You can do this by running the following command:

systemctl restart mariadb

Verify query cache is enabled:

To verify that query cache is enabled, you can log into the MariaDB server and execute the SHOW VARIABLES LIKE 'query_cache_type'; command again. If the value of query_cache_type is now ON, it means that query cache has been enabled.

By following these steps, you can enable query cache in MariaDB and improve the performance of your database queries.