Lamp script for CentOS 8

Here is a simple lamp install script for CentOS 8.

#update system
dnf update
#install apache
dnf install httpd httpd-tools -y
systemctl enable httpd
systemctl start httpd
systemctl status httpd
#update firewall
firewall-cmd --permanent --zone=public --add-service=http
firewall-cmd --permanent --zone=public --add-service=https
firewall-cmd --reload
#install mariadb
dnf install mariadb-server mariadb -y
systemctl start mariadb
systemctl enable mariadb
systemctl status mariadb
# Install PHP 7 on CentOS 8
dnf install
#dnf install dnf-utils
#dnf module list php
dnf install php php-opcache php-gd php-curl php-mysqlnd -y
systemctl start php-fpm
systemctl enable php-fpm
systemctl status php-fpm
setsebool -P httpd_execmem 1
systemctl restart httpd

Check the installation

# nano /var/www/html/info.php

Insert the PHP code below and save the file.

 phpinfo ();

Then head out to your browser, and type the URL below. Remember to replace the server IP address with your server’s actual IP address.


Import a large sql dump file to a MySQL database from command line

How to import a very large SQL dump file (6 Gb) to a MySQL database using windows command line. If you are using linux it is the same. The process is the following:

Open a command prompt (or shell in Linux) with administrative privilleges

Connect to a mysql instance using command line:

# mysql -h --port=3306 -u root -p

if you are in localhost you do not need host and port

# mysql -u root -p

Or if plesk,

# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

You are now in mysql shell. Set network buffer length to a large byte number. The default value may throw errors for such large data files

mysql> set global net_buffer_length=1000000;

Set maximum allowed packet size to a large byte number.The default value may throw errors for such large data files.

mysql> set global max_allowed_packet=1000000000;

Disable foreign key checking to avoid delays,errors and unwanted behaviour

mysql> SET foreign_key_checks = 0;
mysql> SET AUTOCOMMIT = 0;

Import your sql dump file

mysql> use db_name;
mysql> source backup-file.sql;

Remember to enable foreign key checks when procedure is complete!

 mysql> SET foreign_key_checks = 1;
 mysql> SET UNIQUE_CHECKS = 1;
 mysql> SET AUTOCOMMIT = 1;

If you are in Linux you can create a Bash script which will do the dirty job and write to stdout start and end time of import:


  # store start date to a variable

  echo "Import started: OK"

  ddl="set names utf8; "
  ddl="$ddl set global net_buffer_length=1000000;"
  ddl="$ddl set global max_allowed_packet=1000000000; "
  ddl="$ddl SET foreign_key_checks = 0; "
  ddl="$ddl SET UNIQUE_CHECKS = 0; "
  ddl="$ddl SET AUTOCOMMIT = 0; "
  # if your dump file does not create a database, select one
  ddl="$ddl USE jetdb; "
  ddl="$ddl source $dumpfile; "
  ddl="$ddl SET foreign_key_checks = 1; "
  ddl="$ddl SET UNIQUE_CHECKS = 1; "
  ddl="$ddl SET AUTOCOMMIT = 1; "
  ddl="$ddl COMMIT ; "

  echo "Import started: OK"

  time mysql -h -u root -proot -e "$ddl"

  # store end date to a variable

  echo "Start import:$imeron"
  echo "End import:$imeron2"

Other solutions:
Big Dump seems good

Split Files

Mariadb default logs location & –log-error

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
!includedir /etc/mysqld/conf.d
# Disabling symbolic-links is recommended to prevent assorted security risks
### TRT-3229 #####
innodb_support_xa = 1
myisam_repair_threads = 2
myisam_recover_options = FORCE
innodb_log_buffer_size = 8M
### TRT-4685 ###
innodb_log_file_size = 512M

[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.

INFO: Reference:

On the MariaDB Knowledge base page for the error-log there is a paragraph that states.

systemd has its own logging system, and Linux distributions running systemd may log errors there instead. To view the systemd logs, use:

# journalctl -u mariadb.

This answers the question because –log-error would be an option that could be added to MY_SPECIAL.conf file and journalctl is where mariadb logs errors by default on a systemd system.

Determine which Mysql configuration file is being used

Here is how to quickly Determine which Mysql configuration file is being used

$ which mysqld

$ /usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"
Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

MySQL shows warning: Using unique option is deprecated and will be removed in a future release

Error when exporting a dump in plesk databases (See:

Warning: Using unique option prefix database instead of databases is deprecated and will be removed in a future release. Please use the full name instead.


key_buffer and myisam-recover are deprecated in Mysql 5.5. myisam-recover option is renamed as of MySQL 5.5.3 to myisam-recover-options .

1. Log in to Plesk server via SSH

2. Correct /etc/my.cnf or /etc/mysql/my.cnf as below:
Change pass to password
See –

Disable ONLY_FULL_GROUP_BY in Mysql on Ubuntu 16

Recently I ran a command in mysql that resulted in this error:


So I had to add a line to the mysql config file. In Ubuntu 16:

# nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add the following line under mysqld


Restart mysql:

# systemctl restart mysql

MariaDB cnf files

Here is the original /etc/my.cnf file for a default mariadb installation:

# Disabling symbolic-links is recommended to prevent assorted security risks
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in


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

Location of other .cnf files you can use:


How do you calculate mysql max_connections variable

The basic formulas are:

Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM – Global Buffers) / Thread Buffers

To get the list of buffers and their values:


Here’s a list of the buffers and whether they’re Global or Thread:

Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size
Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack

MariaDB [(none)]> SHOW VARIABLES LIKE '%buffer%';
| Variable_name                         | Value     |
| aria_pagecache_buffer_size            | 134217728 |
| aria_sort_buffer_size                 | 134217728 |
| bulk_insert_buffer_size               | 8388608   |
| innodb_blocking_buffer_pool_restore   | OFF       |
| innodb_buffer_pool_instances          | 1         |
| innodb_buffer_pool_populate           | OFF       |
| innodb_buffer_pool_restore_at_startup | 0         |
| innodb_buffer_pool_shm_checksum       | ON        |
| innodb_buffer_pool_shm_key            | 0         |
| innodb_buffer_pool_size               | 134217728 |
| innodb_change_buffering               | all       |
| innodb_log_buffer_size                | 8388608   |
| join_buffer_size                      | 131072    |
| join_buffer_space_limit               | 2097152   |
| key_buffer_size                       | 16777216  |
| mrr_buffer_size                       | 262144    |
| myisam_sort_buffer_size               | 8388608   |
| net_buffer_length                     | 8192      |
| preload_buffer_size                   | 32768     |
| read_buffer_size                      | 262144    |
| read_rnd_buffer_size                  | 524288    |
| sort_buffer_size                      | 524288    |
| sql_buffer_result                     | OFF       |

Lets find out the RAM:

# free -b
              total        used        free      shared  buff/cache   available
Mem:     3975184384   978608128  1691045888     9445376  1305530368  2661937152

Lets get our data together.

RAM = 3975184384
Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_length, query_cache_size
or, from above…
Global Buffers: 16777216 + 134217728 + 8388608 + 0 + 8192 + 0 = 159391744

Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack
or, from above…
Thread Buffers: 524288 + 8388608 + 262144 + 131072 + 524288 + 0 = 9830400

With this information, the following is the calculation:
max_connections = (Available RAM – Global Buffers) / Thread Buffers
max_connections = (3975184384 – 159391744) / 9830400

So the formula shows 378 Max Connections on this machine

Test info with mysqltuner

Log into your server with a root or sudo user via SSH.
Download MySQLTuner by executing the following command:

wget -O --no-check-certificate

Give the script 775 permissions:

chmod 775

Run the script with the following command:


Handy Calculator Download:
Another Calculator:

List sizes of MySQL databases

Do you wonder which databases are actually taking up how much space but only have one huge ibdata1 in your /var/lib/mysql and the directories inside your mysql data directory don’t represent the actual database sizes?

Run from a mysql root console:

mysql> SELECT table_schema AS "Database name", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
| Database name | Size (MB) |
| information_schema | 0.00878906 |
| mysql | 0.62860394 |
| performance_schema | 0.00000000 |
| roundcube | 0.46875000 |
8 rows in set (1.21 sec)