Solved Create a second instance of MySql

eepete

New Member

Reaction score: 8
Messages: 10

Am at day 3 of trying to solve this. I have a working MySql that comes up at boot via the rc.d mysql-server. That server, with the help of a lot of software and some special files in /usr/local/etc/rc.d runs with all files in a ram disk to avoid burning up SSD writes in an edge server processing IoT information. Yes, this is a bit unusual, took a while to get it working.
I now want to bring up a second instance of MySql that runs with the data directory in SSD at /var/db/mysqlSSD. I've copied the original /usr/local/etc/rc.d/mysql-server to /usr/local/etc/rc.d/mysqlssd-server and changed what I believe were the correct fields. I also copied /usr/local/etc/mysql/my.cnf to /usr/local/etc/mysql/myssd.cnf and changed what I thought were the correct fields.
When I try to service mysqlssd-server start I see:
/usr/local/etc/rc.d/mysqlssd-server: WARNING: failed precmd routine for mysqlssd

mysql Ver 8.0.25 for FreeBSD12.2 on amd64 (Source distribution)
FreeBsd 12.2-RELEASE-p6

Here is my mysqlssd-server deprived from the mysql-server file. I have some hard-coded values at the top, and a debug print to compare the parameters passed to /usr/local/libexec/mysqld, but it is derived from what works already for the mysql sever that is up and running with no problems.
Code:
#!/bin/sh

# PROVIDE: mysqlssd
# REQUIRE: LOGIN
# KEYWORD: shutdown

#
# Add the following line to /etc/rc.conf to enable mysql:
# mysql_enable (bool):    Set to "NO" by default
#    Set it to "YES" to enable MySQL.
# mysql_dbdir (str):    Default to "/var/db/mysql"
#    Base database directory.
# mysql_confdir (str):    Default to "/usr/local/etc/mysql"
#    Base configuration directory.
# mysql_optfile (str):    Server-specific option file.
#    Set it in the rc.conf or default behaviour of
#    `mysqld_safe` itself, will be picking
#    ${mysql_confdir}/my.cnf if it exists.
# mysql_pidfile (str):    Custum PID file path and name.
#    Default to "${mysql_dbdir}/${hostname}.pid".
# mysql_args (str):    Custom additional arguments to be passed
#    to mysqld_safe (default empty).
#

. /etc/rc.subr

name="mysqlssd"
rcvar=mysqlssd_enable

# Added these to force database directory and config file location. Config file is called myssd.cnf
mysql_dbdir="/var/db/mysqlSSD"
mysql_confdir="/usr/local/etc/mysql"
mysql_cnf="myssd.cnf"
mysql_optfile="${mysql_confdir}/${mysql_cnf}"

# try to force the socket and port number when creating the auth tables.
mysql_extra="--socket=/tmp/mysqlssd.sock --port=3311"

load_rc_config $name

: ${mysqlssd_enable="NO"}
: ${mysql_dbdir="/var/db/mysqlSSD"}
: ${mysql_confdir"/usr/local/etc/mysql"}
if [ -f "${mysql_confdir}/${mysql_cnf}" ]; then
: ${mysql_optfile="${mysql_confdir}/${mysql_cnf}"}
elif [ -f "${mysql_dbdir}/${mysql_cnf}" ]; then
: ${mysql_optfile="${mysql_dbdir}/${mysql_cnf}"}
fi
if [ ! -z "${mysql_optfile}" ]; then
mysql_extra="--defaults-extra-file=${mysql_optfile}"
fi

mysql_user="mysql"
: ${hostname:=`/bin/hostname`}
pidfile=${mysql_pidfile:-"${mysql_dbdir}/${hostname}.pid"}
command="/usr/sbin/daemon"
command_args="-c -f /usr/local/bin/mysqld_safe ${mysql_extra} --basedir=/usr/local --datadir=${mysql_dbdir} --pid-file=${pidfile} --user=${mysql_user} ${mysql_args}  "
procname="/usr/local/libexec/mysqld"
start_precmd="${name}_prestart"
start_postcmd="${name}_poststart"
mysqld_init="${procname}"
mysqld_init_args="${mysql_extra} --initialize-insecure --basedir=/usr/local --datadir=${mysql_dbdir} --user=${mysql_user}"

mysqlssd_create_auth_tables()
{
# Debug print to see make sure the variables passed are OK
          echo "eval ${mysqld_init} ${mysqld_init_args}"
    eval $mysqld_init $mysqld_init_args >/dev/null 2>/dev/null
}

mysqlssd_prestart()
{
    if [ ! -d "${mysql_dbdir}/mysqlSSD/." ]; then
    mysqlssd_create_auth_tables || return 1
    fi
    return 0
}

mysqlssd_poststart()
{
    local timeout=15
    while [ ! -f "${pidfile}" -a ${timeout} -gt 0 ]; do
    timeout=$(( timeout - 1 ))
    sleep 1
    done
    return 0
}

run_rc_command "$1"

The /usr/local/etc/mysql/myssd.cnf looks like this. I realize many of the fields are not pertinent to my problem, but, do not want to risk removing something that was relevant by mistake. Note also that on this system, /tmp is also a ram disk.
Code:
# $FreeBSD: branches/2020Q2/databases/mysql80-server/files/my.cnf.sample.in 469734 2018-05-12 15:35:25Z mmokhi $

[client]
port                            = 3311
socket                          = /tmp/mysqlssd.sock

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

[mysqld]
user                            = mysql
port                            = 3311
socket                          = /tmp/mysqlssd.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysqlSSD
tmpdir                          = /tmp/mysqlSSD_tmpdir:/var/db/mysqlSSD_tmpdir
slave-load-tmpdir               = /tmp/mysqlSSD_tmpdir:/var/db/mysqlSSD_tmpdir
secure-file-priv                = /var/db/mysqlSSD_secure
log-bin                         = /tmp/mysqlSSD-bin

pid-file    = /var/db/mysqlSSD/abcde.org.pid
plugin-dir    = /usr/local/lib/mysql/plugin
log-error    = /var/db/mysqlSSD/abcde.org.err

log-output                      = TABLE
relay-log-recovery              = 1
slow-query-log                  = 1
server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
binlog_expire_logs_seconds      =86400
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
long_query_time                 = 0.8
innodb_buffer_pool_size         = 1G
innodb_data_home_dir            = /var/db/mysqlSSD
innodb_log_group_home_dir       = /var/db/mysqlSSD
innodb_data_file_path           = ibdata1:128M:autoextend
innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_log_at_timeout    =4
innodb_flush_log_at_trx_commit    =2
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

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

Is the process of creating a second running MySql server indeed possible by this approach? There does not seem to be much out there on how to do this, and what I have done was what I found in the

5.8.3 Running Multiple MySQL Instances on Unix​

that I found in the MySql Version 8 documentation. It is also not clear how to figure out what failed in the precede routine for mysqlssd. There was also a post that suggest the same approach. Any help would be appreciated.[/file]
 

covacat

Daemon

Reaction score: 515
Messages: 1,040

this is wrong

if [ ! -d "${mysql_dbdir}/mysqlSSD/." ];
remove SSD
also you have to create the tmp and secure directories if you have not done that
there is a syntax error also
: ${mysql_confdir"/usr/local/etc/mysql"}

Code:
# sockstat |grep mysql
mysql    mysqld     56839 31 tcp4   10.1.1.1:3311         *:*
mysql    mysqld     56839 32 stream /tmp/mysqlssd.sock
mysql    mysqld     49035 28 tcp4   10.1.1.1:3306         *:*
mysql    mysqld     49035 29 stream /tmp/mysql.sock
 
OP
E

eepete

New Member

Reaction score: 8
Messages: 10

Thanks to VladiBFG and covacat ! Your suggestions got me close enough that starting the mysqlssd server that it was able to write to the error file and I could go from there.
Because I wanted the tmpdir's to be in /tmp, which is a ram disk, I had to create the /tmp/mysqlSSD_tmpdir directory in /tmp.
The final rc.d file mysqlssd-server looks like this (for others finding this thread). I suspect some of the lines I added to force some variables would be handled correctly by the conditional code that allows for changes to be made via the /etc/rc.conf. I'll play with that soon enough.
Code:
#!/bin/sh

# PROVIDE: mysqlssd
# REQUIRE: LOGIN
# KEYWORD: shutdown
#

# Add the following line to /etc/rc.conf to enable mysql:
# mysql_enable (bool):    Set to "NO" by default.
#    Set it to "YES" to enable MySQL.
# mysql_dbdir (str):    Default to "/var/db/mysql"
#    Base database directory.
# mysql_confdir (str):    Default to "/usr/local/etc/mysql"
#    Base configuration directory.
# mysql_optfile (str):    Server-specific option file.
#    Set it in the rc.conf or default behaviour of
#    `mysqld_safe` itself, will be picking
#    ${mysql_confdir}/my.cnf if it exists.
# mysql_pidfile (str):    Custum PID file path and name.
#    Default to "${mysql_dbdir}/${hostname}.pid".
# mysql_args (str):    Custom additional arguments to be passed
#    to mysqld_safe (default empty).
#

. /etc/rc.subr

name="mysqlssd"
rcvar=mysqlssd_enable

# Added these to force database directory and config file location. Config file is called myssd.cnf
mysql_dbdir="/var/db/mysqlSSD"
mysql_confdir="/usr/local/etc/mysql"
mysql_cnf="myssd.cnf"
mysql_optfile="${mysql_confdir}/${mysql_cnf}"

# try to force the socket and port number when creating the auth tables.
mysql_extra="--socket=/tmp/mysqlssd.sock --port=3311"

load_rc_config $name

: ${mysqlssd_enable="NO"}
: ${mysql_dbdir="/var/db/mysqlSSD"}
: ${mysql_confdir="/usr/local/etc/mysql"}
if [ -f "${mysql_confdir}/${mysql_cnf}" ]; then
: ${mysql_optfile="${mysql_confdir}/${mysql_cnf}"}
elif [ -f "${mysql_dbdir}/${mysql_cnf}" ]; then
: ${mysql_optfile="${mysql_dbdir}/${mysql_cnf}"}
fi
if [ ! -z "${mysql_optfile}" ]; then
mysql_extra="--defaults-extra-file=${mysql_optfile}"
fi

mysql_user="mysql"
: ${hostname:=`/bin/hostname`}
pidfile=${mysql_pidfile:-"${mysql_dbdir}/${hostname}.pid"}
command="/usr/sbin/daemon"
command_args="-c -f /usr/local/bin/mysqld_safe ${mysql_extra} --basedir=/usr/local --datadir=${mysql_dbdir} --pid-file=${pidfile} --user=${mysql_user} ${mysql_args}  "
procname="/usr/local/libexec/mysqld"
start_precmd="${name}_prestart"
start_postcmd="${name}_poststart"
mysqld_init="${procname}"
mysqld_init_args="${mysql_extra} --initialize-insecure --basedir=/usr/local --datadir=${mysql_dbdir} --user=${mysql_user}"

mysqlssd_create_auth_tables()
{
# Debug print to see make sure the variables passed are OK
    eval $mysqld_init $mysqld_init_args >/dev/null 2>/dev/null
}

mysqlssd_prestart()
{
    if [ ! -d "/tmp/mysqlSSD_tmpdir" ]; then
        eval /bin/mkdir /tmp/mysqlSSD_tmpdir
        eval /bin/chmod 770 /tmp/mysqlSSD_tmpdir
        eval /usr/sbin/chown mysql /tmp/mysqlSSD_tmpdir
        eval /usr/bin/chgrp mysql /tmp/mysqlSSD_tmpdir
    fi
    if [ ! -d "${mysql_dbdir}/mysql/." ]; then
        mysqlssd_create_auth_tables || return 1
    fi
    return 0
}

mysqlssd_poststart()
{
    local timeout=15
    while [ ! -f "${pidfile}" -a ${timeout} -gt 0 ]; do
    timeout=$(( timeout - 1 ))
    sleep 1
    done
    return 0
}

run_rc_command "$1"
The top part of the myssd.cnf file looked like this:
Code:
# $FreeBSD: branches/2020Q2/databases/mysql80-server/files/my.cnf.sample.in 469734 2018-05-12 15:35:25Z mmokhi $

[client]
port                            = 3311
socket                          = /tmp/mysqlssd.sock

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

[mysqld]
user                            = mysql
port                            = 3311
socket                          = /tmp/mysqlssd.sock
bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /var/db/mysqlSSD
tmpdir                          = /tmp/mysqlSSD_tmpdir:/var/db/mysqlSSD_tmpdir
slave-load-tmpdir               = /tmp/mysqlSSD_tmpdir:/var/db/mysqlSSD_tmpdir
secure-file-priv                = /var/db/mysqlSSD_secure
log-bin                         = mysql-bin
pid-file    = /var/db/mysqlSSD/abcde.org.pid
plugin-dir    = /usr/local/lib/mysql/plugin
log-error    = /var/db/mysqlSSD/abcde.org.err
And the InnoDB entires were:
Code:
innodb_data_home_dir            = /var/db/mysqlSSD
innodb_log_group_home_dir       = /var/db/mysqlSSD
I had a bit of a rough time getting into the new server, but then figured out that mysql --port=3311 --host=127.0.0.1 would get me in. I had mysqlssd_args="--skip-grant-tables --skip-networking" in my /etc/rc.conf so I could then enter accounts and passwords for the new MySql instance server.

So now a bit of table shuffling and my edge server will be able to use a ram disk based mysql for the tables that get a lot of real-time data entered into them, and I can use the SSD disk drive for larger tables that rarely change and either have configuration information or various static data in them. Example of this are address database, hazardous maternal information, or the USDA medical database.
On boot, the server has to load up the tables into the ram disk server. Loading everything currently takes about 90 seconds. Shifting the somewhat static content tables to SSD moves about 95% of the "volume" of information out of the ram disk, which should allow the boot delay to fill the ram disk down to less than 10 seconds. The static information will be growing by a factor of almost 50 in the near future.
The mobile sever has about 900 GB of spare space on the M2 3 MLC SSD (two lanes). With out this change, the SSD MTB (Maximum TeraBytes written) would max out in about a year or so. With this change, it's good for at least 15 years. This same technique will be used on the hosted "real"/"big" server at a real site. It has three 4TB 2 MLC SSD drives on it. File system is ZFS. It gets a lot more data than the edge severs. It should last about 20 years instead of 3 years. A final warning: If you do this, be sure you understand the nature of your information. This works for IoT because it looses value quickly with time, so if the server crashes and you loose some real time data, it will get filled in later when the sever comes back up. Using a ram disk based MySql for a transactional system (read e-commerce, banking, medical records) is a very bad idea. My hosted FreeBSD server stays up for 2 years and goes down only when I update the OS and packages. The mobile server has a boot controller built into the custom power supply so the only way to crash the systems is to loose battery power. When cranking the engine, the batter can drop down to 7 volts with no problems. Note that how the MySql in ram disk works and how the the power supply with integrated controller works are both big topics in and of themselves.

It seems like an interesting challenge to transition to SSDs for systems that have a lot of transactions, especially if cost is important as it is in the mobile environment.
Thank you again for your help.
 

richardtoohey2

Aspiring Daemon

Reaction score: 345
Messages: 673

Glad you got it working, but there seems to be a lot of "moving parts" and complexity (hard to update and keep running 24x7). Not sure about lasting 15-20 years. But that's just my 2c and it's your project, time, etc. so hope it all works as you expect. :)

I thought the answer would be "you can't" run two instances of mysqld or "don't do that" - but TIL it's possible so that's interesting.
 
OP
E

eepete

New Member

Reaction score: 8
Messages: 10

Glad you got it working, but there seems to be a lot of "moving parts" and complexity (hard to update and keep running 24x7). Not sure about lasting 15-20 years. But that's just my 2c and it's your project, time, etc. so hope it all works as you expect. :)

I thought the answer would be "you can't" run two instances of mysqld or "don't do that" - but TIL it's possible so that's interesting.
There are a number of times when a system is basically competing with a refrigerator w/r/t/ lifetime. While it seems unlikely that over say 15 years there would not be a reason to change it out due to better technology, things like portable radios and breathing apparatus have an expectation of 10 years of life. I've done more hardware than software, so a 15 to 20 year target should mean 10 years is very doable. I've got a number of things around the house I designed that have been running for 15+ years.
The update problem is real, I'm working on software tools that should make that easier to do. As for running 24x7, for hosted that's a piece of cake. For mobile, the key is the power supply controller that can do things like "Keep the system up for 60 minutes after ignition loss or until the apparatus is on shore power". The power supply can take down the system gracefully, which does an incremental backup-up of all tables that have had a change to them since boot time. It also handles not draining the battery enough that you can't start the vehicle, and dealing with only running when ambient temperatures are within a user settable range. A cron job could be set up to force the database incremental update periodically, like once a week.
Two other odd "expectation of the end users" pieces are you teach everyone that all technology will fail at some time, and you must have a back-up plan or two. This means you can take things down for maintenance every few years. And in that regard, it would be OK to do something like "change out the fans every 5 years". The Big sever fans have been running for 8 years no with no issues, just have to know what to get.
Multiple instances of MySql is document in the official MySql site, and I found 1 example of a "How to run N servers at the same time" on the Net. I've learned a lot about the whole "rc.d" environment, but am still a newbie. Last time I did sh scripts was in the late 1970's. Running MySql in a ram disk required a lot of work, and a program to handle all the cases like boot, load, restore, incremental backup, full backup and be able to deal with changes to table schema too. I see a lot of systems going to SSD or SD card (like all the Raspberry PIs). You can put a big SD card in there, but, you have to do the math. If you have about 50 to 100 commits per second at peak times and each commit could be a 16 KB block, doing that for 5 years can really eat up write cycles. The transition from spinning rust that stops moving to electrons on a deteriorating life raft of metal that slowly leak off is a big one.
One last comment, I've already written too much... on the hardware front, other than the 100 Watt boost-buck power supply I designed, the 12V only input motherboard with an 8 core Atom running 2.2 GHz, 15 MB L3, two 2.4 GHz memory lanes, ECC, 4 ethernet, M2 slot, and more SATA if you want them that idles running FreeBSD V13.0 at 20 watts and has a peak power of 48 watts makes this possible. It's more computer power than a high end PC just 8 years ago.
Thanks for the kind thoughts for success on the project, I'm pretty optimistic about it.
 
OP
E

eepete

New Member

Reaction score: 8
Messages: 10

Update: I have this working just fine after upgrading to 13.0-RELEASE
and MySql Ver 8.0.26 for FreeBSD13.0 on amd64 (Source distribution)

Next step is work on software to do updates to all the source files and two databases from a single distributed tar file, and software to transfer tables between the two databases and/or extract from one big legacy database back-up file and distribute the tables to the correct database (Ram Disk or SSD).

The change from "Write all you want, but I'll only spin so long" and "Read all you want, but you can only write me so many times" is big. While hardware advances move slowly, the changes always quietly steer software and communications architectures. Even after 50 years it can take me by surprise.
 
Top