I needed to do this, but all the tutorials that I could find are incomplete, or already outdated, such as this.

After hacking around for a while, here’s how to correctly setup OpenVPN server in a container on Proxmox:

(btw if you just need to setup an OpenVPN Server in a normal server / non-container, then just do the “in container” part below)

IN HOST

# create special device "tun" for OpenVPN
mkdir -p /devcontainer/net
mknod /devcontainer/net/tun c 10 200
chown 100000:100000 /devcontainer/net/tun

# enable your container to use that tun device
# change 124 into your container's number : pct list
echo "lxc.mount.entry: /devcontainer/net dev/net none bind,create=dir" >> /etc/pve/lxc/124.conf

# forward OpenVPN traffic to your container's IP address
# change 10.10.60.6 to your container's IP address
iptables -t nat -A PREROUTING -i vmbr0 -p tcp -m tcp --dport 1194 -j DNAT --to-destination 10.10.60.6:1194

iptables -t nat -A PREROUTING -i vmbr0 -p udp -m udp --dport 1194 -j DNAT --to-destination 10.10.60.6:1194

iptables -t nat -A PREROUTING -i vmbr1 -p tcp -m tcp --dport 53 -j DNAT --to-destination 10.10.60.6:53

# save iptables's rule
iptables-save > /etc/iptables.rules

IN CONTAINER

# execute the automated OpenVPN installation script 
mkdir /root/scripts
cd /root/scripts

wget git.io/vpn --no-check-certificate -O openvpn-install.sh ; chmod +x openvpn-install.sh ; ./openvpn-install.sh
 
# if you'd like to change the default 10.8.0.xxx IP address, do this :
# vi openvpn-install.sh
# :%s/10.8.0/10.88.0/g

# setup NAT, so the OpenVPN clients can connect to the internet 
# while connected to this OpenVPN server
iptables -I POSTROUTING -t nat -s 10.88.0.0/24 -j MASQUERADE

# save iptables's rule
iptables-save > /etc/iptables.rules

After executing the /root/scripts/openvpn-install.sh script , it will result in a file with ovpn extension

Download that to your computer / client,
install OpenVPN client,
and use that ovpn file as the configuration

Enjoy !

In case that very helpful OpenVPN Server install script suddenly disappear, here it is :

#!/bin/bash
#
# https://github.com/Nyr/openvpn-install
#
# Copyright (c) 2013 Nyr. Released under the MIT License.


# Detect Debian users running the script with "sh" instead of bash
if readlink /proc/$$/exe | grep -q "dash"; then
	echo 'This installer needs to be run with "bash", not "sh".'
	exit
fi

# Discard stdin. Needed when running from an one-liner which includes a newline
read -N 999999 -t 0.001

# Detect OpenVZ 6
if [[ $(uname -r | cut -d "." -f 1) -eq 2 ]]; then
	echo "The system is running an old kernel, which is incompatible with this installer."
	exit
fi

# Detect OS
# $os_version variables aren't always in use, but are kept here for convenience
if grep -qs "ubuntu" /etc/os-release; then
	os="ubuntu"
	os_version=$(grep 'VERSION_ID' /etc/os-release | cut -d '"' -f 2 | tr -d '.')
	group_name="nogroup"
elif [[ -e /etc/debian_version ]]; then
	os="debian"
	os_version=$(grep -oE '[0-9]+' /etc/debian_version | head -1)
	group_name="nogroup"
elif [[ -e /etc/centos-release ]]; then
	os="centos"
	os_version=$(grep -oE '[0-9]+' /etc/centos-release | head -1)
	group_name="nobody"
elif [[ -e /etc/fedora-release ]]; then
	os="fedora"
	os_version=$(grep -oE '[0-9]+' /etc/fedora-release | head -1)
	group_name="nobody"
else
	echo "This installer seems to be running on an unsupported distribution.
Supported distributions are Ubuntu, Debian, CentOS, and Fedora."
	exit
fi

if [[ "$os" == "ubuntu" && "$os_version" -lt 1804 ]]; then
	echo "Ubuntu 18.04 or higher is required to use this installer.
This version of Ubuntu is too old and unsupported."
	exit
fi

if [[ "$os" == "debian" && "$os_version" -lt 9 ]]; then
	echo "Debian 9 or higher is required to use this installer.
This version of Debian is too old and unsupported."
	exit
fi

if [[ "$os" == "centos" && "$os_version" -lt 7 ]]; then
	echo "CentOS 7 or higher is required to use this installer.
This version of CentOS is too old and unsupported."
	exit
fi

# Detect environments where $PATH does not include the sbin directories
if ! grep -q sbin <<< "$PATH"; then
	echo '$PATH does not include sbin. Try using "su -" instead of "su".'
	exit
fi

if [[ "$EUID" -ne 0 ]]; then
	echo "This installer needs to be run with superuser privileges."
	exit
fi

if [[ ! -e /dev/net/tun ]] || ! ( exec 7<>/dev/net/tun ) 2>/dev/null; then
	echo "The system does not have the TUN device available.
TUN needs to be enabled before running this installer."
	exit
fi

new_client () {
	# Generates the custom client.ovpn
	{
	cat /etc/openvpn/server/client-common.txt
	echo "<ca>"
	cat /etc/openvpn/server/easy-rsa/pki/ca.crt
	echo "</ca>"
	echo "<cert>"
	sed -ne '/BEGIN CERTIFICATE/,$ p' /etc/openvpn/server/easy-rsa/pki/issued/"$client".crt
	echo "</cert>"
	echo "<key>"
	cat /etc/openvpn/server/easy-rsa/pki/private/"$client".key
	echo "</key>"
	echo "<tls-crypt>"
	sed -ne '/BEGIN OpenVPN Static key/,$ p' /etc/openvpn/server/tc.key
	echo "</tls-crypt>"
	} > ~/"$client".ovpn
}

if [[ ! -e /etc/openvpn/server/server.conf ]]; then
	clear
	echo 'Welcome to this OpenVPN road warrior installer!'
	# If system has a single IPv4, it is selected automatically. Else, ask the user
	if [[ $(ip -4 addr | grep inet | grep -vEc '127(\.[0-9]{1,3}){3}') -eq 1 ]]; then
		ip=$(ip -4 addr | grep inet | grep -vE '127(\.[0-9]{1,3}){3}' | cut -d '/' -f 1 | grep -oE '[0-9]{1,3}(\.[0-9]{1,3}){3}')
	else
		number_of_ip=$(ip -4 addr | grep inet | grep -vEc '127(\.[0-9]{1,3}){3}')
		echo
		echo "Which IPv4 address should be used?"
		ip -4 addr | grep inet | grep -vE '127(\.[0-9]{1,3}){3}' | cut -d '/' -f 1 | grep -oE '[0-9]{1,3}(\.[0-9]{1,3}){3}' | nl -s ') '
		read -p "IPv4 address [1]: " ip_number
		until [[ -z "$ip_number" || "$ip_number" =~ ^[0-9]+$ && "$ip_number" -le "$number_of_ip" ]]; do
			echo "$ip_number: invalid selection."
			read -p "IPv4 address [1]: " ip_number
		done
		[[ -z "$ip_number" ]] && ip_number="1"
		ip=$(ip -4 addr | grep inet | grep -vE '127(\.[0-9]{1,3}){3}' | cut -d '/' -f 1 | grep -oE '[0-9]{1,3}(\.[0-9]{1,3}){3}' | sed -n "$ip_number"p)
	fi
	# If $ip is a private IP address, the server must be behind NAT
	if echo "$ip" | grep -qE '^(10\.|172\.1[6789]\.|172\.2[0-9]\.|172\.3[01]\.|192\.168)'; then
		echo
		echo "This server is behind NAT. What is the public IPv4 address or hostname?"
		# Get public IP and sanitize with grep
		get_public_ip=$(grep -m 1 -oE '^[0-9]{1,3}(\.[0-9]{1,3}){3}$' <<< "$(wget -T 10 -t 1 -4qO- "http://ip1.dynupdate.no-ip.com/" || curl -m 10 -4Ls "http://ip1.dynupdate.no-ip.com/")")
		read -p "Public IPv4 address / hostname [$get_public_ip]: " public_ip
		# If the checkip service is unavailable and user didn't provide input, ask again
		until [[ -n "$get_public_ip" || -n "$public_ip" ]]; do
			echo "Invalid input."
			read -p "Public IPv4 address / hostname: " public_ip
		done
		[[ -z "$public_ip" ]] && public_ip="$get_public_ip"
	fi
	# If system has a single IPv6, it is selected automatically
	if [[ $(ip -6 addr | grep -c 'inet6 [23]') -eq 1 ]]; then
		ip6=$(ip -6 addr | grep 'inet6 [23]' | cut -d '/' -f 1 | grep -oE '([0-9a-fA-F]{0,4}:){1,7}[0-9a-fA-F]{0,4}')
	fi
	# If system has multiple IPv6, ask the user to select one
	if [[ $(ip -6 addr | grep -c 'inet6 [23]') -gt 1 ]]; then
		number_of_ip6=$(ip -6 addr | grep -c 'inet6 [23]')
		echo
		echo "Which IPv6 address should be used?"
		ip -6 addr | grep 'inet6 [23]' | cut -d '/' -f 1 | grep -oE '([0-9a-fA-F]{0,4}:){1,7}[0-9a-fA-F]{0,4}' | nl -s ') '
		read -p "IPv6 address [1]: " ip6_number
		until [[ -z "$ip6_number" || "$ip6_number" =~ ^[0-9]+$ && "$ip6_number" -le "$number_of_ip6" ]]; do
			echo "$ip6_number: invalid selection."
			read -p "IPv6 address [1]: " ip6_number
		done
		[[ -z "$ip6_number" ]] && ip6_number="1"
		ip6=$(ip -6 addr | grep 'inet6 [23]' | cut -d '/' -f 1 | grep -oE '([0-9a-fA-F]{0,4}:){1,7}[0-9a-fA-F]{0,4}' | sed -n "$ip6_number"p)
	fi
	echo
	echo "Which protocol should OpenVPN use?"
	echo "   1) UDP (recommended)"
	echo "   2) TCP"
	read -p "Protocol [1]: " protocol
	until [[ -z "$protocol" || "$protocol" =~ ^[12]$ ]]; do
		echo "$protocol: invalid selection."
		read -p "Protocol [1]: " protocol
	done
	case "$protocol" in
		1|"") 
		protocol=udp
		;;
		2) 
		protocol=tcp
		;;
	esac
	echo
	echo "What port should OpenVPN listen to?"
	read -p "Port [1194]: " port
	until [[ -z "$port" || "$port" =~ ^[0-9]+$ && "$port" -le 65535 ]]; do
		echo "$port: invalid port."
		read -p "Port [1194]: " port
	done
	[[ -z "$port" ]] && port="1194"
	echo
	echo "Select a DNS server for the clients:"
	echo "   1) Current system resolvers"
	echo "   2) Google"
	echo "   3) 1.1.1.1"
	echo "   4) OpenDNS"
	echo "   5) Quad9"
	echo "   6) AdGuard"
	read -p "DNS server [1]: " dns
	until [[ -z "$dns" || "$dns" =~ ^[1-6]$ ]]; do
		echo "$dns: invalid selection."
		read -p "DNS server [1]: " dns
	done
	echo
	echo "Enter a name for the first client:"
	read -p "Name [client]: " unsanitized_client
	# Allow a limited set of characters to avoid conflicts
	client=$(sed 's/[^0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_-]/_/g' <<< "$unsanitized_client")
	[[ -z "$client" ]] && client="client"
	echo
	echo "OpenVPN installation is ready to begin."
	# Install a firewall in the rare case where one is not already available
	if ! systemctl is-active --quiet firewalld.service && ! hash iptables 2>/dev/null; then
		if [[ "$os" == "centos" || "$os" == "fedora" ]]; then
			firewall="firewalld"
			# We don't want to silently enable firewalld, so we give a subtle warning
			# If the user continues, firewalld will be installed and enabled during setup
			echo "firewalld, which is required to manage routing tables, will also be installed."
		elif [[ "$os" == "debian" || "$os" == "ubuntu" ]]; then
			# iptables is way less invasive than firewalld so no warning is given
			firewall="iptables"
		fi
	fi
	read -n1 -r -p "Press any key to continue..."
	# If running inside a container, disable LimitNPROC to prevent conflicts
	if systemd-detect-virt -cq; then
		mkdir /etc/systemd/system/openvpn-server@server.service.d/ 2>/dev/null
		echo "[Service]
LimitNPROC=infinity" > /etc/systemd/system/openvpn-server@server.service.d/disable-limitnproc.conf
	fi
	if [[ "$os" = "debian" || "$os" = "ubuntu" ]]; then
		apt-get update
		apt-get install -y openvpn openssl ca-certificates $firewall
	elif [[ "$os" = "centos" ]]; then
		yum install -y epel-release
		yum install -y openvpn openssl ca-certificates tar $firewall
	else
		# Else, OS must be Fedora
		dnf install -y openvpn openssl ca-certificates tar $firewall
	fi
	# If firewalld was just installed, enable it
	if [[ "$firewall" == "firewalld" ]]; then
		systemctl enable --now firewalld.service
	fi
	# Get easy-rsa
	easy_rsa_url='https://github.com/OpenVPN/easy-rsa/releases/download/v3.0.8/EasyRSA-3.0.8.tgz'
	mkdir -p /etc/openvpn/server/easy-rsa/
	{ wget -qO- "$easy_rsa_url" 2>/dev/null || curl -sL "$easy_rsa_url" ; } | tar xz -C /etc/openvpn/server/easy-rsa/ --strip-components 1
	chown -R root:root /etc/openvpn/server/easy-rsa/
	cd /etc/openvpn/server/easy-rsa/
	# Create the PKI, set up the CA and the server and client certificates
	./easyrsa init-pki
	./easyrsa --batch build-ca nopass
	EASYRSA_CERT_EXPIRE=3650 ./easyrsa build-server-full server nopass
	EASYRSA_CERT_EXPIRE=3650 ./easyrsa build-client-full "$client" nopass
	EASYRSA_CRL_DAYS=3650 ./easyrsa gen-crl
	# Move the stuff we need
	cp pki/ca.crt pki/private/ca.key pki/issued/server.crt pki/private/server.key pki/crl.pem /etc/openvpn/server
	# CRL is read with each client connection, while OpenVPN is dropped to nobody
	chown nobody:"$group_name" /etc/openvpn/server/crl.pem
	# Without +x in the directory, OpenVPN can't run a stat() on the CRL file
	chmod o+x /etc/openvpn/server/
	# Generate key for tls-crypt
	openvpn --genkey --secret /etc/openvpn/server/tc.key
	# Create the DH parameters file using the predefined ffdhe2048 group
	echo '-----BEGIN DH PARAMETERS-----
MIIBCAKCAQEA//////////+t+FRYortKmq/cViAnPTzx2LnFg84tNpWp4TZBFGQz
+8yTnc4kmz75fS/jY2MMddj2gbICrsRhetPfHtXV/WVhJDP1H18GbtCFY2VVPe0a
87VXE15/V8k1mE8McODmi3fipona8+/och3xWKE2rec1MKzKT0g6eXq8CrGCsyT7
YdEIqUuyyOP7uWrat2DX9GgdT0Kj3jlN9K5W7edjcrsZCwenyO4KbXCeAvzhzffi
7MA0BM0oNC9hkXL+nOmFg/+OTxIy7vKBg8P+OxtMb61zO7X8vC7CIAXFjvGDfRaD
ssbzSibBsu/6iGtCOGEoXJf//////////wIBAg==
-----END DH PARAMETERS-----' > /etc/openvpn/server/dh.pem
	# Generate server.conf
	echo "local $ip
port $port
proto $protocol
dev tun
ca ca.crt
cert server.crt
key server.key
dh dh.pem
auth SHA512
tls-crypt tc.key
topology subnet
server 10.8.0.0 255.255.255.0" > /etc/openvpn/server/server.conf
	# IPv6
	if [[ -z "$ip6" ]]; then
		echo 'push "redirect-gateway def1 bypass-dhcp"' >> /etc/openvpn/server/server.conf
	else
		echo 'server-ipv6 fddd:1194:1194:1194::/64' >> /etc/openvpn/server/server.conf
		echo 'push "redirect-gateway def1 ipv6 bypass-dhcp"' >> /etc/openvpn/server/server.conf
	fi
	echo 'ifconfig-pool-persist ipp.txt' >> /etc/openvpn/server/server.conf
	# DNS
	case "$dns" in
		1|"")
			# Locate the proper resolv.conf
			# Needed for systems running systemd-resolved
			if grep -q '^nameserver 127.0.0.53' "/etc/resolv.conf"; then
				resolv_conf="/run/systemd/resolve/resolv.conf"
			else
				resolv_conf="/etc/resolv.conf"
			fi
			# Obtain the resolvers from resolv.conf and use them for OpenVPN
			grep -v '^#\|^;' "$resolv_conf" | grep '^nameserver' | grep -oE '[0-9]{1,3}(\.[0-9]{1,3}){3}' | while read line; do
				echo "push \"dhcp-option DNS $line\"" >> /etc/openvpn/server/server.conf
			done
		;;
		2)
			echo 'push "dhcp-option DNS 8.8.8.8"' >> /etc/openvpn/server/server.conf
			echo 'push "dhcp-option DNS 8.8.4.4"' >> /etc/openvpn/server/server.conf
		;;
		3)
			echo 'push "dhcp-option DNS 1.1.1.1"' >> /etc/openvpn/server/server.conf
			echo 'push "dhcp-option DNS 1.0.0.1"' >> /etc/openvpn/server/server.conf
		;;
		4)
			echo 'push "dhcp-option DNS 208.67.222.222"' >> /etc/openvpn/server/server.conf
			echo 'push "dhcp-option DNS 208.67.220.220"' >> /etc/openvpn/server/server.conf
		;;
		5)
			echo 'push "dhcp-option DNS 9.9.9.9"' >> /etc/openvpn/server/server.conf
			echo 'push "dhcp-option DNS 149.112.112.112"' >> /etc/openvpn/server/server.conf
		;;
		6)
			echo 'push "dhcp-option DNS 94.140.14.14"' >> /etc/openvpn/server/server.conf
			echo 'push "dhcp-option DNS 94.140.15.15"' >> /etc/openvpn/server/server.conf
		;;
	esac
	echo "keepalive 10 120
cipher AES-256-CBC
user nobody
group $group_name
persist-key
persist-tun
status openvpn-status.log
verb 3
crl-verify crl.pem" >> /etc/openvpn/server/server.conf
	if [[ "$protocol" = "udp" ]]; then
		echo "explicit-exit-notify" >> /etc/openvpn/server/server.conf
	fi
	# Enable net.ipv4.ip_forward for the system
	echo 'net.ipv4.ip_forward=1' > /etc/sysctl.d/30-openvpn-forward.conf
	# Enable without waiting for a reboot or service restart
	echo 1 > /proc/sys/net/ipv4/ip_forward
	if [[ -n "$ip6" ]]; then
		# Enable net.ipv6.conf.all.forwarding for the system
		echo "net.ipv6.conf.all.forwarding=1" >> /etc/sysctl.d/30-openvpn-forward.conf
		# Enable without waiting for a reboot or service restart
		echo 1 > /proc/sys/net/ipv6/conf/all/forwarding
	fi
	if systemctl is-active --quiet firewalld.service; then
		# Using both permanent and not permanent rules to avoid a firewalld
		# reload.
		# We don't use --add-service=openvpn because that would only work with
		# the default port and protocol.
		firewall-cmd --add-port="$port"/"$protocol"
		firewall-cmd --zone=trusted --add-source=10.8.0.0/24
		firewall-cmd --permanent --add-port="$port"/"$protocol"
		firewall-cmd --permanent --zone=trusted --add-source=10.8.0.0/24
		# Set NAT for the VPN subnet
		firewall-cmd --direct --add-rule ipv4 nat POSTROUTING 0 -s 10.8.0.0/24 ! -d 10.8.0.0/24 -j SNAT --to "$ip"
		firewall-cmd --permanent --direct --add-rule ipv4 nat POSTROUTING 0 -s 10.8.0.0/24 ! -d 10.8.0.0/24 -j SNAT --to "$ip"
		if [[ -n "$ip6" ]]; then
			firewall-cmd --zone=trusted --add-source=fddd:1194:1194:1194::/64
			firewall-cmd --permanent --zone=trusted --add-source=fddd:1194:1194:1194::/64
			firewall-cmd --direct --add-rule ipv6 nat POSTROUTING 0 -s fddd:1194:1194:1194::/64 ! -d fddd:1194:1194:1194::/64 -j SNAT --to "$ip6"
			firewall-cmd --permanent --direct --add-rule ipv6 nat POSTROUTING 0 -s fddd:1194:1194:1194::/64 ! -d fddd:1194:1194:1194::/64 -j SNAT --to "$ip6"
		fi
	else
		# Create a service to set up persistent iptables rules
		iptables_path=$(command -v iptables)
		ip6tables_path=$(command -v ip6tables)
		# nf_tables is not available as standard in OVZ kernels. So use iptables-legacy
		# if we are in OVZ, with a nf_tables backend and iptables-legacy is available.
		if [[ $(systemd-detect-virt) == "openvz" ]] && readlink -f "$(command -v iptables)" | grep -q "nft" && hash iptables-legacy 2>/dev/null; then
			iptables_path=$(command -v iptables-legacy)
			ip6tables_path=$(command -v ip6tables-legacy)
		fi
		echo "[Unit]
Before=network.target
[Service]
Type=oneshot
ExecStart=$iptables_path -t nat -A POSTROUTING -s 10.8.0.0/24 ! -d 10.8.0.0/24 -j SNAT --to $ip
ExecStart=$iptables_path -I INPUT -p $protocol --dport $port -j ACCEPT
ExecStart=$iptables_path -I FORWARD -s 10.8.0.0/24 -j ACCEPT
ExecStart=$iptables_path -I FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
ExecStop=$iptables_path -t nat -D POSTROUTING -s 10.8.0.0/24 ! -d 10.8.0.0/24 -j SNAT --to $ip
ExecStop=$iptables_path -D INPUT -p $protocol --dport $port -j ACCEPT
ExecStop=$iptables_path -D FORWARD -s 10.8.0.0/24 -j ACCEPT
ExecStop=$iptables_path -D FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT" > /etc/systemd/system/openvpn-iptables.service
		if [[ -n "$ip6" ]]; then
			echo "ExecStart=$ip6tables_path -t nat -A POSTROUTING -s fddd:1194:1194:1194::/64 ! -d fddd:1194:1194:1194::/64 -j SNAT --to $ip6
ExecStart=$ip6tables_path -I FORWARD -s fddd:1194:1194:1194::/64 -j ACCEPT
ExecStart=$ip6tables_path -I FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
ExecStop=$ip6tables_path -t nat -D POSTROUTING -s fddd:1194:1194:1194::/64 ! -d fddd:1194:1194:1194::/64 -j SNAT --to $ip6
ExecStop=$ip6tables_path -D FORWARD -s fddd:1194:1194:1194::/64 -j ACCEPT
ExecStop=$ip6tables_path -D FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT" >> /etc/systemd/system/openvpn-iptables.service
		fi
		echo "RemainAfterExit=yes
[Install]
WantedBy=multi-user.target" >> /etc/systemd/system/openvpn-iptables.service
		systemctl enable --now openvpn-iptables.service
	fi
	# If SELinux is enabled and a custom port was selected, we need this
	if sestatus 2>/dev/null | grep "Current mode" | grep -q "enforcing" && [[ "$port" != 1194 ]]; then
		# Install semanage if not already present
		if ! hash semanage 2>/dev/null; then
			if [[ "$os_version" -eq 7 ]]; then
				# Centos 7
				yum install -y policycoreutils-python
			else
				# CentOS 8 or Fedora
				dnf install -y policycoreutils-python-utils
			fi
		fi
		semanage port -a -t openvpn_port_t -p "$protocol" "$port"
	fi
	# If the server is behind NAT, use the correct IP address
	[[ -n "$public_ip" ]] && ip="$public_ip"
	# client-common.txt is created so we have a template to add further users later
	echo "client
dev tun
proto $protocol
remote $ip $port
resolv-retry infinite
nobind
persist-key
persist-tun
remote-cert-tls server
auth SHA512
cipher AES-256-CBC
ignore-unknown-option block-outside-dns
block-outside-dns
verb 3" > /etc/openvpn/server/client-common.txt
	# Enable and start the OpenVPN service
	systemctl enable --now openvpn-server@server.service
	# Generates the custom client.ovpn
	new_client
	echo
	echo "Finished!"
	echo
	echo "The client configuration is available in:" ~/"$client.ovpn"
	echo "New clients can be added by running this script again."
else
	clear
	echo "OpenVPN is already installed."
	echo
	echo "Select an option:"
	echo "   1) Add a new client"
	echo "   2) Revoke an existing client"
	echo "   3) Remove OpenVPN"
	echo "   4) Exit"
	read -p "Option: " option
	until [[ "$option" =~ ^[1-4]$ ]]; do
		echo "$option: invalid selection."
		read -p "Option: " option
	done
	case "$option" in
		1)
			echo
			echo "Provide a name for the client:"
			read -p "Name: " unsanitized_client
			client=$(sed 's/[^0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_-]/_/g' <<< "$unsanitized_client")
			while [[ -z "$client" || -e /etc/openvpn/server/easy-rsa/pki/issued/"$client".crt ]]; do
				echo "$client: invalid name."
				read -p "Name: " unsanitized_client
				client=$(sed 's/[^0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_-]/_/g' <<< "$unsanitized_client")
			done
			cd /etc/openvpn/server/easy-rsa/
			EASYRSA_CERT_EXPIRE=3650 ./easyrsa build-client-full "$client" nopass
			# Generates the custom client.ovpn
			new_client
			echo
			echo "$client added. Configuration available in:" ~/"$client.ovpn"
			exit
		;;
		2)
			# This option could be documented a bit better and maybe even be simplified
			# ...but what can I say, I want some sleep too
			number_of_clients=$(tail -n +2 /etc/openvpn/server/easy-rsa/pki/index.txt | grep -c "^V")
			if [[ "$number_of_clients" = 0 ]]; then
				echo
				echo "There are no existing clients!"
				exit
			fi
			echo
			echo "Select the client to revoke:"
			tail -n +2 /etc/openvpn/server/easy-rsa/pki/index.txt | grep "^V" | cut -d '=' -f 2 | nl -s ') '
			read -p "Client: " client_number
			until [[ "$client_number" =~ ^[0-9]+$ && "$client_number" -le "$number_of_clients" ]]; do
				echo "$client_number: invalid selection."
				read -p "Client: " client_number
			done
			client=$(tail -n +2 /etc/openvpn/server/easy-rsa/pki/index.txt | grep "^V" | cut -d '=' -f 2 | sed -n "$client_number"p)
			echo
			read -p "Confirm $client revocation? [y/N]: " revoke
			until [[ "$revoke" =~ ^[yYnN]*$ ]]; do
				echo "$revoke: invalid selection."
				read -p "Confirm $client revocation? [y/N]: " revoke
			done
			if [[ "$revoke" =~ ^[yY]$ ]]; then
				cd /etc/openvpn/server/easy-rsa/
				./easyrsa --batch revoke "$client"
				EASYRSA_CRL_DAYS=3650 ./easyrsa gen-crl
				rm -f /etc/openvpn/server/crl.pem
				cp /etc/openvpn/server/easy-rsa/pki/crl.pem /etc/openvpn/server/crl.pem
				# CRL is read with each client connection, when OpenVPN is dropped to nobody
				chown nobody:"$group_name" /etc/openvpn/server/crl.pem
				echo
				echo "$client revoked!"
			else
				echo
				echo "$client revocation aborted!"
			fi
			exit
		;;
		3)
			echo
			read -p "Confirm OpenVPN removal? [y/N]: " remove
			until [[ "$remove" =~ ^[yYnN]*$ ]]; do
				echo "$remove: invalid selection."
				read -p "Confirm OpenVPN removal? [y/N]: " remove
			done
			if [[ "$remove" =~ ^[yY]$ ]]; then
				port=$(grep '^port ' /etc/openvpn/server/server.conf | cut -d " " -f 2)
				protocol=$(grep '^proto ' /etc/openvpn/server/server.conf | cut -d " " -f 2)
				if systemctl is-active --quiet firewalld.service; then
					ip=$(firewall-cmd --direct --get-rules ipv4 nat POSTROUTING | grep '\-s 10.8.0.0/24 '"'"'!'"'"' -d 10.8.0.0/24' | grep -oE '[^ ]+$')
					# Using both permanent and not permanent rules to avoid a firewalld reload.
					firewall-cmd --remove-port="$port"/"$protocol"
					firewall-cmd --zone=trusted --remove-source=10.8.0.0/24
					firewall-cmd --permanent --remove-port="$port"/"$protocol"
					firewall-cmd --permanent --zone=trusted --remove-source=10.8.0.0/24
					firewall-cmd --direct --remove-rule ipv4 nat POSTROUTING 0 -s 10.8.0.0/24 ! -d 10.8.0.0/24 -j SNAT --to "$ip"
					firewall-cmd --permanent --direct --remove-rule ipv4 nat POSTROUTING 0 -s 10.8.0.0/24 ! -d 10.8.0.0/24 -j SNAT --to "$ip"
					if grep -qs "server-ipv6" /etc/openvpn/server/server.conf; then
						ip6=$(firewall-cmd --direct --get-rules ipv6 nat POSTROUTING | grep '\-s fddd:1194:1194:1194::/64 '"'"'!'"'"' -d fddd:1194:1194:1194::/64' | grep -oE '[^ ]+$')
						firewall-cmd --zone=trusted --remove-source=fddd:1194:1194:1194::/64
						firewall-cmd --permanent --zone=trusted --remove-source=fddd:1194:1194:1194::/64
						firewall-cmd --direct --remove-rule ipv6 nat POSTROUTING 0 -s fddd:1194:1194:1194::/64 ! -d fddd:1194:1194:1194::/64 -j SNAT --to "$ip6"
						firewall-cmd --permanent --direct --remove-rule ipv6 nat POSTROUTING 0 -s fddd:1194:1194:1194::/64 ! -d fddd:1194:1194:1194::/64 -j SNAT --to "$ip6"
					fi
				else
					systemctl disable --now openvpn-iptables.service
					rm -f /etc/systemd/system/openvpn-iptables.service
				fi
				if sestatus 2>/dev/null | grep "Current mode" | grep -q "enforcing" && [[ "$port" != 1194 ]]; then
					semanage port -d -t openvpn_port_t -p "$protocol" "$port"
				fi
				systemctl disable --now openvpn-server@server.service
				rm -rf /etc/openvpn/server
				rm -f /etc/systemd/system/openvpn-server@server.service.d/disable-limitnproc.conf
				rm -f /etc/sysctl.d/30-openvpn-forward.conf
				if [[ "$os" = "debian" || "$os" = "ubuntu" ]]; then
					apt-get remove --purge -y openvpn
				else
					# Else, OS must be CentOS or Fedora
					yum remove -y openvpn
				fi
				echo
				echo "OpenVPN removed!"
			else
				echo
				echo "OpenVPN removal aborted!"
			fi
			exit
		;;
		4)
			exit
		;;
	esac
fi

I was restoring the backup of a MySQL 5.x server into MySQL 8.x server – and found out that it corrupt the MySQL 8.x ‘s mysql table

Which stores the usernames and passwords.

So I had to delete the mysql table from the backup, before trying to restore it again

Turn out it’s pretty easy, just will take some time since it’s a pretty big backup :

# search for beginning of 'mysql' table
cat backup.mysql | grep -n Current Database: `mysql`

# 155604:-- Current Database: `mysql`

# search for ending of 'mysql' table
tail -n +155604 backup.mysql | grep -n "Current Database"

# 1:  -- Current Database: `mysql`
# 916:-- Current Database: `phpmyadmin`

# cut that table out
head -155603 backup.mysql                > new.mysql
tail -n +$(( 155603+916 )) backup.mysql >> new.mysql

# voila !

Panduan Utama : MySQL & Hardwarenya Yang Diperlukan

Beberapa client utama saya adalah pengguna MySQL. Software yang mereka gunakan memanfaatkan MySQL sebagai databasenya dengan intensif, dan dengan beban kerja yang banyak & berat.

Karena itu, semua hal seputar topik performa MySQL sangat menarik perhatian saya.

Secara rutin saya & tim kami melakukan berbagai penelitian seputar kinerja MySQL.
Untuk beban kerja tertentu, bagian sistim yang mana yang menjadi bottleneck ? Bagaimana cara melenyapkan bottleneck tersebut ? Bagaimana meningkatkan response time dari MySQL ? Bagaimana cara meningkatkan kapasitas pemrosesan datanya ? Dan seterusnya.

Karena itu saya cukup terkejut gembira menemukan file presentasi berikut ini — dimana berbagai isinya selaras dengan banyak dari temuan kami selama ini. Maka, Anda akan dapat memanfaatkan informasi di dokumen ini seketika; dan tidak perlu sampai melakukan riset selama bertahun-tahun seperti yang kami lakukan secara rutin:

[ Linux & Hardware Optimizations For MySQL ]

Berikut ini adalah beberapa poin-poin ringkasannya :

• Hardware RAID bisa mengakibatkan performa yang buruk : Tidak setaranya kualitas berbagai produk hardware RAID yang ada jadi bisa menyebabkan justru turunnya performa dibandingkan jika tanpa RAID. Prosesor yang melempem, firmware yang buggy / tidak berkualitas, dst — semua ini dapat menyebabkan penurunan kinerja yang cukup serius. Karena itu sudah cukup lama kami menyarankan untuk melakukan RAID secara software, terutama RAID0 / RAID10.
• Banyak RAM = Kinerja Naik : MySQL dapat memanfaatkan RAM yang ada untuk banyak hal — mengatur agar proses INSERT menjadi sequential ke disk (sangat signifikan untuk harddisk / bukan SSD), “join” buffer, cache, dst.

  Pasang RAM sebanyak-banyaknya di server database Anda, dan lalu atur setting MySQL, agar bisa memanfaatkan RAM yang ada tersebut.

• RAID0 / RAID10 adalah cara termurah untuk meningkatkan kinerja disk
• InnoDB lebih kencang untuk Write-Ops : InnoDB melakukan proses row-level locking pada saat write — dan bukannya table-level locking seperti MyISAM. Pada MyISAM, jika sedang ada proses Write (insert / update / delete) maka seluruh operasi lainnya dihentikan oleh MySQL. Karena itu, pada table MyISAM yang proses Write nya cukup banyak, performanya bisa langsung anjlok dengan sangat drastis.
• SSD SATA lebih lambat daripada SSD PCI-Express : kadang bisa lebih dari 2x lipat.
• BBWC = Battery Backed-up Write Cache : satu kelebihan Hardware RAID adalah BBWC. Dengan BBWC, maka proses Write bisa menjadi jauh lebih cepat. Jangan lupa untuk mematikan dulu write cache di masing-masing disk nya sendiri.

Dan masih ada sangat banyak lagi tips & informasi berguna lainnya, yang sulit untuk ditemukan dimana-mana.
Berbeda dengan berbagai artikel di banyak situs, dokumen ini sangat padat isinya, dan tidak bertele-tele. Straight to the point. Sampai kadang bisa kebingungan sendiri membacanya 🙂

Sangat direkomendasikan untuk mereka yang banyak bersentuhan dengan MySQL.

Untuk melakukan sendiri berbagai proses profiling & optimization nya, saya juga bisa merekomendasikan dokumen ini : [ Performance TUning & Stabilization Tips ]

MySQL Bantai Memcached & NoSQL : 750.000 query / second !

Beberapa tahun terakhir ini kita sering mendengar bagaimana MySQL itu lambat. Bahwa musti pakai Memcached baru bisa agak cepat / meningkat kapasitas (pemrosesan data) nya. Bahwa untuk jumlah query yang sangat banyak, maka musti membuang MySQL dan pindah ke NoSQL.

Yoshinori Matsunobu membuktikan bahwa MySQL sebetulnya amat kencang – dengan Plugin “HandlerSocket”, maka MySQL jadi bisa memproses 750.000 query per detik ! Sementara memcached hanya bisa mencapai 420.000 qps, dan Yoshinori belum berhasil menemukan produk NoSQL yang performanya bisa menyaingi ini.

Secara ringkas – HandlerSocket adalah plugin yang memungkinkan kita untuk mengakses database MySQL secara direct / bypass layer SQL internalnya. Jadinya seperti NoSQL juga, hanya saja ini di MySQL – bukan menggunakan database server lainnya.

HandlerSocket ini, karena berbasis MySQL, jadi memiliki berbagai kelebihan berikut ini dibandingkan dengan software NoSQL biasa :

• Databasenya bisa diakses secara normal : karena mengakses database MySQL, maka datanya tetap bisa diakses secara normal / dengan perintah / query SQL biasa. Contoh: pembuatan laporan, tetap bisa menggunakan query SQL biasa.

  Tidak seperti software NoSQL lainnya; dimana seluruh akses harus berupa perintah NoSQL.

• Crash-safe : HandlerSocket di desain untuk menggunakan database engine InnoDB di MySQL.
• Semua keuntungan operasional MySQL : karena HandlerSocket berjalan di dalam MySQL sebagai plugin, maka semua manfaat MySQL bisa didapatkan — replication, online backup, SQL, internal cache / buffer pool, monitoring, SHOW xxxx STATUS, dst.
• Tidak perlu merubah MySQL : karena berupa plugin, sehingga langsung kompatibel dengan semua versi MySQL.
• Kompatibel dengan semua engine database di MySQL.
• Dll

Siapa sangka ternyata kita tidak perlu pindah ke software NoSQL untuk mendapatkan performa yang tinggi ? Terus gunakan saja MySQL + HandlerSocket plugin.

Penutup

Berbeda dengan mitos & khayalan yang lazim ada, MySQL sebetulnya mampu memberikan kinerja yang sangat tinggi, dengan tetap menjaga integritas data kita.

Dan di luar dugaan, ketakutan saya paska akuisisi MySQL oleh Oracle tidak terjadi. MySQL tidak dibunuh oleh Oracle – tetapi, malah dikembangkan dengan jauh lebih cepat & mengesankan.
MySQL 5.5.x & MySQL Cluster 7.2.x adalah produk-produk MySQL yang paling impresif yang pernah saya temui. InnoDB di MySQL 5.5.x sudah sangat advanced dibandingkan versi sebelumnya. Dan MySQL Cluster 7.2.x akhirnya bisa menyimpan data di disk. Dan banyak kemajuan signifikan lainnya.

Pengetahuan mengenai seluk-beluk MySQL akan memungkinkan kita untuk membuat performanya melesat jauh dari para kompetitornya.

Bahan bacaan lebih lanjut :

• Planet.MySQL.com : Performance
• Forge.MySQL.com : Top SQL Performance Tips
• InnoDB Performance Tuning
• MySQL User Accounting
• Versatile RAID on Linux : using loop device + mdadm

Belum lama ini Oracle telah merilis versi terbaru dari MySQL Cluster, yaitu versi 7.2.x. Ada banyak kelebihan versi terbaru ini, namun yang paling signifikan adalah kemampuannya untuk menyimpan database di disk.

Ya, dulu MySQL Cluster hanya bisa menyimpan data di memory.
Memang ini jadi meningkatkan performa, namun di lain sisi, otomatis jadi sangat beresiko. Jika server mati mendadak, maka seluruh data akan lenyap.

MySQL Cluster versi 7.2.x sudah tidak memiliki limitasi ini lagi. Ditambah dengan berbagai enhancements lainnya, maka saya bisa merekomendasikan versi ini sebagai awal yang baik untuk mulai belajar / menggunakan MySQL Cluster.

Panduan ini akan memungkinkan Anda untuk memasang MySQL Cluster tanpa memerlukan user root. Ini berarti bahwa Anda jadi bisa untuk memasang bahkan banyak MySQL Cluster di satu komputer.
Ini akan sangat berguna misalnya untuk melakukan riset / ujicoba.

Seri BOSCA = Belajar Open Source Cara copAs (copy-paste) :: artikel ini saya tuliskan sedemikian rupa, sehingga Anda tinggal copas / copy-paste saja ke Terminal / Console.
Sehingga hasilnya langsung bisa dinikmati.

Saya percaya bahwa learning by doing adalah cara belajar yang terbaik. Pengetahuan & pemahaman akan otomatis didapatkan dengan langsung praktek / melakukan.
Seri artikel BOSCA bertujuan untuk membantu Anda agar dapat segera mulai melakukan praktek, dengan usaha yang seminimal mungkin.

Selamat menikmati :

### perlu install library AIO (Asynchronous I/O) di beberapa distro
sudo apt-get install libaio1

mkdir $HOME/mysql-cluster
cd  $HOME/mysql-cluster

### Download dari http://www.mysql.com/downloads/cluster/
### Pilih "Linux - Generic 2.6 (x86, 64-bit), Compressed TAR Archive"
wget -c http://mysql.ntu.edu.tw/Downloads/MySQL-Cluster-7.2/mysql-cluster-gpl-7.2.5-linux2.6-x86_64.tar.gz

tar xzvf mysql-cluster-gpl-7.2.5-linux2.6-x86_64.tar.gz

ln -s mysql-cluster-gpl-7.2.5-linux2.6-x86_64 mysqlc
mkdir my_cluster my_cluster/ndb_data my_cluster/mysqld_data my_cluster/conf

### buat beberapa direktori untuk file PID, socket, dan log
mkdir $HOME/mysql-cluster/mysqlc/var
mkdir $HOME/mysql-cluster/mysqlc/var/log
mkdir $HOME/mysql-cluster/mysqlc/var/run
chown -R $USER:$USER $HOME/mysql-cluster/mysqlc/var

########## membuat file-file konfigurasi
### buat file $HOME/mysql-cluster/my_cluster/conf/my.cnf
echo "[mysqld]" >> $HOME/mysql-cluster/my_cluster/conf/my.cnf
echo "ndbcluster" >> $HOME/mysql-cluster/my_cluster/conf/my.cnf
echo "datadir=$HOME/mysql-cluster/my_cluster/mysqld_data" >> $HOME/mysql-cluster/my_cluster/conf/my.cnf
echo "basedir=$HOME/mysql-cluster/mysqlc" >> $HOME/mysql-cluster/my_cluster/conf/my.cnf
echo "port=5000" >> $HOME/mysql-cluster/my_cluster/conf/my.cnf

### buat file $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "[ndb_mgmd]" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "hostname=localhost" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "datadir=$HOME/mysql-cluster/my_cluster/ndb_data" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "NodeId=1" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "[ndbd default]" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "noofreplicas=2" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "datadir=$HOME/mysql-cluster/my_cluster/ndb_data" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "[ndbd]" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "hostname=localhost" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "NodeId=3" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "[ndbd]" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "hostname=localhost" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "NodeId=4" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "[mysqld]" >> $HOME/mysql-cluster/my_cluster/conf/config.ini
echo "NodeId=50" >> $HOME/mysql-cluster/my_cluster/conf/config.ini


################ inisialisasi
### Inisialisasi database
cd $HOME/mysql-cluster/mysqlc
$HOME/mysql-cluster/mysqlc/scripts/mysql_install_db --no-defaults --datadir=$HOME/mysql-cluster/my_cluster/mysqld_data/

### menjalankan MySQL Cluster untuk pertama kali
cd ../my_cluster/
$HOME/mysql-cluster/mysqlc/bin/ndb_mgmd -f $HOME/mysql-cluster/my_cluster/conf/config.ini --initial --configdir=$HOME/mysql-cluster/my_cluster/conf/

$HOME/mysql-cluster/mysqlc/bin/ndbd -c localhost:1186
$HOME/mysql-cluster/mysqlc/bin/ndbd -c localhost:1186

$HOME/mysql-cluster/mysqlc/bin/mysqld --defaults-file=$HOME/mysql-cluster/my_cluster/conf/my.cnf --socket=$HOME/mysql-cluster/mysqlc/var/run/mysql.sock --pid-file=$HOME/mysql-cluster/mysqlc/var/run/mysql.pid&


### tunggu sekitar 30 detik, MySQL CLuster sedang launch
### lalu kita bisa cek statusnya :
$HOME/mysql-cluster/mysqlc/bin/ndb_mgm -e show

### terlampir cara untuk membuat table dengan engine NDB / MySQL Cluster
$HOME/mysql-cluster/mysqlc/bin/mysql -h 127.0.0.1 -P 5000 -u root
####### perintah-perintah berikut ini di copy-paste di prompt mysql> #########
create database clusterdb;use clusterdb;
create table simples (id int not null primary key) engine=ndb;
insert into simples values (1),(2),(3),(4);
select * from simples;
exit


### shutdown
$HOME/mysql-cluster/mysqlc/bin/mysqladmin -u root -h 127.0.0.1 -P 5000 shutdown
$HOME/mysql-cluster/mysqlc/bin/ndb_mgm -e shutdown


### buat script startup
echo '#!/bin/bash' >> $HOME/mysql-cluster/startup-cluster.sh
echo "$HOME/mysql-cluster/mysqlc/bin/ndb_mgmd -f $HOME/mysql-cluster/my_cluster/conf/config.ini --initial --configdir=$HOME/mysql-cluster/my_cluster/conf/" >> $HOME/mysql-cluster/startup-cluster.sh

echo "$HOME/mysql-cluster/mysqlc/bin/ndbd -c localhost:1186" >> $HOME/mysql-cluster/startup-cluster.sh
echo "$HOME/mysql-cluster/mysqlc/bin/ndbd -c localhost:1186" >> $HOME/mysql-cluster/startup-cluster.sh

echo "$HOME/mysql-cluster/mysqlc/bin/mysqld --defaults-file=$HOME/mysql-cluster/my_cluster/conf/my.cnf --socket=$HOME/mysql-cluster/mysqlc/var/run/mysql.sock --pid-file=$HOME/mysql-cluster/mysqlc/var/run/mysql.pid&" >> $HOME/mysql-cluster/startup-cluster.sh

chmod 755 $HOME/mysql-cluster/startup-cluster.sh


### buat script shutdown
echo '#!/bin/bash' >> $HOME/mysql-cluster/shutdown-cluster.sh
echo "$HOME/mysql-cluster/mysqlc/bin/mysqladmin -u root -h 127.0.0.1 -P 5000 shutdown" >> $HOME/mysql-cluster/shutdown-cluster.sh
echo "$HOME/mysql-cluster/mysqlc/bin/ndb_mgm -e shutdown" >> $HOME/mysql-cluster/shutdown-cluster.sh
chmod 755  $HOME/mysql-cluster/shutdown-cluster.sh


### untuk startup / shutdown MySQL Cluster, cukup ketik :
$HOME/mysql-cluster/startup-cluster.sh
$HOME/mysql-cluster/shutdown-cluster.sh


### Enjoy !

Panduan ini akan memungkinkan Anda untuk memasang MySQL Server tanpa memerlukan user root. Ini berarti bahwa Anda jadi bisa untuk memasang bahkan banyak MySQL Cluster di satu komputer. Ini akan sangat berguna misalnya untuk melakukan riset / ujicoba.

Memasang banyak MySQL Server di satu komputer juga berguna untuk meningkatkan concurrent throughput – pada jumlah koneksi / user yang banyak, kadang MySQL Server nya sendiri yang menjadi bottleneck.
Memasang banyak MySQL Server di komputer tersebut, dengan masing-masing listening di Port yang berbeda, bisa menjadi salah satu alternatif solusinya.

Seri BOSCA = Belajar Open Source Cara copAs (copy-paste) :: artikel ini saya tuliskan sedemikian rupa, sehingga Anda tinggal copas / copy-paste saja ke Terminal / Console.
Sehingga hasilnya langsung bisa dinikmati.

Saya percaya bahwa learning by doing adalah cara belajar yang terbaik. Pengetahuan & pemahaman akan otomatis didapatkan dengan langsung praktek / melakukan.
Seri artikel BOSCA bertujuan untuk membantu Anda agar dapat segera mulai melakukan praktek, dengan usaha yang seminimal mungkin.

Selamat menikmati :

### perlu install library AIO (Asynchronous I/O) di beberapa distro
sudo apt-get install libaio1

mkdir $HOME/mysql-cluster
mkdir $HOME/mysql-cluster/server1
cd  $HOME/mysql-cluster/server1

### Download dari http://www.mysql.com/downloads/mysql/
### Pilih "Linux - Generic 2.6 (x86, 64-bit), Compressed TAR Archive"
wget -c --timeout=10 --tries=0 http://ftp.ntu.edu.tw/pub2/MySQL/Downloads/MySQL-5.5/mysql-5.5.22-linux2.6-x86_64.tar.gz

tar xzvf mysql-5.5.22-linux2.6-x86_64.tar.gz
ln -s mysql-5.5.22-linux2.6-x86_64 mysql

### buat beberapa direktori untuk file PID, socket, dan log
mkdir $HOME/mysql-cluster/server1/mysql/etc
mkdir $HOME/mysql-cluster/server1/mysql/var
mkdir $HOME/mysql-cluster/server1/mysql/var/log
mkdir $HOME/mysql-cluster/server1/mysql/var/run
chown -R $USER:$USER $HOME/mysql-cluster/server1/mysql/var
chown -R $USER:$USER $HOME/mysql-cluster/server1/mysql/etc

################# menyiapkan file konfigurasi
cp $HOME/mysql-cluster/server1/mysql/support-files/config.medium.ini $HOME/mysql-cluster/server1/mysql/etc/my.cnf

################ inisialisasi database
cd $HOME/mysql-cluster/server1/mysql
$HOME/mysql-cluster/server1/mysql/scripts/mysql_install_db --user=$USER --defaults-file=$HOME/mysql-cluster/server1/mysql/etc/my.cnf --basedir=$HOME/mysql-cluster/server1/mysql/  --datadir=$HOME/mysql-cluster/server1/mysql/data/

###### start database
$HOME/mysql-cluster/server1/mysql/bin/mysqld_safe --defaults-file=$HOME/mysql-cluster/server1/mysql/etc/my.cnf  --user=$USER  --basedir=$HOME/mysql-cluster/server1/mysql/  --datadir=$HOME/mysql-cluster/server1/mysql/data/  --socket=$HOME/mysql-cluster/server1/mysql/var/run/mysql.sock --pid-file=$HOME/mysql-cluster/server1/mysql/var/run/mysql.pid --port=9001 &

##### akses database
$HOME/mysql-cluster/server1/mysql/bin/mysql -u root -h 127.0.0.1 -P 9001 

##### shutdown database
$HOME/mysql-cluster/server1/mysql/bin/mysqladmin -u root -h 127.0.0.1 -P 9001 shutdown

### buat script startup
echo '#!/bin/bash' >> $HOME/mysql-cluster/server1/startup-server1.sh
echo "$HOME/mysql-cluster/server1/mysql/bin/mysqld_safe --defaults-file=$HOME/mysql-cluster/server1/mysql/etc/my.cnf  --user=$USER  --basedir=$HOME/mysql-cluster/server1/mysql/  --datadir=$HOME/mysql-cluster/server1/mysql/data/  --socket=$HOME/mysql-cluster/server1/mysql/var/run/mysql.sock --pid-file=$HOME/mysql-cluster/server1/mysql/var/run/mysql.pid --port=9001 &" >> $HOME/mysql-cluster/server1/startup-server1.sh
chmod 755 $HOME/mysql-cluster/server1/startup-server1.sh


### buat script shutdown
echo '#!/bin/bash' >> $HOME/mysql-cluster/server1/shutdown-server1.sh
echo "$HOME/mysql-cluster/server1/mysql/bin/mysqladmin -u root -h 127.0.0.1 -P 9001 shutdown"  >> $HOME/mysql-cluster/server1/shutdown-server1.sh
chmod 755  $HOME/mysql-cluster/server1/shutdown-server1.sh


### untuk startup / shutdown MySQL Cluster, cukup ketik :
$HOME/mysql-cluster/server1/startup-server1.sh
$HOME/mysql-cluster/server1/shutdown-server1.sh


### Enjoy !

This is the #2 article in the backup topics, check out the first one : MySQL Archived Backup

Nowadays, nearly everyone / every company have a website of sorts. What most of us forgot is to back them up, in case of problems.
And as Murphy’s Law stated :

Backup of your data can save you from such occasion.

An archived-backup can help you further – should you need to retrieve certain version of your data, then you can. Because it saves multiple copies of your data, each from certain point of time.

A way to do this is by using tools like the excellent rdiff-backup. In short – it’s like Apple’s Time Machine, but can be easily scripted & automated.

For other cases, you may need to create your own script for it.

Below is a script we use to backup our customer’s website.
It will backup both their MySQL database and the actual website itself.

Note that the backup script will archive for 2 weeks.
If you need diffent archive period, please feel free to modify the script, following the example in this article.

Here’s the script : backup-website.sh


#!/bin/bash

Enjoy 🙂

I regard backup as a very important matter. Because one of the certainties in life is this :

However, with so many computers in our care, we need a way to make it automatic. Otherwise, it will simply take too much time.

Thankfully, this is very easy to do in UNIX / Linux computers.

We have several kind of backup scripts. All of them are quite simple, but essential. Nobody should be without them.

Below you can find a backup script we use to backup MySQL database for our clients. Basically, this is how we use it :

1. Modify the script to archive for the time range that we’d like.
   The script can archive the backup from 1 week to unlimited. Just remark out the time range that you don’t need (by putting the “#” character in the first column of the particular row).
2. Make sure we can run mysqldump automatically / with no user intervention.
   This can be done easily by creating a file name ~/.my.cnf
3. Setup the backup to run automatically via cron
4. Routinely backup the backup to other servers.
   Yes, there is no such thing as “too much backup”.

Without further ado, here is the script : backup-archive-mysql.sh


#!/bin/bash

### No Archive
### WARNING: always monitor your free disk space, or the following line may consume them all
$bmysqldump -u $userdb $database > $targetdir/$targetfile-`date +%Y%m%d`$targetsuffix


Here is a sample of ~/.my.cnf; if this file exist, then various MySQL tools & software will be able to run with no intervention.
Of course you’ll need to adjust its user & password :


[client]
user=root
password=mypassword


Hope you’ll find this useful.
I’ll post our other scripts here from time to time as well. Stay tuned.

That was quite scary – I almost bricked (read: killed!) my phone this morning, when I tried to upgrade it to Gingerbread (Android version 2.3.x).

To cut long story short – tried to install / “flash” Gingerbread ROM into my Samsung Galaxy Mini. There are many excellent tutorials that shows how easy to do this, for example :

# Upgrade Samsung Galaxy Mini and Root to Android Gingerbread 2.3.3
# Samsung Galaxy Mini S5570XXKPI Android 2.3.4 Gingerbread
# And many others.

However, in my case, the process got stuck at the following text :

<1> START!!!
<1> set download environment.
<1> big partition size!!
<1> big partition size!!
<1> big partition size!!
<1> big partition size!!
<1> big partition size!!
<1> big partition size!!
<1> mibib download..

I was quite rightly scared at this point, thinking “dang, that’s it – my phone is bricked”.

Thankfully, that was not the case 😀 after MUCH googling around, I was able to finally upgrade my Galaxy Mini by doing the following :

DISCLAIMER: I shared this info with the intention to help others. However, I offer NO guarantee whatsoever that it will work for you as well.
Be aware that there is always a chance that your phone will end up bricked. Flashing ROM is not yet a 100% safe operation.

Now let’s continue, shall we ?

(1) Find another computer which has NOT Kies installed in it. (Kies is the software provided along with Samsung phones).
Looks like somehow that Kies is interfering with Odin / flashing operation on our Galaxy Mini. NOT good. Bad, bad Kies !

(2) Install Samsung USB driver there. If you haven’t got it, you can download it from here : [ Samsung USB driver ] (18 MB)

(3) Now, start following one of the tutorials listed above.

Now my Galaxy Mini is running Gingerbread 2.3.4 😀 yeah !

A bit more info – this is what you’ll get in Odin’s Message Box when everything’s going okay :

Download Start…
<0> Create File…
<1> StartThread Detected : 1
<2> StartThread Detected : 0
<3> StartThread Detected : 0
<4> StartThread Detected : 0
<5> StartThread Detected : 0
<6> StartThread Detected : 0
<7> StartThread Detected : 0
<8> StartThread Detected : 0
<1> setup connection…
<1> odin mode.. check plcatform verification.
<1> VerifyPlatform:V0, P0,R0
<1> START!!!
<1> set download environment.
<1> big partition size!!
<1> big partition size!!
<1> big partition size!!
<1> big partition size!!
<1> big partition size!!
<1> big partition size!!
<1> mibib download..
<1> 1/10 Finished.
<1> qcsbl download..
<1> 2/10 Finished.
<1> oemsbl download..
<1> 3/10 Finished.
<1> amss download..
<1> 4/10 Finished.
<1> arm11boot download..
<1> 5/10 Finished.
<1> boot.img download..
<1> 6/10 Finished.
<1> recovery.img download..
<1> 7/10 Finished.
<1> system.rfs download..
<1> 8/10 Finished.
<1> data.rfs download..
<1> 9/10 Finished.
<1> csc.rfs download..
<1> 10/10 Finished.
<1> reset pda..
<0> Started Timer
<1> Close serial port and wait until rebooting.
<1> PASS!!!
<0> Destroy instant..
<0> Killed timer

So that’s how I did it. Hopefully it’ll help someone else out there 🙂
Enjoy !