Category: MySQL DB Server

MariaDB – Multithreaded SQL Database Server/Client

MariaDB – Multithreaded SQL Database Server/Client

Description:

MariaDB is a database server that offers drop-in replacement functionality for MySQL. MariaDB is built by some of the original authors of MySQL, with assistance from the broader community of Free and open-source software developers. In addition to the core functionality of MySQL, MariaDB offers a rich set of feature enhancements, including alternate storage engines, server optimizations, and patches.

Developers primarily drive MariaDB at Monty Program, a company founded by Michael “Monty” Widenius, the original author of MySQL, but this is not the whole story about MariaDB. On the “About MariaDB” page, you will find more information about all participants in the MariaDB community, including storage engines XtraDB and PBXT.

WWW: https://mariadb.org/.

Creat ZFS datasets for MariaDB databas storage

One area that definitely needs Specialized Tuning is when you create a ZFS mount specifically for a database.

N.B.: This should be done on a new MariaDB install. It is assumed mySQL has never run on this machine, so the data directories are not populated (on FreeBSD, the first run of mySQL creates the data files. If you have run it, back up any data, then:

### WARNING!, WARNING!, WARNING ###
This will delete any existing databases!

user@freebsdsrv:~ $ sudo rm -fR /var/db/mysql/* [enter]
user@freebsdsrv:~ $

Create ZFS datasets for MariaDB with:

user@freebsdsrv:~ $ sudo zfs create zroot/db ; sudo zfs create zroot/db/logs ; sudo zfs create zroot/db/innodb ; sudo zfs create zroot/db/myisam [enter]
user@freebsdsrv:~ $

Set global ZFS paraemters with:

user@freebsdsrv:~ $ sudo zfs set zfs:zfs_nocacheflush=1 zroot/db [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set setuid=off zroot/db [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set primarycache=metadata zroot/db [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set atime=off zroot/db [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set sync=disabled zroot/db [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set compression=off zroot/db [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set logbias=throughput zroot/db [enter]
user@freebsdsrv:~ $

Set individual parameters (blocksizes) with:

user@freebsdsrv:~ $ sudo zfs set recordsize=16k zroot/db/innodb [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set recordsize=128k zroot/db/logs [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set recordsize=8k zroot/db/myisam [enter]
user@freebsdsrv:~ $

Create directories, set the mount points and then set ownership with:

user@freebsdsrv:~ $ sudo mkdir /var/log/mysql [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set mountpoint=/var/log/mysql zroot/db/logs [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo mkdir /var/db/mysql [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set mountpoint=/var/db/mysql zroot/db/myisam [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo mkdir /var/db/mysql-innodb [enter]
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo zfs set mountpoint=/var/db/mysql-innodb zroot/db/innodb [enter]
user@freebsdsrv:~ $

To start the MariaDB Server on system boot:

user@freebsdsrv:~ $ sudo sysrc mysql_enable="YES" [enter]
mysql_enable:  -> YES
user@freebsdsrv:~ $

Installation

Search for “mariadb” in the remote package repositories with:

user@freebsdsrv:~ $ pkg search mariadb1 [enter]
mariadb1011-client-10.11.10_1  Multithreaded SQL database (client)
mariadb1011-server-10.11.10_1  Multithreaded SQL database (server)
mariadb105-client-10.5.27      Multithreaded SQL database (client)
mariadb105-server-10.5.27      Multithreaded SQL database (server)
mariadb106-client-10.6.20      Multithreaded SQL database (client)
mariadb106-server-10.6.20      Multithreaded SQL database (server)
mariadb114-client-11.4.4       Multithreaded SQL database (client)
mariadb114-server-11.4.4       Multithreaded SQL database (server)
user@freebsdsrv:~ $

In this example, mariadb114-server and mariadb114-client will be installed with;

user@freebsdsrv:~ $ sudo pkg install -y mariadb114-server mariadb114-client [enter]
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 10 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	bash: 5.2.37
	boost-libs: 1.85.0
	galera26: 26.4.16_3
	icu: 74.2_1,1
	libfmt: 10.2.1
	mariadb114-client: 11.4.3
	mariadb114-server: 11.4.3
	rsync: 3.3.0
	unixODBC: 2.3.12_1
	xxhash: 0.8.2_1

Number of packages to be installed: 10

The process will require 591 MiB more space.
82 MiB to be downloaded.
[1/10] Fetching galera26-26.4.16_3.pkg: 100%  908 KiB 929.6kB/s    00:01    
[2/10] Fetching boost-libs-1.85.0.pkg: 100%   15 MiB  15.2MB/s    00:01    
[3/10] Fetching unixODBC-2.3.12_1.pkg: 100%  547 KiB 559.9kB/s    00:01    
[4/10] Fetching mariadb114-server-11.4.3.pkg: 100%   49 MiB  51.3MB/s    00:01    
[5/10] Fetching icu-74.2_1,1.pkg: 100%   13 MiB  13.2MB/s    00:01    
[6/10] Fetching xxhash-0.8.2_1.pkg: 100%   92 KiB  93.9kB/s    00:01    
[7/10] Fetching bash-5.2.37.pkg: 100%    2 MiB   1.8MB/s    00:01    
[8/10] Fetching mariadb114-client-11.4.3.pkg: 100%    3 MiB   2.9MB/s    00:01    
[9/10] Fetching rsync-3.3.0.pkg: 100%  396 KiB 405.7kB/s    00:01    
[10/10] Fetching libfmt-10.2.1.pkg: 100%  178 KiB 182.2kB/s    00:01    
Checking integrity... done (0 conflicting)
[1/10] Installing icu-74.2_1,1...
[1/10] Extracting icu-74.2_1,1: 100%
[2/10] Installing boost-libs-1.85.0...
[2/10] Extracting boost-libs-1.85.0: 100%
[3/10] Installing xxhash-0.8.2_1...
[3/10] Extracting xxhash-0.8.2_1: 100%
[4/10] Installing libfmt-10.2.1...
[4/10] Extracting libfmt-10.2.1: 100%
[5/10] Installing galera26-26.4.16_3...
[5/10] Extracting galera26-26.4.16_3: 100%
[6/10] Installing unixODBC-2.3.12_1...
[6/10] Extracting unixODBC-2.3.12_1: 100%
[7/10] Installing bash-5.2.37...
[7/10] Extracting bash-5.2.37: 100%
[8/10] Installing mariadb114-client-11.4.3...
===> Creating groups
Creating group 'mysql' with gid '88'
===> Creating users
Creating user 'mysql' with uid '88'
===> Creating homedir(s)
[8/10] Extracting mariadb114-client-11.4.3: 100%
[9/10] Installing rsync-3.3.0...
[9/10] Extracting rsync-3.3.0: 100%
[10/10] Installing mariadb114-server-11.4.3...
===> Creating groups
Using existing group 'mysql'
===> Creating users
Using existing user 'mysql'
===> Creating homedir(s)
[10/10] Extracting mariadb114-server-11.4.3: 100%
=====
Message from boost-libs-1.85.0:

--
You have built the Boost library with thread support.

Don't forget to add -pthread to your linker options when
linking your code.
=====
Message from galera26-26.4.16_3:

--
Find the Galera Cluster documentation at
https://galeracluster.com/library/documentation/index.html
=====
Message from mariadb114-client-11.4.3:

--
MariaDB respects hier(7) and doesn't check /etc and /etc/mysql for
my.cnf. Please move existing my.cnf files from those paths to
/usr/local/etc/mysql or /usr/local/etc. Sample
configuration files are provided in /usr/local/etc/mysql
and /usr/local/etc/mysql/conf.d.
The rc(8) script no longer uses /var/db/mysql/my.cnf for configuration
nor /var/db/mysql for logs and PID-file.

This port does NOT include the mytop perl script, this is included in
the MariaDB tarball but the most recent version can be found in the
databases/mytop port

%%WSREP%%The Using wsrep clustering requires additional configuration.
%%WSREP%%Edit /usr/local/etc/mysql/conf.d/wsrep.conf and change what you need there.

The hashicorp_key_management, s3 and spider plugins must be enabled
before they can be used, check the specific .cnf files in /usr/local/etc/mysql/conf.d/
=====
Message from rsync-3.3.0:

--
Some scripts provided by rsync, such as rrsync,
require Python, which is not installed by default.
=====
Message from mariadb114-server-11.4.3:

--
MariaDB respects hier(7) and doesn't check /etc and /etc/mysql for
my.cnf. Please move existing my.cnf files from those paths to
/usr/local/etc/mysql or /usr/local/etc. Sample
configuration files are provided in /usr/local/etc/mysql
and /usr/local/etc/mysql/conf.d.
The rc(8) script no longer uses /var/db/mysql/my.cnf for configuration
nor /var/db/mysql for logs and PID-file.

This port does NOT include the mytop perl script, this is included in
the MariaDB tarball but the most recent version can be found in the
databases/mytop port

The Using wsrep clustering requires additional configuration.
Edit /usr/local/etc/mysql/conf.d/wsrep.conf and change what you need there.

The hashicorp_key_management, s3 and spider plugins must be enabled
before they can be used, check the specific .cnf files in /usr/local/etc/mysql/conf.d/
user@freebsdsrv:~ $

Change owner on MaraDB directories with:

user@freebsdsrv:~ $ sudo chown mysql:mysql /var/log/mysql ; sudo chown mysql:mysql /var/db/mysql ; sudo chown mysql:mysql /var/db/mysql-innodb [enter]
user@freebsdsrv:~ $

Initialize the MariaDB data directory and sart the MySQL server with:

user@freebsdsrv:~ $ sudo service mysql-server start [enter]
Installing MariaDB/MySQL system tables in './data' ...
OK

To start mariadbd at boot time you have to copy
support-files/mariadb.service to the right place for your system


Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb

You can start the MariaDB daemon with:
cd '/usr/local' ; /usr/local/bin/mariadbd-safe --datadir='./data'

You can test the MariaDB daemon with mariadb-test-run.pl
cd '/usr/local/' ; perl mariadb-test-run.pl

Please report any problems at https://mariadb.org/jiraThe latest information about MariaDB is available at https://mariadb.org/.

Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/
user@freebsdsrv:~ $

Verify status with:

user@freebsdsrv:~ $ sudo service mysql-server status [enter]
mysql is running as pid 14018.
user@freebsdsrv:~ $

Set MariaDB server root password with:

user@freebsdsrv:~ $ sudo /usr/local/bin/mariadb-admin -u root password 'DBrootPassWd' [enter]
user@freebsdsrv:~ $

Confirm that the MariaDB service is up and running by checking its status with:

user@freebsdsrv:~ $ sudo service mysql-server status [enter]
mysql is running as pid 6971.
user@freebsdsrv:~ $

Display file /var/log/mysql/mysqld.err with:

user@freebsdsrv:~ $ sudo cat /var/log/mysql/mysqld.err [enter]
2025-02-03 22:42:02 0 [Warning] 'wsrep-causal-reads' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2025-02-03 22:42:05 0 [Note] Starting MariaDB 11.4.4-MariaDB source revision e9a502df08bad16aa8a354e854f3c014b1380e32 server_uid DByzGKgo5W7evv3aCV6HD1tucBU= as process 14018
2025-02-03 22:42:05 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2025-02-03 22:42:05 0 [Note] InnoDB: Number of transaction pools: 1
2025-02-03 22:42:05 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2025-02-03 22:42:05 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2025-02-03 22:42:05 0 [Note] InnoDB: Completed initialization of buffer pool
2025-02-03 22:42:06 0 [Note] InnoDB: End of log at LSN=47907
2025-02-03 22:42:06 0 [Note] InnoDB: Opened 3 undo tablespaces
2025-02-03 22:42:06 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
2025-02-03 22:42:06 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2025-02-03 22:42:06 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2025-02-03 22:42:06 0 [Note] InnoDB: log sequence number 47907; transaction id 16
2025-02-03 22:42:06 0 [Note] InnoDB: Loading buffer pool(s) from /var/db/mysql/ib_buffer_pool
2025-02-03 22:42:06 0 [Note] Plugin 'FEEDBACK' is disabled.
2025-02-03 22:42:06 0 [Note] Plugin 'wsrep-provider' is disabled.
2025-02-03 22:42:06 0 [Warning] 'wsrep-causal-reads' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2025-02-03 22:42:06 0 [Warning] 'wsrep-causal-reads' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2025-02-03 22:42:06 0 [Note] InnoDB: Buffer pool(s) load completed at 250203 22:42:06
2025-02-03 22:42:09 0 [Note] Server socket created on IP: '0.0.0.0'.
2025-02-03 22:42:09 0 [Note] mariadbd: Event Scheduler: Loaded 0 events
2025-02-03 22:42:09 0 [Note] /usr/local/libexec/mariadbd: ready for connections.
Version: '11.4.4-MariaDB'  socket: '/var/run/mysql/mysql.sock'  port: 3306  FreeBSD Ports

Harden the MariaDB database server with:

user@freebsdsrv:~ $ sudo mariadb-secure-installation [enter]

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): <--DBrootPassWd [enter]
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] [enter]
Enabled successfully!
Reloading privilege tables..
 ... Success!


You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] n [enter]
 ... skipping.

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] [enter]
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] [enter]
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] [enter]
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] [enter]
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
user@freebsdsrv:~ $

Access MariaDB using the root user:

user@freebsdsrv:~ $ sudo mysql -u root -p [enter]
Enter password: <-- DBrootPassWd [enter]
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 11.4.4-MariaDB FreeBSD Ports

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]>

Exit MariaDB root with:

root@localhost [(none)]> exit [enter]
Bye
user@freebsdsrv:~ $

Lists open Internet or UNIX domain sockets MariaDB service binds to with:

user@freebsdsrv:~ $ sockstat | grep 3306 [enter]
mysql    mariadbd   43037 24  tcp4   *:3306                *:*
user@freebsdsrv:~ $

To prevent remote access to MariaDB, we must make sure that the daemon of MariaDB only listens to the localhost with:

user@freebsdsrv:~ $ sudo sysrc mysql_args="--bind-address=127.0.0.1" [enter]
mysql_args:  -> --bind-address=127.0.0.1
user@freebsdsrv:~ $
user@freebsdsrv:~ $ sudo service mysql-server restart [enter]
Stopping mysql.
Waiting for PIDS: 51757.
Starting mysql.
user@freebsdsrv:~ $

Lists open Internet or UNIX domain sockets MariaDB service binds to with:

user@freebsdsrv:~ $ sockstat | grep 3306 [enter]
mysql    mariadbd   50299 24  tcp4   127.0.0.1:3306        *:*
user@freebsdsrv:~ $

Test MariaDB on FreeBSD

Test the connectivity to the databases with:

user@freebsdsrv:~ $ sudo mysql -u root -p -e "show databases" [enter]
Enter password: DBrootPassWd [enter]
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
user@freebsdsrv:~ $
phpMyAdmin with PHP v. 7.0

phpMyAdmin with PHP v. 7.0

Description

phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. phpMyAdmin supports a wide range of operations on MySQL and MariaDB. Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, etc) can be performed via the user interface, while you still have the ability to directly execute any SQL statement.

WWW: http://www.phpmyadmin.net.

Requirentments

The following applications must be installed, configured and running before installation of phpMyAdmin:

Preparation for Installation

Start PuTTY on a Windows PC, Terminal on a Mac or similar terminal application on a Linux PC.

In this example Terminal on a Mac is used.

Open a remote SSH session to the server with:

Mac:~ user$ ssh user@192.168.1.4 [enter]
N.B.: Replace user@192.168.1.4 with User ID and IP Address on Your server!
[user@server ~]$

Enable superuser privileges with:

[user@server ~]$ sudo -s [enter]
Password: <-- passwd [enter]
[root@server /usr/home/user]#

N.B.: Enter user password, not the root password!

Warning!

N.B.: Do NOT install phpMyAdmin from port if the PHP version installed is > 5.6!

Verify version of PHP installed with:

[root@server /usr/home/user]# php -v [enter]
PHP 7.0.15 (cli) (built: Jan 24 2017 01:18:59) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache v7.0.15, Copyright (c) 1999-2017, by Zend Technologies
[root@server /usr/home/user]#

In this example PHP version 7.0 is installed. Due to this phpMyAdmin will be downloaded from the phpMyAdmin site.

Download and Verification

[root@server /usr/home/user]# wget https://files.phpmyadmin.net/phpMyAdmin/4.6.6/phpMyAdmin-4.6.6-all-languages.zip [enter]
Resolving files.phpmyadmin.net (files.phpmyadmin.net)... 185.59.222.19
Connecting to files.phpmyadmin.net (files.phpmyadmin.net)|185.59.222.19|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 11349727 (11M) [application/zip]
Saving to: ‘phpMyAdmin-4.6.6-all-languages.zip’

phpMyAdmin-4.6.6-al 100%[===================>]  10,82M  2,62MB/s    in 4,3s    

2017-02-22 00:00:00 (2,55 MB/s) - ‘phpMyAdmin-4.6.6-all-languages.zip’ saved [11349727/11349727]

[root@server /usr/home/user]#
[root@server /usr/home/user]# wget https://files.phpmyadmin.net/phpMyAdmin/4.6.6/phpMyAdmin-4.6.6-all-languages.zip.asc [enter]
--2017-02-22 12:12:36--  https://files.phpmyadmin.net/phpMyAdmin/4.6.6/phpMyAdmin-4.6.6-all-languages.zip.asc
Resolving files.phpmyadmin.net (files.phpmyadmin.net)... 185.76.9.11
Connecting to files.phpmyadmin.net (files.phpmyadmin.net)|185.76.9.11|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 819 [application/octet-stream]
Saving to: ‘phpMyAdmin-4.6.6-all-languages.zip.asc’

phpMyAdmin-4.6.6-al 100%[===================>]     819  --.-KB/s    in 0s      

2017-02-22 00:00:00 (61,9 MB/s) - ‘phpMyAdmin-4.6.6-all-languages.zip.asc’ saved [819/819]
[root@server /usr/home/user]#

Download the keyring from the phpMyAdmin download server with:

[root@server /usr/home/user]# wget https://files.phpmyadmin.net/phpmyadmin.keyring [enter]
--2017-02-22 12:49:01--  https://files.phpmyadmin.net/phpmyadmin.keyring
Resolving files.phpmyadmin.net (files.phpmyadmin.net)... 185.76.9.11
Connecting to files.phpmyadmin.net (files.phpmyadmin.net)|185.76.9.11|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 34198 (33K) [application/octet-stream]
Saving to: ‘phpmyadmin.keyring’

phpmyadmin.keyring  100%[===================>]  33,40K  --.-KB/s    in 0,006s  

2017-02-22 00:00:00 (5,27 MB/s) - ‘phpmyadmin.keyring’ saved [34198/34198]
[root@server /usr/home/user]#

…and then import the keyring data with:

[root@server /usr/home/user]# gpg --import phpmyadmin.keyring [enter]
gpg: /root/.gnupg/trustdb.gpg: trustdb created
gpg: key 9C27B31342B7511D: public key "Michal Čihař <michal@cihar.com>" imported
gpg: key FEFC65D181AF644A: public key "Marc Delisle <marc@infomarc.info>" imported
gpg: key CE752F178259BD92: public key "Isaac Bennetch <bennetch@gmail.com>" imported
gpg: key DA68AB39218AB947: public key "phpMyAdmin Security Team <security@phpmyadmin.net>" imported
gpg: Total number processed: 4
gpg:               imported: 4
[root@server /usr/home/user]#

N.B.: Verify the public keys corresponds with the information publiced the phpMyAdmin web site: Verifying phpMyAdmin releases.

[root@server /usr/home/user]# gpg --verify phpMyAdmin-4.6.6-all-languages.zip.asc [enter]
gpg: assuming signed data in 'phpMyAdmin-4.6.6-all-languages.zip'
gpg: Signature made Mon 23 Jan 20:22:46 2017 CET
gpg:                using RSA key CE752F178259BD92
gpg: Good signature from "Isaac Bennetch <bennetch@gmail.com>" [unknown]
gpg:                 aka "Isaac Bennetch <isaac@bennetch.org>" [unknown]
gpg: WARNING: This key is not certified with a trusted signature!
gpg:          There is no indication that the signature belongs to the owner.
Primary key fingerprint: 3D06 A59E CE73 0EB7 1B51  1C17 CE75 2F17 8259 BD92
[root@server /usr/home/user]#

Beginning in January 2016, the release manager for phpMyAdmin is Isaac Bennetch. His RSA key id is:

CE752F178259BD92

…and his PGP primary key fingerprint is:

3D06 A59E CE73 0EB7 1B51 1C17 CE75 2F17 8259 BD92

You should verify that the signature matches the archive you have downloaded. This way you can be sure that you are using the same code that was released. You should also verify the date of the signature to make sure that you downloaded the latest version.

Installation

Extract file phpMyAdmin-4.6.6-all-languages.zip to /usr/local/www/ with:

[root@server /usr/home/user]# unzip phpMyAdmin-4.6.6-all-languages.zip  -d /usr/local/www [enter]
Archive:  phpMyAdmin-4.6.6-all-languages.zip
   creating: /usr/local/www/phpMyAdmin-4.6.6-all-languages/
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/CONTRIBUTING.md  
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/ChangeLog  
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/DCO  
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/LICENSE  
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/README  
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/RELEASE-DATE-4.6.6  
.
.
.
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/url.php  
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/user_password.php  
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/version_check.php  
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/view_create.php  
 extracting: /usr/local/www/phpMyAdmin-4.6.6-all-languages/view_operations.ph  |
[root@server /usr/home/user]#

Delete file phpMyAdmin-4.6.6-all-languages.zip with:

[root@server /usr/home/user]# rm phpMyAdmin-4.6.6-all-languages.zip [enter]
[root@server /usr/home/user]#

Rename folder /usr/local/www/phpMyAdmin-4.6.6-all-languages to /usr/local/www/phpMyAdmin with:

[root@server /usr/home/user]# mv /usr/local/www/phpMyAdmin-4.6.6-all-languages /usr/local/www/phpMyAdmin [enter]
[root@server /usr/home/user]#

N.B.: Only not installed required PHP shared extension will be installed with the following command!

Install shared PHP extension required by phpMyAdmin with:

[root@server /usr/home/user]# pkg install php70-session php70-xml php70-bz2 php70-ctype php70-filter php70-zip php70-openssl php70-gd php70-mcrypt php70-mbstring php70-mysqli php70-json php70-zlib [enter]
Updating FreeBSD repository catalogue...
FreeBSD repository is up-to-date.
All repositories are up-to-date.
The following 9 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	php70-session: 7.0.15
	php70-bz2: 7.0.15
	php70-ctype: 7.0.15
	php70-openssl: 7.0.15
	php70-mcrypt: 7.0.15
	php70-mbstring: 7.0.15
	php70-json: 7.0.15
	libltdl: 2.4.6
	libmcrypt: 2.5.8_3

Number of packages to be installed: 9

The process will require 5 MiB more space.
988 KiB to be downloaded.

Proceed with this action? [y/N]: y [enter]
Fetching php70-session-7.0.15.txz: 100%   32 KiB  32.3kB/s    00:01    
Fetching php70-bz2-7.0.15.txz: 100%   11 KiB  11.0kB/s    00:01    
Fetching php70-ctype-7.0.15.txz: 100%    7 KiB   6.8kB/s    00:01    
Fetching php70-openssl-7.0.15.txz: 100%   44 KiB  44.7kB/s    00:01    
Fetching php70-mcrypt-7.0.15.txz: 100%   15 KiB  14.9kB/s    00:01    
Fetching php70-mbstring-7.0.15.txz: 100%  712 KiB 728.9kB/s    00:01    
Fetching php70-json-7.0.15.txz: 100%   19 KiB  19.9kB/s    00:01    
Fetching libltdl-2.4.6.txz: 100%   36 KiB  36.6kB/s    00:01    
Fetching libmcrypt-2.5.8_3.txz: 100%  114 KiB 116.9kB/s    00:01    
Checking integrity... done (0 conflicting)
[1/9] Installing libltdl-2.4.6...
[1/9] Extracting libltdl-2.4.6: 100%
[2/9] Installing libmcrypt-2.5.8_3...
[2/9] Extracting libmcrypt-2.5.8_3: 100%
[3/9] Installing php70-session-7.0.15...
[3/9] Extracting php70-session-7.0.15: 100%
[4/9] Installing php70-bz2-7.0.15...
[4/9] Extracting php70-bz2-7.0.15: 100%
[5/9] Installing php70-ctype-7.0.15...
[5/9] Extracting php70-ctype-7.0.15: 100%
[6/9] Installing php70-openssl-7.0.15...
[6/9] Extracting php70-openssl-7.0.15: 100%
[7/9] Installing php70-mcrypt-7.0.15...
[7/9] Extracting php70-mcrypt-7.0.15: 100%
[8/9] Installing php70-mbstring-7.0.15...
[8/9] Extracting php70-mbstring-7.0.15: 100%
[9/9] Installing php70-json-7.0.15...
[9/9] Extracting php70-json-7.0.15: 100%
Message from libmcrypt-2.5.8_3:
===>   NOTICE:

The libmcrypt port currently does not have a maintainer. As a result, it is
more likely to have unresolved issues, not be up-to-date, or even be removed in
the future. To volunteer to maintain this port, please create an issue at:

https://bugs.freebsd.org/bugzilla

More information about port maintainership is available at:

https://www.freebsd.org/doc/en/articles/contributing/ports-contributing.html#maintain-port
Message from php70-session-7.0.15:
****************************************************************************

The following line has been added to your /usr/local/etc/php/ext-18-session.ini
configuration file to automatically load the installed extension:

extension=session.so

****************************************************************************
Message from php70-bz2-7.0.15:
****************************************************************************

The following line has been added to your /usr/local/etc/php/ext-20-bz2.ini
configuration file to automatically load the installed extension:

extension=bz2.so

****************************************************************************
Message from php70-ctype-7.0.15:
****************************************************************************

The following line has been added to your /usr/local/etc/php/ext-20-ctype.ini
configuration file to automatically load the installed extension:

extension=ctype.so

****************************************************************************
Message from php70-openssl-7.0.15:
****************************************************************************

The following line has been added to your /usr/local/etc/php/ext-20-openssl.ini
configuration file to automatically load the installed extension:

extension=openssl.so

****************************************************************************
Message from php70-mcrypt-7.0.15:
****************************************************************************

The following line has been added to your /usr/local/etc/php/ext-20-mcrypt.ini
configuration file to automatically load the installed extension:

extension=mcrypt.so

****************************************************************************
Message from php70-mbstring-7.0.15:
****************************************************************************

The following line has been added to your /usr/local/etc/php/ext-20-mbstring.ini
configuration file to automatically load the installed extension:

extension=mbstring.so

****************************************************************************
Message from php70-json-7.0.15:
****************************************************************************

The following line has been added to your /usr/local/etc/php/ext-20-json.ini
configuration file to automatically load the installed extension:

extension=json.so

****************************************************************************
[root@server /usr/home/user]#

Configuration

Change file owner and group recursively for /usr/local/www/phpMyAdmin with:

[root@server /usr/home/user]# chown -R www:www /usr/local/www/phpMyAdmin [enter]
[root@server /usr/home/user]#

To make phpMyAdmin available on the web site:

[root@server /usr/home/user]# ee /usr/local/etc/apache24/Includes/phpmyadmin.conf [enter]

Add the following lines;

Alias /phpmyadmin/ "/usr/local/www/phpMyAdmin/"
<Directory "/usr/local/www/phpMyAdmin/">
  AllowOverride All
  Require all granted
</Directory>

…the restart the apache24 service with:

[root@server /usr/home/user]# service apache24 restart [enter]
Performing sanity check on apache24 configuration:
Syntax OK
Stopping apache24.
Waiting for PIDS: 12555.
Performing sanity check on apache24 configuration:
Syntax OK
Starting apache24.
[root@server /usr/home/user]#

N.B.: The phpMyAdmin documentation is stored on your server at: http://www.example.com/phpmyadmin/doc/html/!

Start your browser and go to: http://server.example.net/phpmyadmin/setup/

Login as the MySQL root user and complete the configured by enabling the phpMyAdmin extended features.

Create file /usr/local/www/phpMyAdmin/config.inc.php with:

[root@server /usr/home/user]# ee /usr/local/www/phpMyAdmin/config.inc.php [enter]

…and copy the following text – example – that was created for you:

<?php
/*
 * Generated configuration file
 * Generated by: phpMyAdmin 4.6.6 setup script
 * Date: Tue, 22 Feb 2017 00:00:00 +0000
 */

/* Servers configuration */
$i = 0;

/* Server: localhost [1] */
$i++;
$cfg['Servers'][$i]['verbose'] = '';
$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['port'] = '';
$cfg['Servers'][$i]['socket'] = '';
$cfg['Servers'][$i]['ssl'] = true;
$cfg['Servers'][$i]['connect_type'] = 'tcp';
$cfg['Servers'][$i]['compress'] = true;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['user'] = 'root';
$cfg['Servers'][$i]['password'] = '';

/* End of servers configuration */

$cfg['blowfish_secret'] = '----------------------------------';
$cfg['DefaultLang'] = 'en';
$cfg['ServerDefault'] = 1;
$cfg['UploadDir'] = '';
$cfg['SaveDir'] = '';
?>
WordPress with PHP v. 7.0

WordPress with PHP v. 7.0

Description

WordPress is a state-of-the-art semantic personal publishing platform with a focus on aesthetics, web standards, and usability.

More simply, WordPress is what you use when you want to work with your blogging software, not fight it.

WWW: http://wordpress.org/.

Requirements

The following applications must be installed, configured and running before installation of WordPress:

  1. Apache HTTP Server
  2. PHP (v. 7.0)
  3. MySQL DB Server
  4. GNUwget

Preparation for Installation

Start PuTTY on a Windows PC, Terminal on a Mac or similar terminal application on a Linux PC.

In this example Terminal on a Mac is used.

Open a remote SSH session to the server with:

Mac:~ user$ ssh user@192.168.1.4 [enter]
N.B.: Replace user@192.168.1.4 with User ID and IP Address on Your server!
[user@server ~]$

Enable superuser privileges with:

[user@server ~]$ sudo -s [enter]
Password: <-- passwd [enter]
[root@server /usr/home/user]#

N.B.: Enter user password, not the root password!

Warning!

N.B.: Do NOT install WordPress from port if the PHP version installed is > 5.6!

Verify version of PHP installed with:

[root@server /usr/home/user]# php -v <enter>
PHP 7.0.15 (cli) (built: Jan 24 2017 01:18:59) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache v7.0.15, Copyright (c) 1999-2017, by Zend Technologies
[root@server /usr/home/user]#

In this example PHP version 7.0 is installed. Due to this WordPress will be downloaded from the WordPress site.

Download

Download the latest version of WordPress with:

[root@server /usr/home/user]# wget https://wordpress.org/latest.tar.gz <enter>
--2017-02-22 18:26:38--  https://wordpress.org/latest.tar.gz
Resolving wordpress.org (wordpress.org)... 66.155.40.249, 66.155.40.250
Connecting to wordpress.org (wordpress.org)|66.155.40.249|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7997959 (7,6M) [application/octet-stream]
Saving to: ‘latest.tar.gz’

latest.tar.gz       100%[===================>]   7,63M  1,74MB/s    in 6,0s    

2017-02-22 18:26:45 (1,27 MB/s) - ‘latest.tar.gz’ saved [7997959/7997959]

[root@server /usr/home/user]#

Installation

Extract file latest.tar.gz to /usr/local/www/ with:

[root@server /usr/home/user]# tar -xzvf latest.tar.gz  -C /usr/local/www <enter>
x wordpress/
x wordpress/wp-settings.php
x wordpress/wp-cron.php
x wordpress/wp-comments-post.php
x wordpress/wp-activate.php
x wordpress/wp-admin/
.
.
x wordpress/wp-includes/comment.php
x wordpress/wp-includes/class-wp-text-diff-renderer-table.php
x wordpress/wp-config-sample.php
[root@server /usr/home/user]#

Delete file latest.tar.gz with:

[root@server /usr/home/user]# rm latest.tar.gz <enter>
[root@server /usr/home/user]#

N.B.: Only not installed required PHP shared extension will be installed with the following command!

Install shared PHP extension required by phpMyAdmin with:

[root@server /usr/home/user]# pkg install curl php70-curl libnghttp2 php70-gd php70-hash php70-xml php70-tokenizer php70-mysqli php70-zip php70-ftp php70-zlib <enter>
Updating FreeBSD repository catalogue...
FreeBSD repository is up-to-date.
All repositories are up-to-date.
The following 4 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
	curl: 7.52.1_1
	php70-curl: 7.0.15
	libnghttp2: 1.18.0
	php70-ftp: 7.0.15

Number of packages to be installed: 4

The process will require 4 MiB more space.
1 MiB to be downloaded.

Proceed with this action? [y/N]: y <enter>
Fetching curl-7.52.1_1.txz: 100%    1 MiB   1.1MB/s    00:01    
Fetching php70-curl-7.0.15.txz: 100%   26 KiB  26.8kB/s    00:01    
Fetching libnghttp2-1.18.0.txz: 100%  104 KiB 106.4kB/s    00:01    
Fetching php70-ftp-7.0.15.txz: 100%   22 KiB  22.4kB/s    00:01    
Checking integrity... done (0 conflicting)
[1/4] Installing libnghttp2-1.18.0...
[1/4] Extracting libnghttp2-1.18.0: 100%
[2/4] Installing curl-7.52.1_1...
[2/4] Extracting curl-7.52.1_1: 100%
[3/4] Installing php70-curl-7.0.15...
[3/4] Extracting php70-curl-7.0.15: 100%
[4/4] Installing php70-ftp-7.0.15...
[4/4] Extracting php70-ftp-7.0.15: 100%
Message from php70-curl-7.0.15:
****************************************************************************

The following line has been added to your /usr/local/etc/php/ext-20-curl.ini
configuration file to automatically load the installed extension:

extension=curl.so

****************************************************************************
Message from php70-ftp-7.0.15:
****************************************************************************

The following line has been added to your /usr/local/etc/php/ext-20-ftp.ini
configuration file to automatically load the installed extension:

extension=ftp.so

****************************************************************************
[root@server /usr/home/user]#

Configuration

Change file owner and group recursively for /usr/local/www/wordpress with:

[root@server /usr/home/user]# chown -R www:www /usr/local/www/wordpress <enter>
[root@server /usr/home/user]#

Change file modes recursively for /usr/local/www/wordpress with:

[root@server /usr/home/user]# chmod -R 755 /usr/local/www/wordpress <enter>
[root@server /usr/home/user]#

Enable the Apache rewrite_module with:

[root@server /usr/home/user]# perl -pi -e 's/#LoadModule rewrite_module/LoadModule rewrite_module/g' /usr/local/etc/apache24/httpd.conf <enter>
[root@server /usr/home/user]#

To make wordpress available on the web site:

[root@server /usr/home/user]# ee /usr/local/etc/apache24/Includes/wordpress.conf <enter>

Add the following lines;

Alias /wp/ "/usr/local/www/wordpress/"
<Directory "/usr/local/www/wordpress/">
  AllowOverride All
  Require all granted
</Directory>

…the restart the apache24 service with:

[root@server /usr/home/user]# service apache24 restart <enter>
Performing sanity check on apache24 configuration:
Syntax OK
Stopping apache24.
Waiting for PIDS: 12555.
Performing sanity check on apache24 configuration:
Syntax OK
Starting apache24.
[root@server /usr/home/user]#

Prepare MySQL Database

WordPress uses a relational database, such as MySQL, to manage and store site and user information.

Login to the MySQL database with:

[root@server /usr/home/user]# mysql -u root -p <enter>
Enter password: <-- password <enter>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 422
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]>

Create a MySQL wordpress database with:

root@localhost [(none)]> CREATE DATABASE wordpress; &enter>
Query OK, 1 row affected (0,02 sec)

root@localhost [(none)]>

Create a MySQL user account wpadmin that WordPress will use to interact with the wordpress database with:

root@localhost [(none)]> CREATE USER wpadmin@localhost IDENTIFIED BY 'password'; &enter>
Query OK, 0 rows affected (0,44 sec)

root@localhost [(none)]>

 

Grant the WordPress wpadmin user full access to the wordpress database with:

 

root@localhost [(none)]> GRANT ALL PRIVILEGES ON wordpress.* TO wpadmin@localhost; &enter> Query OK, 0 rows affected (0,44 sec) root@localhost [(none)]>

Before this privileges change will go into effect, we must flush the privileges with:

root@localhost [(none)]> FLUSH PRIVILEGES; &enter>
Query OK, 0 rows affected (0,43 sec)

root@localhost [(none)]>

Exit the MySQL prompt with:

root@localhost [(none)]> exit &enter>
Bye
[root@server /usr/home/user]#

Make a copy of the WordPress sample configuration file with:

[root@server /usr/home/user]# cp /usr/local/www/wordpress/wp-config-sample.php /usr/local/www/wordpress/wp-config.php <enter>
[root@server /usr/home/user]#

Start editing file /usr/local/www/wordpress/wp-config.php with:

[root@server /usr/home/user]# ee /usr/local/www/wordpress/wp-config.php <enter>

…and update DB_NAME, DB_USER and DB_PASSWORD as in this example:

<?php
/**
 * The base configuration for WordPress
 *
 * The wp-config.php creation script uses this file during the
 * installation. You don't have to use the web site, you can
 * copy this file to "wp-config.php" and fill in the values.
 *
 * This file contains the following configurations:
 *
 * * MySQL settings
 * * Secret keys
 * * Database table prefix
 * * ABSPATH
 *
 * @link https://codex.wordpress.org/Editing_wp-config.php
 *
 * @package WordPress
 */

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'wpadmin');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', 'localhost');

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');

/**#@+
 * Authentication Unique Keys and Salts.
 *
 * Change these to different unique phrases!
 * You can generate these using the {@link https://api.wordpress.org/secret-key/1.1/salt/ WordPress.org secret-key service}
 * You can change these at any point in time to invalidate all existing cookies. This will force all users to have to log in again.
 *
 * @since 2.6.0
 */
define('AUTH_KEY',         'put your unique phrase here');
define('SECURE_AUTH_KEY',  'put your unique phrase here');
define('LOGGED_IN_KEY',    'put your unique phrase here');
define('NONCE_KEY',        'put your unique phrase here');
define('AUTH_SALT',        'put your unique phrase here');
define('SECURE_AUTH_SALT', 'put your unique phrase here');
define('LOGGED_IN_SALT',   'put your unique phrase here');
define('NONCE_SALT',       'put your unique phrase here');

/**#@-*/

/**
 * WordPress Database Table prefix.
 *
 * You can have multiple installations in one database if you give each
 * a unique prefix. Only numbers, letters, and underscores please!
 */
$table_prefix  = 'wp_';

/**
 * For developers: WordPress debugging mode.
 *
 * Change this to true to enable the display of notices during development.
 * It is strongly recommended that plugin and theme developers use WP_DEBUG
 * in their development environments.
 *
 * For information on other constants that can be used for debugging,
 * visit the Codex.
 *
 * @link https://codex.wordpress.org/Debugging_in_WordPress
 */
define('WP_DEBUG', false);

/* That's all, stop editing! Happy blogging. */

/** Absolute path to the WordPress directory. */
if ( !defined('ABSPATH') )
	define('ABSPATH', dirname(__FILE__) . '/');

/** Sets up WordPress vars and included files. */
require_once(ABSPATH . 'wp-settings.php');
?>

Authentication Unique Keys and Salts

You can generate the unique keys at: https://api.wordpress.org/secret-key/1.1/salt/

Run WordPress Installation Script

Start your browser and got to http://www.example.net/wp/ and complete the installation of WordPress.

MySQL DB Server

MySQL DB Server

Description

MySQL is a very fast, multi-threaded, multi-user and robust SQL (Structured Query Language) database server.

WWW: http://www.mysql.com.

Preparation for Installation

Start PuTTY on a Windows PC, Terminal on a Mac or similar terminal application on a Linux PC.

In this example Terminal on a Mac is used.

Open a remote SSH session to the server with:

Mac:~ user$ ssh user@192.168.1.4 [enter]
N.B.: Replace user@192.168.1.4 with User ID and IP Address on Your server!
[user@server ~]$

Enable superuser privileges with:

[user@server ~]$ sudo -s [enter]
Password: <-- passwd [enter]
[root@server /usr/home/user]#

N.B.: Enter user password, not the root password!

Installation

Search for mysql57-server in the remote package repositories with:

[root@server /usr/home/user]# pkg search "mysql5" [enter]
mysql++-mysql56-3.2.2          Complex C++ API for MySQL56
mysql++1-mysql56-1.7.40_3      Complex C++ API for MySQL56
mysql-connector-odbc-unixodbc-mysql56-5.3.4_1 ODBC driver for MySQL56 / unixodbc
mysql55-client-5.5.56          Multithreaded SQL database (client)
mysql55-server-5.5.56          Multithreaded SQL database (server)
mysql56-client-5.6.36          Multithreaded SQL database (client)
mysql56-q4m-0.9.14             Message queue that works as a pluggable storage engine of MySQL
mysql56-server-5.6.36          Multithreaded SQL database (server)
mysql57-client-5.7.18          Multithreaded SQL database (client)
mysql57-server-5.7.18          Multithreaded SQL database (server)
[root@server /usr/home/user]#

In this example mysql57-server will be installed.

Install mysql57-server with:

[root@server /usr/home/user]# pkg install mysql57-server p5-DBI [enter]
Updating FreeBSD repository catalogue...
FreeBSD repository is up to date.
All repositories are up to date.
The following 3 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
        mysql57-server: 5.7.18
        libevent: 2.1.8
        mysql57-client: 5.7.18

Number of packages to be installed: 3

The process will require 188 MiB more space.
16 MiB to be downloaded.

Proceed with this action? [y/N]: y [enter]
[1/3] Fetching mysql57-server-5.7.18.txz: 100%   13 MiB   7.1MB/s    00:02
[2/3] Fetching libevent-2.1.8.txz: 100%  300 KiB 307.7kB/s    00:01
[3/3] Fetching mysql57-client-5.7.18.txz: 100%    2 MiB   1.9MB/s    00:01
Checking integrity... done (0 conflicting)
[1/3] Installing libevent-2.1.8...
[1/3] Extracting libevent-2.1.8: 100%
[2/3] Installing mysql57-client-5.7.18...
[2/3] Extracting mysql57-client-5.7.18: 100%
[3/3] Installing mysql57-server-5.7.18...
===> Creating groups.
Creating group 'mysql' with gid '88'.
===> Creating users
Creating user 'mysql' with uid '88'.
Extracting mysql57-server-5.7.18: 100%
Message from mysql57-client-5.7.18:
* * * * * * * * * * * * * * * * * * * * * * * *

This is the mysql CLIENT without the server.
for complete server and client, please install databases/mysql57-server

* * * * * * * * * * * * * * * * * * * * * * * *
Message from mysql57-server-5.7.18:
*****************************************************************************

Remember to run mysql_upgrade the first time you start the MySQL server
after an upgrade from an earlier version.

Initial password for first time use of MySQL is saved in $HOME/.mysql_secret
ie. when you want to use "mysql -u root -p" first you should see password
in /root/.mysql_secret

MySQL57 has a default %%ETCDIR%%/my.cnf,
remember to replace it wit your own
or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf.

*****************************************************************************
[root@server /usr/home/user]#

Configuration

List installed services with:

[root@server /usr/home/user]# service -r | grep mysql [enter]
/usr/local/etc/rc.d/mysql-server
[root@server /usr/home/user]#

Find the rcvar for /etc/rc.conf:

[root@server /usr/home/user]# /usr/local/etc/rc.d/mysql-server rcvar [enter]
# mysql
#
mysql_enable="NO"
#   (default: "")

[root@server /usr/home/user]#

To start MySQL on system boot, add information to /etc/rc.conf with this commands:

[root@server /usr/home/user]# echo '' >> /etc/rc.conf; echo '# MySQL Server' >> /etc/rc.conf; echo 'mysql_enable="YES"' >> /etc/rc.conf [enter]
[root@server /usr/home/user]#

Then edit /usr/local/etc/mysql/my.cnf with:

[root@server /usr/home/user]# ee /usr/local/etc/mysql/my.cnf [enter]
# $FreeBSD: head/databases/mysql57-server/files/my.cnf.sample.in 414707 2016-05-06 14:39:59Z riggs $

[client]
port                            = 3306
socket                          = /tmp/mysql.sock

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysql
tmpdir                          = /var/db/mysql_tmpdir
slave-load-tmpdir               = /var/db/mysql_tmpdir
secure-file-priv                = /var/db/mysql_secure
log-bin                         = mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
expire_logs_days                = 30
default_password_lifetime       = 0
enforce-gtid-consistency        = 1
gtid-mode                       = ON
safe-user-create                = 1
lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = BACKUP,FORCE
open_files_limit                = 32768
table_open_cache                = 16384
table_definition_cache          = 8192
net_retry_count                 = 16384
key_buffer_size                 = 256M
max_allowed_packet              = 64M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.5
innodb_buffer_pool_size         = 1G
innodb_data_home_dir            = /var/db/mysql
innodb_log_group_home_dir       = /var/db/mysql
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
skip-symbolic-links

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

For advice on how to change settings please see https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

N.B.: MySQL databases is stored in /var/db/mysql by default.

Change file owner and group for file /usr/local/etc/my.cnf with:

[root@server /usr/home/user]# chown mysql:mysql /usr/local/etc/mysql/my.cnf [enter]
[root@server /usr/home/user]#

Start

Manually start MySQL Server with;

[root@server /usr/home/user]# service mysql-server start [enter]
Starting mysql.
[root@server /usr/home/user]#

Check if the service is running with:

[root@server /usr/home/user]# service mysql-server status [enter]
mysql-server is running as pid 91514.
[root@server /usr/home/user]#

…and:

[root@server /usr/home/user]# ps -waux | grep mysql [enter]
mysql     821   0,0  0,0  17088   2540  -  Is   11:03pm    0:00,01 /bin/sh /usr/local/bin/mysqld_safe --defaults-extra-file=/var/db/mysql/my.cnf --user=mysql --datadir=/var/db/mysql --pid-file=/var/db/mysql/server.polymathic.net.pid
mysql     939   0,0  3,0 674172 465456  -  I    11:03pm    0:02,87 /usr/local/libexec/mysqld --defaults-extra-file=/var/db/mysql/my.cnf --basedir=/usr/local --datadir=/var/db/mysql --plugin-dir=/usr/local/lib/mysql/plugin --log-error=/var/db/mysql/server.polymathic.net.err --pid-file=/var/db/mysql/server.polymathic.net.pid
[root@server /usr/home/user]#

Securing MySQL

Protect the MySQL installation with:

[root@server /usr/home/user]# mysql_secure_installation [enter]

Securing the MySQL server deployment.

Connecting to MySQL server using password in '/root/.mysql_secret'

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: Y [enter]

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2 [enter]
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : N [enter]

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : Y [enter]
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : [enter]

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : Y [enter]
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y [enter]
Success.

All done! 
[root@server /usr/home/user]#

N.B.: The root password for MySQL can be found in file /root/.mysql_secret!

[root@server /usr/home/user]# cat /root/.mysql_secret
# Password set for user 'root@localhost' at 2017-02-20 21:33:40 
{passwd}
[root@server /usr/home/user]#

Logon to the MySQL service as root with:

[root@server /usr/home/user]# mysql -u root -p [enter]
Enter password: <-- password in file /root/.mysql_secret [enter]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17-log

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]>

Logout from the MySQL service with:

root@localhost [(none)]> exit [enter]
Bye
[root@server /usr/home/user]#

MySQL User Administration

To set the MySQL root password for the first time:

[root@server /usr/home/user]# mysqladmin -u root password 'passwd' [enter]
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@server /usr/home/user]#

To change the MySQL root password:

[root@server /usr/home/user]# mysqladmin -u root -p'oldpassword' password 'newpasswd' [enter]
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@server /usr/home/user]#

Tweaking MySQL

Default minimum character search length in MySQL is 4 characters. This can easily be adjusted to let’s say 2 characters.

[root@server /usr/home/user]# ee /usr/local/etc/my.cnf

…and add the following text:

[mysqld]
ft_min_word_len=2

[myisamchk]
ft_min_word_len=2

[mysqldump]
ignore-table=mysql.event

Manually restart MySQL Server with;

[root@server /usr/home/user]# service mysql-server restart [enter]
Stopping mysql.
Waiting for PIDS: 939.
Starting mysql.
[root@server /usr/home/user]#

Optional: packet filter (pf)

Network access to the MySQL Server service must be enabled in the packet filter (pf) configuration file.

Start editing file /etc/pf.conf with:

[root@server /usr/home/user]# ee /etc/pf.conf [enter]

…and add default port information to enable access to the MySQL Server service from clients on the local network as in this example:

.
.
# Ports:
# 123 TCP       Network Time Protocol
.
.
# 3306 TCP      MySQL database system

tcp_pass="{ 123, 3306 }"
.
.

Check /etc/pf.conf for errors, but do not load ruleset with:

[root@server /usr/home/user]# pfctl -vvnf /etc/pf.conf [enter]

…and then reload /etc/pf.conf with:

[root@server /usr/home/user]# service pf reload [enter]
Reloading pf rules.
[root@server /usr/home/user]#

Move a MySQL Database from one server to another via SSH

Create a new empty database on the receiving server, srv2:

[root@srv2 ~]# mysql -uroot -p [enter]
Enter password: passwd [enter]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3122
Server version: 5.5.23-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> CREATE DATABASE db_name; [enter]
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> exit [enter]
[root@srv2 ~]#

On the server with the database, srv1:

[root@srv1 ~]# mysqldump -u{user} -p{password} {db_name} | ssh user@{IP Address srv2} "mysql -u{user} -p{password} {db_name}" [enter]
[root@srv1 ~]#

Using the parameters -e -f -q -Q -K for mysqldump a good idea and will make the insert faster and more secure. Look them up in the docs!