Setting up MySQL for IndiMail

45 views
Skip to first unread message

Manvendra Bhangui

unread,
Feb 8, 2018, 9:05:41 AM2/8/18
to indimail...@lists.sourceforge.net, indimail
The most problematic portion of the RPM/Debian installation has always been creating a initialized MySQL database. Due to frequent changes by MySQL/MariaDB developers, the script in svctool gets outdated. Also there are differences in the way a new database is initialized between MySQL and MariaDB. MySQL uses mysqld --initialize while Mariadb uses /usr/bin/mysql_install_db script. This tutorial explains how you can setup MySQL or MariaDB for IndiMail even if the binary install fails to create a database without any error.

Setting up MySQL

This section assumes that you have already installed MySQL or MariaDB. There are few difference between them. Also remember that the MySQL developers have a habit of changing & breaking things. Anything written here could stop working few versions down the road. This document has been written with MySQL Version 5.7 and MariaDB 5.5

There are few things that IndiMail requires

  1. A Database on which IndiMail has certain privileges. We will discuss this under the section ‘Database Initialization’

  2. A consistent method of starting MySQL daemon mysqld. This can easily be achieved by having mysqld under supervise. We will discuss this under the section ‘MySQL Service’

  3. Setting up MySQL users named mysql, indimail, admin and repl.

  4. A MySQL config file which sets variables like the socket path. This should be named indimail.cnf. This can be created by running the svctool command. We will discuss this under the section ‘MySQL Configuration’

  5. A control file which is used by all IndiMail client to access MySQL. This control file will have the MySQL host, username, password, port or socket path. This can be created using any text editor or using the echo command on the shell. We will discuss this under the section ‘MySQL Control File’

The role of the users discussed in point 3. above are


  1. mysql to allow all administration access to MySQL database. This is equivalent to the MySQL root user created by default MySQL binary installation.

  2. indimail to allow IndiMail programs to access MySQL. This user does not have access to any other database.

  3. admin with reload and shutdown privileges on the MySQL database. This user can shutdown the MySQL database.

  4. repl to with replication privileges. This allows a slave MySQL setup to replicate database from the MySQL master. This is needed only when you want to have a Master Slave setup.

1. Database Initialization

a. Using svctool

svctool automates the process of MySQL db creation as required for IndiMail. You just need to run the following command


$ /usr/sbin/svctool --config=mysqldb --mysqlPrefix=/usr \

--databasedir=/var/indimail/mysqldb --default-domain=`uname -n` \

--base_path=/home/mail



The above command will create and initialize a MySQL database for first time use by IndiMail. It will also create the four users mysql, indimail, admin and repl. If you want to setup MySQL manually or want to use an existing MySQL instance/setup or want to do a manual initialization of MySQL database, you will have to create these users manually. The creation for these users will be discussed in Section 3.

b. Manual Initialization

In the examples shown here, the server is going to run under the user ID of the mysql login account. This assumes that such an account exists. Either create the account if it does not exist, or substitute the name of a different existing login account that you plan to use for running the server.

Create a directory whose location can be provided to the secure_file_priv system variable, which limits import/export operations to that specific directory:


$ mkdir -p /var/indimail/mysqldb/data

$ mkdir -p var/indimail/mysqldb/logs

$ chown -R mysql:mysql /var/indimail/mysqldb/data

$ chmod 750 /var/indimail/mysqldb/data



Initialize the data directory, including the mysql database containing the initial MySQL grant tables that determine how users are permitted to connect to the server.

Typically, data directory initialization need be done only after you first installed MySQL. If you are upgrading an existing installation, you should run mysql_upgrade instead (see mysql_upgrade — Check and Upgrade MySQL Tables). However, the command that initializes the data directory does not overwrite any existing privilege tables, so it should be safe to run in any circumstances. Use the server to initialize the data directory; for example:



For MySQL from Oracle

$ sudo mysqld --initialize --datadir=/var/indimail/mysqldb/data –user=mysql --log-error=/var/indimail/mysqldb/logs/mysqld.log



For MySQL from MariaDB

$ sudo mysql_install_db –user=mysql –datadir=/var/indimail/mysqldb/data --log-error=/var/indimail/mysqldb/logs/mysqld.log



You can check if the database has been created by using the ls command. You should be able to see two directories mysql and indimail under /var/indimail/mysqldb/data, indicating creation of two databases named mysql and indimail.

$ ls -l /var/indimail/mysqldb/data

total 176228

-rw-rw----. 1 mysql mysql 16384 Dec 20 10:00 aria_log.00000001

-rw-rw----. 1 mysql mysql 52 Dec 20 10:00 aria_log_control

-rw-r-----. 1 mysql mysql 56 Mar 24 2017 auto.cnf

-rw-------. 1 mysql mysql 1675 Mar 24 2017 ca-key.pem

-rw-r--r--. 1 mysql mysql 1074 Mar 24 2017 ca.pem

-rw-r--r--. 1 mysql mysql 1078 Mar 24 2017 client-cert.pem

-rw-------. 1 mysql mysql 1675 Mar 24 2017 client-key.pem

drwxr-x---. 2 mysql mysql 4096 Mar 24 2017 ezmlm

-rw-r-----. 1 mysql mysql 726 Feb 8 07:59 ib_buffer_pool

-rw-r-----. 1 mysql mysql 79691776 Feb 8 07:59 ibdata1

-rw-r-----. 1 mysql mysql 50331648 Feb 8 07:59 ib_logfile0

-rw-r-----. 1 mysql mysql 50331648 Mar 24 2017 ib_logfile1

drwxr-x---. 2 mysql mysql 4096 Feb 3 18:02 indimail

-rw-rw----. 1 mysql mysql 0 Dec 17 18:52 multi-master.info

drwxr-x---. 2 mysql mysql 4096 Feb 3 18:02 mysql

drwxr-x---. 2 mysql mysql 4096 Mar 24 2017 performance_schema

-rw-------. 1 mysql mysql 1675 Mar 24 2017 private_key.pem

-rw-r--r--. 1 mysql mysql 451 Mar 24 2017 public_key.pem

-rw-r--r--. 1 mysql mysql 1078 Mar 24 2017 server-cert.pem

-rw-------. 1 mysql mysql 1675 Mar 24 2017 server-key.pem

drwxr-x---. 2 mysql mysql 12288 Mar 24 2017 sys



You now need to check the log /var/indimail/mysqldb/logs/mysqld.log. The last line in this log will give you the password for the root user. This user will have all privileges and we will use this to create the user indimail and grant it privileges to access the indimail database. Note down this password.

$ cat /var/indimail/mysqldb/logs/mysqld.log

2018-02-07T03:34:41.509241Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)

2018-02-07T03:34:41.509393Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)

2018-02-07T03:34:43.457211Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-02-07T03:34:43.712425Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-02-07T03:34:43.801374Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d65b11f7-0bb7-11e8-b137-b8763fc3c7f1.

2018-02-07T03:34:43.811013Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2018-02-07T03:34:43.811968Z 1 [Note] A temporary password is generated for root@localhost: po!aj=Zi(8+b



If you want the server to be able to deploy with automatic support for secure connections, use the mysql_ssl_rsa_setup utility to create default SSL and RSA files:

$ sudo bin/mysql_ssl_rsa_setup -uid=mysql --datadir=/var/ndimail/mysqldb/data

For more information, see mysql_ssl_rsa_setup — Create SSL/RSA Files.

Now using the password obtained from var/log/mysqld.log, we will connect to MySQL and create users



    
	
  1. 

    If the plugin directory (the directory named by the
	plugin_dir
	system variable) is writable by the server, it may be possible for a
	user to write executable code to a file in the directory using
	SELECT
	... INTO DUMPFILE. This can be prevented by making the
	plugin directory read only to the server or by setting the
	secure_file_priv
	system variable at server startup to a directory where SELECT
	writes can be performed safely. (For example, set it to the
	mysql-files directory created earlier.)
		
    
	
  2. 

    To specify options that the MySQL server should use at
	startup, put them in a /etc/my.cnf or
	/etc/mysql/my.cnf file. You can use
	such a file to set, for example, the secure_file_priv
	system variable. See Server
	Configuration Defaults. If you do not do this, the server starts
	with its default settings. You should set the datadir variable to
	/var/indimail/mysqldb/data
    
	
  3. 

    If you want MySQL to start automatically when you boot your
	machine, see Section 9.5,
	“Starting and Stopping MySQL Automatically”. 
	
    



Data directory initialization creates time zone tables in the
mysql database but does not populate
them. To do so, use the instructions in MySQL
Server Time Zone Support. 



2.
MySQL Startup


If you have done a binary installation of MySQL (yum/dnf/apt-get
or RPM/DEB installation), the post install scripts should have
installed MySQL to be started during boot. The preferred method for
IndiMail is using supervise, though not mandatory. 



a)
System Default


If you decide to use have MySQL daemon started as setup by the
MySQL package installation, you need to modify /etc/my.cnf
or /etc/mysql/mysql.cnf
and change datadir
to /var/indimail/mysqldb/data.


You
can start mysqld
my issuing the command



$ sudo service mysqld start










NOTE: On some system you might have to replace mysqld with mysql
while issuing the above command. After you do this, you will see
mysqld_safe in the process list.


If you have installed MariaDB there is no confusion to startup
MySQL. The command will be









$ sudo service mariadb start











b) MySQL startup under supervise


This is the
preferred method. This allows for MySQL to be started automatically
when IndiMail starts up and shutdown when IndiMail shuts down. The
supervise service can be created by running the svctool command.









$ sudo /usr/sbin/svctool --mysql=3306 --servicedir=/service \



        --mysqlPrefix=/usr --databasedir=/var/indimail/mysqldb \



        --config=/etc/indimail/indimail.cnf --default-domain=`uname
-n`










Once you do this, you will see the directory /service/mysql.3306









$ /bin/ls -lR /service/mysql.3306



/service/mysql.3306:



total 20



drwxr-xr-x. 3 root root 4096 Feb  3 17:46 log



-rwxr-xr-x. 1 root root 1345 Feb  5 12:57 run



-r-x------. 1 root root  423 Feb  5 12:57 shutdown



dr-x------. 2 root root 4096 Feb  3 17:43 variables










/service/mysql.3306/log:



total 8



-rwxr-xr-x. 1 root root  423 Feb  5 12:57 run


NOTE: You don’t need the supervise supervise service if you use
the system installation default. If you have created this service,
you can have supervise not start up mysqld by issuing the following
command









$ sudo touch /service/mysql.3306/down










If you are doing to have supervise startup MySQL, then you need to
disable the service from getting started up at boot. On modern
systems, the command will be









$ sudo systemctl disable mysqld.service # MySQL from Oracle (it could
also be mysql.service



$ sudo systemctl disable mariadb.service # MariaDB










3.
Creating MySQL Users


If you have created the MySQL database manually, you will need to
create the uses using the MySQL client /usr/bin/mysql.
In Section 1, we noted down the MySQL password for the root
user. You would have started MySQL service in Section 2. We
will now connect to the database and create users using the following
set of commands.


First step is to connect using the password noted in Section 1
and change the password



$ mysqladmin -u root -p password



Enter password: 




New password: 




Confirm new password: 




Warning: Since password will be sent to server in plain text, use ssl
connection to ensure password safety.


NOTE: In case of MariaDB, the root password is not set and you
will have to just press the Return key for the “Enter password:”
prompt.


Now you can connect to MySQL using the new password set for user
root
above.









$ mysql -u root -p



Enter password: 




Welcome to the MySQL monitor.  Commands end with ; or \g.



Your MySQL connection id is 10



Server version: 5.7.20-log MySQL Community Server (GPL)










Copyright (c) 2000, 2017, 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> CREATE USER indimail identified by 'ssh-1.5-';



mysql> CREATE USER mysql    identified by '4-57343-';



mysql> CREATE USER admin    identified by 'benhur20';



mysql> CREATE USER repl     identified by 'slaveserver';



mysql> GRANT ALL on *.* to 'mysql';



mysql> GRANT SELECT,CREATE,ALTER,INDEX,INSERT,UPDATE,DELETE, \



        ->  CREATE TEMPORARY TABLES, \



         -> LOCK TABLES ON indimail.* to 'indimail';



mysql> GRANT RELOAD,SHUTDOWN,PROCESS on *.* to admin;



mysql> GRANT REPLICATION SLAVE on *.* to repl;



mysql> CREATE DATABASE indimail;



mysql> use mysql;



mysql> DELETE from user  where host=’localhost’ and
user=’root’;



mysql> FLUSH PRIVILEGES;










NOTE: for MariaDB, you will have to execute few additional MySQL
statements. The statements below also achieves what the script
/bin/mysql_secure_installation does for MariaDB
installations.









$ mysql -u root -p



Enter password: 




Welcome to the MariaDB monitor.  Commands end with ; or \g.



Your MariaDB connection id is 6



Server version: 5.5.59-MariaDB MariaDB Server










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










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










MariaDB [(none)]> use mysql;



MariaDB [(none)]> DELETE from user where host != ‘%’;



MariaDB [(none)]> DROP DATABASE test;



MariaDB [(none)]> FLUSH PRIVILEGES;










NOTE: After using the above commands, you will no longer have the
MySQL user root. Instead you will use the user mysql
for all privileged operations.



4. Creating MySQL Configuration


This involves creating indimail.cnf in /etc/indimail. This file
looks like this









$ cat /etc/indimail/indimail.cnf



[client]



port      = 3306



socket    = /var/run/mysqld/mysqld.sock










[mysqld]



#



# * Basic Settings



#










#



# * IMPORTANT



#   If you make changes to these settings and your system uses
apparmor, you may



#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.



#










sql_mode="NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,STRICT_ALL_TABLES"



explicit_defaults_for_timestamp=TRUE



user     = mysql



socket   = /var/run/mysqld/mysqld.sock



port     = 3306



basedir  = /usr



datadir  = /var/indimail/mysqldb










[inlookup]



#The number of seconds the server waits for activity on an



#interactive connection before closing it. An interactive client is



#defined as a client that uses the 'CLIENT_INTERACTIVE' option to
connect



interactive_timeout=28880










#The number of seconds to wait for more data from a connection



#before aborting the read. This timeout applies only to TCP/IP



#connections, not to connections made via Unix socket files, named



#pipes, or shared memory.



net_read_timeout=5










#The number of seconds to wait for a block to be written to a



#connection before aborting the write. This timeout applies only to



#TCP/IP connections, not to connections made via Unix socket files,



#named pipes, or shared memory.



net_write_timeout=5










#The number of seconds the server waits for activity on a



#non-interactive connection before closing it. This timeout applies



#only to TCP/IP and Unix socket file connections, not to



#connections made via named pipes, or shared memory.



wait_timeout=28800










You can use your favourite editor to create the above file or
create it using the svctool commands. Also you need to have a link to
this file in /etc/mysql



$ sudo /usr/sbin/svctool --config=mysql --mysqlPrefix=/usr \



           --mysqlport=3306 –mysqlsocket=/var/run/mysqld/mysqld.sock



$ ln -s /etc/indmail/indimail.cnf /etc/mysql/indimail.cnf










For MariaDB you might find a directory etc/my.cnf.d. In that case
create a link to /etc/indimail/indimail.cnf in that directory









$ ln -s /etc/indmail/indimail.cnf /etc/my.cnf.d/indimail.cnf











5. MySQL Control File


Once you have MySQL up and running, you need to tell IndiMail how
to use it. This is done by having the config file
/etc/indimail/control/host.mysql.
The format for this file is


mysql_host:mysql_user:mysql_pass:mysql_socket


or


mysql_host:mysql_user:mysql_pass:mysql_port


You will use the first syntax when you have MySQL and IndiMail
installed on the same host. You will use the second form when you
have MySQL installed on a host different from the host on which you
have installed IndiMail. The user mysql_user needs to
have certain privileges, which we will discuss under the section
MySQL Privileges.



$ sudo /bin/sh -c
“localhost:indimail:ssh-1.5-:/var/run/mysqld/mysqld.sock > 




    /etc/indimail/control/host.mysql










Now we have everything ready. We can test the connection and also
create all default IndiMail tables by running the install_tables
command.









$ /usr/sbin/install_tables



created table indimail on local



created table indibak on local



created table relay on local



created table atrn_map on local



created table bulkmail on local



created table fstab on local



created table ip_alias_map on local



created table lastauth on local



created table userquota on local



created table valias on local



created table vfilter on local



created table mailing_list on local



created table vlimits on local



created table vlog on local



skipped table aliasdomain on master



skipped table dbinfo on master



skipped table fstab on master



skipped table host_table on master



skipped table mgmtaccess on master



skipped table smtp_port on master



skipped table vpriv on master



skipped table spam on master



skipped table badmailfrom on master



skipped table badrcptto on master



skipped table spamdb on master






Reply all
Reply to author
Forward
0 new messages