MySQL DB Server
Last Updated on 2017-07-07 by Sture
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][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
quickFor 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.eventManually 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!