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 192.168.1.1 --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 UNIQUE_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:


  #!/bin/sh 

  # store start date to a variable
  imeron=`date`

  echo "Import started: OK"
  dumpfile="/home/bob/bobiras.sql"

  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 127.0.0.1 -u root -proot -e "$ddl"

  # store end date to a variable
  imeron2=`date`

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

Other solutions:
Big Dump
http://www.ozerov.de/bigdump/ seems good

Split Files
http://www.rusiczki.net/2007/01/24/sql-dump-file-splitter/

Using the MutiPHP Editor changes are overwritten for fopen and exec

Recently there was an issue where the fopen and curl function and shell_exec functions were reverting back to original values. Using the MutiPHP Editor was not working.

the resolution was that the php-fmp handler was being used on a cpanel server. So the php-fpm config file needed to be changed.

The line that needs to be edited is:in /opt/cpanel/ea-php56/root/etc/php-fpm.d/$DOMAIN

Remove the disable_functions which includes shell_exec

php_admin_value[disable_functions] =

Then restart the following services.

systemctl restart httpd.service
/scripts/restartsrv_apache_php_fpm
/scripts/restartsrv_cpanel_php_fpm

UPDATE; Seems the yaml files need to be updated:
https://forums.cpanel.net/threads/stop-cpanel-from-overwriting-php-fpm-settings-file.596527/

This information is for editing the yaml file which should result in permanent changes.

More information here on php-fpm configurations:
https://documentation.cpanel.net/display/64Docs/Configurations+Values+of+PHP-FPM#ConfigurationsValuesofPHP-FPM-Howtoremovedefaultvaluesfromaconfiguration

UPDATE 2:

Also, there was an issue with fopen where the changes in the MultiPHP Editor were being overwritten.

If  PHP handler is LSAPI, you can adjustment to your Apache configuration. Please ensure the setting is enabled for the site(s) in question under MultiPHP Ini Editor. If this still does not work, in whm from   Home »Software »MultiPHP Manager there is a System PHP-FPM Configuration tab and from it you can change the user override ability for allow_url_fopen and other options.

 

How do I manage databases in the Plesk control panel?

The Plesk Control Panel has the ability to create multiple MySQL and PostgreSQL databases as well as multiple users within each database. Also, directly accessible via Plesk, are the links to phpMyAdmin and PhpPGAdmin, the PHP interface applications that abstract mysql or postgresql into a web-based administration tool, allowing you to sort, edit, and create tables within a given database.

Before creating your databases within the Plesk Control Panel, please ensure that you have already added a hosted domain name to your dedicated server.

Here is how you can create MySQL (or PostgreSQL) databases using Plesk:
Login to your Plesk Control Panel. The login URL is in the following format (https://ipaddress:8443).
Click on Websites and Domains from the navigation bar on the left.

Select the domain name that you’d like to associate your database with.
Click the Databases icon on the top right menu.

Click the Add Database icon.

Add the Database Name
Select the related site for the data base.
Click the “Create a Database User” box in order to setup database administrator’s credentials.
Enter a username and a password which will be used for accessing the contents of the database.
Select if the User has access to all databases.
Select Access Control for the database.
Click OK.
Now, you can use the phpMyAdmin and other tools where you can manage your database.

PHP Custom Sessions Not Working in Plesk

I went and adjusted the expected time out to 4 hours on every domain and on the default settings through Plesk. But it seems that instead of time outs being handled by php, the server runs a cron job that handles the timeouts and ignore php settings. That breaks our apps. Is it there a way to change his?

See: https://websavers.ca/plesk-php-sessions-timing-earlier-expected

After switching a WordPress website to FPM served by nginx in Plesk, it fails to load with “404 Not Found” on all pages except start page

After switching a WordPress website to FPM served by nginx in Plesk, it fails to load with “404 Not Found” on all pages except start page.

See: https://support.plesk.com/hc/en-us/articles/213912945-A-WordPress-website-on-a-Plesk-server-shows-404-Not-Found-on-all-pages-except-index-php-when-running-on-FPM-by-nginx

For one website

In Plesk, add the content below to the Additional nginx directives field of the WordPress domain at Domains > example.com > Apache & nginx Settings:


    if (!-e $request_filename) {
    set $test P;
    }
    if ($uri !~ ^/(plesk-stat|webstat|webstat-ssl|ftpstat|anon_ftpstat|awstats-icon|internal-nginx-static-location)) {
    set $test "${test}C";
    }
    if ($test = PC) {
    rewrite ^/(.*)$ /index.php?$1;
    }


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

INFO: Reference: https://mariadb.com/kb/en/mariadb/error-log/

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.

How To Install and Configure Zabbix Agent on Ubuntu 18.04

This KB explores How To Install and Configure Zabbix on Ubuntu 18.04

Navigate to https://repo.zabbix.com/zabbix/

Find the release from the current version.

wget https://repo.zabbix.com/zabbix/4.1/ubuntu/pool/main/z/zabbix/zabbix-agent_4.2.0~alpha1-1%2Bbionic_amd64.deb
# dpkg -i zabbix-agent_4.2.0~alpha1-1+bionic_amd64.deb
apt update
# service zabbix-agent start