Install Nginx With PHP-FPM

• Ubuntu/Debian
• Nginx latest stable
• php 5.6-fpm

Click here for download

Step to Recover Size of Ibdata1

• Export / dumping all databases except 3 tables; information_schema, performance_schema, and mysql
• Move ibdata1, ib_logfile0 and ib_logfile1 to / home or another folder just in case there is an error in the repair
• Stop mysql
• Edit /etc/mysql/my.cnf

[mysql]
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_log_file_size = 500M
innodb_buffer_pool_size = 1G

• Large innodb_buffer_pool_size can be 4 fold or at least 2 times from innodb_log_file_size
• Start mysql
• Restore the database that we have exported. when restoring data, ibdata1 will get bigger but will only store metadata only, while the original data from each database will be created a folder and each folder will contain many files, each file represents a particular table.

Repository Ubuntu 14.04 Kambing UI

deb http://kambing.ui.ac.id/ubuntu/ trusty main restricted universe multiverse
deb http://kambing.ui.ac.id/ubuntu/ trusty-updates main restricted universe multiverse
deb http://kambing.ui.ac.id/ubuntu/ trusty-security main restricted universe multiverse
deb http://kambing.ui.ac.id/ubuntu/ trusty-backports main restricted universe multiverse
deb http://kambing.ui.ac.id/ubuntu/ trusty-proposed main restricted universe multiverse

Panduan Instalasi LAMP+ Ubuntu 16.04 LTS di KiosK

Includes:

1. Instalasi Ubuntu 16.04 LTS
2. Mengganti Repositori ke Kambing UI
3. Instalasi Apache2.4.x Web Server
4. Instalasi PHP5.6 Instalasi MariaDB 10.2.x
5. Instalasi PHPMyAdmin
6. Instalasi GNOME Classic
7. Mengganti Desktop ke Gnome Classic

Dapat diunduh di: Panduan Instalasi LAMP+ di KiosK Ubuntu 16.04

Installing Python on Ubuntu 17.04

– Open browser, see http://www.python.org/downloads/source/
– Download the newest version of Python as tgz file for Unix/Linux
– Extract file
– Run ./configure or ./configure –enable-optimizations for any live optimazion
– Edit Modules/Setup file if you want customization Python
– make
– make install

Show Nginx Modules

nginx -V
2>&1 nginx -V | tr ' ' '\n'
2>&1 nginx -V | xargs -n1
2>&1 nginx -V | tr -- - '\n' | grep _module

or see : https://serverfault.com/questions/223509/how-can-i-see-which-flags-nginx-was-compiled-with

Nginx Upstream prematurely closed FastCGI stdout while reading response header from upstream

Error Log:

Nginx Upstream prematurely closed FastCGI stdout while reading response header from upstream

Error Code

• 502 Bad Gateway
• 502 Proxy Error
• 502 Bad Gateway NGINX
• 502 Service Temporarily Overloaded
• Error 502
• HTTP 502
• HTTP Error 502 – Bad Gateway

Cek tcp conn:

netstat -ant|awk '/tcp/ {print $6}'|sort|uniq -c

SetUp system /etc/sysctl.conf

net.core.somaxconn = 65536
fs.file-max = 2097152
net.ipv4.tcp_fin_timeout = 60
net.ipv4.tcp_keepalive_time = 7200
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_keepalive_intvl = 75
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1

Setup php-fpm pool: /etc/php/5.6/fpm/pool.d/www.conf

 

listen.owner = www-data
listen.group = www-data
listen.mode = 0660
listen = /run/php/php5.6-fpm.sock
listen.backlog = 65536
pm = dynamic
pm.max_children = 300
pm.start_servers = 30
pm.min_spare_servers = 10
pm.max_spare_servers = 30
pm.max_requests = 400

or adjust to your needs

chown -R www-data:root /var/lib/nginx
chown -R www-data:root /var/lib/php
chmod 755 /var/lib/php/sessions
chown -R www-data:root /var/run/php/
chown -R www-data:root /usr/lib/nginx/modules
chown -R www-data:root /usr/lib/php/5.6

install php-xdebug!
Activated php-opcache!

Tweak Nginx.conf

#Adjust client timeouts
 client_max_body_size 0;
 client_body_buffer_size 1k;
 client_header_buffer_size 1k;
 large_client_header_buffers 1 2k;
 client_body_timeout 15s;
 client_header_timeout 15s;
 keepalive_timeout 15s;
 send_timeout 15s;
 sendfile on;
 tcp_nopush on;
 tcp_nodelay on;

#Adjust output buffers
 fastcgi_buffers 256 48k;
 fastcgi_buffer_size 48k;
 fastcgi_connect_timeout 15s;
 fastcgi_send_timeout 15s;
 fastcgi_read_timeout 15s;
 fastcgi_busy_buffers_size 256k;
 #fastcgi_temp_file_write_size 256k;
 fastcgi_max_temp_file_size 0;
 reset_timedout_connection on;
 server_names_hash_bucket_size 100;

Restart nginx & php-fpm!

Field ‘xxx’ doesn’t have a default value

Edit /etc/mysql/my.cnf

# If applications support it, this stricter sql_mode prevents $
# mistakes like inserting invalid dates etc.
sql_mode = NO_ENGINE_SUBSTITUTIO

Check

MariaDB [(none)]> SELECT @@sql_mode;
+------------------------+
| @@sql_mode |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

[Galera Cluster] Starting MariaDB database server mysqld [Fail] After All Node Down

cat /var/log/syslog

Aug 16 10:02:22 ubuntu mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
Aug 16 10:02:22 ubuntu mysqld_safe: WSREP: Running position recovery with –log_error=’/var/lib/mysql/wsrep_recovery.PySORv’ –pid-file=’/var/lib/mysql/ubuntu-recover.pid’
Aug 16 10:02:22 ubuntu mysqld: 2017-08-16 10:02:22 140084985264000 [Note] /usr/sbin/mysqld (mysqld 10.2.7-MariaDB-10.2.7+maria~trusty-log) starting as process 4821 …
Aug 16 10:02:28 ubuntu mysqld_safe: WSREP: Recovered position e0cf993b-8175-11e7-bd60-2326e7fcbf42:4399
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] /usr/sbin/mysqld (mysqld 10.2.7-MariaDB-10.2.7+maria~trusty-log) starting as process 4904 …
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Read nil XID from storage engines, skipping position init
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: wsrep_load(): loading provider library ‘/usr/lib/galera/libgalera_smm.so’
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: wsrep_load(): Galera 25.3.20(r3703) by Codership Oy <info@codership.com> loaded successfully.
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: CRC-32C: using hardware acceleration.
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Found saved state: e0cf993b-8175-11e7-bd60-2326e7fcbf42:-1, safe_to_bootsrap: 0
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Passing config to GCS: base_dir = /var/lib/mysql/; base_host = 192.168.8.18; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 2; evs.view_forget_timeout = PT24H; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 256M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announc
Aug 16 10:02:28 ubuntu mysqld: e_timeout = PT3S; pc.checksum = false; pc.i
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: GCache history reset: old(e0cf993b-8175-11e7-bd60-2326e7fcbf42:0) -> new(e0cf993b-8175-11e7-bd60-2326e7fcbf42:4399)
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Assign initial position for certification: 4399, protocol version: -1
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: wsrep_sst_grab()
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Start replication
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: ‘wsrep-new-cluster’ option used, bootstrapping the cluster
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [Note] WSREP: Setting initial position to e0cf993b-8175-11e7-bd60-2326e7fcbf42:4399
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [ERROR] WSREP: wsrep::connect(gcomm://192.168.8.18,192.168.8.19,192.168.8.20) failed: 7
Aug 16 10:02:28 ubuntu mysqld: 2017-08-16 10:02:28 139985818675072 [ERROR] Aborting
Aug 16 10:02:28 ubuntu mysqld:
Aug 16 10:02:29 ubuntu mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]: 0 processes alive and ‘/usr/bin/mysqladmin –defaults-file=/etc/mysql/debian.cnf ping’ resulted in
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]: #007/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]: error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (111)’
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]: Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists!
Aug 16 10:02:53 ubuntu /etc/init.d/mysql[5144]:

The red fonts is the key

So, we must see tha node that safe_to_bootstrap is 1, and then sudo service mysql start –wsrep-new-cluster

Galera Cluster with MariaDB 10.2 (3 Nodes) on Ubuntu 14.04 / 16.04 Servers

Note:

node_1, IP : 192.168.212.1

node_2, IP : 192.168.212.2

node_3, IP : 192.168.212.3

1.  Install MariaDB 10.2

If you have MariaDB 10.1 or later before, please make sure that upgrade or see https://askubuntu.com/questions/703123/mariadb-10-1-server-wont-start-after-update

Here for more detail installing mariaDB 10.2; https://downloads.mariadb.org/mariadb/repositories/#mirror=Beritagar&distro=Ubuntu&distro_release=trusty–ubuntu_trusty&version=10.2

sudo apt-get install software-properties-common 
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db 
sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://sumberterbuka.beritagar.id/mariadb/repo/10.2/ubuntu trusty main'

sudo apt-get update sudo apt-get install mariadb-server

2. Configure local hostname

In all node

sudo nano /etc/hosts

Paste :

192.168.212.1   node_1
192.168.212.2   node_2
192.168.212.3   node_3

sudo service networking restart

3. [mysql] * Basic Settings

Uncomment in /etc/mysql/my.cnf node_1, node_2 and node_3

#bind-address           = 127.0.0.1
#default_storage_engine = InnoDB
#query_cache_limit              = 128K
#query_cache_size               = 64M

4. [galera] * Galera-related settings

a. node_1

binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://192.168.212.1,192.168.212.2,192.168.212.3
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="192.168.212.1"
wsrep_node_name="node_1"
wsrep_slave_threads=16
wsrep_sst_method=rsync
bind-address=0.0.0.0

b. node_1

wsrep_on=ON
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://192.168.212.1,192.168.212.2,192.168.212.3
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="192.168.212.2"
wsrep_node_name="node_2"
bind-address=0.0.0.0

c. node_1

wsrep_on=ON
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
innodb_doublewrite=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_provider_options="gcache.size=256M; gcache.page_size=128M"
wsrep_cluster_address=gcomm://192.168.212.1,192.168.212.2,192.168.212.3
wsrep_cluster_name="MariaDB_Cluster"
wsrep_node_address="192.168.212.3"
wsrep_node_name="node_3"
wsrep_slave_threads=16
wsrep_sst_method=rsync
bind-address=0.0.0.0

5. Stop MariaDB Service on all node

Sudo service mysql stop

6. Start MariaDB Service

a. node_1

sudo service mysql start –wsrep-new-cluster

Check that galera is running well

mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size'”

Output

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

b. node_2

Service mysql start

If you see an ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES), copy /etc/mysql/debian.cnf from the first node (node_1) and then restart mysql in node_2 : service mysql restart

Check that galera is running well

mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size'”

Output

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

c. node_3

Service mysql start

If you see an ERROR 1045 (28000): Access denied for user ‘debian-sys-maint’@’localhost’ (using password: YES), copy /etc/mysql/debian.cnf from the first node (node_1) and then restart mysql in node_3 : service mysql restart

Check that galera is running well

mysql -u root -p -e “SHOW STATUS LIKE ‘wsrep_cluster_size'”

Output

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

7. Set Firewall (IPTABLES)

-A INPUT -s 192.168.212.0/24 -p tcp -m tcp –dport 22 -j ACCEPT
-A INPUT -s 192.168.212.0/24 -p tcp -m multiport –dports 3306,4567,4568,4444 -j ACCEPT
-A INPUT -p udp -m udp –dport 4567 -j ACCEPT