Alpine Linux - User contributions [en]

Alpine production deployment

2025-03-11T23:48:16Z

Greenman: /* DataBases */ copyedit

{{style|This reads more like an essay than a useful tutorial or advice}}
The production environment is the final destination of all development results. Almost always when someone does some kind of development, no matter how small it is (for example a simple script), the development environment is not the only place or system where it will be exclusively used (for example the script could be used on other computers in the network).

The development environment is never the same as the production environment, commonly, production environment only has necessary software to run the installed and deployment products. In professional Linux are no graphical tools and everything is minimalist.. just like Alpine Linux.

= Servers and Docker containers =

The most famous cases in old days was use a CentOS environment, but over time, Linux became more heavy and more slow. That changes with Alpine and Docker, if you think Red Hat and CentOS are the best here an article that describes in short the real life of those systems: https://gitea.com/venenux/venenux/src/branch/master/docs/others-infodocs-details-centos-vs-fedora-en.md , Both projects are Red Hat related, one are ahead of and the other behind of, main difference from others distributions it’s their lack of packages and setups, due the shared market focused target of both.. obviously due the RedHAt relationship.

But today all of this changed, now there's Docker: a helpful tool for packaging, shipping, and running applications within "containers" (like virtual machines) that remove the need for physical hardware, allowing for more efficient use of computing resources, in terms of energy consumption and cost effectiveness. Today even the big dogs like Google, VMware and Amazon are building services to support it.

The server are mostly a very powerful machine in production environments, but think about it! a 500GB disk to only run a DNS for a local network? that's the reason of the Dockers and their relationship with the servers.

The one big difference between containers and a Server is that containers *share* the host server system’s kernel and server resources with other containers in a isolated environment.

== Server focused documents ==

TODO: webpack, npm (advanced.. cos is includen below in lamp), bigdata, Dockers focused well made tutorial

=== Web deploy: LAMP ===

In production web, LAMP means '''L'''inux + '''A'''pache + '''M'''ysql + '''P'''hp installed and integrated, but today the "A" of apache is more used as Nginx or Lighttpd, and the "M" of MySQL is more used as [[MariaDB]]. The LAMP focused documents are:

* LAMP deploy of the Web Server: [[Production Web server: Lighttpd]]
* LAMP deploy of the Web Server with PHP, user html_dir and [[MariaDB]]: [[MySQL]]
* Deploy usage of Lets Encrypt without chain-tools – just add water: [[Production Lets Encrypt: dehydrated]]

=== Web deploy: LUA ===

WIP

== Databases ==

* SQlite, most easy to use database system without engine: [[Debelovers : sqlite]]
* UnixODBC, how to connect with multiple databases: [[Production DataBases : unixodbc]]
* MySQL/MariaDB deployment: [[MariaDB]]/[[MySQL]]

== Networking ==

* Dual stack (ipv4 to ipv6) DNS with chain slave, just only for brave linux sysadmin: : [[Production DNS: dual stack Bind server]]
* [[Cacti: traffic analysis and monitoring network]]

== Docker focused documents ==

WIP

[[Category:Newbie]]
[[Category:Server]]
[[Category:PHP]]
[[Category:Monitoring]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

Alpine production deployment

2025-03-11T23:48:07Z

Greenman: /* DataBases */ link

{{style|This reads more like an essay than a useful tutorial or advice}}
The production environment is the final destination of all development results. Almost always when someone does some kind of development, no matter how small it is (for example a simple script), the development environment is not the only place or system where it will be exclusively used (for example the script could be used on other computers in the network).

The development environment is never the same as the production environment, commonly, production environment only has necessary software to run the installed and deployment products. In professional Linux are no graphical tools and everything is minimalist.. just like Alpine Linux.

= Servers and Docker containers =

The most famous cases in old days was use a CentOS environment, but over time, Linux became more heavy and more slow. That changes with Alpine and Docker, if you think Red Hat and CentOS are the best here an article that describes in short the real life of those systems: https://gitea.com/venenux/venenux/src/branch/master/docs/others-infodocs-details-centos-vs-fedora-en.md , Both projects are Red Hat related, one are ahead of and the other behind of, main difference from others distributions it’s their lack of packages and setups, due the shared market focused target of both.. obviously due the RedHAt relationship.

But today all of this changed, now there's Docker: a helpful tool for packaging, shipping, and running applications within "containers" (like virtual machines) that remove the need for physical hardware, allowing for more efficient use of computing resources, in terms of energy consumption and cost effectiveness. Today even the big dogs like Google, VMware and Amazon are building services to support it.

The server are mostly a very powerful machine in production environments, but think about it! a 500GB disk to only run a DNS for a local network? that's the reason of the Dockers and their relationship with the servers.

The one big difference between containers and a Server is that containers *share* the host server system’s kernel and server resources with other containers in a isolated environment.

== Server focused documents ==

TODO: webpack, npm (advanced.. cos is includen below in lamp), bigdata, Dockers focused well made tutorial

=== Web deploy: LAMP ===

In production web, LAMP means '''L'''inux + '''A'''pache + '''M'''ysql + '''P'''hp installed and integrated, but today the "A" of apache is more used as Nginx or Lighttpd, and the "M" of MySQL is more used as [[MariaDB]]. The LAMP focused documents are:

* LAMP deploy of the Web Server: [[Production Web server: Lighttpd]]
* LAMP deploy of the Web Server with PHP, user html_dir and [[MariaDB]]: [[MySQL]]
* Deploy usage of Lets Encrypt without chain-tools – just add water: [[Production Lets Encrypt: dehydrated]]

=== Web deploy: LUA ===

WIP

== DataBases ==

* SQlite, most easy to use database system without engine: [[Debelovers : sqlite]]
* UnixODBC, how to connect with multiple databases: [[Production DataBases : unixodbc]]
* MySQL/MariaDB deployment: [[MariaDB]]/[[MySQL]]

== Networking ==

* Dual stack (ipv4 to ipv6) DNS with chain slave, just only for brave linux sysadmin: : [[Production DNS: dual stack Bind server]]
* [[Cacti: traffic analysis and monitoring network]]

== Docker focused documents ==

WIP

[[Category:Newbie]]
[[Category:Server]]
[[Category:PHP]]
[[Category:Monitoring]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

Alpine production deployment

2025-03-11T23:47:41Z

Greenman: /* Web deploy: LAMP */links

{{style|This reads more like an essay than a useful tutorial or advice}}
The production environment is the final destination of all development results. Almost always when someone does some kind of development, no matter how small it is (for example a simple script), the development environment is not the only place or system where it will be exclusively used (for example the script could be used on other computers in the network).

The development environment is never the same as the production environment, commonly, production environment only has necessary software to run the installed and deployment products. In professional Linux are no graphical tools and everything is minimalist.. just like Alpine Linux.

= Servers and Docker containers =

The most famous cases in old days was use a CentOS environment, but over time, Linux became more heavy and more slow. That changes with Alpine and Docker, if you think Red Hat and CentOS are the best here an article that describes in short the real life of those systems: https://gitea.com/venenux/venenux/src/branch/master/docs/others-infodocs-details-centos-vs-fedora-en.md , Both projects are Red Hat related, one are ahead of and the other behind of, main difference from others distributions it’s their lack of packages and setups, due the shared market focused target of both.. obviously due the RedHAt relationship.

But today all of this changed, now there's Docker: a helpful tool for packaging, shipping, and running applications within "containers" (like virtual machines) that remove the need for physical hardware, allowing for more efficient use of computing resources, in terms of energy consumption and cost effectiveness. Today even the big dogs like Google, VMware and Amazon are building services to support it.

The server are mostly a very powerful machine in production environments, but think about it! a 500GB disk to only run a DNS for a local network? that's the reason of the Dockers and their relationship with the servers.

The one big difference between containers and a Server is that containers *share* the host server system’s kernel and server resources with other containers in a isolated environment.

== Server focused documents ==

TODO: webpack, npm (advanced.. cos is includen below in lamp), bigdata, Dockers focused well made tutorial

=== Web deploy: LAMP ===

In production web, LAMP means '''L'''inux + '''A'''pache + '''M'''ysql + '''P'''hp installed and integrated, but today the "A" of apache is more used as Nginx or Lighttpd, and the "M" of MySQL is more used as [[MariaDB]]. The LAMP focused documents are:

* LAMP deploy of the Web Server: [[Production Web server: Lighttpd]]
* LAMP deploy of the Web Server with PHP, user html_dir and [[MariaDB]]: [[MySQL]]
* Deploy usage of Lets Encrypt without chain-tools – just add water: [[Production Lets Encrypt: dehydrated]]

=== Web deploy: LUA ===

WIP

== DataBases ==

* SQlite, most easy to use database system without engine: [[Debelovers : sqlite]]
* UnixODBC, how to connect with multiple databases: [[Production DataBases : unixodbc]]
* MySQL/MariaDB deployment: [[MySQL]]

== Networking ==

* Dual stack (ipv4 to ipv6) DNS with chain slave, just only for brave linux sysadmin: : [[Production DNS: dual stack Bind server]]
* [[Cacti: traffic analysis and monitoring network]]

== Docker focused documents ==

WIP

[[Category:Newbie]]
[[Category:Server]]
[[Category:PHP]]
[[Category:Monitoring]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

Tutorials and Howtos

2025-03-11T23:46:51Z

Greenman: /* HTTP and web services */ MariaDB

[[Image:package_edutainment.svg|right|link=]]
{{TOC left}}

'''Welcome to Tutorials and Howtos, a place of basic and advanced configuration tasks for your Alpine Linux.'''

'''Howtos are smaller articles''' explaining how to perform a particular task with Alpine Linux, that expects a minimal knowledge from reader to perform actions. Howto's have been organized in the below page based on the topics.

'''The [[#Tutorials|tutorials]] are hands-on''' and the reader is expected to try and achieve the goals described in each step, possibly with the help of a good example. The output in one step is the starting point for the following step.

{{Note|
* Contributors are requested to refer to [[Help:Editing]] first and make use of resources like [[How to write a HOWTO]].
* Contributions must be complete articles.
* Don't override already made contributions, unless there is a mistake.
* If you want to request a topic, please add your request in this page's [[Talk:Tutorials_and_Howtos|Discussion]].}}

== Desktop ==

* {{:Daily driver guide}}
=== Power management ===

* [[Configure action when power-button is pressed]]
* [[Suspend on LID close]]
* [[Configure Wake-on-LAN]]

=== Networking ===

* [[Bluetooth]] - Instructions for installing and configuring Bluetooth
* [[Bonding]] - Bond (or aggregate) multiple ethernet interfaces
* [[Bridge]] - Configuring a network bridge
** [[Bridge wlan0 to eth0]]
* [[Configure Networking]]
* [[How to configure static routes]]
* Modem
** [[Using HSDPA modem]]
** [[Using serial modem]]
* [[mDNS]] - Howto implement multicast DNS resolution in Alpine.
* [[Multi ISP]] ''(Dual-ISP setup with load-balancing and automatic failover)''
* [[PXE boot]]
* Wi-Fi
** [[Wi-Fi|Connecting to a wireless access point]]
** [[How to setup a wireless access point]] ''(Setting up Secure Wireless AP w/ WPA encryption with bridge to wired network)''
* [[VLAN]]

=== Security ===

* [[Securing Alpine Linux]] How to Secure Alpine Linux using Security Technical Implementation Guides (STIGs)
* Understand [[UEFI]] and enable [[UEFI Secure Boot]]

=== Backup and data migration ===

* [[Migrating data]]
* [[Rsnapshot]] - setting up periodic backups

=== Other topics ===

* [[Gaming on Alpine]]
* [[Remote Desktop Server]]
* [[Default applications|How to change default application]]
* [[CPU frequency scaling]]
* [[Mimalloc]]
* [[Enable Serial Console on Boot]]
* [[How to build the Alpine Linux kernel]]
* [[Nextcloud]] ''(Self hostable cloud suite - Dropbox Alternative)''
* [[Setting up lm_sensors]]
* [[Desktop environments and Window managers|List of supported Desktop environments and Window managers]]

== Diskless ==

* [[Alpine local backup|Alpine local backup (lbu)]] ''(Permanently store your modifications in case your box needs reboot)''
** [[Back Up a Flash Memory Installation]]
** [[Manually editing a existing apkovl]]

== Other Architectures ==

=== ARM ===

* [[Alpine on ARM]]

==== Raspberry Pi ====

* [[Raspberry Pi|Raspberry Pi main page]]
* [[Raspberry Pi Bluetooth Speaker|Raspberry Pi - Bluetooth Speaker]]
* [[Linux Router with VPN on a Raspberry Pi|Raspberry Pi - Router with VPN]]
* [[Linux Router with VPN on a Raspberry Pi (IPv6)|Raspberry Pi - Router with VPN (IPv6)]]
* [[Classic install or sys mode on Raspberry Pi|Raspberry Pi - Sys mode install]]
* [[Raspberry Pi LVM on LUKS|Raspberry Pi - Sys mode install - LVM on LUKS]]
* [[RPI Video Receiver|Raspberry Pi - Video Receiver]] ''(network video decoder using Rasperry Pi and omxplayer)''
* [[Raspberry Pi 3 - Browser Client]] - kiosk or digital sign
* [[Raspberry Pi 3 - Configuring it as wireless access point -AP Mode]]
* [[Raspberry Pi 3 - Setting Up Bluetooth]]
* [[Raspberry Pi 4 - Persistent system acting as a NAS and Time Machine]]
* [[How to set up Alpine as a wireless router|Raspberry Pi Zero W - Wireless router]] ''(Setting up a firewalled, Wireless AP with wired network on a Pi Zero W)''
* [[RPI Video Receiver]]

=== IBM Z (IBM z Systems) ===

* [[s390x|s390x - Installation]]

=== PowerPC ===

* [[Ppc64le|Powerpc64le - Installation]]

== Services ==

{{Note| Services are arranged in alphabetical order.}}

=== Content management systems ===

* [[DokuWiki]]
* [[Drupal]] ''(Content Management System (CMS) written in PHP)''
* [[Kopano]] ''(Microsoft Outlook compatible Groupware)''
* [[Mahara]] ''(E-portfolio and social networking system)''
* [[MediaWiki]] ''(Free web-based wiki software application)''
* [[Pastebin]] ''(Pastebin software application)''
* [[WordPress]] ''(Web software to create website or blog)''
* [[Moodle]] ''(Online Learning Management system)''

=== Database ===

* [[MariaDB]] or [[MySQL|MySQL]]

=== DNS ===

* [[DNSCrypt-Proxy]] ''Encrypt and authenticate DNS calls from your system''
* [[Setting up nsd DNS server]]
* [[Small-Time DNS with BIND9]] ''(A simple configuration with ad blocking for your home network)''
* Unbound
** [[Setting up unbound DNS server]]
** [[Using Unbound as an Ad-blocker]] ''(Setup ad blocking for your network)''
* [[TinyDNS Format]]

=== File server ===

* [[Setting up an NFS server|nfs-server]]
* [[Setting up a Samba server|samba-server]] ''(standard file sharing)''
* [[Setting up a samba-ad-dc|samba-ad-dc]] ''(Active Directory compatible domain controller)''

=== Firewall and VPN ===

* Alpine Wall ''(a new firewall management framework)''
** [[Alpine Wall]]
** [https://git.alpinelinux.org/awall/about/ Alpine Wall User's Guide]
** [[How-To Alpine Wall]]
* [[Freeradius Active Directory Integration]]
* [[GNUnet]]
* [[Setting up a OpenVPN server|OpenVPN server]] ''(Allowing single users or devices to remotely connect to your network)''
* [[OpenVSwitch]]
* [[Using Alpine on Windows domain with IPSEC isolation]]
* [[Configure a Wireguard interface (wg)|Wireguard]]
* [[IGMPproxy]]

=== HTTP and web services ===

* [[Apache]]
** [[Apache with php-fpm]]
** [[Setting Up Apache with PHP]]
** [[Apache authentication: NTLM Single Signon]]
* [[Darkhttpd]]
* [[Lighttpd]]
** [[Lighttpd Advanced security]]
** [[Setting Up Lighttpd With FastCGI]]
** [[Production Web server: Lighttpd|Production web server: Lighttpd‎‎]]
* [[Nginx]]
** [[Nginx as reverse proxy with acme (letsencrypt)]]
** [[Nginx with PHP]]
* Squid Proxy
** [[Obtaining user information via SNMP]] ''(Using squark-auth-snmp as a Squid authentication helper)'' 
** [[Setting up Explicit Squid Proxy]]
** [[Setting up Transparent Squid Proxy]] ''(Covers Squid proxy and URL Filtering system)''
** [[SqStat]] ''(Script to look at active squid users connections)''
* [[Tomcat]]
** [[Production LAMP system: Lighttpd + PHP + MySQL‎‎|Production LAMP system: Lighttpd + PHP + MariaDB/MySQL‎‎]]

=== IRC ===

* [[NgIRCd]] ''(Server for Internet Relay Chat/IRC)''

=== Mail ===

* [[Hosting services on Alpine]] ''(Hosting mail, webservices and other services)''
* [[Hosting Web/Email services on Alpine]]
* Exim/Dovecot
** [[Small-Time Email with Exim and Dovecot]] ''(A simple configuration for your home network.)
** [[Setting up dovecot with imap and tls]]
* [[relay email to gmail (msmtp, mailx, sendmail]]
* [[relay email (nullmailer)]]
* [[Roundcube]] ''(Webmail system)''
* [[Setting up postfix with virtual domains]]
* Server protection
** [[Setting up clamsmtp]]

=== Monitoring ===

* [[Awstats]] ''(Free log file analyzer)''
* [[Cacti: traffic analysis and monitoring network]] ''(Front-end for rrdtool networking monitor)''
* [[Cvechecker]] ''(Compare installed packages for Common Vulnerabilities Exposure)'' 
* [[Linfo]]
* [[Obtaining user information via SNMP]] ''(Using squark-auth-snmp as a Squid authentication helper)'' 
* [[PhpSysInfo]] ''(A simple application that displays information about the host it's running on)''
* [[Logcheck]] ''(log file monitoring tool)''
* [[Matomo]] ''(A real time web analytics software program)''
* [[Rasdaemon]] ''(Platform Reliability, Availability and Serviceability monitoring tool)''
* [[Setting up A Network Monitoring and Inventory System]] ''(Nagios + OpenAudit and related components)'' 
** [[Setting up NRPE daemon]] ''(Performs remote Nagios checks)'' 
* [[Setting Up Fprobe And Ntop|Ntop]] ''(NetFlow collection and analysis using a remote fprobe instance; for alpine 3.10-3.12 only)'' 
* [[SqStat]] ''(Script to look at active squid users connections)''
* [[Traffic monitoring]] 
** [[Setting up monitoring using rrdtool (and rrdcollect)]]
** [[Setting up traffic monitoring using rrdtool (and snmp)]] 
* [[Zabbix|Zabbix - the professional complete manager]] ''(Monitor and track the status of network services and hardware)''
* [[ZoneMinder video camera security and surveillance]]

=== Remote Administration ===

* ACF
** [[Changing passwords for ACF|ACF - changing passwords]]
** [[Generating SSL certs with ACF]] 
** [[setup-acf| ACF - setup]] ''(Configures ACF (webconfiguration/webmin) so you can manage your box through https)''
* [[Setting up a SSH server]] ''(Using ssh is a good way to administer your box remotely)''
** [[HOWTO OpenSSH 2FA with password and Google Authenticator |OpenSSH 2FA]] ''(A simple two factor setup for OpenSSH)''
* [[OpenVCP]] ''(VServer Control Panel)''
* [[PhpMyAdmin]] ''(Web-based administration tool for MYSQL)''
* [[PhpPgAdmin]] ''(Web-based administration tool for PostgreSQL)''
* [[Webmin]] ''(A web-based interface for Linux system)''

=== Telephony ===

* [[FreePBX|FreePBX on Alpine Linux]]
* [[Setting up Zaptel/Asterisk on Alpine]]
* [[Kamailio]] ''(SIP Server, formerly OpenSER)''

=== Other Servers ===

* [[apcupsd]] ''(UPS Monitoring with apcupsd)''
* [[Chrony and GPSD | Chrony, gpsd, and a garmin LVC 18 as a Stratum 1 NTP source ]]
* [[Glpi]] ''(Manage inventory of technical resources)''
* [[How to setup a Alpine Linux mirror]]
* [[nut-ups|NUT UPS]] ''(UPS Monitoring with Network UPS Tools)''
* [[Odoo]]
* [[Configure OpenLDAP | OpenLDAP]] ''(Installing and configuring the Alpine package for OpenLDAP)''
* [[Setting up a LLDAP server|lldap-server]] ''(Directory Server)''
* [[Setting up Transmission (bittorrent) with Clutch WebUI]]

== Software development ==

* [[Cgit]]
* [[OsTicket]] ''(Ticket system)''
* [[Patchwork]] ''(Patch review management system)''
* [[Redmine]] ''(Project management system)''
* [[Request Tracker]] ''(Ticket system)''
* [[Setting up trac wiki|Trac]] ''(Enhanced wiki and issue tracking system for software development projects)''
* [[Ansible]] ''(Configuration management)''
* [[Installing Oracle Java|Oracle Java (installation)]]

== Storage ==

* [[Setting up disks manually|Manual partitioning]]
* [[Disk Replication with DRBD|DRBD: Disk Replication]]
* [[Filesystems]]
** [[Burning ISOs]]
* [[Setting up iSCSI|iSCSI Setup]]
** [[iSCSI Raid and Clustered File Systems]]
** [[Linux iSCSI Target (TCM)|iSCSI Target (TCM)/LinuxIO (LIO)]]
** [[Linux iSCSI Target (tgt)|User space iSCSI Target (tgt)]]
* [[Setting up Logical Volumes with LVM|LVM Setup]]
** [[Setting up LVM on GPT-labeled disks|LVM on GPT-labeled disks]]
** [[Installing on GPT LVM|LVM on GPT-labeled disks (updated)]]
** [[LVM on LUKS]]
* RAID
** [[Raid Administration]]
** [[Setting up a software RAID array]]
* ZFS
** [[Root on ZFS with native encryption]]
** [[Setting up ZFS on LUKS]]
** [[Setting up ZFS with native encryption]]
** [[ZFS scrub and trim]]
* [[CEPH|CEPH]]

== Virtualization ==

* [[Docker]]
* [[Installing Alpine in a virtual machine]]
** [[Install Alpine on VMware ESXi]]
* [[KVM]] ''(Setting up Alpine as a KVM hypervisor)''
* [[LXC]] ''(Setting up a Linux container in Alpine Linux)''
* [[QEMU]]
* Xen
** [[Xen Dom0]] ''(Setting up Alpine as a dom0 for Xen hypervisor)''
** [[Xen Dom0 on USB or SD]]
** [[Create Alpine Linux PV DomU|Xen DomU (paravirtualized)]]
** [[Xen LiveCD]]
** [[Xen PCI Passthrough]]
** [[K8s]] Building a K8s Kubernetes Cluster on Alpine Linux

== Tutorials ==

* [[TTY_Autologin|TTY Autologin]]
* [[Kexec|Faster rebooting with kexec]]
* [[Dynamic Multipoint VPN (DMVPN)]] combined with [[Small Office Services]]
* [[DIY Fully working Alpine Linux for Allwinner and Other ARM SOCs]]
* [[Fault Tolerant Routing with Alpine Linux]]
* [[High Availability High Performance Web Cache]] ''(uCarp + HAProxy for High Availability Services such as Squid web proxy)''
* [[Linux iSCSI Target (TCM)]]
* [[ISP Mail Server 3.x HowTo]] ''(Postfix+PostfixAdmin+DoveCot+Roundcube+ClamAV+Spamd - A full-service ISP mail server)''
* [[Grommunio Mail Server]] ''(Mariadb+Postfix+Rspamd+Grommunio - Full-service mail server as MS exchange replacement)''
* [[Replacing non-Alpine Linux with Alpine remotely]]
* [[Setting up A Network Monitoring and Inventory System]] ''(Nagios + OpenAudit and related components)'' 
* [[Streaming Security Camera Video with VLC]]
* [[Install Alpine on a btrfs filesystem with refind as boot manager]]
* [[Compile software from source|How to Compile a software from source in Alpine Linux]]

Tutorials and Howtos

2025-03-11T23:45:48Z

Greenman: /* HTTP and web services */ MariaDB

[[Image:package_edutainment.svg|right|link=]]
{{TOC left}}

'''Welcome to Tutorials and Howtos, a place of basic and advanced configuration tasks for your Alpine Linux.'''

'''Howtos are smaller articles''' explaining how to perform a particular task with Alpine Linux, that expects a minimal knowledge from reader to perform actions. Howto's have been organized in the below page based on the topics.

'''The [[#Tutorials|tutorials]] are hands-on''' and the reader is expected to try and achieve the goals described in each step, possibly with the help of a good example. The output in one step is the starting point for the following step.

{{Note|
* Contributors are requested to refer to [[Help:Editing]] first and make use of resources like [[How to write a HOWTO]].
* Contributions must be complete articles.
* Don't override already made contributions, unless there is a mistake.
* If you want to request a topic, please add your request in this page's [[Talk:Tutorials_and_Howtos|Discussion]].}}

== Desktop ==

* {{:Daily driver guide}}
=== Power management ===

* [[Configure action when power-button is pressed]]
* [[Suspend on LID close]]
* [[Configure Wake-on-LAN]]

=== Networking ===

* [[Bluetooth]] - Instructions for installing and configuring Bluetooth
* [[Bonding]] - Bond (or aggregate) multiple ethernet interfaces
* [[Bridge]] - Configuring a network bridge
** [[Bridge wlan0 to eth0]]
* [[Configure Networking]]
* [[How to configure static routes]]
* Modem
** [[Using HSDPA modem]]
** [[Using serial modem]]
* [[mDNS]] - Howto implement multicast DNS resolution in Alpine.
* [[Multi ISP]] ''(Dual-ISP setup with load-balancing and automatic failover)''
* [[PXE boot]]
* Wi-Fi
** [[Wi-Fi|Connecting to a wireless access point]]
** [[How to setup a wireless access point]] ''(Setting up Secure Wireless AP w/ WPA encryption with bridge to wired network)''
* [[VLAN]]

=== Security ===

* [[Securing Alpine Linux]] How to Secure Alpine Linux using Security Technical Implementation Guides (STIGs)
* Understand [[UEFI]] and enable [[UEFI Secure Boot]]

=== Backup and data migration ===

* [[Migrating data]]
* [[Rsnapshot]] - setting up periodic backups

=== Other topics ===

* [[Gaming on Alpine]]
* [[Remote Desktop Server]]
* [[Default applications|How to change default application]]
* [[CPU frequency scaling]]
* [[Mimalloc]]
* [[Enable Serial Console on Boot]]
* [[How to build the Alpine Linux kernel]]
* [[Nextcloud]] ''(Self hostable cloud suite - Dropbox Alternative)''
* [[Setting up lm_sensors]]
* [[Desktop environments and Window managers|List of supported Desktop environments and Window managers]]

== Diskless ==

* [[Alpine local backup|Alpine local backup (lbu)]] ''(Permanently store your modifications in case your box needs reboot)''
** [[Back Up a Flash Memory Installation]]
** [[Manually editing a existing apkovl]]

== Other Architectures ==

=== ARM ===

* [[Alpine on ARM]]

==== Raspberry Pi ====

* [[Raspberry Pi|Raspberry Pi main page]]
* [[Raspberry Pi Bluetooth Speaker|Raspberry Pi - Bluetooth Speaker]]
* [[Linux Router with VPN on a Raspberry Pi|Raspberry Pi - Router with VPN]]
* [[Linux Router with VPN on a Raspberry Pi (IPv6)|Raspberry Pi - Router with VPN (IPv6)]]
* [[Classic install or sys mode on Raspberry Pi|Raspberry Pi - Sys mode install]]
* [[Raspberry Pi LVM on LUKS|Raspberry Pi - Sys mode install - LVM on LUKS]]
* [[RPI Video Receiver|Raspberry Pi - Video Receiver]] ''(network video decoder using Rasperry Pi and omxplayer)''
* [[Raspberry Pi 3 - Browser Client]] - kiosk or digital sign
* [[Raspberry Pi 3 - Configuring it as wireless access point -AP Mode]]
* [[Raspberry Pi 3 - Setting Up Bluetooth]]
* [[Raspberry Pi 4 - Persistent system acting as a NAS and Time Machine]]
* [[How to set up Alpine as a wireless router|Raspberry Pi Zero W - Wireless router]] ''(Setting up a firewalled, Wireless AP with wired network on a Pi Zero W)''
* [[RPI Video Receiver]]

=== IBM Z (IBM z Systems) ===

* [[s390x|s390x - Installation]]

=== PowerPC ===

* [[Ppc64le|Powerpc64le - Installation]]

== Services ==

{{Note| Services are arranged in alphabetical order.}}

=== Content management systems ===

* [[DokuWiki]]
* [[Drupal]] ''(Content Management System (CMS) written in PHP)''
* [[Kopano]] ''(Microsoft Outlook compatible Groupware)''
* [[Mahara]] ''(E-portfolio and social networking system)''
* [[MediaWiki]] ''(Free web-based wiki software application)''
* [[Pastebin]] ''(Pastebin software application)''
* [[WordPress]] ''(Web software to create website or blog)''
* [[Moodle]] ''(Online Learning Management system)''

=== Database ===

* [[MariaDB]] or [[MySQL|MySQL]]

=== DNS ===

* [[DNSCrypt-Proxy]] ''Encrypt and authenticate DNS calls from your system''
* [[Setting up nsd DNS server]]
* [[Small-Time DNS with BIND9]] ''(A simple configuration with ad blocking for your home network)''
* Unbound
** [[Setting up unbound DNS server]]
** [[Using Unbound as an Ad-blocker]] ''(Setup ad blocking for your network)''
* [[TinyDNS Format]]

=== File server ===

* [[Setting up an NFS server|nfs-server]]
* [[Setting up a Samba server|samba-server]] ''(standard file sharing)''
* [[Setting up a samba-ad-dc|samba-ad-dc]] ''(Active Directory compatible domain controller)''

=== Firewall and VPN ===

* Alpine Wall ''(a new firewall management framework)''
** [[Alpine Wall]]
** [https://git.alpinelinux.org/awall/about/ Alpine Wall User's Guide]
** [[How-To Alpine Wall]]
* [[Freeradius Active Directory Integration]]
* [[GNUnet]]
* [[Setting up a OpenVPN server|OpenVPN server]] ''(Allowing single users or devices to remotely connect to your network)''
* [[OpenVSwitch]]
* [[Using Alpine on Windows domain with IPSEC isolation]]
* [[Configure a Wireguard interface (wg)|Wireguard]]
* [[IGMPproxy]]

=== HTTP and web services ===

* [[Apache]]
** [[Apache with php-fpm]]
** [[Setting Up Apache with PHP]]
** [[Apache authentication: NTLM Single Signon]]
* [[Darkhttpd]]
* [[Lighttpd]]
** [[Lighttpd Advanced security]]
** [[Setting Up Lighttpd With FastCGI]]
** [[Production Web server: Lighttpd|Production web server: Lighttpd‎‎]]
* [[Nginx]]
** [[Nginx as reverse proxy with acme (letsencrypt)]]
** [[Nginx with PHP]]
* Squid Proxy
** [[Obtaining user information via SNMP]] ''(Using squark-auth-snmp as a Squid authentication helper)'' 
** [[Setting up Explicit Squid Proxy]]
** [[Setting up Transparent Squid Proxy]] ''(Covers Squid proxy and URL Filtering system)''
** [[SqStat]] ''(Script to look at active squid users connections)''
* [[Tomcat]]
** [[Production LAMP system: Lighttpd + PHP + MariaDB/MySQL‎‎]]

=== IRC ===

* [[NgIRCd]] ''(Server for Internet Relay Chat/IRC)''

=== Mail ===

* [[Hosting services on Alpine]] ''(Hosting mail, webservices and other services)''
* [[Hosting Web/Email services on Alpine]]
* Exim/Dovecot
** [[Small-Time Email with Exim and Dovecot]] ''(A simple configuration for your home network.)
** [[Setting up dovecot with imap and tls]]
* [[relay email to gmail (msmtp, mailx, sendmail]]
* [[relay email (nullmailer)]]
* [[Roundcube]] ''(Webmail system)''
* [[Setting up postfix with virtual domains]]
* Server protection
** [[Setting up clamsmtp]]

=== Monitoring ===

* [[Awstats]] ''(Free log file analyzer)''
* [[Cacti: traffic analysis and monitoring network]] ''(Front-end for rrdtool networking monitor)''
* [[Cvechecker]] ''(Compare installed packages for Common Vulnerabilities Exposure)'' 
* [[Linfo]]
* [[Obtaining user information via SNMP]] ''(Using squark-auth-snmp as a Squid authentication helper)'' 
* [[PhpSysInfo]] ''(A simple application that displays information about the host it's running on)''
* [[Logcheck]] ''(log file monitoring tool)''
* [[Matomo]] ''(A real time web analytics software program)''
* [[Rasdaemon]] ''(Platform Reliability, Availability and Serviceability monitoring tool)''
* [[Setting up A Network Monitoring and Inventory System]] ''(Nagios + OpenAudit and related components)'' 
** [[Setting up NRPE daemon]] ''(Performs remote Nagios checks)'' 
* [[Setting Up Fprobe And Ntop|Ntop]] ''(NetFlow collection and analysis using a remote fprobe instance; for alpine 3.10-3.12 only)'' 
* [[SqStat]] ''(Script to look at active squid users connections)''
* [[Traffic monitoring]] 
** [[Setting up monitoring using rrdtool (and rrdcollect)]]
** [[Setting up traffic monitoring using rrdtool (and snmp)]] 
* [[Zabbix|Zabbix - the professional complete manager]] ''(Monitor and track the status of network services and hardware)''
* [[ZoneMinder video camera security and surveillance]]

=== Remote Administration ===

* ACF
** [[Changing passwords for ACF|ACF - changing passwords]]
** [[Generating SSL certs with ACF]] 
** [[setup-acf| ACF - setup]] ''(Configures ACF (webconfiguration/webmin) so you can manage your box through https)''
* [[Setting up a SSH server]] ''(Using ssh is a good way to administer your box remotely)''
** [[HOWTO OpenSSH 2FA with password and Google Authenticator |OpenSSH 2FA]] ''(A simple two factor setup for OpenSSH)''
* [[OpenVCP]] ''(VServer Control Panel)''
* [[PhpMyAdmin]] ''(Web-based administration tool for MYSQL)''
* [[PhpPgAdmin]] ''(Web-based administration tool for PostgreSQL)''
* [[Webmin]] ''(A web-based interface for Linux system)''

=== Telephony ===

* [[FreePBX|FreePBX on Alpine Linux]]
* [[Setting up Zaptel/Asterisk on Alpine]]
* [[Kamailio]] ''(SIP Server, formerly OpenSER)''

=== Other Servers ===

* [[apcupsd]] ''(UPS Monitoring with apcupsd)''
* [[Chrony and GPSD | Chrony, gpsd, and a garmin LVC 18 as a Stratum 1 NTP source ]]
* [[Glpi]] ''(Manage inventory of technical resources)''
* [[How to setup a Alpine Linux mirror]]
* [[nut-ups|NUT UPS]] ''(UPS Monitoring with Network UPS Tools)''
* [[Odoo]]
* [[Configure OpenLDAP | OpenLDAP]] ''(Installing and configuring the Alpine package for OpenLDAP)''
* [[Setting up a LLDAP server|lldap-server]] ''(Directory Server)''
* [[Setting up Transmission (bittorrent) with Clutch WebUI]]

== Software development ==

* [[Cgit]]
* [[OsTicket]] ''(Ticket system)''
* [[Patchwork]] ''(Patch review management system)''
* [[Redmine]] ''(Project management system)''
* [[Request Tracker]] ''(Ticket system)''
* [[Setting up trac wiki|Trac]] ''(Enhanced wiki and issue tracking system for software development projects)''
* [[Ansible]] ''(Configuration management)''
* [[Installing Oracle Java|Oracle Java (installation)]]

== Storage ==

* [[Setting up disks manually|Manual partitioning]]
* [[Disk Replication with DRBD|DRBD: Disk Replication]]
* [[Filesystems]]
** [[Burning ISOs]]
* [[Setting up iSCSI|iSCSI Setup]]
** [[iSCSI Raid and Clustered File Systems]]
** [[Linux iSCSI Target (TCM)|iSCSI Target (TCM)/LinuxIO (LIO)]]
** [[Linux iSCSI Target (tgt)|User space iSCSI Target (tgt)]]
* [[Setting up Logical Volumes with LVM|LVM Setup]]
** [[Setting up LVM on GPT-labeled disks|LVM on GPT-labeled disks]]
** [[Installing on GPT LVM|LVM on GPT-labeled disks (updated)]]
** [[LVM on LUKS]]
* RAID
** [[Raid Administration]]
** [[Setting up a software RAID array]]
* ZFS
** [[Root on ZFS with native encryption]]
** [[Setting up ZFS on LUKS]]
** [[Setting up ZFS with native encryption]]
** [[ZFS scrub and trim]]
* [[CEPH|CEPH]]

== Virtualization ==

* [[Docker]]
* [[Installing Alpine in a virtual machine]]
** [[Install Alpine on VMware ESXi]]
* [[KVM]] ''(Setting up Alpine as a KVM hypervisor)''
* [[LXC]] ''(Setting up a Linux container in Alpine Linux)''
* [[QEMU]]
* Xen
** [[Xen Dom0]] ''(Setting up Alpine as a dom0 for Xen hypervisor)''
** [[Xen Dom0 on USB or SD]]
** [[Create Alpine Linux PV DomU|Xen DomU (paravirtualized)]]
** [[Xen LiveCD]]
** [[Xen PCI Passthrough]]
** [[K8s]] Building a K8s Kubernetes Cluster on Alpine Linux

== Tutorials ==

* [[TTY_Autologin|TTY Autologin]]
* [[Kexec|Faster rebooting with kexec]]
* [[Dynamic Multipoint VPN (DMVPN)]] combined with [[Small Office Services]]
* [[DIY Fully working Alpine Linux for Allwinner and Other ARM SOCs]]
* [[Fault Tolerant Routing with Alpine Linux]]
* [[High Availability High Performance Web Cache]] ''(uCarp + HAProxy for High Availability Services such as Squid web proxy)''
* [[Linux iSCSI Target (TCM)]]
* [[ISP Mail Server 3.x HowTo]] ''(Postfix+PostfixAdmin+DoveCot+Roundcube+ClamAV+Spamd - A full-service ISP mail server)''
* [[Grommunio Mail Server]] ''(Mariadb+Postfix+Rspamd+Grommunio - Full-service mail server as MS exchange replacement)''
* [[Replacing non-Alpine Linux with Alpine remotely]]
* [[Setting up A Network Monitoring and Inventory System]] ''(Nagios + OpenAudit and related components)'' 
* [[Streaming Security Camera Video with VLC]]
* [[Install Alpine on a btrfs filesystem with refind as boot manager]]
* [[Compile software from source|How to Compile a software from source in Alpine Linux]]

Tutorials and Howtos

2025-03-11T23:45:16Z

Greenman: /* Database */ MariaDB

[[Image:package_edutainment.svg|right|link=]]
{{TOC left}}

'''Welcome to Tutorials and Howtos, a place of basic and advanced configuration tasks for your Alpine Linux.'''

'''Howtos are smaller articles''' explaining how to perform a particular task with Alpine Linux, that expects a minimal knowledge from reader to perform actions. Howto's have been organized in the below page based on the topics.

'''The [[#Tutorials|tutorials]] are hands-on''' and the reader is expected to try and achieve the goals described in each step, possibly with the help of a good example. The output in one step is the starting point for the following step.

{{Note|
* Contributors are requested to refer to [[Help:Editing]] first and make use of resources like [[How to write a HOWTO]].
* Contributions must be complete articles.
* Don't override already made contributions, unless there is a mistake.
* If you want to request a topic, please add your request in this page's [[Talk:Tutorials_and_Howtos|Discussion]].}}

== Desktop ==

* {{:Daily driver guide}}
=== Power management ===

* [[Configure action when power-button is pressed]]
* [[Suspend on LID close]]
* [[Configure Wake-on-LAN]]

=== Networking ===

* [[Bluetooth]] - Instructions for installing and configuring Bluetooth
* [[Bonding]] - Bond (or aggregate) multiple ethernet interfaces
* [[Bridge]] - Configuring a network bridge
** [[Bridge wlan0 to eth0]]
* [[Configure Networking]]
* [[How to configure static routes]]
* Modem
** [[Using HSDPA modem]]
** [[Using serial modem]]
* [[mDNS]] - Howto implement multicast DNS resolution in Alpine.
* [[Multi ISP]] ''(Dual-ISP setup with load-balancing and automatic failover)''
* [[PXE boot]]
* Wi-Fi
** [[Wi-Fi|Connecting to a wireless access point]]
** [[How to setup a wireless access point]] ''(Setting up Secure Wireless AP w/ WPA encryption with bridge to wired network)''
* [[VLAN]]

=== Security ===

* [[Securing Alpine Linux]] How to Secure Alpine Linux using Security Technical Implementation Guides (STIGs)
* Understand [[UEFI]] and enable [[UEFI Secure Boot]]

=== Backup and data migration ===

* [[Migrating data]]
* [[Rsnapshot]] - setting up periodic backups

=== Other topics ===

* [[Gaming on Alpine]]
* [[Remote Desktop Server]]
* [[Default applications|How to change default application]]
* [[CPU frequency scaling]]
* [[Mimalloc]]
* [[Enable Serial Console on Boot]]
* [[How to build the Alpine Linux kernel]]
* [[Nextcloud]] ''(Self hostable cloud suite - Dropbox Alternative)''
* [[Setting up lm_sensors]]
* [[Desktop environments and Window managers|List of supported Desktop environments and Window managers]]

== Diskless ==

* [[Alpine local backup|Alpine local backup (lbu)]] ''(Permanently store your modifications in case your box needs reboot)''
** [[Back Up a Flash Memory Installation]]
** [[Manually editing a existing apkovl]]

== Other Architectures ==

=== ARM ===

* [[Alpine on ARM]]

==== Raspberry Pi ====

* [[Raspberry Pi|Raspberry Pi main page]]
* [[Raspberry Pi Bluetooth Speaker|Raspberry Pi - Bluetooth Speaker]]
* [[Linux Router with VPN on a Raspberry Pi|Raspberry Pi - Router with VPN]]
* [[Linux Router with VPN on a Raspberry Pi (IPv6)|Raspberry Pi - Router with VPN (IPv6)]]
* [[Classic install or sys mode on Raspberry Pi|Raspberry Pi - Sys mode install]]
* [[Raspberry Pi LVM on LUKS|Raspberry Pi - Sys mode install - LVM on LUKS]]
* [[RPI Video Receiver|Raspberry Pi - Video Receiver]] ''(network video decoder using Rasperry Pi and omxplayer)''
* [[Raspberry Pi 3 - Browser Client]] - kiosk or digital sign
* [[Raspberry Pi 3 - Configuring it as wireless access point -AP Mode]]
* [[Raspberry Pi 3 - Setting Up Bluetooth]]
* [[Raspberry Pi 4 - Persistent system acting as a NAS and Time Machine]]
* [[How to set up Alpine as a wireless router|Raspberry Pi Zero W - Wireless router]] ''(Setting up a firewalled, Wireless AP with wired network on a Pi Zero W)''
* [[RPI Video Receiver]]

=== IBM Z (IBM z Systems) ===

* [[s390x|s390x - Installation]]

=== PowerPC ===

* [[Ppc64le|Powerpc64le - Installation]]

== Services ==

{{Note| Services are arranged in alphabetical order.}}

=== Content management systems ===

* [[DokuWiki]]
* [[Drupal]] ''(Content Management System (CMS) written in PHP)''
* [[Kopano]] ''(Microsoft Outlook compatible Groupware)''
* [[Mahara]] ''(E-portfolio and social networking system)''
* [[MediaWiki]] ''(Free web-based wiki software application)''
* [[Pastebin]] ''(Pastebin software application)''
* [[WordPress]] ''(Web software to create website or blog)''
* [[Moodle]] ''(Online Learning Management system)''

=== Database ===

* [[MariaDB]] or [[MySQL|MySQL]]

=== DNS ===

* [[DNSCrypt-Proxy]] ''Encrypt and authenticate DNS calls from your system''
* [[Setting up nsd DNS server]]
* [[Small-Time DNS with BIND9]] ''(A simple configuration with ad blocking for your home network)''
* Unbound
** [[Setting up unbound DNS server]]
** [[Using Unbound as an Ad-blocker]] ''(Setup ad blocking for your network)''
* [[TinyDNS Format]]

=== File server ===

* [[Setting up an NFS server|nfs-server]]
* [[Setting up a Samba server|samba-server]] ''(standard file sharing)''
* [[Setting up a samba-ad-dc|samba-ad-dc]] ''(Active Directory compatible domain controller)''

=== Firewall and VPN ===

* Alpine Wall ''(a new firewall management framework)''
** [[Alpine Wall]]
** [https://git.alpinelinux.org/awall/about/ Alpine Wall User's Guide]
** [[How-To Alpine Wall]]
* [[Freeradius Active Directory Integration]]
* [[GNUnet]]
* [[Setting up a OpenVPN server|OpenVPN server]] ''(Allowing single users or devices to remotely connect to your network)''
* [[OpenVSwitch]]
* [[Using Alpine on Windows domain with IPSEC isolation]]
* [[Configure a Wireguard interface (wg)|Wireguard]]
* [[IGMPproxy]]

=== HTTP and web services ===

* [[Apache]]
** [[Apache with php-fpm]]
** [[Setting Up Apache with PHP]]
** [[Apache authentication: NTLM Single Signon]]
* [[Darkhttpd]]
* [[Lighttpd]]
** [[Lighttpd Advanced security]]
** [[Setting Up Lighttpd With FastCGI]]
** [[Production Web server: Lighttpd|Production web server: Lighttpd‎‎]]
* [[Nginx]]
** [[Nginx as reverse proxy with acme (letsencrypt)]]
** [[Nginx with PHP]]
* Squid Proxy
** [[Obtaining user information via SNMP]] ''(Using squark-auth-snmp as a Squid authentication helper)'' 
** [[Setting up Explicit Squid Proxy]]
** [[Setting up Transparent Squid Proxy]] ''(Covers Squid proxy and URL Filtering system)''
** [[SqStat]] ''(Script to look at active squid users connections)''
* [[Tomcat]]
** [[Production LAMP system: Lighttpd + PHP + MySQL‎‎]]

=== IRC ===

* [[NgIRCd]] ''(Server for Internet Relay Chat/IRC)''

=== Mail ===

* [[Hosting services on Alpine]] ''(Hosting mail, webservices and other services)''
* [[Hosting Web/Email services on Alpine]]
* Exim/Dovecot
** [[Small-Time Email with Exim and Dovecot]] ''(A simple configuration for your home network.)
** [[Setting up dovecot with imap and tls]]
* [[relay email to gmail (msmtp, mailx, sendmail]]
* [[relay email (nullmailer)]]
* [[Roundcube]] ''(Webmail system)''
* [[Setting up postfix with virtual domains]]
* Server protection
** [[Setting up clamsmtp]]

=== Monitoring ===

* [[Awstats]] ''(Free log file analyzer)''
* [[Cacti: traffic analysis and monitoring network]] ''(Front-end for rrdtool networking monitor)''
* [[Cvechecker]] ''(Compare installed packages for Common Vulnerabilities Exposure)'' 
* [[Linfo]]
* [[Obtaining user information via SNMP]] ''(Using squark-auth-snmp as a Squid authentication helper)'' 
* [[PhpSysInfo]] ''(A simple application that displays information about the host it's running on)''
* [[Logcheck]] ''(log file monitoring tool)''
* [[Matomo]] ''(A real time web analytics software program)''
* [[Rasdaemon]] ''(Platform Reliability, Availability and Serviceability monitoring tool)''
* [[Setting up A Network Monitoring and Inventory System]] ''(Nagios + OpenAudit and related components)'' 
** [[Setting up NRPE daemon]] ''(Performs remote Nagios checks)'' 
* [[Setting Up Fprobe And Ntop|Ntop]] ''(NetFlow collection and analysis using a remote fprobe instance; for alpine 3.10-3.12 only)'' 
* [[SqStat]] ''(Script to look at active squid users connections)''
* [[Traffic monitoring]] 
** [[Setting up monitoring using rrdtool (and rrdcollect)]]
** [[Setting up traffic monitoring using rrdtool (and snmp)]] 
* [[Zabbix|Zabbix - the professional complete manager]] ''(Monitor and track the status of network services and hardware)''
* [[ZoneMinder video camera security and surveillance]]

=== Remote Administration ===

* ACF
** [[Changing passwords for ACF|ACF - changing passwords]]
** [[Generating SSL certs with ACF]] 
** [[setup-acf| ACF - setup]] ''(Configures ACF (webconfiguration/webmin) so you can manage your box through https)''
* [[Setting up a SSH server]] ''(Using ssh is a good way to administer your box remotely)''
** [[HOWTO OpenSSH 2FA with password and Google Authenticator |OpenSSH 2FA]] ''(A simple two factor setup for OpenSSH)''
* [[OpenVCP]] ''(VServer Control Panel)''
* [[PhpMyAdmin]] ''(Web-based administration tool for MYSQL)''
* [[PhpPgAdmin]] ''(Web-based administration tool for PostgreSQL)''
* [[Webmin]] ''(A web-based interface for Linux system)''

=== Telephony ===

* [[FreePBX|FreePBX on Alpine Linux]]
* [[Setting up Zaptel/Asterisk on Alpine]]
* [[Kamailio]] ''(SIP Server, formerly OpenSER)''

=== Other Servers ===

* [[apcupsd]] ''(UPS Monitoring with apcupsd)''
* [[Chrony and GPSD | Chrony, gpsd, and a garmin LVC 18 as a Stratum 1 NTP source ]]
* [[Glpi]] ''(Manage inventory of technical resources)''
* [[How to setup a Alpine Linux mirror]]
* [[nut-ups|NUT UPS]] ''(UPS Monitoring with Network UPS Tools)''
* [[Odoo]]
* [[Configure OpenLDAP | OpenLDAP]] ''(Installing and configuring the Alpine package for OpenLDAP)''
* [[Setting up a LLDAP server|lldap-server]] ''(Directory Server)''
* [[Setting up Transmission (bittorrent) with Clutch WebUI]]

== Software development ==

* [[Cgit]]
* [[OsTicket]] ''(Ticket system)''
* [[Patchwork]] ''(Patch review management system)''
* [[Redmine]] ''(Project management system)''
* [[Request Tracker]] ''(Ticket system)''
* [[Setting up trac wiki|Trac]] ''(Enhanced wiki and issue tracking system for software development projects)''
* [[Ansible]] ''(Configuration management)''
* [[Installing Oracle Java|Oracle Java (installation)]]

== Storage ==

* [[Setting up disks manually|Manual partitioning]]
* [[Disk Replication with DRBD|DRBD: Disk Replication]]
* [[Filesystems]]
** [[Burning ISOs]]
* [[Setting up iSCSI|iSCSI Setup]]
** [[iSCSI Raid and Clustered File Systems]]
** [[Linux iSCSI Target (TCM)|iSCSI Target (TCM)/LinuxIO (LIO)]]
** [[Linux iSCSI Target (tgt)|User space iSCSI Target (tgt)]]
* [[Setting up Logical Volumes with LVM|LVM Setup]]
** [[Setting up LVM on GPT-labeled disks|LVM on GPT-labeled disks]]
** [[Installing on GPT LVM|LVM on GPT-labeled disks (updated)]]
** [[LVM on LUKS]]
* RAID
** [[Raid Administration]]
** [[Setting up a software RAID array]]
* ZFS
** [[Root on ZFS with native encryption]]
** [[Setting up ZFS on LUKS]]
** [[Setting up ZFS with native encryption]]
** [[ZFS scrub and trim]]
* [[CEPH|CEPH]]

== Virtualization ==

* [[Docker]]
* [[Installing Alpine in a virtual machine]]
** [[Install Alpine on VMware ESXi]]
* [[KVM]] ''(Setting up Alpine as a KVM hypervisor)''
* [[LXC]] ''(Setting up a Linux container in Alpine Linux)''
* [[QEMU]]
* Xen
** [[Xen Dom0]] ''(Setting up Alpine as a dom0 for Xen hypervisor)''
** [[Xen Dom0 on USB or SD]]
** [[Create Alpine Linux PV DomU|Xen DomU (paravirtualized)]]
** [[Xen LiveCD]]
** [[Xen PCI Passthrough]]
** [[K8s]] Building a K8s Kubernetes Cluster on Alpine Linux

== Tutorials ==

* [[TTY_Autologin|TTY Autologin]]
* [[Kexec|Faster rebooting with kexec]]
* [[Dynamic Multipoint VPN (DMVPN)]] combined with [[Small Office Services]]
* [[DIY Fully working Alpine Linux for Allwinner and Other ARM SOCs]]
* [[Fault Tolerant Routing with Alpine Linux]]
* [[High Availability High Performance Web Cache]] ''(uCarp + HAProxy for High Availability Services such as Squid web proxy)''
* [[Linux iSCSI Target (TCM)]]
* [[ISP Mail Server 3.x HowTo]] ''(Postfix+PostfixAdmin+DoveCot+Roundcube+ClamAV+Spamd - A full-service ISP mail server)''
* [[Grommunio Mail Server]] ''(Mariadb+Postfix+Rspamd+Grommunio - Full-service mail server as MS exchange replacement)''
* [[Replacing non-Alpine Linux with Alpine remotely]]
* [[Setting up A Network Monitoring and Inventory System]] ''(Nagios + OpenAudit and related components)'' 
* [[Streaming Security Camera Video with VLC]]
* [[Install Alpine on a btrfs filesystem with refind as boot manager]]
* [[Compile software from source|How to Compile a software from source in Alpine Linux]]

MariaDB

2025-03-11T23:43:54Z

Greenman: /* Create a user */ this is not enough

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

== Installation ==

The Alpine Linux repositories no longer include the actual MySQL binaries, installing the <code>mysql-*</code> packages will instead install MariaDB.

Installing <code>mariadb</code> will create the user <code>mysql</code>. When the database is initialized, two users will be added to the database: <code>root</code> and <code>mysql</code>. By default these users will only be accessible if you are logged in as the corresponding system user.

{{Cmd|apk add {{pkg|mariadb|arch=}} {{pkg|mariadb-client|arch=}}}}

Installing the above packages will add the main components of MariaDB to the system: <code>mariadb-cient</code> and <code>mariadb-server</code>. Other available packages are described in the table below, and are listed in order of relevance for a production server.

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| {{Pkg|mysql}} || v2 || a transitional package that installs mariadb || mariadb
|-
| {{Pkg|mysql-client}} || v2 || a transitional package that installs the mariadb client tools || mariadb-client
|-
| {{Pkg|mariadb}} || v2 || server equivalent to mysql-server || mariadb-common
|-
| {{Pkg|mariadb-client}} || v2 || connection command line and tools || mariadb-common
|-
| {{Pkg|mariadb-doc}} || v3.0 || manpages for mariadb || man man-pages
|-
| {{Pkg|mariadb-connector-odbc}} || edge || coding or making OS level connections, to any DB without libs install || .
|-
| {{Pkg|mariadb-connector-c}} || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| {{Pkg|mariadb-backup}} || v3.8 || tool for physical online backups, no longer widely used || .
|-
| {{Pkg|mariadb-server-utils}} || v3.8 || server commands not widely used, in past was inside MariaDB package || .
|-
| {{Pkg|mariadb-dev}} || v3.1 || development files for MariaDB || .
|-
| {{Pkg|mariadb-test}} || v3.3 || testing suite from MariaDB tools || .
|-
| {{Pkg|mariadb-mytop}} || v3.9 || data performance monitoring || .
|-
| {{Pkg|mariadb-plugin-rocksdb}} || v3.9 || plain key-value event relational for data || .
|-
| {{Pkg|mariadb-static}} || v3.8 || static libs for static non depends linking in builds || .
|-
| {{Pkg|mariadb-embedded}} || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| {{Pkg|mariadb-embedded-dev}} || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| {{Pkg|mariadb-openrc}} || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The version of MariaDB in the Alpine repositories behave like the MySQL tarball. No graphical tools are included.

The ''datadir'' located at {{Path|/var/lib/mysql}} must be owned by the mysql user and group. The location of the ''datadir'' can be changed by editing the <code>mariadb</code> service file in {{Path|/etc/init.d}}. The new location will also need to be set by adding <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> in the <code>[mysqld]</code> section in a mariadb configuration file.

Normal initialization of mariadb can be done as follows:

# Start the main service. At this point there will be no root password set. <code>rc-service mariadb start</code>
# Secure the database by running <code>mysql_secure_installation</code>
# Setup permissions for managing others users and databases see: '''[[#Configuration|Configuration]]'''
# Add MariaDb to OpenRC. <code>rc-update add mariadb default</code>

== Configuration ==

In order to help with the basic configuration of the database engine, MariaDB provides [https://mariadb.com/kb/en/mariadb-secure-installation/ mariadb-secure-installation].

Many of the reasons for running this script are no longer necessary, since MariaDB has defaulted to Unix socket authentication since MariaDB 10.4.

This script walks you through the basics of securing the database. The options are explained below.

# '''Enter current password for root (enter for none):''' If you have previously set up a root password, provide it here and press enter. If not, just press enter.
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the Unix_socket ensures that only admins can log into engine database. For non-production servers just press "n" to setup a root password, which will give you the response <code>... skipping.</code>
# '''Change the root password? [Y/n]''' Here you can change the root password, or set one if needed. Press "Y" and enter the new password.
# '''Remove anonymous users? [Y/n]''' Remove anonymous users created to log in using socket authentication. Unless you're sure you need this, answer "Y" to remove them.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost' in order to protect from password sniffing attempts over the network. Answer "Y".
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. If this is not needed, answer "Y".
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Answer "Y".

After the script exits, restart the service with <code>rc-service mariadb restart</code>

To start the database daemon on every boot, run <code>rc-update add mariadb default</code>

=== Configuration files and customization ===

Rather than being stored in {{Path|my.cnf}}, configuration settings for MariaDB are now organized in separate files. The primary configuration is done by adding files to {{Path|/etc/my.cnf.d/}}. User-specific configuration files are stored in {{Path|~/.my.cnf}}. User-specific configuration files are loaded after the system-wide configuration. The locations of the various configuration files are listed below.

{| class="wikitable"
|-
! Config file !! Versions of Alpine !! Contents to configure
|-
| {{Path|/etc/mysql/my.cnf}} || v2 to v3.8 || All the directives, global config file
|-
| {{Path|/etc/my.cnf.d/mariadb-server.cnf}} || since 3.9 || First global config file, main directives
|-
| {{path|$HOME/.my.cnf}} || all || user name only config directives
|}

As previously mentioned, this page describes basic usage of MariaDB. For professional usage, [[MySQL]] should also be referenced.

* The following command will configure the server to accept all incoming connections. This should only be done for development, or if the database is not exposed to the Internet or a sensitive network.

{{Cmd|<nowiki>sed -i "s|.*bind-address\s*=.*|bind-address=0.0.0.0|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=0.0.0.0|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>}}

* For simple installations, disabling hostname search can improve performance, but is only useful for local servers.

{{Cmd|<nowiki>sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>}}

== Updating or coming from upgrading ==

When upgrading between Alpine Linux releases, MariaDB may also have a major version change, and the databases should be upgraded to match. The recommended steps in this process are detailed below.

# While it may no longer be strictly necessary, it's useful to backup your databases before upgrading the database version.
# Update Alpine Linux and the MariaDB/MySQL packages.
# Install mariadb-server-utils by running <code>apk add {{pkg|mariadb-server-utils|arch=}}</code>.
# Run <code>mysql_upgrade -u root -p</code> script, and provide the password for the root database user.
# Restart the service by running <code>rc-service mariadb restart</code>.

If <code>mysql_upgrade</code> fails because MySQL cannot start, try running MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code>, and then run <code>mysql_upgrade -u root -p</code> again.

= Create a user =
You may want to create a user with remote access to the database.

Open mariadb Client: <code>mariadb</code>

add the user with associated host and password: <code> CREATE OR REPLACE USER admin@'%' IDENTIFIED BY 'ASecurePassword';</code>

''NB: @'%' allow connection from any host''

This is insufficient to allow remote access. See [https://mariadb.com/kb/en/configuring-mariadb-for-remote-client-access/ Configuring MariaDB for Remote Client Access].

= See Also =

* [[MySQL]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

MariaDB

2025-03-11T23:42:39Z

Greenman: /* Create a user */ ce

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

== Installation ==

The Alpine Linux repositories no longer include the actual MySQL binaries, installing the <code>mysql-*</code> packages will instead install MariaDB.

Installing <code>mariadb</code> will create the user <code>mysql</code>. When the database is initialized, two users will be added to the database: <code>root</code> and <code>mysql</code>. By default these users will only be accessible if you are logged in as the corresponding system user.

{{Cmd|apk add {{pkg|mariadb|arch=}} {{pkg|mariadb-client|arch=}}}}

Installing the above packages will add the main components of MariaDB to the system: <code>mariadb-cient</code> and <code>mariadb-server</code>. Other available packages are described in the table below, and are listed in order of relevance for a production server.

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| {{Pkg|mysql}} || v2 || a transitional package that installs mariadb || mariadb
|-
| {{Pkg|mysql-client}} || v2 || a transitional package that installs the mariadb client tools || mariadb-client
|-
| {{Pkg|mariadb}} || v2 || server equivalent to mysql-server || mariadb-common
|-
| {{Pkg|mariadb-client}} || v2 || connection command line and tools || mariadb-common
|-
| {{Pkg|mariadb-doc}} || v3.0 || manpages for mariadb || man man-pages
|-
| {{Pkg|mariadb-connector-odbc}} || edge || coding or making OS level connections, to any DB without libs install || .
|-
| {{Pkg|mariadb-connector-c}} || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| {{Pkg|mariadb-backup}} || v3.8 || tool for physical online backups, no longer widely used || .
|-
| {{Pkg|mariadb-server-utils}} || v3.8 || server commands not widely used, in past was inside MariaDB package || .
|-
| {{Pkg|mariadb-dev}} || v3.1 || development files for MariaDB || .
|-
| {{Pkg|mariadb-test}} || v3.3 || testing suite from MariaDB tools || .
|-
| {{Pkg|mariadb-mytop}} || v3.9 || data performance monitoring || .
|-
| {{Pkg|mariadb-plugin-rocksdb}} || v3.9 || plain key-value event relational for data || .
|-
| {{Pkg|mariadb-static}} || v3.8 || static libs for static non depends linking in builds || .
|-
| {{Pkg|mariadb-embedded}} || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| {{Pkg|mariadb-embedded-dev}} || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| {{Pkg|mariadb-openrc}} || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The version of MariaDB in the Alpine repositories behave like the MySQL tarball. No graphical tools are included.

The ''datadir'' located at {{Path|/var/lib/mysql}} must be owned by the mysql user and group. The location of the ''datadir'' can be changed by editing the <code>mariadb</code> service file in {{Path|/etc/init.d}}. The new location will also need to be set by adding <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> in the <code>[mysqld]</code> section in a mariadb configuration file.

Normal initialization of mariadb can be done as follows:

# Start the main service. At this point there will be no root password set. <code>rc-service mariadb start</code>
# Secure the database by running <code>mysql_secure_installation</code>
# Setup permissions for managing others users and databases see: '''[[#Configuration|Configuration]]'''
# Add MariaDb to OpenRC. <code>rc-update add mariadb default</code>

== Configuration ==

In order to help with the basic configuration of the database engine, MariaDB provides [https://mariadb.com/kb/en/mariadb-secure-installation/ mariadb-secure-installation].

Many of the reasons for running this script are no longer necessary, since MariaDB has defaulted to Unix socket authentication since MariaDB 10.4.

This script walks you through the basics of securing the database. The options are explained below.

# '''Enter current password for root (enter for none):''' If you have previously set up a root password, provide it here and press enter. If not, just press enter.
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the Unix_socket ensures that only admins can log into engine database. For non-production servers just press "n" to setup a root password, which will give you the response <code>... skipping.</code>
# '''Change the root password? [Y/n]''' Here you can change the root password, or set one if needed. Press "Y" and enter the new password.
# '''Remove anonymous users? [Y/n]''' Remove anonymous users created to log in using socket authentication. Unless you're sure you need this, answer "Y" to remove them.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost' in order to protect from password sniffing attempts over the network. Answer "Y".
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. If this is not needed, answer "Y".
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Answer "Y".

After the script exits, restart the service with <code>rc-service mariadb restart</code>

To start the database daemon on every boot, run <code>rc-update add mariadb default</code>

=== Configuration files and customization ===

Rather than being stored in {{Path|my.cnf}}, configuration settings for MariaDB are now organized in separate files. The primary configuration is done by adding files to {{Path|/etc/my.cnf.d/}}. User-specific configuration files are stored in {{Path|~/.my.cnf}}. User-specific configuration files are loaded after the system-wide configuration. The locations of the various configuration files are listed below.

{| class="wikitable"
|-
! Config file !! Versions of Alpine !! Contents to configure
|-
| {{Path|/etc/mysql/my.cnf}} || v2 to v3.8 || All the directives, global config file
|-
| {{Path|/etc/my.cnf.d/mariadb-server.cnf}} || since 3.9 || First global config file, main directives
|-
| {{path|$HOME/.my.cnf}} || all || user name only config directives
|}

As previously mentioned, this page describes basic usage of MariaDB. For professional usage, [[MySQL]] should also be referenced.

* The following command will configure the server to accept all incoming connections. This should only be done for development, or if the database is not exposed to the Internet or a sensitive network.

{{Cmd|<nowiki>sed -i "s|.*bind-address\s*=.*|bind-address=0.0.0.0|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=0.0.0.0|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>}}

* For simple installations, disabling hostname search can improve performance, but is only useful for local servers.

{{Cmd|<nowiki>sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>}}

== Updating or coming from upgrading ==

When upgrading between Alpine Linux releases, MariaDB may also have a major version change, and the databases should be upgraded to match. The recommended steps in this process are detailed below.

# While it may no longer be strictly necessary, it's useful to backup your databases before upgrading the database version.
# Update Alpine Linux and the MariaDB/MySQL packages.
# Install mariadb-server-utils by running <code>apk add {{pkg|mariadb-server-utils|arch=}}</code>.
# Run <code>mysql_upgrade -u root -p</code> script, and provide the password for the root database user.
# Restart the service by running <code>rc-service mariadb restart</code>.

If <code>mysql_upgrade</code> fails because MySQL cannot start, try running MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code>, and then run <code>mysql_upgrade -u root -p</code> again.

= Create a user =
You may want to create a user with remote access to the database.

Open mariadb Client: <code>mariadb</code>

add the user with associated host and password: <code> CREATE OR REPLACE USER admin@'%' IDENTIFIED BY 'ASecurePassword';</code>

''NB: @'%' allow connection from any host''

= See Also =

* [[MySQL]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

Zabbix - cgi and mysql

2025-03-11T23:38:57Z

Greenman: /* Zabbix infraestructure */ sp, rest needs improvement too

Zabbix is the most popular monitoring manager in open source world, there's monitoring itself only like [[Cacti:_traffic_analysis_and_monitoring_network|catci]], but the hint is that '''''Zabbix''' is manager also, '''is ipv6 ready! fully supported''', also featured proxy-server for hidden and firewalled networks.'' Its like nagios one but more big, cutting edge and professional.

'''In the wiki there are two approaches for its use''', [[Production_monitoring_:_zabbix|the professional one]] (for servers and deploys) and the fast and simple usage (for developers and/or enthusiasts). For more curses on that (of course focused on alpine) check [https://qgqlochekone.blogspot.com/search/?q=zabbix Complete alpine documentation on english for zabbix curse]

'''This documentation is divided in two big parts.. the prerequisites and the installation''' of zabbix minimal infrastructure. The first part are in sync with the current alpine wiki pages of [https://wiki.alpinelinux.org/wiki/Category:Production Production pages]

Note: There is a possibility that this page may have been complexified / obfuscated. See the talk page or review an earlier edit: https://wiki.alpinelinux.org/w/index.php?title=Zabbix&oldid=17984.
[[File:Zabbix-5-alpine.png|thumb]]

= Where to use zabbix and alpine =

Since Zabbix it focuses on hosts: So '''is the right choice for monitoring distributed networks''' (was originally developed for monitoring servers).

In cases where there is no option to install an agent, Zabbix offers basic agentless monitoring. With it, you can check the availability of network services, as well as execute remote commands

== Zabbix infrastructure ==

Zabbix is divided into '''two large parts: server(s) and agent(s)''', and yes "servers" cos can be centralized or decentralized due the "proxyes" semi-servers.

* '''The servers''': represent all the '''recollected data''' and '''has management capabilities'''. Each one if are more than one, collects and stores statistical data.
* '''The agents''': represent each host, the clients, those machines or devices from which data is collected. Those support both passive (polling) and active checks (trapping).
* '''The proxys''': represent a server that can act as man-in-the-middle for distributed load, cases of hideden networking or firewalled, of course '''with management capabilities cos act as server''' for those agents/hosts.

'''Passive checks''' mean that the Zabbix server requests a value from the Zabbix agent, and the agent processes the request and returns the value to the Zabbix server.

'''Active checks''' mean that the Zabbix agent requests a list of active checks from the Zabbix server and then periodically sends the results.

[[File:Zabbix-agent-server-proxy-main-remote.jpg|center]]

= Part 1 Pre-requisites: php, webserver and database =

{{:Production_LAMP_system:_Lighttpd_+_PHP_+_MySQL}}

= Part 2: zabbix =

Zabbix depends on which type of database you will use, here we will show you how to doit with mysql, as more easy to use, for production is recommended posrgresql as in

== 1. install zabbix packages ==

Remenber that Zabbix has 3 components, and the server is the main one

* Central servers to represent the data ( can be one zabbix server, o combined zabbix server with more zabbix proxies)
* Clients hosts to monitored (that is made by Agents, SNMP or just ICMP)
* Distributed proxy servers (that is only need for hosts behind firewall or closed networks)

In the server we need the central base service at leas, and of course one agent for monitoring that server:

<pre>
<nowiki>
cat > /etc/apk/repositories << EOF
https://dl-4.alpinelinux.org/alpine/v$(cat /etc/alpine-release | cut -d'.' -f1,2)/main
https://dl-4.alpinelinux.org/alpine/v$(cat /etc/alpine-release | cut -d'.' -f1,2)/community
EOF

apk update

apk add zabbix zabbix-mysql zabbix-webif zabbix-setup zabbix-utils zabbix-agentd
</nowiki>
</pre>

Now lest configure the server and the UI frontend in that server:

== 2. Configure the base service ==

Server zabbix, or proxy one always need a main database, the service of monitoring is running with it, the UI and agents always send the data to the zabbix server or zabbix proxy (and from proxy to server).

{{Warning|The '''zabbix service DB user is <code>zabbixdb</code> and NOT <code>zabbix</code>''', the user <code>zabbixdb</code> whatever you use postgresql or mysql, will not be used with password for agent monitoring, cos in postgresql will use the <code>ident</code> way autentication, and in mysql will use the <code>socket</code> way autentication: '''this makes sense cos user agent is <code>zabbix</code> and does not have home.''' This is the correct and most secure way.}}

<pre>
<nowiki>
mysql -u root -e "CREATE USER 'zabbix'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('clavezabix');"
mysql -u root -e "CREATE DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_bin;"
mysql -u root -e "GRANT ALL PRIVILEGES ON zabbixdb.* TO 'zabbixdb'@'localhost' WITH GRANT OPTION;"

mysql -u zabbixdb --password=clavezabix zabbixdb < /usr/share/zabbix/database/mysql/schema.sql
mysql -u zabbixdb --password=clavezabix zabbixdb < /usr/share/zabbix/database/mysql/images.sql
mysql -u zabbixdb --password=clavezabix zabbixdb < /usr/share/zabbix/database/mysql/data.sql

sed -i 's|.*DBHost.*=.*|DBHost=localhost|g' /etc/zabbix/zabbix_server.conf
sed -i 's|DBName.*=.*|DBName=zabbixdb|g' /etc/zabbix/zabbix_server.conf
sed -i 's|.*DBPassword.*=.*|DBPassword=zabbixdb.db.1.com.1|g' /etc/zabbix/zabbix_server.conf
sed -i 's|.*DBUser.*=.*|DBUser=zabbixdb|g' /etc/zabbix/zabbix_server.conf
sed -i 's|.*DBSocket.*=.*|DBSocket=/run/mysqld/mysqld.sock|g' /etc/zabbix/zabbix_server.conf
sed -i 's|.*Fping6Location.*=.*|Fping6Location=|g' /etc/zabbix/zabbix_server.conf
sed -i 's|.*FpingLocation.*=.*|FpingLocation=/usr/sbin/fping|g' /etc/zabbix/zabbix_server.conf
sed -i 's|Nostname=.*|Nostname=monitor.zabbixnetwork|g' /etc/zabbix/zabbix_server.conf
sed -i 's|.*SourceIP.*=.*|SourceIP=0.0.0.0|g' /etc/zabbix/zabbix_server.conf

chown root:zabbix /usr/sbin/fping

rc-update add zabbix-server default

rc-service zabbix-server restart
</nowiki>
</pre>

{{Note|Remenber that the hostname of the server is <nowiki>monitor.zabbixnetwork</nowiki>, you can use the ip address too, but this name must be the same in the config files for active or passive servers, the web ui configured hostname server and/or zabbix configured hostname proxy server.}}

== 3. Setup and configure web frontend ==

The main server can be managed with the zabbix-frontend:

<pre>
<nowiki>
cp /usr/share/webapps/zabbix/conf/zabbix.conf.php.example /usr/share/webapps/zabbix/conf/zabbix.conf.php

sed -i "s|.*DB\['TYPE'\].*=.*|\$DB\['TYPE'\] = 'MYSQL';|g" /usr/share/webapps/zabbix/conf/zabbix.conf.php
sed -i "s|.*DB\['SERVER'\].*=.*|\$DB\['SERVER'\] = 'localhost';|g" /usr/share/webapps/zabbix/conf/zabbix.conf.php
sed -i "s|.*DB\['DATABASE'\].*=.*|\$DB\['DATABASE'\] = 'zabbixdb';|g" /usr/share/webapps/zabbix/conf/zabbix.conf.php
sed -i "s|.*DB\['USER'\].*=.*|\$DB\['USER'\] = 'zabbixdb';|g" /usr/share/webapps/zabbix/conf/zabbix.conf.php
sed -i "s|.*DB\['PASSWORD'\].*=.*|\$DB\['PASSWORD'\] = 'zabbixdb.db.1.com.1';|g" /usr/share/webapps/zabbix/conf/zabbix.conf.php
sed -i "s|.*ZBX_SERVER_PORT.*=.*|\$ZBX_SERVER_PORT = '10051';|g" /usr/share/webapps/zabbix/conf/zabbix.conf.php
sed -i "s|.*ZBX_SERVER_NAME.*=.*|\$ZBX_SERVER_NAME = 'monitor.ruices';|g" /usr/share/webapps/zabbix/conf/zabbix.conf.php
sed -i "s|.*IMAGE_FORMAT_DEFAULT.*=.*|\$IMAGE_FORMAT_DEFAULT = IMAGE_FORMAT_GIF;|g" /usr/share/webapps/zabbix/conf/zabbix.conf.php
sed -i "s|.*ZBX_SERVER.*=.*'localhost';|\$ZBX_SERVER = 'localhost';|g" /usr/share/webapps/zabbix/conf/zabbix.conf.php

cat > /usr/share/webapps/zabbix/.user.ini << EOF
date.timezone = "America/Caracas"
display_errors = Off
log_errors = On
upload_max_filesize = 16M
post_max_size = 24M
memory_limit = 512M
register_globals = Off
magic_quotes_gpc = Off
magic_quotes_runtime = Off
session.auto_start = 0
mbstring.func_overload = 0
max_execution_time = 600
max_input_time = 600
EOF
</nowiki>
</pre>

We do not use the stupid link of direct put on htdocs, we are professional and we use aliasing,
now lest configure the webserver to server the frontend by web:

<pre>
<nowiki>
cat > /etc/lighttpd/mod_zabbix.conf << EOF
alias.url += (
"/zabbix/" => "/usr/share/webapps/zabbix/"
)
\$HTTP["url"] =~ "^/zabbix/" {
dir-listing.activate = "disable"
}
EOF

sed -i 's#\#.*mod_rewrite.*,.*# "mod_rewrite",#g' /etc/lighttpd/lighttpd.conf
sed -i 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf
sed -i 's#\#.*mod_accesslog.*,.*# "mod_accesslog",#g' /etc/lighttpd/lighttpd.conf
sed -i 's#\#.*mod_setenv.*,.*# "mod_setenv",#g' /etc/lighttpd/lighttpd.conf
sed -i 's#\#.*mod_redirect.*,.*# "mod_redirect",#g' /etc/lighttpd/lighttpd.conf

sed -i 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

check="";check=$(grep 'include "mod_zabbix.conf' /etc/lighttpd/lighttpd.conf);[[ "$check" != "" ]] && echo listo || sed -i 's#.*include "mod_fastcgi_fpm.conf".*#include "mod_fastcgi_fpm.conf"\ninclude "mod_zabbix.conf"#g' /etc/lighttpd/lighttpd.conf

chown -R lighttpd:www-data /usr/share/webapps/zabbix/

rc-service lighttpd restart
</nowiki>
</pre>

You should now be able to browse to the Zabbix frontend: <nowiki>http://yourservername/zabbix/</nowiki> or in <nowiki>http://localhost/zabbix</nowiki>.

The login using: Login name: '''Admin''' Password:'''zabbix'''. (as described at https://www.zabbix.com/documentation/4.0/manual/installation)

There's no need to web setup cos we made all in cli as best hackers admins.

== Zabbix tools for items maps or scripts ==

There's no complete features in busybox so need more tools, also, Zabbix requires special permissions to use tools

<pre>
<nowiki>
apk add bash fping tcptraceroute coreutils net-snmp-tools nmap perl rrdtool font-dejavu net-snmp-libs

chown root:zabbix /usr/sbin/fping

chmod u+s /usr/bin/ping

chmod u+s /bin/ping

chmod u+s /usr/bin/nmap
</nowiki>
</pre>

{{Warning|this is preferable to use sudo, that has more issues in security holes, so set ui bit is a security mess buyt less than the crap usage or "sudo", cos these tools are need to run as root to right usage}}

= Zabbix and the Monitored host =

'''Zabbix can monitor almost any device system''', including Alpine Linux hosts, home computers, celphones, network devices, etc.

== types of monitoring ==

The '''zabbix agent''' is a zabbix service that runs on computers and is fully feartured, the '''SNMP(Simple Network Manager Protocol)''' is a common standard protocol and is the second most supported, the '''ICMP(Internet Control Message Protocol)''' is the most used and simples way but most limited, check the features:

{| class="wikitable"
|-
! Feature !! By Agent !! By SNMP !! By ICMP
|-
| Ping to check alive (direct ip) || Yes || Yes but with zabbbix proxy if firewall || Only on direct ip, no firewall
|-
| Retreive data from programs (direct ping) || Yes || Yes but with zabbbix proxy if firewall || No
|-
| Send commands to manage || Yes || Yes but using zabbix proxy if firewall || No
|-
| Runs on any device || No, only computers || Mostly any network device || Yes
|-
| Runs on any OS || YEs, limited in androita and mainframes || Yes || Yes
|-
| Requires provilegies || Not necesary for most fearures || It depends || No
|}

{{Note|Support to allow zabbix-agentd to view running processes on Alpine Linux has been added since linux-grsec-2.6.35.9-r2. Please ensure you have that kernel installed prior to attempting to run zabbix-agentd.}}

Ensure that the readproc group exists (support added since alpine-baselayout-2.0_rc1-r1), by adding the following line to /etc/group:

{{Cmd|readproc:x:30:zabbix}}

== Monitoring by Zabbix Agents ==

The agent way to monitoring is the most complete featured, but only applies to computers host, becouse the zabbiz-agent software is only available for those devices, for networking devices you must use ICMP or SNMP (check next subsection).

So, install the agent package and configure it on each host to be monitored/managed (on server too):

{{Note|Remenber that the hostname of the server is <nowiki>monitor.zabbixnetwork</nowiki>, you can use the ip address too, but this name must be the same in the config files for active or passive servers, the web ui configured hostname server and/or zabbix configured hostname proxy server.}}

<pre>
<nowiki>
cat > /etc/apk/repositories << EOF
https://dl-4.alpinelinux.org/alpine/v$(cat /etc/alpine-release | cut -d'.' -f1,2)/main
https://dl-4.alpinelinux.org/alpine/v$(cat /etc/alpine-release | cut -d'.' -f1,2)/community
EOF

apk update

apk add zabbix-agent net-snmp-libs net-snmp-agent-libs

sed -i 's|.*ListenPort.*=.*|ListenPort=10050|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|.*Hostname=.*|Hostname=monitordhost1|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|Server =.*|Server=localhost,monitor.zabbixnetwork|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|ServerActive.*=.*|ServerActive=localhost,monitor.zabbixnetwork |g' /etc/zabbix/zabbix_agentd.conf|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|.*EnableRemoteCommands.*=.*|EnableRemoteCommands=1|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|.*LogRemoteCommands.*=.*|LogRemoteCommands=1|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|# Include=.*|Include=/etc/zabbix/zabbix_agentd.d/*.conf|' /etc/zabbix/zabbix_agentd.conf

rc-update add zabbix-agentd default

rc-service zabbix-agentd restart
</nowiki>
</pre>

{{Note|The configured hostname in agent here is <nowiki>monitordhost1</nowiki>, you can use the ip address too, but this name must be the same in the config files for active or passive host configured in the web ui, if the agent way will be used.}}

{{Warning|The '''zabbix service DB user is <code>zabbixdb</code> and NOT <code>zabbix</code>''', the user <code>zabbixdb</code> whatever you use postgresql or mysql, will not be used with password for agent monitoring, cos in postgresql will use the <code>ident</code> way autentication, and in mysql will use the <code>socket</code> way autentication: '''this makes sense cos user agent is <code>zabbix</code> and does not have home.''' This is the correct and most secure way.}}

== Monitor by SNMP protocol ==

Then in each monitor system, you shoul define a community word due ISP's always will punish if you used <code>public</code> and open SNMP to world, inclusivelly you shoul protect inside your private network too, so on each host to be monitored you shoul made those commands to configure SNMP (including the zabbix server) using the <code></code>:

<pre>
<nowiki>
apk add net-snmp net-snmp-perl net-snmp-tools

cat > /etc/snmp/snmpd.conf << EOF
view systemonly included .1.3.6.1.2.1.1
view systemonly included .1.3.6.1.2.1.25.1
rocommunity monitor
rocommunity public localhost
rocommunity public default -V systemonly
sysLocation client host at monitor.zabbixnetwork
sysContact infoadmin <venenux@venenux.net>
sysServices 72
EOF

rc-update add snmpd default

rc-service snmpd restart
</nowiki>
</pre>

This will allow to any host consult SNMP v2c with word "monitor", but is best you use if have distributed network a SNMP v3 autentiated.

{{Warning|So in zabbix web ui you must use "monitor" in the community word.}}

== DB monitoring ==

Due limitations any host server that will have and database, must have and agent aither have behind or not have direct zabbix server/proxy connection.

In this case we used Mysql as example

=== on the monitored side: DBMS server to monitor ===

<pre>
<nowiki>
apk add zabbix-agent

sed -i 's|.*ListenPort.*=.*|ListenPort=10050|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|.*Hostname=.*|Hostname=theDBserver|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|Server =.*|Server=monitor.zabbixnetwork|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|ServerActive.*=.*|ServerActive=monitor.zabbixnetwork|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|.*EnableRemoteCommands.*=.*|EnableRemoteCommands=1|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|.*LogRemoteCommands.*=.*|LogRemoteCommands=1|g' /etc/zabbix/zabbix_agentd.conf
sed -i 's|# Include=|Include=/etc/zabbix/zabbix_agentd.d/*.conf|g' /etc/zabbix/zabbix_agentd.conf

rc-update add zabbix-agentd default

rc-service zabbix-agentd restart

apk add mariadb-client

wget -O /etc/zabbix/zabbix-agent/userparameter_mysql.conf "https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/conf/zabbix_agentd/userparameter_mysql.conf?at=release/5.0"

mysql -u root -e "CREATE USER 'zabbix'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('zabbix.db.1.com.1');"
mysql -u root -e "GRANT USAGE,REPLICATION CLIENT,PROCESS,SHOW DATABASES,SHOW VIEW ON *.* TO 'zabbix'@'localhost';"
</nowiki>
</pre>

{{Note|The configured hostname in agent here is <nowiki>theDBserver</nowiki>, you can use the ip address too, but this name must be the same in the config files for active or passive host configured in the web ui, if the agent way will be used to monitor this server host.}}

{{Warning|An new issue of missing files is on alpine zabbix packages so read [https://gitlab.alpinelinux.org/alpine/aports/-/issues/12791 <nowiki>https://gitlab.alpinelinux.org/alpine/aports/-/issues/12791</nowiki>], for files that you could download manually}}

=== on the zabbix server side: to configure the monitored host ===

Use the ''template DB mysql'' o ''DB postgresql'' on the configured host and remenber that hostname in the configured host must be the same in the hostname configured in the agent configuration file of the monitored host.

For most details check the next section crash course:

= Optional: Crash course in adding hosts, checks, and notifications =

''Note:'' This is optional since it's not specific to Alpine Linux, but I wanted a couple notes for how to perform a simple check on a server that doesn't have the agent installed on it, and be notified on state changes.

Administration -> Media Types -> Email
* Setup server, helo, email from address

Administration -> Users
* Setup each user who'll get notified, make sure they have media type "Email" added with their address

Configuration -> Hosts -> Create host
* In Linux Servers hostgroup
* Define dns name, ip, connect by IP
* If the machine is a simple networking device that will only be monitored using SNMP, add it to Template_SNMPv2_Device, and you're done.

Configuration -> Templates -> Create template
* Give it a name (Template_Alpine_Linux_Infra_HTTP)
* In Templates group

Configuration -> Templates -> Template_Alpine_Linux_Infra_HTTP -> Items
* Create Item
* Host: Template_Alpine_Linux_Infra_HTTP
* Description: HTTP Basic Check
* Type: Simple_check
* Key: http,80

Configuration -> Templates -> Template_Alpine_Linux_Infra_HTTP -> Triggers
* Create Trigger
* Name: "HTTP Trigger"
* Expression: {Template_Alpine_Linux_Infra_HTTP:http,80.last(0)}#1
* Severity: High

Configuration -> Actions ->
* Create Action
* name: Email notifications
* Event source: triggers
* Default Subject: add "{HOST.DNS}:" to the beginning
* Default message: add "{HOST.DNS}:" to the beginning
* Conditions: make host have to be from "Linux Servers" hostgroup, and Template_Alpine_Linux_Infra_HTTP:HTTP trigger" is not 1
* Email affected users

== See also ==

* [[Cacti:_traffic_analysis_and_monitoring_network]]

== External resources and complete guides ==

* [https://qgqlochekone.blogspot.com/search/?q=zabbix Complete alpine documentation on englilsh for zabbix curse]
* [https://vegnuli.wordpress.com/?s=zabbix Complete alpine documentation on spanish for zabbix curse]

[[Category:Monitoring]]
[[Category:PHP]]
[[Category:SQL]]

Converting mediawiki database from postgresql to mysql/mariadb

2025-03-11T23:35:30Z

Greenman: /* Why to use mysql/mariadb instead of postgresql? */ clarify Wikipedia uses MariaDB

==Why to use mysql/mariadb instead of postgresql?==

Here is what [[MediaWiki]] says [[https://www.mediawiki.org/wiki/Manual:PostgreSQL]]:
<blockquote>Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems. 
Is PostgreSQL a good choice for MediaWiki? 
Wikipedia uses MariaDB so MediaWiki gets more testing on MariaDB than PostgreSQL. While support for PostgreSQL is maintained by volunteers, most core functionality is working.</blockquote>

==Challenge==
====Why not to use dumpBackup.php script in mediawiki? [[https://www.mediawiki.org/wiki/Manual:DumpBackup.php]]====
If you need to save only mediawiki data and pages modify history then the best and easiest way probably would be:
{{Cmd|<nowiki>php maintenance/dumpBackup.php --full --include-files --uploads > /tmp/wiki.xml
php maintenance/importDump.php /tmp/wiki.xml
</nowiki>}}
However user accounts, user contrubutions, logs, etc will not be migrated this way. 

====Why not to use tools for converting postgresql databases to mysql?====
Mediawiki postgresql version is quite different from mysql version. For example: some table names are different, field order, timestamp format (pg: '2015-11-01 08:42:31+00', mysql: '20151101084231'), many fields that are NULL in postgresql are not allowed to be NULL in mysql. That is why there will be issues when dumping data from postgresql and importing it to mysql. 
 
There is a script mentioned in https://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql that should be converting postgresql database to mysql. However in my case it did not work because of the issues mentioned above (that script was written in 2009-01-16).

==Mediawiki location==
In this example mediawiki files root is located in '''/var/www/wiki.wikiname.org/htdocs/w''' 
Web server is '''lighttpd'''. /etc/lighttpd/lighttpd.conf contains lines:
{{Cmd|<nowiki>simple-vhost.server-root = "/var/www"
simple-vhost.default-host = "wiki.wikiname.org"
simple-vhost.document-root = "/htdocs/"
url.rewrite-once = (
"^/wiki/([^?]*)(?:\?(.*))?" => "/w/index.php?title=$1&$2",
"^/wiki" => "/w/index.php",
)
</nowiki>}}

==Disallowing editing mediawiki until database is converted==
{{Cmd|vi /var/www/wiki.wikiname.org/htdocs/w/LocalSettings.php}}
<nowiki>$wgReadOnly = 'This wiki is currently being upgraded to a newer software version.';</nowiki>

==Preparing dump files for importing data to mysql==
====Making postgresql dump====
{{Cmd|<nowiki>su - postgres
pg_dump wikidb > /tmp/wikidb-pg.dump
exit
</nowiki>}}

====Making temporary postgresql database looking like mysql====
{{Cmd|<nowiki>su - postgres
echo "CREATE DATABASE tmpdb ENCODING 'SQL_ASCII' LC_COLLATE='C' LC_CTYPE='C' TEMPLATE=template0;" | psql
psql tmpdb < /tmp/wikidb-pg.dump
exit
</nowiki>}}

====SQL script modifying postgresql database====
For avoiding issues when importing data to mysql we need to modify mediawiki postgresql database so that it would look like mediawiki mysql database. Basically the script will make field order in pg tables like in mysql and will set non-NULL values to the fields that are not allowed to be NULL in mysql 
'''/tmp/pg-prepare-for-mysql.sql'''
{{Cmd|<nowiki>/* archive */
CREATE VIEW tmp_view AS
select ar_id, ar_namespace, ar_title, ar_text, ar_comment, ar_user, ar_user_text, ar_timestamp, ar_minor_edit, ar_flags, ar_rev_id, ar_text_id, ar_deleted, ar_len, ar_page_id, ar_parent_id, ar_sha1, ar_content_model, ar_content_format FROM archive;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set ar_text = '' where ar_text is NULL;
update tmp_table set ar_comment = '' where ar_comment is NULL;
update tmp_table set ar_user = 0 where ar_user is NULL;
update tmp_table set ar_flags = '' where ar_flags is NULL;
ALTER TABLE archive RENAME TO orig_archive;
ALTER TABLE tmp_table RENAME TO archive;

/* category */
CREATE VIEW tmp_view AS
select cat_id, cat_title, cat_pages, cat_subcats, cat_files FROM category;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
ALTER TABLE category RENAME TO orig_category;
ALTER TABLE tmp_table RENAME TO category;

/* categorylinks */
CREATE VIEW tmp_view AS
select cl_from, cl_to, cl_sortkey, cl_sortkey_prefix, cl_timestamp, cl_collation, cl_type FROM categorylinks;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set cl_sortkey = '' where cl_sortkey is NULL;
ALTER TABLE categorylinks RENAME TO orig_categorylinks;
ALTER TABLE tmp_table RENAME TO categorylinks;

/* change_tag (OK) */

/* externallinks */
CREATE VIEW tmp_view AS
select el_id, el_from, el_to, el_index FROM externallinks;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
ALTER TABLE externallinks RENAME TO orig_externallinks;
ALTER TABLE tmp_table RENAME TO externallinks;

/* filearchive (OK) */

/* image */
CREATE VIEW tmp_view AS
select img_name, img_size, img_width, img_height, img_metadata, img_bits, img_media_type, img_major_mime, img_minor_mime, img_description, img_user, img_user_text, img_timestamp, img_sha1 FROM image;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set img_bits = 0 where img_bits is NULL;
update tmp_table set img_major_mime = '' where img_major_mime is NULL;
update tmp_table set img_minor_mime = '' where img_minor_mime is NULL;
ALTER TABLE image RENAME TO orig_image;
ALTER TABLE tmp_table RENAME TO image;

/* imagelinks */
CREATE VIEW tmp_view AS
select il_from, il_from_namespace, il_to FROM imagelinks;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
ALTER TABLE imagelinks RENAME TO orig_imagelinks;
ALTER TABLE tmp_table RENAME TO imagelinks;

/* interwiki */
CREATE VIEW tmp_view AS
select iw_prefix, iw_url, iw_api, iw_wikiid, iw_local, iw_trans FROM interwiki;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
ALTER TABLE interwiki RENAME TO orig_interwiki;
ALTER TABLE tmp_table RENAME TO interwiki;

/* ipblocks */
CREATE VIEW tmp_view AS
select ipb_id, ipb_address, ipb_user, ipb_by, ipb_by_text, ipb_reason, ipb_timestamp, ipb_auto, ipb_anon_only, ipb_create_account, ipb_enable_autoblock, ipb_expiry, ipb_range_start, ipb_range_end, ipb_deleted, ipb_block_email, ipb_allow_usertalk, ipb_parent_block_id FROM ipblocks;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set ipb_address = '' where ipb_address is NULL;
update tmp_table set ipb_user = 0 where ipb_user is NULL;
update tmp_table set ipb_range_start = '' where ipb_range_start is NULL;
update tmp_table set ipb_range_end = '' where ipb_range_end is NULL;
ALTER TABLE ipblocks RENAME TO orig_ipblocks;
ALTER TABLE tmp_table RENAME TO ipblocks;

/* iwlinks (OK) */

/* job */
CREATE VIEW tmp_view AS
select job_id, job_cmd, job_namespace, job_title, job_timestamp, job_params, job_random, job_attempts, job_token, job_token_timestamp, job_sha1 FROM job;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
ALTER TABLE job RENAME TO orig_job;
ALTER TABLE tmp_table RENAME TO job;

/* l10n_cache (OK) */

/* langlinks */
CREATE VIEW tmp_view AS
select ll_from, ll_lang, ll_title FROM langlinks;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set ll_lang = '' where ll_lang is NULL;
update tmp_table set ll_title = '' where ll_title is NULL;
ALTER TABLE langlinks RENAME TO orig_langlinks;
ALTER TABLE tmp_table RENAME TO langlinks;

/* logging */
CREATE VIEW tmp_view AS
select log_id, log_type, log_action, log_timestamp, log_user, log_user_text, log_namespace, log_title, log_page, log_comment, log_params, log_deleted FROM logging;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set log_user = 0 where log_user is NULL;
update tmp_table set log_comment = '' where log_comment is NULL;
update tmp_table set log_params = '' where log_params is NULL;
ALTER TABLE logging RENAME TO orig_logging;
ALTER TABLE tmp_table RENAME TO logging;

/* log_search (OK) */

/* module_deps (OK) */

/* msg_resource (OK) */

/* msg_resource_links (OK) */

/* objectcache */
CREATE VIEW tmp_view AS
select keyname, value, exptime FROM objectcache;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set keyname = '' where keyname is NULL;
ALTER TABLE objectcache RENAME TO orig_objectcache;
ALTER TABLE tmp_table RENAME TO objectcache;

/* oldimage */
CREATE VIEW tmp_view AS
select oi_name, oi_archive_name, oi_size, oi_width, oi_height, oi_bits, oi_description, oi_user, oi_user_text, oi_timestamp, oi_metadata, oi_media_type, oi_major_mime, oi_minor_mime, oi_deleted, oi_sha1 FROM oldimage;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set oi_bits = 0 where oi_bits is NULL;
update tmp_table set oi_description = '' where oi_description is NULL;
update tmp_table set oi_user = 0 where oi_user is NULL;
update tmp_table set oi_major_mime = '' where oi_major_mime is NULL;
update tmp_table set oi_minor_mime = '' where oi_minor_mime is NULL;
ALTER TABLE oldimage RENAME TO orig_oldimage;
ALTER TABLE tmp_table RENAME TO oldimage;

/* page */
CREATE VIEW tmp_view AS
select page_id, page_namespace, page_title, page_restrictions, page_is_redirect, page_is_new, page_random, page_touched, page_links_updated, page_latest, page_len, page_content_model, page_lang FROM page;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set page_restrictions = '' where page_restrictions is NULL;
/*update tmp_table set page_touched = '' where page_touched is NULL;*/
ALTER TABLE page RENAME TO orig_page;
ALTER TABLE tmp_table RENAME TO page;

/* pagelinks */
CREATE VIEW tmp_view AS
select pl_from, pl_from_namespace, pl_namespace, pl_title FROM pagelinks;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
ALTER TABLE pagelinks RENAME TO orig_pagelinks;
ALTER TABLE tmp_table RENAME TO pagelinks;

/* page_props (OK) */

/* page_restrictions */
CREATE VIEW tmp_view AS
select pr_id, pr_page, pr_type, pr_level, pr_cascade, pr_user, pr_expiry FROM page_restrictions;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
ALTER TABLE page_restrictions RENAME TO orig_page_restrictions;
ALTER TABLE tmp_table RENAME TO page_restrictions;

/* protected_titles */
CREATE VIEW tmp_view AS
select pt_namespace, pt_title, pt_user, pt_reason, pt_timestamp, pt_expiry, pt_create_perm FROM protected_titles;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set pt_user = 0 where pt_user is NULL;
/*update tmp_table set pt_expiry = '' where pt_expiry is NULL;*/
ALTER TABLE protected_titles RENAME TO orig_protected_titles;
ALTER TABLE tmp_table RENAME TO protected_titles;

/* querycache (OK) */

/* querycachetwo (OK) */

/* querycache_info */
CREATE VIEW tmp_view AS
select qci_type, qci_timestamp FROM querycache_info;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set qci_type = '' where qci_type is NULL;
/* update tmp_table set qci_timestamp = '' where qci_timestamp is NULL; */
ALTER TABLE querycache_info RENAME TO orig_querycache_info;
ALTER TABLE tmp_table RENAME TO querycache_info;

/* recentchanges (rc_cur_time was removed in 1.24wmf6) */
CREATE VIEW tmp_view AS
select rc_id, rc_timestamp, rc_user, rc_user_text, rc_namespace, rc_title, rc_comment, rc_minor, rc_bot, rc_new, rc_cur_id, rc_this_oldid, rc_last_oldid, rc_type, rc_source, rc_patrolled, rc_ip, rc_old_len, rc_new_len, rc_deleted, rc_logid, rc_log_type, rc_log_action, rc_params FROM recentchanges;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set rc_user = 0 where rc_user is NULL;
update tmp_table set rc_comment = '' where rc_comment is NULL;
update tmp_table set rc_cur_id = 0 where rc_cur_id is NULL;
/* update tmp_table set rc_ip = '' where rc_ip is NULL; */
ALTER TABLE recentchanges RENAME TO orig_recentchanges;
ALTER TABLE tmp_table RENAME TO recentchanges;

/* redirect (OK) */

/* revision */
CREATE VIEW tmp_view AS
select rev_id, rev_page, rev_text_id, rev_comment, rev_user, rev_user_text, rev_timestamp, rev_minor_edit, rev_deleted, rev_len, rev_parent_id, rev_sha1, rev_content_model, rev_content_format FROM revision;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set rev_page = 0 where rev_page is NULL;
update tmp_table set rev_text_id = 0 where rev_text_id is NULL;
update tmp_table set rev_comment = '' where rev_comment is NULL;
ALTER TABLE revision RENAME TO orig_revision;
ALTER TABLE tmp_table RENAME TO revision;

/* searchindex (does not exists in pg) */

/* sites (OK) */

/* site_identifiers (OK) */

/* site_stats */
CREATE VIEW tmp_view AS
select ss_row_id, ss_total_edits, ss_good_articles, ss_total_pages, ss_users, ss_active_users, ss_images FROM site_stats;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
ALTER TABLE site_stats RENAME TO orig_site_stats;
ALTER TABLE tmp_table RENAME TO site_stats;

/* tag_summary (OK) */

/* templatelinks */
CREATE VIEW tmp_view AS
select tl_from, tl_from_namespace, tl_namespace, tl_title FROM templatelinks;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
ALTER TABLE templatelinks RENAME TO orig_templatelinks;
ALTER TABLE tmp_table RENAME TO templatelinks;

CREATE VIEW tmp_view AS
select old_id, old_text, old_flags FROM pagecontent;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set old_text = '' where old_text is NULL;
update tmp_table set old_flags = '' where old_flags is NULL;
ALTER TABLE pagecontent RENAME TO orig_pagecontent;
ALTER TABLE tmp_table RENAME TO pagecontent;

/* transcache (OK) */

/* updatelog (OK) */

/* uploadstash */
CREATE VIEW tmp_view AS
select us_id, us_user, us_key, us_orig_path, us_path, us_source_type, us_timestamp, us_status, us_chunk_inx, us_props, us_size, us_sha1, us_mime, us_media_type, us_image_width, us_image_height, us_image_bits FROM uploadstash;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set us_user = 0 where us_user is NULL;
update tmp_table set us_key = '' where us_key is NULL;
update tmp_table set us_orig_path = '' where us_orig_path is NULL;
update tmp_table set us_path = '' where us_path is NULL;
/* update tmp_table set us_timestamp = '' where us_timestamp is NULL; */
update tmp_table set us_status = '' where us_status is NULL;
update tmp_table set us_size = 0 where us_size is NULL;
update tmp_table set us_sha1 = '' where us_sha1 is NULL;
ALTER TABLE uploadstash RENAME TO orig_uploadstash;
ALTER TABLE tmp_table RENAME TO uploadstash;

/* user (in pg it is named mwuser)*/
CREATE VIEW tmp_view AS
select user_id, user_name, user_real_name, user_password, user_newpassword, user_newpass_time, user_email, user_touched, user_token, user_email_authenticated, user_email_token, user_email_token_expires, user_registration, user_editcount, user_password_expires FROM mwuser;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set user_real_name = '' where user_real_name is NULL;
update tmp_table set user_password = '' where user_password is NULL;
update tmp_table set user_newpassword = '' where user_newpassword is NULL;
update tmp_table set user_token = '' where user_token is NULL;
update tmp_table set user_email = '' where user_email is NULL;
/* update tmp_table set user_touched = '' where user_touched is NULL; */
delete from tmp_table where user_id = 0;
ALTER TABLE mwuser RENAME TO orig_mwuser;
ALTER TABLE tmp_table RENAME TO mwuser;
/* it cannot be named 'user', like in mysql */

/* user_former_groups */
CREATE VIEW tmp_view AS
select ufg_user, ufg_group FROM user_former_groups;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set ufg_user = 0 where ufg_user is NULL;
ALTER TABLE user_former_groups RENAME TO orig_user_former_groups;
ALTER TABLE tmp_table RENAME TO user_former_groups;

/* user_groups */
CREATE VIEW tmp_view AS
select ug_user, ug_group FROM user_groups;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set ug_user = 0 where ug_user is NULL;
ALTER TABLE user_groups RENAME TO orig_user_groups;
ALTER TABLE tmp_table RENAME TO user_groups;

/* user_newtalk */
CREATE VIEW tmp_view AS
select user_id, user_ip, user_last_timestamp FROM user_newtalk;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set user_ip = '' where user_ip is NULL;
ALTER TABLE user_newtalk RENAME TO orig_user_newtalk;
ALTER TABLE tmp_table RENAME TO user_newtalk;

/* user_properties */
CREATE VIEW tmp_view AS
select up_user, up_property, up_value FROM user_properties;
SELECT * INTO tmp_table FROM tmp_view;
DROP VIEW tmp_view;
update tmp_table set up_user = 0 where up_user is NULL;
ALTER TABLE user_properties RENAME TO orig_user_properties;
ALTER TABLE tmp_table RENAME TO user_properties;

/* valid_tag (OK) */

/* watchlist (OK) */

/* external_user (not needed) */
ALTER TABLE external_user RENAME TO orig_external_user;

/* hitcounter (removed in MediaWiki 1.25) */
ALTER TABLE hitcounter RENAME TO orig_hitcounter;

/* mediawiki_version ('Nobody actually uses it anymore, it just gets silently updated (and only for Postgres at that)') */
ALTER TABLE mediawiki_version RENAME TO orig_mediawiki_version;

/* profiling ('It's only used in an obscure debugging mode.') */
ALTER TABLE profiling RENAME TO orig_profiling;

/* trackbacks (This feature was removed completely in version MediaWiki 1.19.) */
ALTER TABLE trackbacks RENAME TO orig_trackbacks;
</nowiki>}}

{{Cmd|<nowiki>su - postgres
psql tmpdb < /tmp/pg-prepare-for-mysql.sql
exit
</nowiki>}}

====Making dump files for mysql import====
{{Cmd|<nowiki>su - postgres
mkdir /tmp/wikidb-pg-dump
psql -d wikidb -c "SELECT table_schema || '.' || table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');" > /tmp/t
cat /tmp/t | grep mediawiki | sed 's/ mediawiki.//g' | sort > /tmp/pg-tables.txt

for t in `cat /tmp/pg-tables.txt`; do echo "dumping $t"; pg_dump -d tmpdb --data-only --inserts -t mediawiki.$t > /tmp/wikidb-pg-dump/$t.sql; done
exit
</nowiki>}}

====Modifying dump files====
Postgresql specific sql should be removed
{{Cmd|<nowiki>cd /tmp/wikidb-pg-dump
for f in *.sql; do sed -i 's/^SET statement_timeout = 0;$//g' $f; done
for f in *.sql; do sed -i 's/^SET lock_timeout = 0;$//g' $f; done
for f in *.sql; do sed -i "s/^SET client_encoding = 'SQL_ASCII';$//g" $f; done
for f in *.sql; do sed -i 's/^SET standard_conforming_strings = on;$//g' $f; done
for f in *.sql; do sed -i 's/^SET check_function_bodies = false;$//g' $f; done
for f in *.sql; do sed -i 's/^SET client_min_messages = warning;$//g' $f; done
for f in *.sql; do sed -i 's/^SET search_path = mediawiki, pg_catalog;$//g' $f; done
for f in *.sql; do sed -i "s/^SELECT pg_catalog.setval('filearchive_fa_id_seq', 25, true);$//g" $f; done
</nowiki>}}

Postgresql table '''mwuser''' corresponds to mysql table '''user''' 
{{Cmd|<nowiki>for f in *.sql; do sed -i "s/^INSERT INTO mwuser VALUES/INSERT INTO user VALUES/g" $f; done</nowiki>}}

Postgresql table '''pagecontent''' corresponds to mysql table '''text''' 
{{Cmd|<nowiki>for f in *.sql; do sed -i "s/^INSERT INTO pagecontent VALUES/INSERT INTO text VALUES/g" $f; done</nowiki>}}

Back slashes should be replaced with double backslashes
{{Cmd|<nowiki>for f in *.sql; do sed -i 's/\\/\\\\/g' $f; done</nowiki>}}

Timestamp format in postgresql is different from mysql (pg: '2015-10-31 10:10:10+00', mysql: '20151031101010'). All timestamps should be converted to avoid errors like: 
<code>... Language::sprintfDate: The timestamp should be a number</code>
{{Cmd|<nowiki>for f in *.sql; do sed -i "s/'$[0-9][0-9][0-9][0-9]$-$[0-9][0-9]$-$[0-9][0-9]$ $[0-9][0-9]$:$[0-9][0-9]$:$[0-9][0-9]$[+-][0-9][0-9]'/'\1\2\3\4\5\6'/g" $f; done</nowiki>}}

In postgresql '''ipblocks''' table may contain records with duplicated field '''ipb_address'''. mysql requires that ipb_address field is unique.
Check manually '''ipblocks.sql''' file. Delete records with duplicated field '''ipb_address''' to prevent errors like: 
<code># ERROR 1062 (23000) at line 152: Duplicate entry 'xxxxxx-0-0-0' for key 'ipb_address'</code>
{{Cmd|vi ipblocks.sql}}

==Changing mysql root password, creating user for mediawiki==
{{Cmd|<nowiki>mysql -u root
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('newpassword');
SET PASSWORD FOR 'root'@'::1' = PASSWORD('newpassword');
FLUSH PRIVILEGES;
CREATE USER 'wikiuser'@'localhost' IDENTIFIED BY 'wikiuserpassword';
GRANT ALL PRIVILEGES ON *.* TO 'wikiuser'@'localhost' WITH GRANT OPTION;
CREATE USER 'wikiuser'@'%' IDENTIFIED BY 'wikiuserpassword';
GRANT ALL PRIVILEGES ON *.* TO 'wikiuser'@'%' WITH GRANT OPTION;
exit
</nowiki>}}

==Setting up new mediawiki==
{{Cmd|<nowiki>cd /var/www/wiki.wikiname.org/htdocs
wget https://releases.wikimedia.org/mediawiki/1.25/mediawiki-1.25.3.tar.gz
tar zxf mediawiki-1.25.3.tar.gz
mv mediawiki-1.25.3 w
rm mediawiki-1.25.3.tar.gz
</nowiki>}}

{{Cmd|vi index.php}}
<nowiki>
<?php
header('Location: /wiki');
?></nowiki>

Open your wiki in browser <nowiki>https://wiki.wikiname.org</nowiki>, go through setup process (DB type:mysql, engin: innodb, charset: binary) 
Compare LocalSettings.php file generated by setup process with old mediawiki LocalSettings.php file 
Take needed settings from old LocalSettings.php 
Copy new LocalSettings.php into mediawiki directory /var/www/wiki.wikiname.org/htdocs/w 
Copy needed files from old mediawiki (images/*, extensions/..., resources/...)

{{Cmd|<nowiki>cd /var/www/wiki.wikiname.org/htdocs/w
find . -type d > /tmp/d
while read d; do chmod 755 "$d"; done < /tmp/d
find . -type f > /tmp/f
while read f; do chmod 644 "$f"; done < /tmp/f
chown -R lighttpd:lighttpd .
cd /var/www/wiki.wikiname.org/htdocs/w
php maintenance/update.php # it will activate extensions and create needed tables in mysql db
</nowiki>}}

==Clearing mysql tables before import==
{{Cmd|<nowiki>mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 -Bse 'show tables;' > /tmp/t
for t in `cat /tmp/t`; do echo "TRUNCATE $t;"; done >/tmp/mysql-clear-tables.sql
mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 < /tmp/mysql-clear-tables.sql
</nowiki>}}

==Importing data into mysql==
{{Cmd|<nowiki>for t in `cat /tmp/pg-tables.txt`; do echo "importing $t"; mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8 < /path/to/wikidb-pg-dump/$t.sql; done
</nowiki>}}

==Running maintenance scripts==
{{Cmd|php maintenance/update.php
php maintenance/rebuildrecentchanges.php
php maintenance/rebuildtextindex.php
}}

==Regenerating cache==
Old cache in '''l10n_cache''' table may couse problems. It can be deleted and regenerated again.
{{Cmd|<nowiki>echo "TRUNCATE l10n_cache;" | mysql wikidb -uUSERNAME -pPASSWORD --default-character-set=utf8
cd /var/www/wiki.wikiname.org/htdocs/w
php maintenance/rebuildLocalisationCache.php
</nowiki>}}

==See also==
* [[MariaDB]]

[[Category:SQL]]

Production LAMP system: Lighttpd + PHP + MySQL

2025-03-11T23:34:00Z

Greenman: /* Configuration */ already default

In production web, '''LAMP''' means '''L'''inux + '''A'''pache + '''M'''ysql + '''P'''hp installed and integrated, but today, more and more, ''Apache'' is being replaced by ''Nginx'' or ''Lighttpd'' and ''[[MySQL]]'' by ''[[MariaDB]]''. The ''LAMP'' documents are:

== 1. The web server: Lighttpd ==

[https://www.lighttpd.net/ lighttpd] is a simple, standards-compliant, secure, and flexible web server, Nginx is the most used due to beeing manageable by ISP panel's software, but '''lighttpd performs better. Nginx cannot process fast-cgi programs'''. For more lighttpd information, consult the [[Production Web server: Lighttpd]] wiki page.

=== Lighttpd Installation ===

Production environment will handle only needed packages. So no doc or managers allowed:

# run apk for needed packages

<pre>
<nowiki>
apk add lighttpd gamin
</nowiki>
</pre>

=== Lighttpd pre php configuration ===

# make the htdos public web root directories
# change default port to production one, http is used with 80
# use FAM style (gamin) file alteration monitor, increases performance '''ONLY ON 3.4 to 3.8 releases!!!'''
# use linux event handler, increases performance due Alpine are linux only
# added the service to the default runlevel, not to boot, because networking needs to be active first
# started the web server service
# Enable the mod_status at the config files
# change path in the config file, we are using security by obfuscation
# restart the service to see changes at the browser

<pre>
<nowiki>
mkdir -p /var/www/localhost/htdocs/stats /var/log/lighttpd /var/lib/lighttpd

sed -i -r 's#\#.*server.port.*=.*#server.port = 80#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#\#.*server.event-handler = "linux-sysepoll".*#server.event-handler = "linux-sysepoll"#g' /etc/lighttpd/lighttpd.conf

chown -R lighttpd:lighttpd /var/www/localhost/

chown -R lighttpd:lighttpd /var/lib/lighttpd

chown -R lighttpd:lighttpd /var/log/lighttpd

rc-update add lighttpd default

rc-service lighttpd restart

echo "it works" > /var/www/localhost/htdocs/index.html

sed -i -r 's#\#.*mod_status.*,.*# "mod_status",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*status.status-url.*=.*#status.status-url = "/stats/server-status"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*status.config-url.*=.*#status.config-url = "/stats/server-config"#g' /etc/lighttpd/lighttpd.conf

rc-service lighttpd restart
</nowiki>
</pre>

'''For testing, open a browser and go to <code><nowiki>http://<webserveripaddres></nowiki></code>. You will see "it works"'''. The "webserveripaddres" is the ip address of your setup/server machine.

'''There's a problem in Alpine linux, FAM (gamin) is activated as a lighttpd only service''', that makes sense in docker, but on a server, it could be a problem if FAM (gamin) is also needed for other services at the same time.

'''OPTIONAL:''' alpine packagers are a mess, removed FAM on recent, so older releases of alpine can use compiled FAM packages with <code>sed -i -r 's#.*server.stat-cache-engine.*=.*# server.stat-cache-engine = "fam"#g' /etc/lighttpd/lighttpd.conf</code>

== 2. php scripting: PHP fpm ==

In Alpine there are two main languages for programming dynamic web pages: PHP and LUA. Alpine is minimalist so not all PHP packages are need in most cases.
Both repositories must be enabled (main and community). Here we explain the most common use in production.

=== PHP Installation ===

{{Obsolete|Update to a newer or generic php version}}

<pre><nowiki>
apk add php7 php7-bcmath php7-bz2 php7-ctype php7-curl php7-dom php7-enchant php7-exif php7-fpm php7-gd php7-gettext php7-gmp php7-iconv php7-imap php7-intl php7-json php7-mbstring php7-opcache php7-openssl php7-phar php7-posix php7-pspell php7-recode php7-session php7-simplexml php7-sockets php7-sysvmsg php7-sysvsem php7-sysvshm php7-tidy php7-xml php7-xmlreader php7-xmlrpc php7-xmlwriter php7-xsl php7-zip php7-sqlite3
</nowiki></pre>

{{Note|'''The packages below are only for specific situations. Install them only when needed (especially php-pear)''', for example, <code>cacti</code> and <code>cacti-php7</code> depend on <code>php7</code>, but you must install '''only''' the cacti package, all the dependencies must be previously installed from stable.}}

<pre><nowiki>
apk add php7-pgsql php7-mysqli php7-mysqlnd php7-snmp php7-soap php7-ldap php7-pcntl php7-pear php7-shmop php7-wddx php7-cgi php7-pdo php7-snmp php7-tokenizer
</nowiki></pre>

{{Note|'''The packages below are only for database access using php in specific ways. Install them only if you need them (specially php--pdo ones)'''. For example, <code>cacti</code> and <code>cacti-php7</code> depend on <code>php7-mysqli</code>, but you must install only the cacti package, all the dependencies like <code>php7</code> and <code>php7-mysqli</code> must be previously installed from stable.}}

<pre><nowiki>
apk add php7-dba php7-sqlite3 php7-mysqli php7-mysqlnd php7-pgsql php7-pdo_dblib php7-pdo_odbc php7-pdo_pgsql php7-pdo_sqlite
</nowiki></pre>

A special case is <code>php7-odbc</code>. Unless the others, that are able php to connect to only specific database, '''[[Production DataBases : unixodbc|unixodbc]]''' is a universal way to do so. The most important difference is, for example, the <code>php7-mysqli</code> package has better functions to manage data via php.

=== PHP Global Configuration ===

# Use fix.pathinfo
# Set safe mode off
# Dont expose php code if something fails
# Set amount of memory limit for execution to 536Mb (most servers are minimum of 1 GB of RAM)
# Set upload size to 128Mb as maximun.
# Set POST max size to 256Mb based on the upload max size limit.
# Turn on the URL open method
# Set default charset to UTF-8 for increased compatibility
# Increase the execution time and the input time for.

<pre><nowiki>
sed -i -r 's|.*cgi.fix_pathinfo=.*|cgi.fix_pathinfo=1|g' /etc/php*/php.ini
sed -i -r 's#.*safe_mode =.*#safe_mode = Off#g' /etc/php*/php.ini
sed -i -r 's#.*expose_php =.*#expose_php = Off#g' /etc/php*/php.ini
sed -i -r 's#memory_limit =.*#memory_limit = 536M#g' /etc/php*/php.ini
sed -i -r 's#upload_max_filesize =.*#upload_max_filesize = 128M#g' /etc/php*/php.ini
sed -i -r 's#post_max_size =.*#post_max_size = 256M#g' /etc/php*/php.ini
sed -i -r 's#^file_uploads =.*#file_uploads = On#g' /etc/php*/php.ini
sed -i -r 's#^max_file_uploads =.*#max_file_uploads = 12#g' /etc/php*/php.ini
sed -i -r 's#^allow_url_fopen = .*#allow_url_fopen = On#g' /etc/php*/php.ini
sed -i -r 's#^.default_charset =.*#default_charset = "UTF-8"#g' /etc/php*/php.ini
sed -i -r 's#^.max_execution_time =.*#max_execution_time = 150#g' /etc/php*/php.ini
sed -i -r 's#^max_input_time =.*#max_input_time = 90#g' /etc/php*/php.ini
</nowiki></pre>

=== PHP-FPM Configuration ===

# Create directory for php socket and pid files, MUST BE EQUAL to openrc defined!
# Set into configuration file the socket path, MUST BE EQUAL to openrc defined!
# Set into configuration file the pid file path, MUST BE EQUAL to openrc defined!

<pre>
<nowiki>
mkdir -p /var/run/php-fpm7/

chown lighttpd:root /var/run/php-fpm7

sed -i -r 's|^.*listen =.*|listen = /run/php-fpm7/php7-fpm.sock|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^pid =.*|pid = /run/php-fpm7/php7-fpm.pid|g' /etc/php*/php-fpm.conf

sed -i -r 's|^.*listen.mode =.*|listen.mode = 0640|g' /etc/php*/php-fpm.d/www.conf

rc-update add php-fpm7 default

service php-fpm7 restart
</nowiki>
</pre>

The PHP-FPM defines a master process with a process pool for each service request. By default, there's only one process pool, www.

Default values are good for starting, but will need tuning later. The best is a static one, but testing is needed to get the right configuration.

=== Lighttpd + PHP-FPM ===

The web server comes with a minimal config file, so we must handle all the required settings:

# enable the mod_alias at the config file, a specific path is needed for cgi file security
# be sure and disable the fastcgi-php module by cgi only
# and then enable the fastcgi-php-fpm specific module then
# write a much much better approach of the php handler in the local server using the socket
# configure the php to use also the socket for direct connection locally
# restart the service to see changes at the browser

<pre>
<nowiki>
mkdir -p /var/www/localhost/cgi-bin

sed -i -r 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi.conf".*#\# include "mod_fastcgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi_fpm.conf".*# include "mod_fastcgi_fpm.conf"#g' /etc/lighttpd/lighttpd.conf

sed -e '/index-file.names/ s/^#*/#/' -i /etc/lighttpd/lighttpd.conf

cat > /etc/lighttpd/mod_fastcgi_fpm.conf << EOF
server.modules += ( "mod_fastcgi" )
index-file.names += ( "index.php" )
fastcgi.server = (
".php" => (
"localhost" => (
"socket" => "/var/run/php-fpm7/php7-fpm.sock",
"broken-scriptfilename" => "enable"
))
)
EOF

sed -i -r 's|^.*listen =.*|listen = /var/run/php-fpm7/php7-fpm.sock|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.owner = .*|listen.owner = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.group = .*|listen.group = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.mode = .*|listen.mode = 0660|g' /etc/php*/php-fpm.d/www.conf

rc-service php-fpm7 restart

rc-service lighttpd restart

echo "<?php echo phpinfo(); ?>" > /var/www/localhost/htdocs/info.php
</nowiki>
</pre>

'''For testing, open a browser and go to <code><nowiki>http://<webserveripaddres>/info.php</nowiki></code>. You will see the info as used in production.
There's no sense givig too much information to crackers. The "webserveripaddres" is the ip address of your setup/server machine.

After that, all the files with php will be procesed faster than used a host based. Under the <code>/var/www/localhost/cgi-bin</code> directory will be shown as <nowiki>http://localhost/cgi-bin/</nowiki> path.

=== Multiple PHP-FPM cluster ===

As we said, FPM is managed by process pools, but the connection can be over a network or over a direct n socket. The configuration for a typical server that can handle an average number requests is with socket and localhost. For high availability, a CAT6 wired network connection of 1000Mbps and php-fpm by network connection in roundrobin mode is needed.

The PHP FPM pool will be on a specific machine and the web server(s) will simply connect to these machines with PHP to serve the PHP pages. The result is a cluster of lighttpd web servers against other PHP-FPM process clusters. The PHP code can be the same on all web servers and can connect to a single database.

At the Linux console the changes are, for example, two machines 10.10.1.10 and 10.10.2.10 both have php and lighttpd, so each will set up the php of the other:

<pre>
<nowiki>
mkdir -p /var/www/localhost/cgi-bin

sed -i -r 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi.conf".*#\# include "mod_fastcgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi_fpm.conf".*# include "mod_fastcgi_fpm.conf"#g' /etc/lighttpd/lighttpd.conf

cat > /etc/lighttpd/mod_fastcgi_fpm.conf << EOF
server.modules += ( "mod_fastcgi" )
index-file.names += ( "index.php" )
fastcgi.server = ( ".php" =>
(
( "host" => "10.10.1.10",
"port" => 9000
),
( "host" => "10.10.2.10",
"port" => 9000 )
)
)
EOF

sed -i -r 's|^.*listen =.*|listen = 9000|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.owner = .*|listen.owner = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.group = .*|listen.group = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.mode = .*|listen.mode = 0660|g' /etc/php*/php-fpm.d/www.conf

rc-service php-fpm7 restart

rc-service lighttpd restart

echo "<?php echo phpinfo(); ?>" > /var/www/localhost/htdocs/info.php
</nowiki>
</pre>

== 3. The DBMS part: mysql/mariadb ==

Alpine Linux has dummy counterpart packages for those not changed from ''mysql'' to ''mariadb''.

=== Installation ===

Take into consideration the user <code>mysql</code> was created during package installation. In the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and at that point only if they are in their respective system accounts, will they be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

=== Initialization ===

The ''datadir'' located at <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but you must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Set up the root account by assigning a proper password. This is pure paranoia. the next step does just that!
# Set up and init the installation by running the <code>mysql_secure_installation</code>
# Set up permissions for managing other users and databases
# Run the <code>mysql_secure_installation</code> script and answer the questions (see section below)

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor

mysql_secure_installation
</nowiki>
</pre>

# '''Enter current password for root (enter for none):''' must be provided because we set it previously. Correct response is <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' this must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' Just press "n" only if you provided a good password, otherwise change it!
# '''Remove anonymous users? [Y/n]''' In any case, '''production system must remove it, so answer Y''' and proper respond mus be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' For sure answer Y''' and proper respond mus be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' Should be removed, so answer Y''' and proper respond mus be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Aanswer Y''' and proper respond mus be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration ===

Newer Alpine system packages can set in independent files. In any case, those commands always work and where not applicable, they'll ignore the output. For more info about that, see the [[MariaDB#Configuration_files_and_customization|MariaDB Configuration files]] section of the MariaDB wiki page.

* On older Alpine system you must set config files for MAX ALLOWED PACKETS to minimun proper amount:
* Only allow local connections in cases where there's only one server or no expected connections from others:
* Set default charset to UTF8MB4 (default from MariaDB 11.8 and MySQL 8)
* Add the start service process, but don't set it as a boot process because networking needs to already be running.
* Restart the service to apply changes.

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf

sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf

cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF

rc-service mariadb restart

rc-update add mariadb default
</nowiki>
</pre>

'''Upgrading:''' If you are unable to run any mysql commands after an upgrade, it's because MySQL cannot start. Try to run MySQL in safe mode with the <code>mysqld_safe --datadir=/var/lib/mysql/</code> command, then run the <code>mysql_upgrade -u root -p</code> script. For more information see the [[MariaDB#Updating_or_comming_from_upgrading|MariaDB upgrading section]] of the MariaDB wiki page.

=== adminer: Web Frontend administration ===

[https://www.adminer.org/ Adminer] is a simple standalone tool, tons of times faster than PhpMysqladmin that is great but has too many security issues and lots of complex settings. We need a single, simpler solution. One that's easy to manage and upgrade.

Take into consideration this needs as a prerequisite, the previous sections of the web server, php scripting and mysql/mariadb engine configured and running:

<pre>
<nowiki>

mkdir -p /var/www/webapps/adminer

wget https://github.com/vrana/adminer/releases/download/v4.7.6/adminer-4.7.6.php -O /var/www/webapps/adminer/adminer-4.7.6.php

ln -s adminer-4.7.6.php /var/www/webapps/adminer/index.php

cat > /etc/lighttpd/mod_adminer.conf << EOF
# NOTE: this requires mod_alias
alias.url += (
"/adminer/" => "/var/www/webapps/adminer/"
)
$HTTP["url"] =~ "^/adminer/" {
# disable directory listings
dir-listing.activate = "disable"
}
EOF

sed -i -r 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

checkssl="";checkssl=$(grep 'include "mod_adminer.conf' /etc/lighttpd/lighttpd.conf);[[ "$checkssl" != "" ]] && echo listo || sed -i -r 's#.*include "mod_cgi.conf".*#include "mod_cgi.conf"\ninclude "mod_adminer.conf"#g' /etc/lighttpd/lighttpd.conf

rc-service lighttpd restart
</nowiki>
</pre>

The administrator must use the exact URL <code><nowiki>http://<ipaddress>/adminer/index.php</nowiki></code> There are two reasons: there's no directory listing and there's no direct PHP index reference on the web server, all because of paranoid settings.

[[Category:Newbie]]
[[Category:Server]]
[[Category:Web_Server]]
[[Category:Monitoring]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

Production LAMP system: Lighttpd + PHP + MySQL

2025-03-11T23:29:47Z

Greenman: copyedits

In production web, '''LAMP''' means '''L'''inux + '''A'''pache + '''M'''ysql + '''P'''hp installed and integrated, but today, more and more, ''Apache'' is being replaced by ''Nginx'' or ''Lighttpd'' and ''[[MySQL]]'' by ''[[MariaDB]]''. The ''LAMP'' documents are:

== 1. The web server: Lighttpd ==

[https://www.lighttpd.net/ lighttpd] is a simple, standards-compliant, secure, and flexible web server, Nginx is the most used due to beeing manageable by ISP panel's software, but '''lighttpd performs better. Nginx cannot process fast-cgi programs'''. For more lighttpd information, consult the [[Production Web server: Lighttpd]] wiki page.

=== Lighttpd Installation ===

Production environment will handle only needed packages. So no doc or managers allowed:

# run apk for needed packages

<pre>
<nowiki>
apk add lighttpd gamin
</nowiki>
</pre>

=== Lighttpd pre php configuration ===

# make the htdos public web root directories
# change default port to production one, http is used with 80
# use FAM style (gamin) file alteration monitor, increases performance '''ONLY ON 3.4 to 3.8 releases!!!'''
# use linux event handler, increases performance due Alpine are linux only
# added the service to the default runlevel, not to boot, because networking needs to be active first
# started the web server service
# Enable the mod_status at the config files
# change path in the config file, we are using security by obfuscation
# restart the service to see changes at the browser

<pre>
<nowiki>
mkdir -p /var/www/localhost/htdocs/stats /var/log/lighttpd /var/lib/lighttpd

sed -i -r 's#\#.*server.port.*=.*#server.port = 80#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#\#.*server.event-handler = "linux-sysepoll".*#server.event-handler = "linux-sysepoll"#g' /etc/lighttpd/lighttpd.conf

chown -R lighttpd:lighttpd /var/www/localhost/

chown -R lighttpd:lighttpd /var/lib/lighttpd

chown -R lighttpd:lighttpd /var/log/lighttpd

rc-update add lighttpd default

rc-service lighttpd restart

echo "it works" > /var/www/localhost/htdocs/index.html

sed -i -r 's#\#.*mod_status.*,.*# "mod_status",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*status.status-url.*=.*#status.status-url = "/stats/server-status"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*status.config-url.*=.*#status.config-url = "/stats/server-config"#g' /etc/lighttpd/lighttpd.conf

rc-service lighttpd restart
</nowiki>
</pre>

'''For testing, open a browser and go to <code><nowiki>http://<webserveripaddres></nowiki></code>. You will see "it works"'''. The "webserveripaddres" is the ip address of your setup/server machine.

'''There's a problem in Alpine linux, FAM (gamin) is activated as a lighttpd only service''', that makes sense in docker, but on a server, it could be a problem if FAM (gamin) is also needed for other services at the same time.

'''OPTIONAL:''' alpine packagers are a mess, removed FAM on recent, so older releases of alpine can use compiled FAM packages with <code>sed -i -r 's#.*server.stat-cache-engine.*=.*# server.stat-cache-engine = "fam"#g' /etc/lighttpd/lighttpd.conf</code>

== 2. php scripting: PHP fpm ==

In Alpine there are two main languages for programming dynamic web pages: PHP and LUA. Alpine is minimalist so not all PHP packages are need in most cases.
Both repositories must be enabled (main and community). Here we explain the most common use in production.

=== PHP Installation ===

{{Obsolete|Update to a newer or generic php version}}

<pre><nowiki>
apk add php7 php7-bcmath php7-bz2 php7-ctype php7-curl php7-dom php7-enchant php7-exif php7-fpm php7-gd php7-gettext php7-gmp php7-iconv php7-imap php7-intl php7-json php7-mbstring php7-opcache php7-openssl php7-phar php7-posix php7-pspell php7-recode php7-session php7-simplexml php7-sockets php7-sysvmsg php7-sysvsem php7-sysvshm php7-tidy php7-xml php7-xmlreader php7-xmlrpc php7-xmlwriter php7-xsl php7-zip php7-sqlite3
</nowiki></pre>

{{Note|'''The packages below are only for specific situations. Install them only when needed (especially php-pear)''', for example, <code>cacti</code> and <code>cacti-php7</code> depend on <code>php7</code>, but you must install '''only''' the cacti package, all the dependencies must be previously installed from stable.}}

<pre><nowiki>
apk add php7-pgsql php7-mysqli php7-mysqlnd php7-snmp php7-soap php7-ldap php7-pcntl php7-pear php7-shmop php7-wddx php7-cgi php7-pdo php7-snmp php7-tokenizer
</nowiki></pre>

{{Note|'''The packages below are only for database access using php in specific ways. Install them only if you need them (specially php--pdo ones)'''. For example, <code>cacti</code> and <code>cacti-php7</code> depend on <code>php7-mysqli</code>, but you must install only the cacti package, all the dependencies like <code>php7</code> and <code>php7-mysqli</code> must be previously installed from stable.}}

<pre><nowiki>
apk add php7-dba php7-sqlite3 php7-mysqli php7-mysqlnd php7-pgsql php7-pdo_dblib php7-pdo_odbc php7-pdo_pgsql php7-pdo_sqlite
</nowiki></pre>

A special case is <code>php7-odbc</code>. Unless the others, that are able php to connect to only specific database, '''[[Production DataBases : unixodbc|unixodbc]]''' is a universal way to do so. The most important difference is, for example, the <code>php7-mysqli</code> package has better functions to manage data via php.

=== PHP Global Configuration ===

# Use fix.pathinfo
# Set safe mode off
# Dont expose php code if something fails
# Set amount of memory limit for execution to 536Mb (most servers are minimum of 1 GB of RAM)
# Set upload size to 128Mb as maximun.
# Set POST max size to 256Mb based on the upload max size limit.
# Turn on the URL open method
# Set default charset to UTF-8 for increased compatibility
# Increase the execution time and the input time for.

<pre><nowiki>
sed -i -r 's|.*cgi.fix_pathinfo=.*|cgi.fix_pathinfo=1|g' /etc/php*/php.ini
sed -i -r 's#.*safe_mode =.*#safe_mode = Off#g' /etc/php*/php.ini
sed -i -r 's#.*expose_php =.*#expose_php = Off#g' /etc/php*/php.ini
sed -i -r 's#memory_limit =.*#memory_limit = 536M#g' /etc/php*/php.ini
sed -i -r 's#upload_max_filesize =.*#upload_max_filesize = 128M#g' /etc/php*/php.ini
sed -i -r 's#post_max_size =.*#post_max_size = 256M#g' /etc/php*/php.ini
sed -i -r 's#^file_uploads =.*#file_uploads = On#g' /etc/php*/php.ini
sed -i -r 's#^max_file_uploads =.*#max_file_uploads = 12#g' /etc/php*/php.ini
sed -i -r 's#^allow_url_fopen = .*#allow_url_fopen = On#g' /etc/php*/php.ini
sed -i -r 's#^.default_charset =.*#default_charset = "UTF-8"#g' /etc/php*/php.ini
sed -i -r 's#^.max_execution_time =.*#max_execution_time = 150#g' /etc/php*/php.ini
sed -i -r 's#^max_input_time =.*#max_input_time = 90#g' /etc/php*/php.ini
</nowiki></pre>

=== PHP-FPM Configuration ===

# Create directory for php socket and pid files, MUST BE EQUAL to openrc defined!
# Set into configuration file the socket path, MUST BE EQUAL to openrc defined!
# Set into configuration file the pid file path, MUST BE EQUAL to openrc defined!

<pre>
<nowiki>
mkdir -p /var/run/php-fpm7/

chown lighttpd:root /var/run/php-fpm7

sed -i -r 's|^.*listen =.*|listen = /run/php-fpm7/php7-fpm.sock|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^pid =.*|pid = /run/php-fpm7/php7-fpm.pid|g' /etc/php*/php-fpm.conf

sed -i -r 's|^.*listen.mode =.*|listen.mode = 0640|g' /etc/php*/php-fpm.d/www.conf

rc-update add php-fpm7 default

service php-fpm7 restart
</nowiki>
</pre>

The PHP-FPM defines a master process with a process pool for each service request. By default, there's only one process pool, www.

Default values are good for starting, but will need tuning later. The best is a static one, but testing is needed to get the right configuration.

=== Lighttpd + PHP-FPM ===

The web server comes with a minimal config file, so we must handle all the required settings:

# enable the mod_alias at the config file, a specific path is needed for cgi file security
# be sure and disable the fastcgi-php module by cgi only
# and then enable the fastcgi-php-fpm specific module then
# write a much much better approach of the php handler in the local server using the socket
# configure the php to use also the socket for direct connection locally
# restart the service to see changes at the browser

<pre>
<nowiki>
mkdir -p /var/www/localhost/cgi-bin

sed -i -r 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi.conf".*#\# include "mod_fastcgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi_fpm.conf".*# include "mod_fastcgi_fpm.conf"#g' /etc/lighttpd/lighttpd.conf

sed -e '/index-file.names/ s/^#*/#/' -i /etc/lighttpd/lighttpd.conf

cat > /etc/lighttpd/mod_fastcgi_fpm.conf << EOF
server.modules += ( "mod_fastcgi" )
index-file.names += ( "index.php" )
fastcgi.server = (
".php" => (
"localhost" => (
"socket" => "/var/run/php-fpm7/php7-fpm.sock",
"broken-scriptfilename" => "enable"
))
)
EOF

sed -i -r 's|^.*listen =.*|listen = /var/run/php-fpm7/php7-fpm.sock|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.owner = .*|listen.owner = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.group = .*|listen.group = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.mode = .*|listen.mode = 0660|g' /etc/php*/php-fpm.d/www.conf

rc-service php-fpm7 restart

rc-service lighttpd restart

echo "<?php echo phpinfo(); ?>" > /var/www/localhost/htdocs/info.php
</nowiki>
</pre>

'''For testing, open a browser and go to <code><nowiki>http://<webserveripaddres>/info.php</nowiki></code>. You will see the info as used in production.
There's no sense givig too much information to crackers. The "webserveripaddres" is the ip address of your setup/server machine.

After that, all the files with php will be procesed faster than used a host based. Under the <code>/var/www/localhost/cgi-bin</code> directory will be shown as <nowiki>http://localhost/cgi-bin/</nowiki> path.

=== Multiple PHP-FPM cluster ===

As we said, FPM is managed by process pools, but the connection can be over a network or over a direct n socket. The configuration for a typical server that can handle an average number requests is with socket and localhost. For high availability, a CAT6 wired network connection of 1000Mbps and php-fpm by network connection in roundrobin mode is needed.

The PHP FPM pool will be on a specific machine and the web server(s) will simply connect to these machines with PHP to serve the PHP pages. The result is a cluster of lighttpd web servers against other PHP-FPM process clusters. The PHP code can be the same on all web servers and can connect to a single database.

At the Linux console the changes are, for example, two machines 10.10.1.10 and 10.10.2.10 both have php and lighttpd, so each will set up the php of the other:

<pre>
<nowiki>
mkdir -p /var/www/localhost/cgi-bin

sed -i -r 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi.conf".*#\# include "mod_fastcgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi_fpm.conf".*# include "mod_fastcgi_fpm.conf"#g' /etc/lighttpd/lighttpd.conf

cat > /etc/lighttpd/mod_fastcgi_fpm.conf << EOF
server.modules += ( "mod_fastcgi" )
index-file.names += ( "index.php" )
fastcgi.server = ( ".php" =>
(
( "host" => "10.10.1.10",
"port" => 9000
),
( "host" => "10.10.2.10",
"port" => 9000 )
)
)
EOF

sed -i -r 's|^.*listen =.*|listen = 9000|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.owner = .*|listen.owner = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.group = .*|listen.group = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.mode = .*|listen.mode = 0660|g' /etc/php*/php-fpm.d/www.conf

rc-service php-fpm7 restart

rc-service lighttpd restart

echo "<?php echo phpinfo(); ?>" > /var/www/localhost/htdocs/info.php
</nowiki>
</pre>

== 3. The DBMS part: mysql/mariadb ==

Alpine Linux has dummy counterpart packages for those not changed from ''mysql'' to ''mariadb''.

=== Installation ===

Take into consideration the user <code>mysql</code> was created during package installation. In the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and at that point only if they are in their respective system accounts, will they be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

=== Initialization ===

The ''datadir'' located at <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but you must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Set up the root account by assigning a proper password. This is pure paranoia. the next step does just that!
# Set up and init the installation by running the <code>mysql_secure_installation</code>
# Set up permissions for managing other users and databases
# Run the <code>mysql_secure_installation</code> script and answer the questions (see section below)

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor

mysql_secure_installation
</nowiki>
</pre>

# '''Enter current password for root (enter for none):''' must be provided because we set it previously. Correct response is <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' this must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' Just press "n" only if you provided a good password, otherwise change it!
# '''Remove anonymous users? [Y/n]''' In any case, '''production system must remove it, so answer Y''' and proper respond mus be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' For sure answer Y''' and proper respond mus be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' Should be removed, so answer Y''' and proper respond mus be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Aanswer Y''' and proper respond mus be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration ===

Newer Alpine system packages can set in independent files. In any case, those commands always work and where not applicable, they'll ignore the output. For more info about that, see the [[MariaDB#Configuration_files_and_customization|MariaDB Configuration files]] section of the MariaDB wiki page.

* On older Alpine system you must set config files for MAX ALLOWED PACKETS to minimun proper amount:
* Only allow local connections in cases where there's only one server or no expected connections from others:
* Set default charset to UTF8MB4
* Add the start service process, but don't set it as a boot process because networking needs to already be running.
* Restart the service to apply changes.

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf

sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf

cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF

rc-service mariadb restart

rc-update add mariadb default
</nowiki>
</pre>

'''Upgrading:''' If you are unable to run any mysql commands after an upgrade, it's because MySQL cannot start. Try to run MySQL in safe mode with the <code>mysqld_safe --datadir=/var/lib/mysql/</code> command, then run the <code>mysql_upgrade -u root -p</code> script. For more information see the [[MariaDB#Updating_or_comming_from_upgrading|MariaDB upgrading section]] of the MariaDB wiki page.

=== adminer: Web Frontend administration ===

[https://www.adminer.org/ Adminer] is a simple standalone tool, tons of times faster than PhpMysqladmin that is great but has too many security issues and lots of complex settings. We need a single, simpler solution. One that's easy to manage and upgrade.

Take into consideration this needs as a prerequisite, the previous sections of the web server, php scripting and mysql/mariadb engine configured and running:

<pre>
<nowiki>

mkdir -p /var/www/webapps/adminer

wget https://github.com/vrana/adminer/releases/download/v4.7.6/adminer-4.7.6.php -O /var/www/webapps/adminer/adminer-4.7.6.php

ln -s adminer-4.7.6.php /var/www/webapps/adminer/index.php

cat > /etc/lighttpd/mod_adminer.conf << EOF
# NOTE: this requires mod_alias
alias.url += (
"/adminer/" => "/var/www/webapps/adminer/"
)
$HTTP["url"] =~ "^/adminer/" {
# disable directory listings
dir-listing.activate = "disable"
}
EOF

sed -i -r 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

checkssl="";checkssl=$(grep 'include "mod_adminer.conf' /etc/lighttpd/lighttpd.conf);[[ "$checkssl" != "" ]] && echo listo || sed -i -r 's#.*include "mod_cgi.conf".*#include "mod_cgi.conf"\ninclude "mod_adminer.conf"#g' /etc/lighttpd/lighttpd.conf

rc-service lighttpd restart
</nowiki>
</pre>

The administrator must use the exact URL <code><nowiki>http://<ipaddress>/adminer/index.php</nowiki></code> There are two reasons: there's no directory listing and there's no direct PHP index reference on the web server, all because of paranoid settings.

[[Category:Newbie]]
[[Category:Server]]
[[Category:Web_Server]]
[[Category:Monitoring]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

Production LAMP system: Lighttpd + PHP + MySQL

2025-03-11T23:29:11Z

Greenman: links

In production web, '''LAMP''' means '''L'''inux + '''A'''pache + '''M'''ysql + '''P'''hp installed and integrated, but today, more and more, ''Apache'' is being replaced by ''Nginx'' or ''Lighttpd'' and ''[[MySQL]]'' by ''[[MariaDB]]''. The ''LAMP'' documents are:

== 1. The web server: Lighttpd ==

[https://www.lighttpd.net/ lighttpd] is a simple, standards-compliant, secure, and flexible web server, Nginx is the most used due to beeing manageable by ISP panel's software, but '''lighttpd performs better. Nginx cannot process fast-cgi programs'''. For more lighttpd information, consult the [[Production Web server: Lighttpd]] wiki page.

=== Lighttpd Installation ===

Production environment will handle only needed packages. So no doc or managers allowed:

# run apk for needed packages

<pre>
<nowiki>
apk add lighttpd gamin
</nowiki>
</pre>

=== Lighttpd pre php configuration ===

# make the htdos public web root directories
# change default port to production one, http is used with 80
# use FAM style (gamin) file alteration monitor, increases performance '''ONLY ON 3.4 to 3.8 releases!!!'''
# use linux event handler, increases performance due Alpine are linux only
# added the service to the default runlevel, not to boot, because networking needs to be active first
# started the web server service
# Enable the mod_status at the config files
# change path in the config file, we are using security by obfuscation
# restart the service to see changes at the browser

<pre>
<nowiki>
mkdir -p /var/www/localhost/htdocs/stats /var/log/lighttpd /var/lib/lighttpd

sed -i -r 's#\#.*server.port.*=.*#server.port = 80#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#\#.*server.event-handler = "linux-sysepoll".*#server.event-handler = "linux-sysepoll"#g' /etc/lighttpd/lighttpd.conf

chown -R lighttpd:lighttpd /var/www/localhost/

chown -R lighttpd:lighttpd /var/lib/lighttpd

chown -R lighttpd:lighttpd /var/log/lighttpd

rc-update add lighttpd default

rc-service lighttpd restart

echo "it works" > /var/www/localhost/htdocs/index.html

sed -i -r 's#\#.*mod_status.*,.*# "mod_status",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*status.status-url.*=.*#status.status-url = "/stats/server-status"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*status.config-url.*=.*#status.config-url = "/stats/server-config"#g' /etc/lighttpd/lighttpd.conf

rc-service lighttpd restart
</nowiki>
</pre>

'''For testing, open a browser and go to <code><nowiki>http://<webserveripaddres></nowiki></code>. You will see "it works"'''. The "webserveripaddres" is the ip address of your setup/server machine.

'''There's a problem in Alpine linux, FAM (gamin) is activated as a lighttpd only service''', that makes sense in docker, but on a server, it could be a problem if FAM (gamin) is also needed for other services at the same time.

'''OPTIONAL:''' alpine packagers are a mess, removed FAM on recent, so older releases of alpine can use compiled FAM packages with <code>sed -i -r 's#.*server.stat-cache-engine.*=.*# server.stat-cache-engine = "fam"#g' /etc/lighttpd/lighttpd.conf</code>

== 2. php scripting: PHP fpm ==

In Alpine there are two main languages for programming dynamic web pages: PHP and LUA. Alpine is minimalist so not all PHP packages are need in most cases.
Both repositories must be enabled (main and community). Here we explain the most common use in production.

=== PHP Installation ===

{{Obsolete|Update to a newer or generic php version}}

<pre><nowiki>
apk add php7 php7-bcmath php7-bz2 php7-ctype php7-curl php7-dom php7-enchant php7-exif php7-fpm php7-gd php7-gettext php7-gmp php7-iconv php7-imap php7-intl php7-json php7-mbstring php7-opcache php7-openssl php7-phar php7-posix php7-pspell php7-recode php7-session php7-simplexml php7-sockets php7-sysvmsg php7-sysvsem php7-sysvshm php7-tidy php7-xml php7-xmlreader php7-xmlrpc php7-xmlwriter php7-xsl php7-zip php7-sqlite3
</nowiki></pre>

{{Note|'''The packages below are only for specific situations. Install them only when needed (especially php-pear)''', for example, <code>cacti</code> and <code>cacti-php7</code> depend on <code>php7</code>, but you must install '''only''' the cacti package, all the dependencies must be previously installed from stable.}}

<pre><nowiki>
apk add php7-pgsql php7-mysqli php7-mysqlnd php7-snmp php7-soap php7-ldap php7-pcntl php7-pear php7-shmop php7-wddx php7-cgi php7-pdo php7-snmp php7-tokenizer
</nowiki></pre>

{{Note|'''The packages below are only for database access using php in specific ways. Install them only if you need them (specially php--pdo ones)'''. For example, <code>cacti</code> and <code>cacti-php7</code> depend on <code>php7-mysqli</code>, but you must install only the cacti package, all the dependencies like <code>php7</code> and <code>php7-mysqli</code> must be previously installed from stable.}}

<pre><nowiki>
apk add php7-dba php7-sqlite3 php7-mysqli php7-mysqlnd php7-pgsql php7-pdo_dblib php7-pdo_odbc php7-pdo_pgsql php7-pdo_sqlite
</nowiki></pre>

A special case is <code>php7-odbc</code>. Unless the others, that are able php to connect to only specific database, '''[[Production DataBases : unixodbc|unixodbc]]''' is a universal way to do so. The most important difference is, for example, the <code>php7-mysqli</code> package has better functions to manage data via php.

=== PHP Global Configuration ===

# Use fix.pathinfo
# Set safe mode off
# Dont expose php code if something fails
# Set amount of memory limit for execution to 536Mb (most servers are minimum of 1 GB of RAM)
# Set upload size to 128Mb as maximun.
# Set POST max size to 256Mb based on the upload max size limit.
# Turn on the URL open method
# Set default charset to UTF-8 for increased compatibility
# Increase the execution time and the input time for.

<pre><nowiki>
sed -i -r 's|.*cgi.fix_pathinfo=.*|cgi.fix_pathinfo=1|g' /etc/php*/php.ini
sed -i -r 's#.*safe_mode =.*#safe_mode = Off#g' /etc/php*/php.ini
sed -i -r 's#.*expose_php =.*#expose_php = Off#g' /etc/php*/php.ini
sed -i -r 's#memory_limit =.*#memory_limit = 536M#g' /etc/php*/php.ini
sed -i -r 's#upload_max_filesize =.*#upload_max_filesize = 128M#g' /etc/php*/php.ini
sed -i -r 's#post_max_size =.*#post_max_size = 256M#g' /etc/php*/php.ini
sed -i -r 's#^file_uploads =.*#file_uploads = On#g' /etc/php*/php.ini
sed -i -r 's#^max_file_uploads =.*#max_file_uploads = 12#g' /etc/php*/php.ini
sed -i -r 's#^allow_url_fopen = .*#allow_url_fopen = On#g' /etc/php*/php.ini
sed -i -r 's#^.default_charset =.*#default_charset = "UTF-8"#g' /etc/php*/php.ini
sed -i -r 's#^.max_execution_time =.*#max_execution_time = 150#g' /etc/php*/php.ini
sed -i -r 's#^max_input_time =.*#max_input_time = 90#g' /etc/php*/php.ini
</nowiki></pre>

=== PHP-FPM Configuration ===

# Create directory for php socket and pid files, MUST BE EQUAL to openrc defined!
# Set into configuration file the socket path, MUST BE EQUAL to openrc defined!
# Set into configuration file the pid file path, MUST BE EQUAL to openrc defined!

<pre>
<nowiki>
mkdir -p /var/run/php-fpm7/

chown lighttpd:root /var/run/php-fpm7

sed -i -r 's|^.*listen =.*|listen = /run/php-fpm7/php7-fpm.sock|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^pid =.*|pid = /run/php-fpm7/php7-fpm.pid|g' /etc/php*/php-fpm.conf

sed -i -r 's|^.*listen.mode =.*|listen.mode = 0640|g' /etc/php*/php-fpm.d/www.conf

rc-update add php-fpm7 default

service php-fpm7 restart
</nowiki>
</pre>

The PHP-FPM defines a master process with a process pool for each service request. By default, there's only one process pool, www.

Default values are good for starting, but will need tuning later. The best is a static one, but testing is needed to get the right configuration.

=== Lighttpd + PHP-FPM ===

The web server comes with a minimal config file, so we must handle all the required settings:

# enable the mod_alias at the config file, a specific path is needed for cgi file security
# be sure and disable the fastcgi-php module by cgi only
# and then enable the fastcgi-php-fpm specific module then
# write a much much better approach of the php handler in the local server using the socket
# configure the php to use also the socket for direct connection locally
# restart the service to see changes at the browser

<pre>
<nowiki>
mkdir -p /var/www/localhost/cgi-bin

sed -i -r 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi.conf".*#\# include "mod_fastcgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi_fpm.conf".*# include "mod_fastcgi_fpm.conf"#g' /etc/lighttpd/lighttpd.conf

sed -e '/index-file.names/ s/^#*/#/' -i /etc/lighttpd/lighttpd.conf

cat > /etc/lighttpd/mod_fastcgi_fpm.conf << EOF
server.modules += ( "mod_fastcgi" )
index-file.names += ( "index.php" )
fastcgi.server = (
".php" => (
"localhost" => (
"socket" => "/var/run/php-fpm7/php7-fpm.sock",
"broken-scriptfilename" => "enable"
))
)
EOF

sed -i -r 's|^.*listen =.*|listen = /var/run/php-fpm7/php7-fpm.sock|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.owner = .*|listen.owner = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.group = .*|listen.group = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.mode = .*|listen.mode = 0660|g' /etc/php*/php-fpm.d/www.conf

rc-service php-fpm7 restart

rc-service lighttpd restart

echo "<?php echo phpinfo(); ?>" > /var/www/localhost/htdocs/info.php
</nowiki>
</pre>

'''For testing, open a browser and go to <code><nowiki>http://<webserveripaddres>/info.php</nowiki></code>. You will see the info as used in production.
There's no sense givig too much information to crackers. The "webserveripaddres" is the ip address of your setup/server machine.

After that, all the files with php will be procesed faster than used a host based. Under the <code>/var/www/localhost/cgi-bin</code> directory will be shown as <nowiki>http://localhost/cgi-bin/</nowiki> path.

=== Multiple PHP-FPM cluster ===

As we said, FPM is managed by process pools, but the connection can be over a network or over a direct n socket. The configuration for a typical server that can handle an average number requests is with socket and localhost. For high availability, a CAT6 wired network connection of 1000Mbps and php-fpm by network connection in roundrobin mode is needed.

The PHP FPM pool will be on a specific machine and the web server(s) will simply connect to these machines with PHP to serve the PHP pages. The result is a cluster of lighttpd web servers against other PHP-FPM process clusters. The PHP code can be the same on all web servers and can connect to a single database.

At the Linux console the changes are, for example, two machines 10.10.1.10 and 10.10.2.10 both have php and lighttpd, so each will set up the php of the other:

<pre>
<nowiki>
mkdir -p /var/www/localhost/cgi-bin

sed -i -r 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi.conf".*#\# include "mod_fastcgi.conf"#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_fastcgi_fpm.conf".*# include "mod_fastcgi_fpm.conf"#g' /etc/lighttpd/lighttpd.conf

cat > /etc/lighttpd/mod_fastcgi_fpm.conf << EOF
server.modules += ( "mod_fastcgi" )
index-file.names += ( "index.php" )
fastcgi.server = ( ".php" =>
(
( "host" => "10.10.1.10",
"port" => 9000
),
( "host" => "10.10.2.10",
"port" => 9000 )
)
)
EOF

sed -i -r 's|^.*listen =.*|listen = 9000|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.owner = .*|listen.owner = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.group = .*|listen.group = lighttpd|g' /etc/php*/php-fpm.d/www.conf

sed -i -r 's|^.*listen.mode = .*|listen.mode = 0660|g' /etc/php*/php-fpm.d/www.conf

rc-service php-fpm7 restart

rc-service lighttpd restart

echo "<?php echo phpinfo(); ?>" > /var/www/localhost/htdocs/info.php
</nowiki>
</pre>

== 3. The DBMS part: mysql/mariadb ==

Alpine Linux has dummy counterpart packages for those not changed from ''mysql'' to ''mariadb''.

=== Installation ===

Take into consideration the user <code>mysql</code> was created during package instalation. In the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and at that point only if they are in their respective system accounts, will they be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

=== Initialization ===

The ''datadir'' located at <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but you must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Set up the root account by asigning a proper password. This is pure paranoia. the next step does just that!
# Set up and init the installation by running the <code>mysql_secure_installation</code>
# Set up permissions for managing other users and databases
# Run the <code>mysql_secure_installation</code> script and answer the questions (see section below)

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor

mysql_secure_installation
</nowiki>
</pre>

# '''Enter current password for root (enter for none):''' must be provided because we set it previously. Correct response is <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' this must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' Just press "n" only if you provided a good password, otherwise change it!
# '''Remove anonymous users? [Y/n]''' In any case, '''production system must remove it, so answer Y''' and proper respond mus be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' For sure answer Y''' and proper respond mus be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' Should be removed, so answer Y''' and proper respond mus be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Aanswer Y''' and proper respond mus be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration ===

Newer Alpine system packages can set in independent files. In any case, those commands always work and where not applicable, they'll ignore the output. For more info about that, see the [[MariaDB#Configuration_files_and_customization|MariaDB Configuration files]] section of the MariaDB wiki page.

* On older Alpine system you must set config files for MAX ALLOWED PACKETS to minimun proper amount:
* Only allow local connections in cases where there's only one server or no expected connections from others:
* Set default charset to UTF8MB4
* Add the start service process, but don't set it as a boot process because networking needs to already be running.
* Restart the service to apply changes.

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf

sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf

cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF

rc-service mariadb restart

rc-update add mariadb default
</nowiki>
</pre>

'''Upgrading:''' If you are unable to run any mysql commands after an upgrade, it's because MySQL cannot start. Try to run MySQL in safe mode with the <code>mysqld_safe --datadir=/var/lib/mysql/</code> command, then run the <code>mysql_upgrade -u root -p</code> script. For more information see the [[MariaDB#Updating_or_comming_from_upgrading|MariaDB upgrading section]] of the MariaDB wiki page.

=== adminer: Web Frontend administration ===

[https://www.adminer.org/ Adminer] is a simple standalone tool, tons of times faster than PhpMysqladmin that is great but has too many security issues and lots of complex settings. We need a single, simpler solution. One that's easy to manage and upgrade.

Take into consideration this needs as a prerequisite, the previous sections of the web server, php scripting and mysql/mariadb engine configured and running:

<pre>
<nowiki>

mkdir -p /var/www/webapps/adminer

wget https://github.com/vrana/adminer/releases/download/v4.7.6/adminer-4.7.6.php -O /var/www/webapps/adminer/adminer-4.7.6.php

ln -s adminer-4.7.6.php /var/www/webapps/adminer/index.php

cat > /etc/lighttpd/mod_adminer.conf << EOF
# NOTE: this requires mod_alias
alias.url += (
"/adminer/" => "/var/www/webapps/adminer/"
)
$HTTP["url"] =~ "^/adminer/" {
# disable directory listings
dir-listing.activate = "disable"
}
EOF

sed -i -r 's#\#.*mod_alias.*,.*# "mod_alias",#g' /etc/lighttpd/lighttpd.conf

sed -i -r 's#.*include "mod_cgi.conf".*# include "mod_cgi.conf"#g' /etc/lighttpd/lighttpd.conf

checkssl="";checkssl=$(grep 'include "mod_adminer.conf' /etc/lighttpd/lighttpd.conf);[[ "$checkssl" != "" ]] && echo listo || sed -i -r 's#.*include "mod_cgi.conf".*#include "mod_cgi.conf"\ninclude "mod_adminer.conf"#g' /etc/lighttpd/lighttpd.conf

rc-service lighttpd restart
</nowiki>
</pre>

The administrator must use the exact URL <code><nowiki>http://<ipaddress>/adminer/index.php</nowiki></code> There are two reasons: there's no directory listing and there's no direct PHP index reference on the web server, all because of paranoid settings.

[[Category:Newbie]]
[[Category:Server]]
[[Category:Web_Server]]
[[Category:Monitoring]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

MySQL

2025-03-11T23:25:03Z

Greenman: /* MariaDB vs MySQL */ copyedit, remove dubious claims

MySQL is the most popular relational database server in free software for two simple reasons (which are not the best technical reasons):
# It's simple and very easy to use
# It is very similar to SQLSERVER and is also used in other systems

'''In the world of Linux Alpine there is a software that provides it''', it is [[MariaDB]], here we have a brief of info about compatibility and differences, but in short there's no great differences, if you have doubts check [[MySQL#MariaDB vs MySQL|MariaDB vs MySQL section here]]

'''In the wiki there are two approaches for its use''', the professional one (for servers and deploys) and the [[MariaDB|fast and simple]] usage (for developers and/or enthusiasts):

= MariaDB - fast and simple use =

{{:MariaDB}}

= MySQL (MariaDB) - Production usage =

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

This page assumes that you have a general knowledge about MariaDB, so if you are new to MySQL first take a look at the [[MariaDB|MariaDB wiki page]] for information about how it is packaged for Alpine Linux.

== Installation ==

Alpine Linux has dummy counterparts packages for those that are not close to that change from ''mysql'' to ''mariadb'' naming packages, please check the [[MariaDB|MariaDB wiki page]] for more information.

Take in consideration that the user <code>mysql</code> was created during instalation of packages, in the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and in that point only if are in their respective system accounts, will be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

That will install the most used ones.. <code>mariadb-cient</code> and <code>mariadb-server</code>, rest of packages are brief described here for more information, here are listed in orden of relevance for production server

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| mysql || v2 || it's a dummy package to easy install of mariadb || mariadb
|-
| mysql-client || v2 || it's a dummy package to easy install of commands tools || mariadb-client
|-
| mariadb || v2 || server equivalent to mysql-server || mariadb-common
|-
| mariadb-client || v2 || connection command line and tools || mariadb-common
|-
| mariadb-doc || v3.0 || manpages are there! || man man-pages
|-
| mariadb-connector-odbc || edge || coding or making OS level connections, to any DB without libs install || .
|-
| mariadb-connector-c || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| mariadb-backup || v3.8 || to external backup devices, not widely used, in past was inside mariadb package || .
|-
| mariadb-server-utils || v3.8 || server commands not widely used, in past was inside mariadb package || .
|-
| mariadb-dev || v3.1 || Need for compilations depends on source code || .
|-
| mariadb-test || v3.3 || testing suite from MariaDB tools || .
|-
| mariadb-mytop || v3.9 || data performance monitoring || .
|-
| mariadb-plugin-rocksdb || v3.9 || plain key-value event relational for data || .
|-
| mariadb-static || v3.8 || static libs for static non depends linking in builds || .
|-
| mariadb-embedded || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| mariadb-embedded-dev || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| mariadb-openrc || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must be know what they want.. there's no automatic window-like here.

The ''datadir'' are located to <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Setup the root account by asignes a proper password, this are purely paranoid. due next step already do that!
# Setup and init the installation by running the <code>mysql_secure_installation</code>
# Setup permissions for manage others users and databases

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor
</nowiki>
</pre>

After that, all are initializated to proceed with configuration, now can be done using the <code>
mysql_secure_installation</code> script at the next section:

== Configuration ==

In order to finish setup into '''MariaDB''' now provide '''this script called <code>mysql_secure_instalation</code> that also are present as <code>mariadb-secure-installation</code>''', too. This script provides minimal and security setup to the database, and here are the questions made explained:

# '''Enter current password for root (enter for none):''' this are if you previously setup as we done in previous section a root password, just provide it and press enter, '''must be provided due we already set previously''' and from now, this sript will access to the engine and alter many setting on the database. Correct respond are <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the unix_socket ensures that only admins can log into engine database. Since mysql 5.6 and mariadb 10.2 a new auth mechanish are set, by socket authentiaction, when system user are same as mysql/mariadb user, in this case, no password are need. In production servers this are not the case and must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' this answer are here only if the first one are just enter, or if can provide a better passowrd if no unix socket are set. Just press "n" only if you provided a good password, otherwise just
# '''Remove anonymous users? [Y/n]''' this permits remove the anonymous user created to log using socket authentication, only working on unix-like system. In any case, '''production system must remove it, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. For sure answer Y''' and proper respond must be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately, so answer Y''' and proper respond must be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration files ===

Due today were influenced by systemd standardization, the famous <nowiki>my.cnf</nowiki> are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the <code>/etc/my.cnf.d/</code> directory, user own config files are under <code>~/.my.cnf</code> config file of each home dir, and are read after global ones; so then we have:

{| class="wikitable"
|-
! Config file !! Path and name !! Versions of Alpine !! Contents to configure
|-
| my.cnf || <nowiki>/etc/mysql/my.cnf</nowiki> || v2 to v3.8 || All the directives, Global config file
|-
| mariadb-server.cnf || /etc/my.cnf.d/mariadb-server.cnf || since 3.9 || First Global config file, main directives
|-
| .my.cnf || <nowiki>$HOME</nowiki> || all || user name only config directives
|}

=== Production settings ===

These setting are only recommended for some server settings, there are the recommendation for high production settings:

{| class="wikitable"
|-
! Config setting !! Default !! Recommended !! Explanation
|-
| main ram || 2G || 8G - 16G || MariaDB/MySQL can run with 512M or 1G of ram, high production must use minimun of 4G or more.
|-
| data dir disk type || any || SSD || MariaDB/MySQL must run with faster SSD if high request will be produced.
|-
| collation_server || utf8_unicode_ci || utf8mb4_unicode_ci || With 8mb4 some characters are able to use more than a single byte.
|-
| character_set_client || iso8859-1 || utf8 or utf8mb4 || Important due are standard, for left to right use 8mb4 flavor
|-
| max_connections || 151 || 100 || total_connections = total_processes * (total_threads + script_servers + 1)
|-
| max_heap_table_size || 16M || 32M || allocate more memory to memory tables, for memory storage engines
|-
| tmp_table_size || 16M || 32M || It allows the sub queries to remain more in memory, making them faster
|-
| join_buffer_size || 32M || 64M || It allows the join queries to remain more in memory rather in temp files
|-
| innodb_file_format || unset || Barracuda || will allow longer indexes for important most used tables
|-
| innodb_large_prefix || unset || 1 || Must set if Barracuda file format are choosen
|-
| innodb_buffer_pool_size || 128M || 456M || hold as much tables and indexes in system memory as is possible
|-
| innodb_read_io_threads || 16 || 32 || On high I/O systems, a value greater than 1 may allow the disk I/O to be more sequential
|-
| innodb_write_io_threads || 16 || 32 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_buffer_pool_instances || 1 || 2 or 4 || Only for older MySQL/MariaDB engines,
|-
| innodb_io_capacity || 200 || 1200 - 2600 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_io_capacity_max || 200 || 2400 - 5200 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|}

If you have SSD disks, use the recommended suggestion, otherwise, use minimum suggested. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used, but those lasted storage devices will be a short life of course.

Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:

* On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Only allow local connections on cases where there's only one server or no expected to connect from others:

<pre>
<nowiki>
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):

<pre>
<nowiki>
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:

<pre>
<nowiki>
cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF
</nowiki>
</pre>

== Updating or comming from upgrading ==

Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB engine need some extra steps when this are performed:

Upgrade databases on major releases
Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

# keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
# upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
# run the <code>mysql_upgrade -u root -p</code> script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
# Restart the service

If are unable to run ''mysql_upgrade'' because MySQL cannot start try run MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code> command and then run the <code>mysql_upgrade -u root -p</code> script.

= Relevant important notes =

== File system notes about the databases managed ==

'''Btrfs Notes'''

If the database (in <code>/var/lib/mysql</code>) resides on a [[Btrfs|btrfs]] file system, you should consider disabling '''Copy-on-Write''' for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.

'''ZFS Bock sizes'''

ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.

It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as <code>zfs set recordsize=8K /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job with <code>zfs set primarycache=metadata /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).

But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.

== Restore root password ==

<pre>
<nowiki>
rc-service mysql stop

kill `cat /run/mysqld/mysqld.pid`

/usr/bin/mysqld --datadir=/var/lib/mysql --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking &

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;set password = password('MyNewPass');"

kill `cat /run/mysqld/mysqld.pid`

rc-service mariadb restart
</nowiki>
</pre>

= See Also =

* [[MariaDB|MariaDB wiki page]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

= MariaDB vs MySQL =

It is more a matter of compatibility than of performance and characteristics and it depends on whether there is a purely business and support approach "zero concern".

MySQL, being from Oracle, establishes limits if a license is not purchased. MariaDB has a large connection pool, more than 200,000 connections, while MySQL has a smaller connection pool if it is not licensed.

In terms of performance, MariaDB is only a little faster than MySQL.

Which is more optimal this is not clear .. in general MySQL should be less, and MariaDB faster. There is a third option, Percona Server, which is the same MySQL service but with special aggressive optimization patches for servers. Percona mysql code must be compiled in Alpine linux.

== Comparison table ==

{| class="wikitable"
|-
! Characteristic !! MariaDB !! MySQL
|-
| Storage Engines || ~10, but some in development stage || less but well tested
|-
| Performance || just a little faster || less, there is almost no difference
|-
| Vectors || Yes, from MariaDB 11.7 || Enterprise-only
|-
| Initial version || 2009 (5.3) || 1995 (3.0)
|-
| Data masking || no || Enterprise-only
|-
| Dynamic columns || [https://mariadb.com/kb/en/dynamic-columns/ yes] || yes
|-
| Temporal tables || yes || no
|-
| Monitoring || SQLyog || MySQLworkbench
|-
| Routing || MariaDB MaxScale || MySQL Router
|-
| Analytics || MariaDB ColumnStore || no
|-
| Git starred times (GitHub)|| [https://github.com/MariaDB/server around 5.9k] || [https://github.com/mysql/mysql-server around 11.2k]
|}

For more info, see:
* https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility
* http://qgqlochekone.blogspot.com/2020/04/mariadb-mysql-and-mysqlworkbench.html

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]

MySQL

2025-03-11T23:21:25Z

Greenman: /* Comparison table */ temporal

MySQL is the most popular relational database server in free software for two simple reasons (which are not the best technical reasons):
# It's simple and very easy to use
# It is very similar to SQLSERVER and is also used in other systems

'''In the world of Linux Alpine there is a software that provides it''', it is [[MariaDB]], here we have a brief of info about compatibility and differences, but in short there's no great differences, if you have doubts check [[MySQL#MariaDB vs MySQL|MariaDB vs MySQL section here]]

'''In the wiki there are two approaches for its use''', the professional one (for servers and deploys) and the [[MariaDB|fast and simple]] usage (for developers and/or enthusiasts):

= MariaDB - fast and simple use =

{{:MariaDB}}

= MySQL (MariaDB) - Production usage =

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

This page assumes that you have a general knowledge about MariaDB, so if you are new to MySQL first take a look at the [[MariaDB|MariaDB wiki page]] for information about how it is packaged for Alpine Linux.

== Installation ==

Alpine Linux has dummy counterparts packages for those that are not close to that change from ''mysql'' to ''mariadb'' naming packages, please check the [[MariaDB|MariaDB wiki page]] for more information.

Take in consideration that the user <code>mysql</code> was created during instalation of packages, in the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and in that point only if are in their respective system accounts, will be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

That will install the most used ones.. <code>mariadb-cient</code> and <code>mariadb-server</code>, rest of packages are brief described here for more information, here are listed in orden of relevance for production server

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| mysql || v2 || it's a dummy package to easy install of mariadb || mariadb
|-
| mysql-client || v2 || it's a dummy package to easy install of commands tools || mariadb-client
|-
| mariadb || v2 || server equivalent to mysql-server || mariadb-common
|-
| mariadb-client || v2 || connection command line and tools || mariadb-common
|-
| mariadb-doc || v3.0 || manpages are there! || man man-pages
|-
| mariadb-connector-odbc || edge || coding or making OS level connections, to any DB without libs install || .
|-
| mariadb-connector-c || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| mariadb-backup || v3.8 || to external backup devices, not widely used, in past was inside mariadb package || .
|-
| mariadb-server-utils || v3.8 || server commands not widely used, in past was inside mariadb package || .
|-
| mariadb-dev || v3.1 || Need for compilations depends on source code || .
|-
| mariadb-test || v3.3 || testing suite from MariaDB tools || .
|-
| mariadb-mytop || v3.9 || data performance monitoring || .
|-
| mariadb-plugin-rocksdb || v3.9 || plain key-value event relational for data || .
|-
| mariadb-static || v3.8 || static libs for static non depends linking in builds || .
|-
| mariadb-embedded || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| mariadb-embedded-dev || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| mariadb-openrc || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must be know what they want.. there's no automatic window-like here.

The ''datadir'' are located to <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Setup the root account by asignes a proper password, this are purely paranoid. due next step already do that!
# Setup and init the installation by running the <code>mysql_secure_installation</code>
# Setup permissions for manage others users and databases

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor
</nowiki>
</pre>

After that, all are initializated to proceed with configuration, now can be done using the <code>
mysql_secure_installation</code> script at the next section:

== Configuration ==

In order to finish setup into '''MariaDB''' now provide '''this script called <code>mysql_secure_instalation</code> that also are present as <code>mariadb-secure-installation</code>''', too. This script provides minimal and security setup to the database, and here are the questions made explained:

# '''Enter current password for root (enter for none):''' this are if you previously setup as we done in previous section a root password, just provide it and press enter, '''must be provided due we already set previously''' and from now, this sript will access to the engine and alter many setting on the database. Correct respond are <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the unix_socket ensures that only admins can log into engine database. Since mysql 5.6 and mariadb 10.2 a new auth mechanish are set, by socket authentiaction, when system user are same as mysql/mariadb user, in this case, no password are need. In production servers this are not the case and must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' this answer are here only if the first one are just enter, or if can provide a better passowrd if no unix socket are set. Just press "n" only if you provided a good password, otherwise just
# '''Remove anonymous users? [Y/n]''' this permits remove the anonymous user created to log using socket authentication, only working on unix-like system. In any case, '''production system must remove it, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. For sure answer Y''' and proper respond must be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately, so answer Y''' and proper respond must be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration files ===

Due today were influenced by systemd standardization, the famous <nowiki>my.cnf</nowiki> are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the <code>/etc/my.cnf.d/</code> directory, user own config files are under <code>~/.my.cnf</code> config file of each home dir, and are read after global ones; so then we have:

{| class="wikitable"
|-
! Config file !! Path and name !! Versions of Alpine !! Contents to configure
|-
| my.cnf || <nowiki>/etc/mysql/my.cnf</nowiki> || v2 to v3.8 || All the directives, Global config file
|-
| mariadb-server.cnf || /etc/my.cnf.d/mariadb-server.cnf || since 3.9 || First Global config file, main directives
|-
| .my.cnf || <nowiki>$HOME</nowiki> || all || user name only config directives
|}

=== Production settings ===

These setting are only recommended for some server settings, there are the recommendation for high production settings:

{| class="wikitable"
|-
! Config setting !! Default !! Recommended !! Explanation
|-
| main ram || 2G || 8G - 16G || MariaDB/MySQL can run with 512M or 1G of ram, high production must use minimun of 4G or more.
|-
| data dir disk type || any || SSD || MariaDB/MySQL must run with faster SSD if high request will be produced.
|-
| collation_server || utf8_unicode_ci || utf8mb4_unicode_ci || With 8mb4 some characters are able to use more than a single byte.
|-
| character_set_client || iso8859-1 || utf8 or utf8mb4 || Important due are standard, for left to right use 8mb4 flavor
|-
| max_connections || 151 || 100 || total_connections = total_processes * (total_threads + script_servers + 1)
|-
| max_heap_table_size || 16M || 32M || allocate more memory to memory tables, for memory storage engines
|-
| tmp_table_size || 16M || 32M || It allows the sub queries to remain more in memory, making them faster
|-
| join_buffer_size || 32M || 64M || It allows the join queries to remain more in memory rather in temp files
|-
| innodb_file_format || unset || Barracuda || will allow longer indexes for important most used tables
|-
| innodb_large_prefix || unset || 1 || Must set if Barracuda file format are choosen
|-
| innodb_buffer_pool_size || 128M || 456M || hold as much tables and indexes in system memory as is possible
|-
| innodb_read_io_threads || 16 || 32 || On high I/O systems, a value greater than 1 may allow the disk I/O to be more sequential
|-
| innodb_write_io_threads || 16 || 32 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_buffer_pool_instances || 1 || 2 or 4 || Only for older MySQL/MariaDB engines,
|-
| innodb_io_capacity || 200 || 1200 - 2600 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_io_capacity_max || 200 || 2400 - 5200 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|}

If you have SSD disks, use the recommended suggestion, otherwise, use minimum suggested. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used, but those lasted storage devices will be a short life of course.

Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:

* On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Only allow local connections on cases where there's only one server or no expected to connect from others:

<pre>
<nowiki>
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):

<pre>
<nowiki>
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:

<pre>
<nowiki>
cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF
</nowiki>
</pre>

== Updating or comming from upgrading ==

Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB engine need some extra steps when this are performed:

Upgrade databases on major releases
Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

# keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
# upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
# run the <code>mysql_upgrade -u root -p</code> script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
# Restart the service

If are unable to run ''mysql_upgrade'' because MySQL cannot start try run MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code> command and then run the <code>mysql_upgrade -u root -p</code> script.

= Relevant important notes =

== File system notes about the databases managed ==

'''Btrfs Notes'''

If the database (in <code>/var/lib/mysql</code>) resides on a [[Btrfs|btrfs]] file system, you should consider disabling '''Copy-on-Write''' for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.

'''ZFS Bock sizes'''

ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.

It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as <code>zfs set recordsize=8K /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job with <code>zfs set primarycache=metadata /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).

But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.

== Restore root password ==

<pre>
<nowiki>
rc-service mysql stop

kill `cat /run/mysqld/mysqld.pid`

/usr/bin/mysqld --datadir=/var/lib/mysql --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking &

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;set password = password('MyNewPass');"

kill `cat /run/mysqld/mysqld.pid`

rc-service mariadb restart
</nowiki>
</pre>

= See Also =

* [[MariaDB|MariaDB wiki page]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

= MariaDB vs MySQL =

It is more a matter of compatibility than of performance and characteristics (with the arrival of MySQL v8) .. and it depends on whether there is a purely business and support approach "zero concern".

MySQL, being from Oracle, establishes limits if a license is not purchased, MariaDB has a large connection pool, more than 200,000 connections, while MySQL has a smaller connection pool if it is not licensed.

However, MariaDB does not support data masking and dynamic column while MySQL supports it, also MariaDB although it has 12 new storage engines while MySQL has less these are very new and MySQL's are widely known and tested.

In terms of performance, MariaDB is only a little faster than MySQL, this is because MySQL implements more business features, but this is only noticeable using these many features.

Which is more optimal this is not clear .. in general MySQL should be less, and MariaDB faster, there is a third option which is Percona which is the same MySQL service but with special aggressive optimization patches for servers. Percona mysql code must be compiled in Alpine linux.

== Comparison table ==

{| class="wikitable"
|-
! Characteristic !! MariaDB !! MySQL
|-
| Storage Engines || ~10, but some in development stage || less but well tested
|-
| Performance || just a little faster || less, there is almost no difference
|-
| Vectors || Yes, from MariaDB 11.7 || Enterprise-only
|-
| Initial version || 2009 (5.3) || 1995 (3.0)
|-
| Data masking || no || Enterprise-only
|-
| Dynamic columns || [https://mariadb.com/kb/en/dynamic-columns/ yes] || yes
|-
| Temporal tables || yes || no
|-
| Monitoring || SQLyog || MySQLworkbench
|-
| Routing || MariaDB MaxScale || MySQL Router
|-
| Analytics || MariaDB ColumnStore || no
|-
| Git starred times (GitHub)|| [https://github.com/MariaDB/server around 5.9k] || [https://github.com/mysql/mysql-server around 11.2k]
|}

For more info, see:
* https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility
* http://qgqlochekone.blogspot.com/2020/04/mariadb-mysql-and-mysqlworkbench.html

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]

MySQL

2025-03-11T23:19:42Z

Greenman: /* Comparison table */

MySQL is the most popular relational database server in free software for two simple reasons (which are not the best technical reasons):
# It's simple and very easy to use
# It is very similar to SQLSERVER and is also used in other systems

'''In the world of Linux Alpine there is a software that provides it''', it is [[MariaDB]], here we have a brief of info about compatibility and differences, but in short there's no great differences, if you have doubts check [[MySQL#MariaDB vs MySQL|MariaDB vs MySQL section here]]

'''In the wiki there are two approaches for its use''', the professional one (for servers and deploys) and the [[MariaDB|fast and simple]] usage (for developers and/or enthusiasts):

= MariaDB - fast and simple use =

{{:MariaDB}}

= MySQL (MariaDB) - Production usage =

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

This page assumes that you have a general knowledge about MariaDB, so if you are new to MySQL first take a look at the [[MariaDB|MariaDB wiki page]] for information about how it is packaged for Alpine Linux.

== Installation ==

Alpine Linux has dummy counterparts packages for those that are not close to that change from ''mysql'' to ''mariadb'' naming packages, please check the [[MariaDB|MariaDB wiki page]] for more information.

Take in consideration that the user <code>mysql</code> was created during instalation of packages, in the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and in that point only if are in their respective system accounts, will be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

That will install the most used ones.. <code>mariadb-cient</code> and <code>mariadb-server</code>, rest of packages are brief described here for more information, here are listed in orden of relevance for production server

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| mysql || v2 || it's a dummy package to easy install of mariadb || mariadb
|-
| mysql-client || v2 || it's a dummy package to easy install of commands tools || mariadb-client
|-
| mariadb || v2 || server equivalent to mysql-server || mariadb-common
|-
| mariadb-client || v2 || connection command line and tools || mariadb-common
|-
| mariadb-doc || v3.0 || manpages are there! || man man-pages
|-
| mariadb-connector-odbc || edge || coding or making OS level connections, to any DB without libs install || .
|-
| mariadb-connector-c || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| mariadb-backup || v3.8 || to external backup devices, not widely used, in past was inside mariadb package || .
|-
| mariadb-server-utils || v3.8 || server commands not widely used, in past was inside mariadb package || .
|-
| mariadb-dev || v3.1 || Need for compilations depends on source code || .
|-
| mariadb-test || v3.3 || testing suite from MariaDB tools || .
|-
| mariadb-mytop || v3.9 || data performance monitoring || .
|-
| mariadb-plugin-rocksdb || v3.9 || plain key-value event relational for data || .
|-
| mariadb-static || v3.8 || static libs for static non depends linking in builds || .
|-
| mariadb-embedded || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| mariadb-embedded-dev || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| mariadb-openrc || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must be know what they want.. there's no automatic window-like here.

The ''datadir'' are located to <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Setup the root account by asignes a proper password, this are purely paranoid. due next step already do that!
# Setup and init the installation by running the <code>mysql_secure_installation</code>
# Setup permissions for manage others users and databases

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor
</nowiki>
</pre>

After that, all are initializated to proceed with configuration, now can be done using the <code>
mysql_secure_installation</code> script at the next section:

== Configuration ==

In order to finish setup into '''MariaDB''' now provide '''this script called <code>mysql_secure_instalation</code> that also are present as <code>mariadb-secure-installation</code>''', too. This script provides minimal and security setup to the database, and here are the questions made explained:

# '''Enter current password for root (enter for none):''' this are if you previously setup as we done in previous section a root password, just provide it and press enter, '''must be provided due we already set previously''' and from now, this sript will access to the engine and alter many setting on the database. Correct respond are <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the unix_socket ensures that only admins can log into engine database. Since mysql 5.6 and mariadb 10.2 a new auth mechanish are set, by socket authentiaction, when system user are same as mysql/mariadb user, in this case, no password are need. In production servers this are not the case and must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' this answer are here only if the first one are just enter, or if can provide a better passowrd if no unix socket are set. Just press "n" only if you provided a good password, otherwise just
# '''Remove anonymous users? [Y/n]''' this permits remove the anonymous user created to log using socket authentication, only working on unix-like system. In any case, '''production system must remove it, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. For sure answer Y''' and proper respond must be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately, so answer Y''' and proper respond must be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration files ===

Due today were influenced by systemd standardization, the famous <nowiki>my.cnf</nowiki> are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the <code>/etc/my.cnf.d/</code> directory, user own config files are under <code>~/.my.cnf</code> config file of each home dir, and are read after global ones; so then we have:

{| class="wikitable"
|-
! Config file !! Path and name !! Versions of Alpine !! Contents to configure
|-
| my.cnf || <nowiki>/etc/mysql/my.cnf</nowiki> || v2 to v3.8 || All the directives, Global config file
|-
| mariadb-server.cnf || /etc/my.cnf.d/mariadb-server.cnf || since 3.9 || First Global config file, main directives
|-
| .my.cnf || <nowiki>$HOME</nowiki> || all || user name only config directives
|}

=== Production settings ===

These setting are only recommended for some server settings, there are the recommendation for high production settings:

{| class="wikitable"
|-
! Config setting !! Default !! Recommended !! Explanation
|-
| main ram || 2G || 8G - 16G || MariaDB/MySQL can run with 512M or 1G of ram, high production must use minimun of 4G or more.
|-
| data dir disk type || any || SSD || MariaDB/MySQL must run with faster SSD if high request will be produced.
|-
| collation_server || utf8_unicode_ci || utf8mb4_unicode_ci || With 8mb4 some characters are able to use more than a single byte.
|-
| character_set_client || iso8859-1 || utf8 or utf8mb4 || Important due are standard, for left to right use 8mb4 flavor
|-
| max_connections || 151 || 100 || total_connections = total_processes * (total_threads + script_servers + 1)
|-
| max_heap_table_size || 16M || 32M || allocate more memory to memory tables, for memory storage engines
|-
| tmp_table_size || 16M || 32M || It allows the sub queries to remain more in memory, making them faster
|-
| join_buffer_size || 32M || 64M || It allows the join queries to remain more in memory rather in temp files
|-
| innodb_file_format || unset || Barracuda || will allow longer indexes for important most used tables
|-
| innodb_large_prefix || unset || 1 || Must set if Barracuda file format are choosen
|-
| innodb_buffer_pool_size || 128M || 456M || hold as much tables and indexes in system memory as is possible
|-
| innodb_read_io_threads || 16 || 32 || On high I/O systems, a value greater than 1 may allow the disk I/O to be more sequential
|-
| innodb_write_io_threads || 16 || 32 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_buffer_pool_instances || 1 || 2 or 4 || Only for older MySQL/MariaDB engines,
|-
| innodb_io_capacity || 200 || 1200 - 2600 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_io_capacity_max || 200 || 2400 - 5200 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|}

If you have SSD disks, use the recommended suggestion, otherwise, use minimum suggested. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used, but those lasted storage devices will be a short life of course.

Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:

* On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Only allow local connections on cases where there's only one server or no expected to connect from others:

<pre>
<nowiki>
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):

<pre>
<nowiki>
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:

<pre>
<nowiki>
cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF
</nowiki>
</pre>

== Updating or comming from upgrading ==

Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB engine need some extra steps when this are performed:

Upgrade databases on major releases
Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

# keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
# upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
# run the <code>mysql_upgrade -u root -p</code> script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
# Restart the service

If are unable to run ''mysql_upgrade'' because MySQL cannot start try run MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code> command and then run the <code>mysql_upgrade -u root -p</code> script.

= Relevant important notes =

== File system notes about the databases managed ==

'''Btrfs Notes'''

If the database (in <code>/var/lib/mysql</code>) resides on a [[Btrfs|btrfs]] file system, you should consider disabling '''Copy-on-Write''' for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.

'''ZFS Bock sizes'''

ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.

It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as <code>zfs set recordsize=8K /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job with <code>zfs set primarycache=metadata /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).

But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.

== Restore root password ==

<pre>
<nowiki>
rc-service mysql stop

kill `cat /run/mysqld/mysqld.pid`

/usr/bin/mysqld --datadir=/var/lib/mysql --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking &

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;set password = password('MyNewPass');"

kill `cat /run/mysqld/mysqld.pid`

rc-service mariadb restart
</nowiki>
</pre>

= See Also =

* [[MariaDB|MariaDB wiki page]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

= MariaDB vs MySQL =

It is more a matter of compatibility than of performance and characteristics (with the arrival of MySQL v8) .. and it depends on whether there is a purely business and support approach "zero concern".

MySQL, being from Oracle, establishes limits if a license is not purchased, MariaDB has a large connection pool, more than 200,000 connections, while MySQL has a smaller connection pool if it is not licensed.

However, MariaDB does not support data masking and dynamic column while MySQL supports it, also MariaDB although it has 12 new storage engines while MySQL has less these are very new and MySQL's are widely known and tested.

In terms of performance, MariaDB is only a little faster than MySQL, this is because MySQL implements more business features, but this is only noticeable using these many features.

Which is more optimal this is not clear .. in general MySQL should be less, and MariaDB faster, there is a third option which is Percona which is the same MySQL service but with special aggressive optimization patches for servers. Percona mysql code must be compiled in Alpine linux.

== Comparison table ==

{| class="wikitable"
|-
! Characteristic !! MariaDB !! MySQL
|-
| Storage Engines || ~10, but some in development stage || less but well tested
|-
| Performance || just a little faster || less, there is almost no difference
|-
| Vectors || Yes, from MariaDB 11.7 || Enterprise-only
|-
| Initial version || 2009 (5.3) || 1995 (3.0)
|-
| Data masking || no || Enterprise-only
|-
| Dynamic columns || [https://mariadb.com/kb/en/dynamic-columns/ yes] || yes
|-
| Monitoring || SQLyog || MySQLworkbench
|-
| Routing || MariaDB MaxScale || MySQL Router
|-
| Analytics || MariaDB ColumnStore || not have
|-
| Git starred times (github)|| [https://github.com/MariaDB/server around 5.9k] || [https://github.com/mysql/mysql-server around 11.2k]
|}

For more info, see:
* https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility
* http://qgqlochekone.blogspot.com/2020/04/mariadb-mysql-and-mysqlworkbench.html

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]

MySQL

2025-03-11T23:18:14Z

Greenman: /* Comparison table */ around 10

MySQL is the most popular relational database server in free software for two simple reasons (which are not the best technical reasons):
# It's simple and very easy to use
# It is very similar to SQLSERVER and is also used in other systems

'''In the world of Linux Alpine there is a software that provides it''', it is [[MariaDB]], here we have a brief of info about compatibility and differences, but in short there's no great differences, if you have doubts check [[MySQL#MariaDB vs MySQL|MariaDB vs MySQL section here]]

'''In the wiki there are two approaches for its use''', the professional one (for servers and deploys) and the [[MariaDB|fast and simple]] usage (for developers and/or enthusiasts):

= MariaDB - fast and simple use =

{{:MariaDB}}

= MySQL (MariaDB) - Production usage =

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

This page assumes that you have a general knowledge about MariaDB, so if you are new to MySQL first take a look at the [[MariaDB|MariaDB wiki page]] for information about how it is packaged for Alpine Linux.

== Installation ==

Alpine Linux has dummy counterparts packages for those that are not close to that change from ''mysql'' to ''mariadb'' naming packages, please check the [[MariaDB|MariaDB wiki page]] for more information.

Take in consideration that the user <code>mysql</code> was created during instalation of packages, in the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and in that point only if are in their respective system accounts, will be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

That will install the most used ones.. <code>mariadb-cient</code> and <code>mariadb-server</code>, rest of packages are brief described here for more information, here are listed in orden of relevance for production server

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| mysql || v2 || it's a dummy package to easy install of mariadb || mariadb
|-
| mysql-client || v2 || it's a dummy package to easy install of commands tools || mariadb-client
|-
| mariadb || v2 || server equivalent to mysql-server || mariadb-common
|-
| mariadb-client || v2 || connection command line and tools || mariadb-common
|-
| mariadb-doc || v3.0 || manpages are there! || man man-pages
|-
| mariadb-connector-odbc || edge || coding or making OS level connections, to any DB without libs install || .
|-
| mariadb-connector-c || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| mariadb-backup || v3.8 || to external backup devices, not widely used, in past was inside mariadb package || .
|-
| mariadb-server-utils || v3.8 || server commands not widely used, in past was inside mariadb package || .
|-
| mariadb-dev || v3.1 || Need for compilations depends on source code || .
|-
| mariadb-test || v3.3 || testing suite from MariaDB tools || .
|-
| mariadb-mytop || v3.9 || data performance monitoring || .
|-
| mariadb-plugin-rocksdb || v3.9 || plain key-value event relational for data || .
|-
| mariadb-static || v3.8 || static libs for static non depends linking in builds || .
|-
| mariadb-embedded || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| mariadb-embedded-dev || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| mariadb-openrc || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must be know what they want.. there's no automatic window-like here.

The ''datadir'' are located to <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Setup the root account by asignes a proper password, this are purely paranoid. due next step already do that!
# Setup and init the installation by running the <code>mysql_secure_installation</code>
# Setup permissions for manage others users and databases

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor
</nowiki>
</pre>

After that, all are initializated to proceed with configuration, now can be done using the <code>
mysql_secure_installation</code> script at the next section:

== Configuration ==

In order to finish setup into '''MariaDB''' now provide '''this script called <code>mysql_secure_instalation</code> that also are present as <code>mariadb-secure-installation</code>''', too. This script provides minimal and security setup to the database, and here are the questions made explained:

# '''Enter current password for root (enter for none):''' this are if you previously setup as we done in previous section a root password, just provide it and press enter, '''must be provided due we already set previously''' and from now, this sript will access to the engine and alter many setting on the database. Correct respond are <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the unix_socket ensures that only admins can log into engine database. Since mysql 5.6 and mariadb 10.2 a new auth mechanish are set, by socket authentiaction, when system user are same as mysql/mariadb user, in this case, no password are need. In production servers this are not the case and must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' this answer are here only if the first one are just enter, or if can provide a better passowrd if no unix socket are set. Just press "n" only if you provided a good password, otherwise just
# '''Remove anonymous users? [Y/n]''' this permits remove the anonymous user created to log using socket authentication, only working on unix-like system. In any case, '''production system must remove it, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. For sure answer Y''' and proper respond must be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately, so answer Y''' and proper respond must be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration files ===

Due today were influenced by systemd standardization, the famous <nowiki>my.cnf</nowiki> are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the <code>/etc/my.cnf.d/</code> directory, user own config files are under <code>~/.my.cnf</code> config file of each home dir, and are read after global ones; so then we have:

{| class="wikitable"
|-
! Config file !! Path and name !! Versions of Alpine !! Contents to configure
|-
| my.cnf || <nowiki>/etc/mysql/my.cnf</nowiki> || v2 to v3.8 || All the directives, Global config file
|-
| mariadb-server.cnf || /etc/my.cnf.d/mariadb-server.cnf || since 3.9 || First Global config file, main directives
|-
| .my.cnf || <nowiki>$HOME</nowiki> || all || user name only config directives
|}

=== Production settings ===

These setting are only recommended for some server settings, there are the recommendation for high production settings:

{| class="wikitable"
|-
! Config setting !! Default !! Recommended !! Explanation
|-
| main ram || 2G || 8G - 16G || MariaDB/MySQL can run with 512M or 1G of ram, high production must use minimun of 4G or more.
|-
| data dir disk type || any || SSD || MariaDB/MySQL must run with faster SSD if high request will be produced.
|-
| collation_server || utf8_unicode_ci || utf8mb4_unicode_ci || With 8mb4 some characters are able to use more than a single byte.
|-
| character_set_client || iso8859-1 || utf8 or utf8mb4 || Important due are standard, for left to right use 8mb4 flavor
|-
| max_connections || 151 || 100 || total_connections = total_processes * (total_threads + script_servers + 1)
|-
| max_heap_table_size || 16M || 32M || allocate more memory to memory tables, for memory storage engines
|-
| tmp_table_size || 16M || 32M || It allows the sub queries to remain more in memory, making them faster
|-
| join_buffer_size || 32M || 64M || It allows the join queries to remain more in memory rather in temp files
|-
| innodb_file_format || unset || Barracuda || will allow longer indexes for important most used tables
|-
| innodb_large_prefix || unset || 1 || Must set if Barracuda file format are choosen
|-
| innodb_buffer_pool_size || 128M || 456M || hold as much tables and indexes in system memory as is possible
|-
| innodb_read_io_threads || 16 || 32 || On high I/O systems, a value greater than 1 may allow the disk I/O to be more sequential
|-
| innodb_write_io_threads || 16 || 32 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_buffer_pool_instances || 1 || 2 or 4 || Only for older MySQL/MariaDB engines,
|-
| innodb_io_capacity || 200 || 1200 - 2600 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_io_capacity_max || 200 || 2400 - 5200 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|}

If you have SSD disks, use the recommended suggestion, otherwise, use minimum suggested. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used, but those lasted storage devices will be a short life of course.

Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:

* On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Only allow local connections on cases where there's only one server or no expected to connect from others:

<pre>
<nowiki>
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):

<pre>
<nowiki>
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:

<pre>
<nowiki>
cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF
</nowiki>
</pre>

== Updating or comming from upgrading ==

Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB engine need some extra steps when this are performed:

Upgrade databases on major releases
Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

# keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
# upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
# run the <code>mysql_upgrade -u root -p</code> script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
# Restart the service

If are unable to run ''mysql_upgrade'' because MySQL cannot start try run MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code> command and then run the <code>mysql_upgrade -u root -p</code> script.

= Relevant important notes =

== File system notes about the databases managed ==

'''Btrfs Notes'''

If the database (in <code>/var/lib/mysql</code>) resides on a [[Btrfs|btrfs]] file system, you should consider disabling '''Copy-on-Write''' for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.

'''ZFS Bock sizes'''

ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.

It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as <code>zfs set recordsize=8K /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job with <code>zfs set primarycache=metadata /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).

But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.

== Restore root password ==

<pre>
<nowiki>
rc-service mysql stop

kill `cat /run/mysqld/mysqld.pid`

/usr/bin/mysqld --datadir=/var/lib/mysql --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking &

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;set password = password('MyNewPass');"

kill `cat /run/mysqld/mysqld.pid`

rc-service mariadb restart
</nowiki>
</pre>

= See Also =

* [[MariaDB|MariaDB wiki page]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

= MariaDB vs MySQL =

It is more a matter of compatibility than of performance and characteristics (with the arrival of MySQL v8) .. and it depends on whether there is a purely business and support approach "zero concern".

MySQL, being from Oracle, establishes limits if a license is not purchased, MariaDB has a large connection pool, more than 200,000 connections, while MySQL has a smaller connection pool if it is not licensed.

However, MariaDB does not support data masking and dynamic column while MySQL supports it, also MariaDB although it has 12 new storage engines while MySQL has less these are very new and MySQL's are widely known and tested.

In terms of performance, MariaDB is only a little faster than MySQL, this is because MySQL implements more business features, but this is only noticeable using these many features.

Which is more optimal this is not clear .. in general MySQL should be less, and MariaDB faster, there is a third option which is Percona which is the same MySQL service but with special aggressive optimization patches for servers. Percona mysql code must be compiled in Alpine linux.

== Comparison table ==

{| class="wikitable"
|-
! Characteristic !! MariaDB !! MySQL
|-
| Storage Engines || ~10, but some in development stage || less but well tested
|-
| Performance || just a little faster || less, there is almost no difference
|-
| Initial version || 2009 (5.3) || 1995 (3.0)
|-
| Data masking || no || yes (Enterprise only)
|-
| Dynamic columns || [https://mariadb.com/kb/en/dynamic-columns/ yes] || yes
|-
| Monitoring || SQLyog || MySQLworkbench
|-
| Routing || MariaDB MaxScale || MySQL Router
|-
| Analytics || MariaDB ColumnStore || not have
|-
| Git starred times (github)|| [https://github.com/MariaDB/server around 5.9k] || [https://github.com/mysql/mysql-server around 11.2k]
|}

For more info, see:
* https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility
* http://qgqlochekone.blogspot.com/2020/04/mariadb-mysql-and-mysqlworkbench.html

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]

MySQL

2025-03-11T23:16:15Z

Greenman: /* MariaDB vs MySQL */ clarify, copyedit

MySQL is the most popular relational database server in free software for two simple reasons (which are not the best technical reasons):
# It's simple and very easy to use
# It is very similar to SQLSERVER and is also used in other systems

'''In the world of Linux Alpine there is a software that provides it''', it is [[MariaDB]], here we have a brief of info about compatibility and differences, but in short there's no great differences, if you have doubts check [[MySQL#MariaDB vs MySQL|MariaDB vs MySQL section here]]

'''In the wiki there are two approaches for its use''', the professional one (for servers and deploys) and the [[MariaDB|fast and simple]] usage (for developers and/or enthusiasts):

= MariaDB - fast and simple use =

{{:MariaDB}}

= MySQL (MariaDB) - Production usage =

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

This page assumes that you have a general knowledge about MariaDB, so if you are new to MySQL first take a look at the [[MariaDB|MariaDB wiki page]] for information about how it is packaged for Alpine Linux.

== Installation ==

Alpine Linux has dummy counterparts packages for those that are not close to that change from ''mysql'' to ''mariadb'' naming packages, please check the [[MariaDB|MariaDB wiki page]] for more information.

Take in consideration that the user <code>mysql</code> was created during instalation of packages, in the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and in that point only if are in their respective system accounts, will be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

That will install the most used ones.. <code>mariadb-cient</code> and <code>mariadb-server</code>, rest of packages are brief described here for more information, here are listed in orden of relevance for production server

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| mysql || v2 || it's a dummy package to easy install of mariadb || mariadb
|-
| mysql-client || v2 || it's a dummy package to easy install of commands tools || mariadb-client
|-
| mariadb || v2 || server equivalent to mysql-server || mariadb-common
|-
| mariadb-client || v2 || connection command line and tools || mariadb-common
|-
| mariadb-doc || v3.0 || manpages are there! || man man-pages
|-
| mariadb-connector-odbc || edge || coding or making OS level connections, to any DB without libs install || .
|-
| mariadb-connector-c || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| mariadb-backup || v3.8 || to external backup devices, not widely used, in past was inside mariadb package || .
|-
| mariadb-server-utils || v3.8 || server commands not widely used, in past was inside mariadb package || .
|-
| mariadb-dev || v3.1 || Need for compilations depends on source code || .
|-
| mariadb-test || v3.3 || testing suite from MariaDB tools || .
|-
| mariadb-mytop || v3.9 || data performance monitoring || .
|-
| mariadb-plugin-rocksdb || v3.9 || plain key-value event relational for data || .
|-
| mariadb-static || v3.8 || static libs for static non depends linking in builds || .
|-
| mariadb-embedded || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| mariadb-embedded-dev || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| mariadb-openrc || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must be know what they want.. there's no automatic window-like here.

The ''datadir'' are located to <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Setup the root account by asignes a proper password, this are purely paranoid. due next step already do that!
# Setup and init the installation by running the <code>mysql_secure_installation</code>
# Setup permissions for manage others users and databases

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor
</nowiki>
</pre>

After that, all are initializated to proceed with configuration, now can be done using the <code>
mysql_secure_installation</code> script at the next section:

== Configuration ==

In order to finish setup into '''MariaDB''' now provide '''this script called <code>mysql_secure_instalation</code> that also are present as <code>mariadb-secure-installation</code>''', too. This script provides minimal and security setup to the database, and here are the questions made explained:

# '''Enter current password for root (enter for none):''' this are if you previously setup as we done in previous section a root password, just provide it and press enter, '''must be provided due we already set previously''' and from now, this sript will access to the engine and alter many setting on the database. Correct respond are <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the unix_socket ensures that only admins can log into engine database. Since mysql 5.6 and mariadb 10.2 a new auth mechanish are set, by socket authentiaction, when system user are same as mysql/mariadb user, in this case, no password are need. In production servers this are not the case and must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' this answer are here only if the first one are just enter, or if can provide a better passowrd if no unix socket are set. Just press "n" only if you provided a good password, otherwise just
# '''Remove anonymous users? [Y/n]''' this permits remove the anonymous user created to log using socket authentication, only working on unix-like system. In any case, '''production system must remove it, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. For sure answer Y''' and proper respond must be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately, so answer Y''' and proper respond must be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration files ===

Due today were influenced by systemd standardization, the famous <nowiki>my.cnf</nowiki> are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the <code>/etc/my.cnf.d/</code> directory, user own config files are under <code>~/.my.cnf</code> config file of each home dir, and are read after global ones; so then we have:

{| class="wikitable"
|-
! Config file !! Path and name !! Versions of Alpine !! Contents to configure
|-
| my.cnf || <nowiki>/etc/mysql/my.cnf</nowiki> || v2 to v3.8 || All the directives, Global config file
|-
| mariadb-server.cnf || /etc/my.cnf.d/mariadb-server.cnf || since 3.9 || First Global config file, main directives
|-
| .my.cnf || <nowiki>$HOME</nowiki> || all || user name only config directives
|}

=== Production settings ===

These setting are only recommended for some server settings, there are the recommendation for high production settings:

{| class="wikitable"
|-
! Config setting !! Default !! Recommended !! Explanation
|-
| main ram || 2G || 8G - 16G || MariaDB/MySQL can run with 512M or 1G of ram, high production must use minimun of 4G or more.
|-
| data dir disk type || any || SSD || MariaDB/MySQL must run with faster SSD if high request will be produced.
|-
| collation_server || utf8_unicode_ci || utf8mb4_unicode_ci || With 8mb4 some characters are able to use more than a single byte.
|-
| character_set_client || iso8859-1 || utf8 or utf8mb4 || Important due are standard, for left to right use 8mb4 flavor
|-
| max_connections || 151 || 100 || total_connections = total_processes * (total_threads + script_servers + 1)
|-
| max_heap_table_size || 16M || 32M || allocate more memory to memory tables, for memory storage engines
|-
| tmp_table_size || 16M || 32M || It allows the sub queries to remain more in memory, making them faster
|-
| join_buffer_size || 32M || 64M || It allows the join queries to remain more in memory rather in temp files
|-
| innodb_file_format || unset || Barracuda || will allow longer indexes for important most used tables
|-
| innodb_large_prefix || unset || 1 || Must set if Barracuda file format are choosen
|-
| innodb_buffer_pool_size || 128M || 456M || hold as much tables and indexes in system memory as is possible
|-
| innodb_read_io_threads || 16 || 32 || On high I/O systems, a value greater than 1 may allow the disk I/O to be more sequential
|-
| innodb_write_io_threads || 16 || 32 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_buffer_pool_instances || 1 || 2 or 4 || Only for older MySQL/MariaDB engines,
|-
| innodb_io_capacity || 200 || 1200 - 2600 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_io_capacity_max || 200 || 2400 - 5200 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|}

If you have SSD disks, use the recommended suggestion, otherwise, use minimum suggested. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used, but those lasted storage devices will be a short life of course.

Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:

* On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Only allow local connections on cases where there's only one server or no expected to connect from others:

<pre>
<nowiki>
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):

<pre>
<nowiki>
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:

<pre>
<nowiki>
cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF
</nowiki>
</pre>

== Updating or comming from upgrading ==

Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB engine need some extra steps when this are performed:

Upgrade databases on major releases
Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

# keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
# upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
# run the <code>mysql_upgrade -u root -p</code> script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
# Restart the service

If are unable to run ''mysql_upgrade'' because MySQL cannot start try run MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code> command and then run the <code>mysql_upgrade -u root -p</code> script.

= Relevant important notes =

== File system notes about the databases managed ==

'''Btrfs Notes'''

If the database (in <code>/var/lib/mysql</code>) resides on a [[Btrfs|btrfs]] file system, you should consider disabling '''Copy-on-Write''' for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.

'''ZFS Bock sizes'''

ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.

It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as <code>zfs set recordsize=8K /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job with <code>zfs set primarycache=metadata /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).

But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.

== Restore root password ==

<pre>
<nowiki>
rc-service mysql stop

kill `cat /run/mysqld/mysqld.pid`

/usr/bin/mysqld --datadir=/var/lib/mysql --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking &

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;set password = password('MyNewPass');"

kill `cat /run/mysqld/mysqld.pid`

rc-service mariadb restart
</nowiki>
</pre>

= See Also =

* [[MariaDB|MariaDB wiki page]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

= MariaDB vs MySQL =

It is more a matter of compatibility than of performance and characteristics (with the arrival of MySQL v8) .. and it depends on whether there is a purely business and support approach "zero concern".

MySQL, being from Oracle, establishes limits if a license is not purchased, MariaDB has a large connection pool, more than 200,000 connections, while MySQL has a smaller connection pool if it is not licensed.

However, MariaDB does not support data masking and dynamic column while MySQL supports it, also MariaDB although it has 12 new storage engines while MySQL has less these are very new and MySQL's are widely known and tested.

In terms of performance, MariaDB is only a little faster than MySQL, this is because MySQL implements more business features, but this is only noticeable using these many features.

Which is more optimal this is not clear .. in general MySQL should be less, and MariaDB faster, there is a third option which is Percona which is the same MySQL service but with special aggressive optimization patches for servers. Percona mysql code must be compiled in Alpine linux.

== Comparison table ==

{| class="wikitable"
|-
! Characteristic !! MariaDB !! MySQL
|-
| Storage Engines || up to 12 but many in development stage || less but well tested
|-
| Performance || just a little faster || less, there is almost no difference
|-
| Initial version || 2009 (5.3) || 1995 (3.0)
|-
| Data masking || no || yes (Enterprise only)
|-
| Dynamic columns || [https://mariadb.com/kb/en/dynamic-columns/ yes] || yes
|-
| Monitoring || SQLyog || MySQLworkbench
|-
| Routing || MariaDB MaxScale || MySQL Router
|-
| Analytics || MariaDB ColumnStore || not have
|-
| Git starred times (github)|| [https://github.com/MariaDB/server around 5.9k] || [https://github.com/mysql/mysql-server around 11.2k]
|}

For more info, see:
* https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility
* http://qgqlochekone.blogspot.com/2020/04/mariadb-mysql-and-mysqlworkbench.html

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]

MySQL

2025-03-11T23:13:31Z

Greenman: /* MySQL (MariaDB) - Production usage */ copyedit

MySQL is the most popular relational database server in free software for two simple reasons (which are not the best technical reasons):
# It's simple and very easy to use
# It is very similar to SQLSERVER and is also used in other systems

'''In the world of Linux Alpine there is a software that provides it''', it is [[MariaDB]], here we have a brief of info about compatibility and differences, but in short there's no great differences, if you have doubts check [[MySQL#MariaDB vs MySQL|MariaDB vs MySQL section here]]

'''In the wiki there are two approaches for its use''', the professional one (for servers and deploys) and the [[MariaDB|fast and simple]] usage (for developers and/or enthusiasts):

= MariaDB - fast and simple use =

{{:MariaDB}}

= MySQL (MariaDB) - Production usage =

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

This page assumes that you have a general knowledge about MariaDB, so if you are new to MySQL first take a look at the [[MariaDB|MariaDB wiki page]] for information about how it is packaged for Alpine Linux.

== Installation ==

Alpine Linux has dummy counterparts packages for those that are not close to that change from ''mysql'' to ''mariadb'' naming packages, please check the [[MariaDB|MariaDB wiki page]] for more information.

Take in consideration that the user <code>mysql</code> was created during instalation of packages, in the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and in that point only if are in their respective system accounts, will be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

That will install the most used ones.. <code>mariadb-cient</code> and <code>mariadb-server</code>, rest of packages are brief described here for more information, here are listed in orden of relevance for production server

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| mysql || v2 || it's a dummy package to easy install of mariadb || mariadb
|-
| mysql-client || v2 || it's a dummy package to easy install of commands tools || mariadb-client
|-
| mariadb || v2 || server equivalent to mysql-server || mariadb-common
|-
| mariadb-client || v2 || connection command line and tools || mariadb-common
|-
| mariadb-doc || v3.0 || manpages are there! || man man-pages
|-
| mariadb-connector-odbc || edge || coding or making OS level connections, to any DB without libs install || .
|-
| mariadb-connector-c || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| mariadb-backup || v3.8 || to external backup devices, not widely used, in past was inside mariadb package || .
|-
| mariadb-server-utils || v3.8 || server commands not widely used, in past was inside mariadb package || .
|-
| mariadb-dev || v3.1 || Need for compilations depends on source code || .
|-
| mariadb-test || v3.3 || testing suite from MariaDB tools || .
|-
| mariadb-mytop || v3.9 || data performance monitoring || .
|-
| mariadb-plugin-rocksdb || v3.9 || plain key-value event relational for data || .
|-
| mariadb-static || v3.8 || static libs for static non depends linking in builds || .
|-
| mariadb-embedded || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| mariadb-embedded-dev || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| mariadb-openrc || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must be know what they want.. there's no automatic window-like here.

The ''datadir'' are located to <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Setup the root account by asignes a proper password, this are purely paranoid. due next step already do that!
# Setup and init the installation by running the <code>mysql_secure_installation</code>
# Setup permissions for manage others users and databases

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor
</nowiki>
</pre>

After that, all are initializated to proceed with configuration, now can be done using the <code>
mysql_secure_installation</code> script at the next section:

== Configuration ==

In order to finish setup into '''MariaDB''' now provide '''this script called <code>mysql_secure_instalation</code> that also are present as <code>mariadb-secure-installation</code>''', too. This script provides minimal and security setup to the database, and here are the questions made explained:

# '''Enter current password for root (enter for none):''' this are if you previously setup as we done in previous section a root password, just provide it and press enter, '''must be provided due we already set previously''' and from now, this sript will access to the engine and alter many setting on the database. Correct respond are <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the unix_socket ensures that only admins can log into engine database. Since mysql 5.6 and mariadb 10.2 a new auth mechanish are set, by socket authentiaction, when system user are same as mysql/mariadb user, in this case, no password are need. In production servers this are not the case and must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' this answer are here only if the first one are just enter, or if can provide a better passowrd if no unix socket are set. Just press "n" only if you provided a good password, otherwise just
# '''Remove anonymous users? [Y/n]''' this permits remove the anonymous user created to log using socket authentication, only working on unix-like system. In any case, '''production system must remove it, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. For sure answer Y''' and proper respond must be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately, so answer Y''' and proper respond must be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration files ===

Due today were influenced by systemd standardization, the famous <nowiki>my.cnf</nowiki> are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the <code>/etc/my.cnf.d/</code> directory, user own config files are under <code>~/.my.cnf</code> config file of each home dir, and are read after global ones; so then we have:

{| class="wikitable"
|-
! Config file !! Path and name !! Versions of Alpine !! Contents to configure
|-
| my.cnf || <nowiki>/etc/mysql/my.cnf</nowiki> || v2 to v3.8 || All the directives, Global config file
|-
| mariadb-server.cnf || /etc/my.cnf.d/mariadb-server.cnf || since 3.9 || First Global config file, main directives
|-
| .my.cnf || <nowiki>$HOME</nowiki> || all || user name only config directives
|}

=== Production settings ===

These setting are only recommended for some server settings, there are the recommendation for high production settings:

{| class="wikitable"
|-
! Config setting !! Default !! Recommended !! Explanation
|-
| main ram || 2G || 8G - 16G || MariaDB/MySQL can run with 512M or 1G of ram, high production must use minimun of 4G or more.
|-
| data dir disk type || any || SSD || MariaDB/MySQL must run with faster SSD if high request will be produced.
|-
| collation_server || utf8_unicode_ci || utf8mb4_unicode_ci || With 8mb4 some characters are able to use more than a single byte.
|-
| character_set_client || iso8859-1 || utf8 or utf8mb4 || Important due are standard, for left to right use 8mb4 flavor
|-
| max_connections || 151 || 100 || total_connections = total_processes * (total_threads + script_servers + 1)
|-
| max_heap_table_size || 16M || 32M || allocate more memory to memory tables, for memory storage engines
|-
| tmp_table_size || 16M || 32M || It allows the sub queries to remain more in memory, making them faster
|-
| join_buffer_size || 32M || 64M || It allows the join queries to remain more in memory rather in temp files
|-
| innodb_file_format || unset || Barracuda || will allow longer indexes for important most used tables
|-
| innodb_large_prefix || unset || 1 || Must set if Barracuda file format are choosen
|-
| innodb_buffer_pool_size || 128M || 456M || hold as much tables and indexes in system memory as is possible
|-
| innodb_read_io_threads || 16 || 32 || On high I/O systems, a value greater than 1 may allow the disk I/O to be more sequential
|-
| innodb_write_io_threads || 16 || 32 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_buffer_pool_instances || 1 || 2 or 4 || Only for older MySQL/MariaDB engines,
|-
| innodb_io_capacity || 200 || 1200 - 2600 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_io_capacity_max || 200 || 2400 - 5200 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|}

If you have SSD disks, use the recommended suggestion, otherwise, use minimum suggested. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used, but those lasted storage devices will be a short life of course.

Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:

* On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Only allow local connections on cases where there's only one server or no expected to connect from others:

<pre>
<nowiki>
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):

<pre>
<nowiki>
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:

<pre>
<nowiki>
cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF
</nowiki>
</pre>

== Updating or comming from upgrading ==

Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB engine need some extra steps when this are performed:

Upgrade databases on major releases
Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

# keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
# upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
# run the <code>mysql_upgrade -u root -p</code> script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
# Restart the service

If are unable to run ''mysql_upgrade'' because MySQL cannot start try run MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code> command and then run the <code>mysql_upgrade -u root -p</code> script.

= Relevant important notes =

== File system notes about the databases managed ==

'''Btrfs Notes'''

If the database (in <code>/var/lib/mysql</code>) resides on a [[Btrfs|btrfs]] file system, you should consider disabling '''Copy-on-Write''' for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.

'''ZFS Bock sizes'''

ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.

It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as <code>zfs set recordsize=8K /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job with <code>zfs set primarycache=metadata /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).

But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.

== Restore root password ==

<pre>
<nowiki>
rc-service mysql stop

kill `cat /run/mysqld/mysqld.pid`

/usr/bin/mysqld --datadir=/var/lib/mysql --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking &

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;set password = password('MyNewPass');"

kill `cat /run/mysqld/mysqld.pid`

rc-service mariadb restart
</nowiki>
</pre>

= See Also =

* [[MariaDB|MariaDB wiki page]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

= MariaDB vs MySQL =

It is more a matter of compatibility than of performance and characteristics (with the arrival of MySQL v8) .. and it depends on whether there is a purely business and support approach "zero concern".

MySQL, being from Oracle, establishes limits if a license is not purchased, MariaDB has a large connection pool, more than 200,000 connections, while MySQL has a smaller connection pool if it is not licensed.

However, MariaDB does not support data masking and dynamic column while MySQL supports it, also MariaDB although it has 12 new storage engines while MySQL has less these are very new and MySQL's are widely known and tested.

In terms of performance, MariaDB is only a little faster than MySQL, this is because MySQL implements more business features, but this is only noticeable using these many features.

Which is more optimal this is not clear .. in general MySQL should be less, and MariaDB faster, there is a third option which is Percona which is the same MySQL service but with special aggressive optimization patches for servers. Percona mysql code must be compiled in Alpine linux.

== Comparison table ==

{| class="wikitable"
|-
! Characteristic !! MariaDB !! MySQL
|-
| Storage Engines || up to 12 but many in development stage || less but well tested
|-
| Performance || just a little faster || less, there is almost no difference
|-
| Initial version || 2009 (5.3) || 1995 (3.0)
|-
| Data masking || no || yes
|-
| Dynamic columns || [https://mariadb.com/kb/en/dynamic-columns/ yes] || yes
|-
| Monitoring || SQLyog || MySQLworkbench
|-
| Routing || MariaDB MaxScale || Mysql Router
|-
| Analytics || MariaDB ColumnStore || not have
|-
| Git starred times (github)|| [https://github.com/MariaDB/server around 5.9k] || [https://github.com/mysql/mysql-server around 11.2k]
|}

For more info, see:
* https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility
* http://qgqlochekone.blogspot.com/2020/04/mariadb-mysql-and-mysqlworkbench.html

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]

MariaDB

2025-03-11T23:11:17Z

Greenman: /* Configuration */ secure-installation

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

== Installation ==

The Alpine Linux repositories no longer include the actual MySQL binaries, installing the <code>mysql-*</code> packages will instead install MariaDB.

Installing <code>mariadb</code> will create the user <code>mysql</code>. When the database is initialized, two users will be added to the database: <code>root</code> and <code>mysql</code>. By default these users will only be accessible if you are logged in as the corresponding system user.

{{Cmd|apk add {{pkg|mariadb|arch=}} {{pkg|mariadb-client|arch=}}}}

Installing the above packages will add the main components of MariaDB to the system: <code>mariadb-cient</code> and <code>mariadb-server</code>. Other available packages are described in the table below, and are listed in order of relevance for a production server.

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| {{Pkg|mysql}} || v2 || a transitional package that installs mariadb || mariadb
|-
| {{Pkg|mysql-client}} || v2 || a transitional package that installs the mariadb client tools || mariadb-client
|-
| {{Pkg|mariadb}} || v2 || server equivalent to mysql-server || mariadb-common
|-
| {{Pkg|mariadb-client}} || v2 || connection command line and tools || mariadb-common
|-
| {{Pkg|mariadb-doc}} || v3.0 || manpages for mariadb || man man-pages
|-
| {{Pkg|mariadb-connector-odbc}} || edge || coding or making OS level connections, to any DB without libs install || .
|-
| {{Pkg|mariadb-connector-c}} || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| {{Pkg|mariadb-backup}} || v3.8 || tool for physical online backups, no longer widely used || .
|-
| {{Pkg|mariadb-server-utils}} || v3.8 || server commands not widely used, in past was inside MariaDB package || .
|-
| {{Pkg|mariadb-dev}} || v3.1 || development files for MariaDB || .
|-
| {{Pkg|mariadb-test}} || v3.3 || testing suite from MariaDB tools || .
|-
| {{Pkg|mariadb-mytop}} || v3.9 || data performance monitoring || .
|-
| {{Pkg|mariadb-plugin-rocksdb}} || v3.9 || plain key-value event relational for data || .
|-
| {{Pkg|mariadb-static}} || v3.8 || static libs for static non depends linking in builds || .
|-
| {{Pkg|mariadb-embedded}} || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| {{Pkg|mariadb-embedded-dev}} || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| {{Pkg|mariadb-openrc}} || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The version of MariaDB in the Alpine repositories behave like the MySQL tarball. No graphical tools are included.

The ''datadir'' located at {{Path|/var/lib/mysql}} must be owned by the mysql user and group. The location of the ''datadir'' can be changed by editing the <code>mariadb</code> service file in {{Path|/etc/init.d}}. The new location will also need to be set by adding <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> in the <code>[mysqld]</code> section in a mariadb configuration file.

Normal initialization of mariadb can be done as follows:

# Start the main service. At this point there will be no root password set. <code>rc-service mariadb start</code>
# Secure the database by running <code>mysql_secure_installation</code>
# Setup permissions for managing others users and databases see: '''[[#Configuration|Configuration]]'''
# Add MariaDb to OpenRC. <code>rc-update add mariadb default</code>

== Configuration ==

In order to help with the basic configuration of the database engine, MariaDB provides [https://mariadb.com/kb/en/mariadb-secure-installation/ mariadb-secure-installation].

Many of the reasons for running this script are no longer necessary, since MariaDB has defaulted to Unix socket authentication since MariaDB 10.4.

This script walks you through the basics of securing the database. The options are explained below.

# '''Enter current password for root (enter for none):''' If you have previously set up a root password, provide it here and press enter. If not, just press enter.
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the Unix_socket ensures that only admins can log into engine database. For non-production servers just press "n" to setup a root password, which will give you the response <code>... skipping.</code>
# '''Change the root password? [Y/n]''' Here you can change the root password, or set one if needed. Press "Y" and enter the new password.
# '''Remove anonymous users? [Y/n]''' Remove anonymous users created to log in using socket authentication. Unless you're sure you need this, answer "Y" to remove them.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost' in order to protect from password sniffing attempts over the network. Answer "Y".
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. If this is not needed, answer "Y".
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Answer "Y".

After the script exits, restart the service with <code>rc-service mariadb restart</code>

To start the database daemon on every boot, run <code>rc-update add mariadb default</code>

=== Configuration files and customization ===

Rather than being stored in {{Path|my.cnf}}, configuration settings for MariaDB are now organized in separate files. The primary configuration is done by adding files to {{Path|/etc/my.cnf.d/}}. User-specific configuration files are stored in {{Path|~/.my.cnf}}. User-specific configuration files are loaded after the system-wide configuration. The locations of the various configuration files are listed below.

{| class="wikitable"
|-
! Config file !! Versions of Alpine !! Contents to configure
|-
| {{Path|/etc/mysql/my.cnf}} || v2 to v3.8 || All the directives, global config file
|-
| {{Path|/etc/my.cnf.d/mariadb-server.cnf}} || since 3.9 || First global config file, main directives
|-
| {{path|$HOME/.my.cnf}} || all || user name only config directives
|}

As previously mentioned, this page describes basic usage of MariaDB. For professional usage, [[MySQL]] should also be referenced.

* The following command will configure the server to accept all incoming connections. This should only be done for development, or if the database is not exposed to the Internet or a sensitive network.

{{Cmd|<nowiki>sed -i "s|.*bind-address\s*=.*|bind-address=0.0.0.0|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=0.0.0.0|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>}}

* For simple installations, disabling hostname search can improve performance, but is only useful for local servers.

{{Cmd|<nowiki>sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>}}

== Updating or coming from upgrading ==

When upgrading between Alpine Linux releases, MariaDB may also have a major version change, and the databases should be upgraded to match. The recommended steps in this process are detailed below.

# While it may no longer be strictly necessary, it's useful to backup your databases before upgrading the database version.
# Update Alpine Linux and the MariaDB/MySQL packages.
# Install mariadb-server-utils by running <code>apk add {{pkg|mariadb-server-utils|arch=}}</code>.
# Run <code>mysql_upgrade -u root -p</code> script, and provide the password for the root database user.
# Restart the service by running <code>rc-service mariadb restart</code>.

If <code>mysql_upgrade</code> fails because MySQL cannot start, try running MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code>, and then run <code>mysql_upgrade -u root -p</code> again.

= Create a user =
You should want to create a user with will access to a database remotly

So open mariadb Client: <code>mariadb</code>

add the user with associated host and password: <code> CREATE OR REPLACE USER admin@'%' IDENTIFIED BY 'ASecurePassword';</code>

''NB: @'%' allow connection from anny host''

= See Also =

* [[MySQL]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

MariaDB

2025-03-11T23:08:14Z

Greenman: No basis for this claim

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

== Installation ==

The Alpine Linux repositories no longer include the actual MySQL binaries, installing the <code>mysql-*</code> packages will instead install MariaDB.

Installing <code>mariadb</code> will create the user <code>mysql</code>. When the database is initialized, two users will be added to the database: <code>root</code> and <code>mysql</code>. By default these users will only be accessible if you are logged in as the corresponding system user.

{{Cmd|apk add {{pkg|mariadb|arch=}} {{pkg|mariadb-client|arch=}}}}

Installing the above packages will add the main components of MariaDB to the system: <code>mariadb-cient</code> and <code>mariadb-server</code>. Other available packages are described in the table below, and are listed in order of relevance for a production server.

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| {{Pkg|mysql}} || v2 || a transitional package that installs mariadb || mariadb
|-
| {{Pkg|mysql-client}} || v2 || a transitional package that installs the mariadb client tools || mariadb-client
|-
| {{Pkg|mariadb}} || v2 || server equivalent to mysql-server || mariadb-common
|-
| {{Pkg|mariadb-client}} || v2 || connection command line and tools || mariadb-common
|-
| {{Pkg|mariadb-doc}} || v3.0 || manpages for mariadb || man man-pages
|-
| {{Pkg|mariadb-connector-odbc}} || edge || coding or making OS level connections, to any DB without libs install || .
|-
| {{Pkg|mariadb-connector-c}} || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| {{Pkg|mariadb-backup}} || v3.8 || tool for physical online backups, no longer widely used || .
|-
| {{Pkg|mariadb-server-utils}} || v3.8 || server commands not widely used, in past was inside MariaDB package || .
|-
| {{Pkg|mariadb-dev}} || v3.1 || development files for MariaDB || .
|-
| {{Pkg|mariadb-test}} || v3.3 || testing suite from MariaDB tools || .
|-
| {{Pkg|mariadb-mytop}} || v3.9 || data performance monitoring || .
|-
| {{Pkg|mariadb-plugin-rocksdb}} || v3.9 || plain key-value event relational for data || .
|-
| {{Pkg|mariadb-static}} || v3.8 || static libs for static non depends linking in builds || .
|-
| {{Pkg|mariadb-embedded}} || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| {{Pkg|mariadb-embedded-dev}} || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| {{Pkg|mariadb-openrc}} || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The version of MariaDB in the Alpine repositories behave like the MySQL tarball. No graphical tools are included.

The ''datadir'' located at {{Path|/var/lib/mysql}} must be owned by the mysql user and group. The location of the ''datadir'' can be changed by editing the <code>mariadb</code> service file in {{Path|/etc/init.d}}. The new location will also need to be set by adding <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> in the <code>[mysqld]</code> section in a mariadb configuration file.

Normal initialization of mariadb can be done as follows:

# Start the main service. At this point there will be no root password set. <code>rc-service mariadb start</code>
# Secure the database by running <code>mysql_secure_installation</code>
# Setup permissions for managing others users and databases see: '''[[#Configuration|Configuration]]'''
# Add MariaDb to OpenRC. <code>rc-update add mariadb default</code>

== Configuration ==

In order to help with the basic configuration of the database engine, MariaDB provides <code>mysql_secure_installation</code>.
This script walks you through the basics of securing the database. The options are explained below.

# '''Enter current password for root (enter for none):''' If you have previously set up a root password, provide it here and press enter. If not, just press enter.
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the Unix_socket ensures that only admins can log into engine database. For non-production servers just press "n" to setup a root password, which will give you the response <code>... skipping.</code>
# '''Change the root password? [Y/n]''' Here you can change the root password, or set one if needed. Press "Y" and enter the new password.
# '''Remove anonymous users? [Y/n]''' Remove anonymous users created to log in using socket authentication. Unless you're sure you need this, answer "Y" to remove them.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost' in order to protect from password sniffing attempts over the network. Answer "Y".
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. If this is not needed, answer "Y".
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Answer "Y".

After the script exits, restart the service with <code>rc-service mariadb restart</code>

To start the database daemon on every boot, run <code>rc-update add mariadb default</code>

=== Configuration files and customization ===

Rather than being stored in {{Path|my.cnf}}, configuration settings for MariaDB are now organized in separate files. The primary configuration is done by adding files to {{Path|/etc/my.cnf.d/}}. User-specific configuration files are stored in {{Path|~/.my.cnf}}. User-specific configuration files are loaded after the system-wide configuration. The locations of the various configuration files are listed below.

{| class="wikitable"
|-
! Config file !! Versions of Alpine !! Contents to configure
|-
| {{Path|/etc/mysql/my.cnf}} || v2 to v3.8 || All the directives, global config file
|-
| {{Path|/etc/my.cnf.d/mariadb-server.cnf}} || since 3.9 || First global config file, main directives
|-
| {{path|$HOME/.my.cnf}} || all || user name only config directives
|}

As previously mentioned, this page describes basic usage of MariaDB. For professional usage, [[MySQL]] should also be referenced.

* The following command will configure the server to accept all incoming connections. This should only be done for development, or if the database is not exposed to the Internet or a sensitive network.

{{Cmd|<nowiki>sed -i "s|.*bind-address\s*=.*|bind-address=0.0.0.0|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=0.0.0.0|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>}}

* For simple installations, disabling hostname search can improve performance, but is only useful for local servers.

{{Cmd|<nowiki>sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>}}

== Updating or coming from upgrading ==

When upgrading between Alpine Linux releases, MariaDB may also have a major version change, and the databases should be upgraded to match. The recommended steps in this process are detailed below.

# While it may no longer be strictly necessary, it's useful to backup your databases before upgrading the database version.
# Update Alpine Linux and the MariaDB/MySQL packages.
# Install mariadb-server-utils by running <code>apk add {{pkg|mariadb-server-utils|arch=}}</code>.
# Run <code>mysql_upgrade -u root -p</code> script, and provide the password for the root database user.
# Restart the service by running <code>rc-service mariadb restart</code>.

If <code>mysql_upgrade</code> fails because MySQL cannot start, try running MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code>, and then run <code>mysql_upgrade -u root -p</code> again.

= Create a user =
You should want to create a user with will access to a database remotly

So open mariadb Client: <code>mariadb</code>

add the user with associated host and password: <code> CREATE OR REPLACE USER admin@'%' IDENTIFIED BY 'ASecurePassword';</code>

''NB: @'%' allow connection from anny host''

= See Also =

* [[MySQL]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

MySQL

2025-03-11T23:04:41Z

Greenman: /* Comparison table */

MySQL is the most popular relational database server in free software for two simple reasons (which are not the best technical reasons):
# It's simple and very easy to use
# It is very similar to SQLSERVER and is also used in other systems

'''In the world of Linux Alpine there is a software that provides it''', it is [[MariaDB]], here we have a brief of info about compatibility and differences, but in short there's no great differences, if you have doubts check [[MySQL#MariaDB vs MySQL|MariaDB vs MySQL section here]]

'''In the wiki there are two approaches for its use''', the professional one (for servers and deploys) and the [[MariaDB|fast and simple]] usage (for developers and/or enthusiasts):

= MariaDB - fast and simple use =

{{:MariaDB}}

= MySQL (MariaDB) - Production usage =

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

This page assumed that you have a general knowledge about MariaDB, so if you are new to MySQL first take a look at the [[MariaDB|MariaDB wiki page]] for information about how are packaged for alpine linux.

== Installation ==

Alpine Linux has dummy counterparts packages for those that are not close to that change from ''mysql'' to ''mariadb'' naming packages, please check the [[MariaDB|MariaDB wiki page]] for more information.

Take in consideration that the user <code>mysql</code> was created during instalation of packages, in the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and in that point only if are in their respective system accounts, will be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

That will install the most used ones.. <code>mariadb-cient</code> and <code>mariadb-server</code>, rest of packages are brief described here for more information, here are listed in orden of relevance for production server

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| mysql || v2 || it's a dummy package to easy install of mariadb || mariadb
|-
| mysql-client || v2 || it's a dummy package to easy install of commands tools || mariadb-client
|-
| mariadb || v2 || server equivalent to mysql-server || mariadb-common
|-
| mariadb-client || v2 || connection command line and tools || mariadb-common
|-
| mariadb-doc || v3.0 || manpages are there! || man man-pages
|-
| mariadb-connector-odbc || edge || coding or making OS level connections, to any DB without libs install || .
|-
| mariadb-connector-c || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| mariadb-backup || v3.8 || to external backup devices, not widely used, in past was inside mariadb package || .
|-
| mariadb-server-utils || v3.8 || server commands not widely used, in past was inside mariadb package || .
|-
| mariadb-dev || v3.1 || Need for compilations depends on source code || .
|-
| mariadb-test || v3.3 || testing suite from MariaDB tools || .
|-
| mariadb-mytop || v3.9 || data performance monitoring || .
|-
| mariadb-plugin-rocksdb || v3.9 || plain key-value event relational for data || .
|-
| mariadb-static || v3.8 || static libs for static non depends linking in builds || .
|-
| mariadb-embedded || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| mariadb-embedded-dev || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| mariadb-openrc || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must be know what they want.. there's no automatic window-like here.

The ''datadir'' are located to <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Setup the root account by asignes a proper password, this are purely paranoid. due next step already do that!
# Setup and init the installation by running the <code>mysql_secure_installation</code>
# Setup permissions for manage others users and databases

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor
</nowiki>
</pre>

After that, all are initializated to proceed with configuration, now can be done using the <code>
mysql_secure_installation</code> script at the next section:

== Configuration ==

In order to finish setup into '''MariaDB''' now provide '''this script called <code>mysql_secure_instalation</code> that also are present as <code>mariadb-secure-installation</code>''', too. This script provides minimal and security setup to the database, and here are the questions made explained:

# '''Enter current password for root (enter for none):''' this are if you previously setup as we done in previous section a root password, just provide it and press enter, '''must be provided due we already set previously''' and from now, this sript will access to the engine and alter many setting on the database. Correct respond are <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the unix_socket ensures that only admins can log into engine database. Since mysql 5.6 and mariadb 10.2 a new auth mechanish are set, by socket authentiaction, when system user are same as mysql/mariadb user, in this case, no password are need. In production servers this are not the case and must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' this answer are here only if the first one are just enter, or if can provide a better passowrd if no unix socket are set. Just press "n" only if you provided a good password, otherwise just
# '''Remove anonymous users? [Y/n]''' this permits remove the anonymous user created to log using socket authentication, only working on unix-like system. In any case, '''production system must remove it, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. For sure answer Y''' and proper respond must be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately, so answer Y''' and proper respond must be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration files ===

Due today were influenced by systemd standardization, the famous <nowiki>my.cnf</nowiki> are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the <code>/etc/my.cnf.d/</code> directory, user own config files are under <code>~/.my.cnf</code> config file of each home dir, and are read after global ones; so then we have:

{| class="wikitable"
|-
! Config file !! Path and name !! Versions of Alpine !! Contents to configure
|-
| my.cnf || <nowiki>/etc/mysql/my.cnf</nowiki> || v2 to v3.8 || All the directives, Global config file
|-
| mariadb-server.cnf || /etc/my.cnf.d/mariadb-server.cnf || since 3.9 || First Global config file, main directives
|-
| .my.cnf || <nowiki>$HOME</nowiki> || all || user name only config directives
|}

=== Production settings ===

These setting are only recommended for some server settings, there are the recommendation for high production settings:

{| class="wikitable"
|-
! Config setting !! Default !! Recommended !! Explanation
|-
| main ram || 2G || 8G - 16G || MariaDB/MySQL can run with 512M or 1G of ram, high production must use minimun of 4G or more.
|-
| data dir disk type || any || SSD || MariaDB/MySQL must run with faster SSD if high request will be produced.
|-
| collation_server || utf8_unicode_ci || utf8mb4_unicode_ci || With 8mb4 some characters are able to use more than a single byte.
|-
| character_set_client || iso8859-1 || utf8 or utf8mb4 || Important due are standard, for left to right use 8mb4 flavor
|-
| max_connections || 151 || 100 || total_connections = total_processes * (total_threads + script_servers + 1)
|-
| max_heap_table_size || 16M || 32M || allocate more memory to memory tables, for memory storage engines
|-
| tmp_table_size || 16M || 32M || It allows the sub queries to remain more in memory, making them faster
|-
| join_buffer_size || 32M || 64M || It allows the join queries to remain more in memory rather in temp files
|-
| innodb_file_format || unset || Barracuda || will allow longer indexes for important most used tables
|-
| innodb_large_prefix || unset || 1 || Must set if Barracuda file format are choosen
|-
| innodb_buffer_pool_size || 128M || 456M || hold as much tables and indexes in system memory as is possible
|-
| innodb_read_io_threads || 16 || 32 || On high I/O systems, a value greater than 1 may allow the disk I/O to be more sequential
|-
| innodb_write_io_threads || 16 || 32 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_buffer_pool_instances || 1 || 2 or 4 || Only for older MySQL/MariaDB engines,
|-
| innodb_io_capacity || 200 || 1200 - 2600 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_io_capacity_max || 200 || 2400 - 5200 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|}

If you have SSD disks, use the recommended suggestion, otherwise, use minimum suggested. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used, but those lasted storage devices will be a short life of course.

Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:

* On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Only allow local connections on cases where there's only one server or no expected to connect from others:

<pre>
<nowiki>
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):

<pre>
<nowiki>
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:

<pre>
<nowiki>
cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF
</nowiki>
</pre>

== Updating or comming from upgrading ==

Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB engine need some extra steps when this are performed:

Upgrade databases on major releases
Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

# keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
# upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
# run the <code>mysql_upgrade -u root -p</code> script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
# Restart the service

If are unable to run ''mysql_upgrade'' because MySQL cannot start try run MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code> command and then run the <code>mysql_upgrade -u root -p</code> script.

= Relevant important notes =

== File system notes about the databases managed ==

'''Btrfs Notes'''

If the database (in <code>/var/lib/mysql</code>) resides on a [[Btrfs|btrfs]] file system, you should consider disabling '''Copy-on-Write''' for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.

'''ZFS Bock sizes'''

ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.

It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as <code>zfs set recordsize=8K /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job with <code>zfs set primarycache=metadata /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).

But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.

== Restore root password ==

<pre>
<nowiki>
rc-service mysql stop

kill `cat /run/mysqld/mysqld.pid`

/usr/bin/mysqld --datadir=/var/lib/mysql --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking &

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;set password = password('MyNewPass');"

kill `cat /run/mysqld/mysqld.pid`

rc-service mariadb restart
</nowiki>
</pre>

= See Also =

* [[MariaDB|MariaDB wiki page]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

= MariaDB vs MySQL =

It is more a matter of compatibility than of performance and characteristics (with the arrival of MySQL v8) .. and it depends on whether there is a purely business and support approach "zero concern".

MySQL, being from Oracle, establishes limits if a license is not purchased, MariaDB has a large connection pool, more than 200,000 connections, while MySQL has a smaller connection pool if it is not licensed.

However, MariaDB does not support data masking and dynamic column while MySQL supports it, also MariaDB although it has 12 new storage engines while MySQL has less these are very new and MySQL's are widely known and tested.

In terms of performance, MariaDB is only a little faster than MySQL, this is because MySQL implements more business features, but this is only noticeable using these many features.

Which is more optimal this is not clear .. in general MySQL should be less, and MariaDB faster, there is a third option which is Percona which is the same MySQL service but with special aggressive optimization patches for servers. Percona mysql code must be compiled in Alpine linux.

== Comparison table ==

{| class="wikitable"
|-
! Characteristic !! MariaDB !! MySQL
|-
| Storage Engines || up to 12 but many in development stage || less but well tested
|-
| Performance || just a little faster || less, there is almost no difference
|-
| Initial version || 2009 (5.3) || 1995 (3.0)
|-
| Data masking || no || yes
|-
| Dynamic columns || [https://mariadb.com/kb/en/dynamic-columns/ yes] || yes
|-
| Monitoring || SQLyog || MySQLworkbench
|-
| Routing || MariaDB MaxScale || Mysql Router
|-
| Analytics || MariaDB ColumnStore || not have
|-
| Git starred times (github)|| [https://github.com/MariaDB/server around 5.9k] || [https://github.com/mysql/mysql-server around 11.2k]
|}

For more info, see:
* https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility
* http://qgqlochekone.blogspot.com/2020/04/mariadb-mysql-and-mysqlworkbench.html

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]

MySQL

2025-03-11T23:02:42Z

Greenman: /* Comparison table */ MariaDB has had since 5.3, in the previous decade :)

MySQL is the most popular relational database server in free software for two simple reasons (which are not the best technical reasons):
# It's simple and very easy to use
# It is very similar to SQLSERVER and is also used in other systems

'''In the world of Linux Alpine there is a software that provides it''', it is [[MariaDB]], here we have a brief of info about compatibility and differences, but in short there's no great differences, if you have doubts check [[MySQL#MariaDB vs MySQL|MariaDB vs MySQL section here]]

'''In the wiki there are two approaches for its use''', the professional one (for servers and deploys) and the [[MariaDB|fast and simple]] usage (for developers and/or enthusiasts):

= MariaDB - fast and simple use =

{{:MariaDB}}

= MySQL (MariaDB) - Production usage =

[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

This page assumed that you have a general knowledge about MariaDB, so if you are new to MySQL first take a look at the [[MariaDB|MariaDB wiki page]] for information about how are packaged for alpine linux.

== Installation ==

Alpine Linux has dummy counterparts packages for those that are not close to that change from ''mysql'' to ''mariadb'' naming packages, please check the [[MariaDB|MariaDB wiki page]] for more information.

Take in consideration that the user <code>mysql</code> was created during instalation of packages, in the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and in that point only if are in their respective system accounts, will be able to connect to the database service.

<pre><nowiki>
apk add mysql mysql-client
</nowiki></pre>

That will install the most used ones.. <code>mariadb-cient</code> and <code>mariadb-server</code>, rest of packages are brief described here for more information, here are listed in orden of relevance for production server

{| class="wikitable"
|-
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
|-
| mysql || v2 || it's a dummy package to easy install of mariadb || mariadb
|-
| mysql-client || v2 || it's a dummy package to easy install of commands tools || mariadb-client
|-
| mariadb || v2 || server equivalent to mysql-server || mariadb-common
|-
| mariadb-client || v2 || connection command line and tools || mariadb-common
|-
| mariadb-doc || v3.0 || manpages are there! || man man-pages
|-
| mariadb-connector-odbc || edge || coding or making OS level connections, to any DB without libs install || .
|-
| mariadb-connector-c || v3.8 || coding connection on C sources || mariadb-connector-c-dev
|-
| mariadb-backup || v3.8 || to external backup devices, not widely used, in past was inside mariadb package || .
|-
| mariadb-server-utils || v3.8 || server commands not widely used, in past was inside mariadb package || .
|-
| mariadb-dev || v3.1 || Need for compilations depends on source code || .
|-
| mariadb-test || v3.3 || testing suite from MariaDB tools || .
|-
| mariadb-mytop || v3.9 || data performance monitoring || .
|-
| mariadb-plugin-rocksdb || v3.9 || plain key-value event relational for data || .
|-
| mariadb-static || v3.8 || static libs for static non depends linking in builds || .
|-
| mariadb-embedded || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
|-
| mariadb-embedded-dev || v3.9 || use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
|-
| mariadb-openrc || v3.8 || separate scripts, in past was embebed on server package || .
|}

== Initialization ==

The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must be know what they want.. there's no automatic window-like here.

The ''datadir'' are located to <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.

# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script
# Then initialize the service, root account and socket connection are enabled without password at this point
# Setup the root account by asignes a proper password, this are purely paranoid. due next step already do that!
# Setup and init the installation by running the <code>mysql_secure_installation</code>
# Setup permissions for manage others users and databases

<pre>
<nowiki>
mysql_install_db --user=mysql --datadir=/var/lib/mysql

rc-service mariadb start

mysqladmin -u root password toor
</nowiki>
</pre>

After that, all are initializated to proceed with configuration, now can be done using the <code>
mysql_secure_installation</code> script at the next section:

== Configuration ==

In order to finish setup into '''MariaDB''' now provide '''this script called <code>mysql_secure_instalation</code> that also are present as <code>mariadb-secure-installation</code>''', too. This script provides minimal and security setup to the database, and here are the questions made explained:

# '''Enter current password for root (enter for none):''' this are if you previously setup as we done in previous section a root password, just provide it and press enter, '''must be provided due we already set previously''' and from now, this sript will access to the engine and alter many setting on the database. Correct respond are <code>OK, successfully used password, moving on...</code>
# '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the unix_socket ensures that only admins can log into engine database. Since mysql 5.6 and mariadb 10.2 a new auth mechanish are set, by socket authentiaction, when system user are same as mysql/mariadb user, in this case, no password are need. In production servers this are not the case and must be disabled, '''so answer NO''', and response will be <code>... skipping.</code>
# '''Change the root password? [Y/n]''' this answer are here only if the first one are just enter, or if can provide a better passowrd if no unix socket are set. Just press "n" only if you provided a good password, otherwise just
# '''Remove anonymous users? [Y/n]''' this permits remove the anonymous user created to log using socket authentication, only working on unix-like system. In any case, '''production system must remove it, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. For sure answer Y''' and proper respond must be <code>... Success!</code>.
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed, so answer Y''' and proper respond must be <code>... Success!</code>.
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately, so answer Y''' and proper respond must be <code>... Success!</code>.

After reponse all the questions.. restart the service with <code>rc-service mariadb restart</code>

=== Configuration files ===

Due today were influenced by systemd standardization, the famous <nowiki>my.cnf</nowiki> are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the <code>/etc/my.cnf.d/</code> directory, user own config files are under <code>~/.my.cnf</code> config file of each home dir, and are read after global ones; so then we have:

{| class="wikitable"
|-
! Config file !! Path and name !! Versions of Alpine !! Contents to configure
|-
| my.cnf || <nowiki>/etc/mysql/my.cnf</nowiki> || v2 to v3.8 || All the directives, Global config file
|-
| mariadb-server.cnf || /etc/my.cnf.d/mariadb-server.cnf || since 3.9 || First Global config file, main directives
|-
| .my.cnf || <nowiki>$HOME</nowiki> || all || user name only config directives
|}

=== Production settings ===

These setting are only recommended for some server settings, there are the recommendation for high production settings:

{| class="wikitable"
|-
! Config setting !! Default !! Recommended !! Explanation
|-
| main ram || 2G || 8G - 16G || MariaDB/MySQL can run with 512M or 1G of ram, high production must use minimun of 4G or more.
|-
| data dir disk type || any || SSD || MariaDB/MySQL must run with faster SSD if high request will be produced.
|-
| collation_server || utf8_unicode_ci || utf8mb4_unicode_ci || With 8mb4 some characters are able to use more than a single byte.
|-
| character_set_client || iso8859-1 || utf8 or utf8mb4 || Important due are standard, for left to right use 8mb4 flavor
|-
| max_connections || 151 || 100 || total_connections = total_processes * (total_threads + script_servers + 1)
|-
| max_heap_table_size || 16M || 32M || allocate more memory to memory tables, for memory storage engines
|-
| tmp_table_size || 16M || 32M || It allows the sub queries to remain more in memory, making them faster
|-
| join_buffer_size || 32M || 64M || It allows the join queries to remain more in memory rather in temp files
|-
| innodb_file_format || unset || Barracuda || will allow longer indexes for important most used tables
|-
| innodb_large_prefix || unset || 1 || Must set if Barracuda file format are choosen
|-
| innodb_buffer_pool_size || 128M || 456M || hold as much tables and indexes in system memory as is possible
|-
| innodb_read_io_threads || 16 || 32 || On high I/O systems, a value greater than 1 may allow the disk I/O to be more sequential
|-
| innodb_write_io_threads || 16 || 32 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_buffer_pool_instances || 1 || 2 or 4 || Only for older MySQL/MariaDB engines,
|-
| innodb_io_capacity || 200 || 1200 - 2600 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|-
| innodb_io_capacity_max || 200 || 2400 - 5200 || Only if you have SSD storage for the data MySQL/MariaDB database and temp files
|}

If you have SSD disks, use the recommended suggestion, otherwise, use minimum suggested. If you have physical hard drives, use 2000 * the number of active drives in the array. If using NVMe or PCIe Flash, much larger numbers as high as 200000 can be used, but those lasted storage devices will be a short life of course.

Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:

* On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:

<pre>
<nowiki>
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Only allow local connections on cases where there's only one server or no expected to connect from others:

<pre>
<nowiki>
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):

<pre>
<nowiki>
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
</nowiki>
</pre>

* Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:

<pre>
<nowiki>
cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF
</nowiki>
</pre>

== Updating or comming from upgrading ==

Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB engine need some extra steps when this are performed:

Upgrade databases on major releases
Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

# keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
# upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
# run the <code>mysql_upgrade -u root -p</code> script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
# Restart the service

If are unable to run ''mysql_upgrade'' because MySQL cannot start try run MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code> command and then run the <code>mysql_upgrade -u root -p</code> script.

= Relevant important notes =

== File system notes about the databases managed ==

'''Btrfs Notes'''

If the database (in <code>/var/lib/mysql</code>) resides on a [[Btrfs|btrfs]] file system, you should consider disabling '''Copy-on-Write''' for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.

'''ZFS Bock sizes'''

ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.

It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as <code>zfs set recordsize=8K /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job with <code>zfs set primarycache=metadata /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).

But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.

== Restore root password ==

<pre>
<nowiki>
rc-service mysql stop

kill `cat /run/mysqld/mysqld.pid`

/usr/bin/mysqld --datadir=/var/lib/mysql --pid-file=/run/mysqld/mysqld.pid --skip-grant-tables --skip-networking &

mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';FLUSH PRIVILEGES;set password = password('MyNewPass');"

kill `cat /run/mysqld/mysqld.pid`

rc-service mariadb restart
</nowiki>
</pre>

= See Also =

* [[MariaDB|MariaDB wiki page]]
* [[Production LAMP system: Lighttpd + PHP + MySQL]]

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]
[[Category:Security]]
[[Category:Production]]

= MariaDB vs MySQL =

It is more a matter of compatibility than of performance and characteristics (with the arrival of MySQL v8) .. and it depends on whether there is a purely business and support approach "zero concern".

MySQL, being from Oracle, establishes limits if a license is not purchased, MariaDB has a large connection pool, more than 200,000 connections, while MySQL has a smaller connection pool if it is not licensed.

However, MariaDB does not support data masking and dynamic column while MySQL supports it, also MariaDB although it has 12 new storage engines while MySQL has less these are very new and MySQL's are widely known and tested.

In terms of performance, MariaDB is only a little faster than MySQL, this is because MySQL implements more business features, but this is only noticeable using these many features.

Which is more optimal this is not clear .. in general MySQL should be less, and MariaDB faster, there is a third option which is Percona which is the same MySQL service but with special aggressive optimization patches for servers. Percona mysql code must be compiled in Alpine linux.

== Comparison table ==

{| class="wikitable"
|-
! Characteristic !! MariaDB !! MySQL
|-
| Storage Engines || up to 12 but many in development stage || less but well tested
|-
| Performance || just a little faster || less, there is almost no difference
|-
| Initial version || 2009 (5.3) || 1995 (3.0)
|-
| Data masking || no || yes
|-
| Dynamic columns || [https://mariadb.com/kb/en/dynamic-columns/ yes] || yes
|-
| Monitoring || SQLyog || MySQLworkbench
|-
| Routing || MariaDB MaxScale || Mysql Router
|-
| Analytics || MariaDB ColumnStore || not have
|-
| Git starred times (github)|| [https://github.com/MariaDB/server around 5.9k] || [https://github.com/mysql/mysql-server around 11.2k]
|}

For more info check a review here: http://qgqlochekone.blogspot.com/2020/04/mariadb-mysql-and-mysqlworkbench.html

[[Category:Newbie]]
[[Category:Server]]
[[Category:Database]]
[[Category:Development]]