Results 1 to 4 of 4

Thread: OS 13.2 - MYSQL MARIADB - Using multiple instances

  1. #1
    Join Date
    Jun 2008
    Location
    South-West France
    Posts
    1,024

    Default OS 13.2 - MYSQL MARIADB - Using multiple instances

    I plan to use mysql with only instance database so I can stop one database for maintenance without stopping every thing.

    When one reads through the my.cnf config file, it is not clear if we must use at the same time a single database mysql plus any instances mysqld2 (for app1), mysqld3 (for app2), and so on.

    Or may I get rid of the mysqld process and start only mysqld2, mysqld3, ....
    I have try that but could not start any instance. Of course I have configured a systemd service file.

    Or may I use mysqld1 as mysql database pointing to mysql as first mandatory database and then add more instance when necessary.

    here code for mysqld@.service
    Code:
    [Unit]
    Description=MySQL Multi Server for instance %i
    After=syslog.target
    After=network.target
    
    [Service]
    User=mysql
    Group=mysql
    Type=forking
    ExecStart=/usr/bin/mysqld_multi --defaults-file=/etc/mysql/cfg_%i.cnf  --log=/var/log/mysql/mysql_multi.log --verbose  start %i
    ExecStop=/usr/bin/mysqld_multi --log=/var/log/mysql/mysql_multi.log  stop %i
    #Restart=always
    #RestartSec=5
    PrivateTmp=true
    
    [Install]
    WantedBy=multi-user.target
    my config file in /etc/my.cnf
    Code:
    [client]
    port       = 3306
    socket     = /bdd/bd_1/sock/mysql_cli.sock
    
    [mysqld_multi]
    mysqld     = /usr/bin/mysqld_safe
    mysqladmin = /usr/bin/mysqladmin
    log        = /var/log/mysqld_multi.log
    
    [mysqld2]
    port       = 63332
    datadir    = /bdd/bd_2/app2
    pid-file   = /bdd/bd_2/app2/mysqld.pid
    socket     = /bdd/bd_2/app2/mysql.sock
    user       = mysql
    
    [mysqld3]
    port       = 63333
    datadir    = /bdd/bd_3/app3
    pid-file   = /bdd/bd_2/app3/mysqld.pid
    socket     = /bdd/bd_2/app3/mysql.sock
    user       = mysql
    
    !includedir /etc/my.cnf.d
    Must I run mysql_secure_installation for each instance mysqld2, mysqld3, ...

    I have follow this link : http://www.anntoin.com/Blog/Tutorial...Instances.html

    This is somehow a general question to help me to continue in my installation.


    I would like some advise.

    Any help is welcome.
    Thanks for helping. JCD
    __________

    server leap 15-- ASUS g75vw KDE leap 42.3 -- ASUS g750JZ KDE leap 42.3 -- acer aspire s13 win 10 home -- HP Omen win 10 home - scan EPSON V500 - Brother HL2250DN - Samsung CLP-325W

  2. #2
    Join Date
    Oct 2011
    Location
    Germany (Ore Mountains)
    Posts
    427

    Default Re: OS 13.2 - MYSQL MARIADB - Using multiple instances

    Hi,

    your service file uses different configuration files instead of /etc/my.cnf:

    Code:
    --defaults-file=/etc/mysql/cfg_%i.cnf
    IMHO you should either create these files or omit them in ExecStart.

    Hendrik

  3. #3
    Join Date
    Jun 2008
    Location
    South-West France
    Posts
    1,024

    Thumbs up Re: OS 13.2 - MYSQL MARIADB - Using multiple instances

    Quote Originally Posted by hendwolt View Post
    Hi,

    your service file uses different configuration files instead of /etc/my.cnf:

    Code:
    --defaults-file=/etc/mysql/cfg_%i.cnf
    IMHO you should either create these files or omit them in ExecStart.

    Hendrik
    Yes.
    I have change my mysqld@.service to ignore specific *.cfg file.

    Thank you very much.
    Thanks for helping. JCD
    __________

    server leap 15-- ASUS g75vw KDE leap 42.3 -- ASUS g750JZ KDE leap 42.3 -- acer aspire s13 win 10 home -- HP Omen win 10 home - scan EPSON V500 - Brother HL2250DN - Samsung CLP-325W

  4. #4
    Join Date
    Jun 2008
    Location
    South-West France
    Posts
    1,024

    Thumbs up Re: OS 13.2 - MYSQL MARIADB - Using multiple instances

    This thread is closed.

    After following the remark from hendwolt, reading this thread http://www.percona.com/blog/2014/08/...ances-of-mysql
    and doing some more correction mariadb with multiple instance is running.

    In summary:

    Every things are configured in /etc/my.cnf
    Accordingly, nothing particular is configured in /etc/systemd/system/mysqld@.service file.
    My database files, socket files, pid files are located in /bdd/bd_1/project1, /bdd/bd_2/project2...
    The owner of all files is set to mysql.
    The log files are located in /var/log/mysql/project1, /var/log/mysql/project2....
    The owner of all files is set to mysql.
    In the config file /etc/my.cnf, rename [mysqld] to [mysqld0] so you get rid of the single database.
    Don't forget to open port for mysql on the firewall

    Enabling instances ( instances start at bootup )
    Code:
    systemctl enable mysqld@1
    systemctl enable mysqld@2
    systemctl enable mysqld@3
    systemctl enable mysqld@4
    here is /etc/systemd/system/mysqld@.service
    Code:
    [Unit]
    Description=MySQL Multi Server for instance %i
    After=syslog.target
    After=network.target
    
    [Service]
    User=mysql
    Group=mysql
    Type=forking
    ExecStart=/usr/bin/mysqld_multi start %i
    ExecStop=/usr/bin/mysqld_multi stop %i
    Restart=always
    RestartSec=5
    PrivateTmp=true
    
    [Install]
    WantedBy=multi-user.target
    here is /etc/my.cnf
    Code:
    # The following options will be passed to all MariaDB clients
    [client]
    port       = 3306
    socket     = /bdd/client/sock
    
    [mysqld0]
    innodb_file_format=Barracuda
    innodb_file_per_table=ON
    
    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id    = 1
    
    bind-address = 0.0.0.0
    
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    
    [mysqld_multi]
    mysqld     = /usr/bin/mysqld_safe
    mysqladmin = /usr/bin/mysqladmin
    log        = /var/log/mysql/mysqld_multi.log
    
    # WARNING
    # --------
    # If you uncomment mysqld1 than make absolutely sure, that database mysql,
    # configured above, is not started.  This may result in corrupted data!
    #
    # renamed as [mysqld0]
    #
    
    [mysqld1]
    bind-address = 0.0.0.0
    port       = 63331
    datadir    = /bdd/bd_1/mysql
    pid-file   = /bdd/bd_1/mysql/sock/mysqld.pid
    socket     = /bdd/bd_1/mysql/sock/mysql.sock
    user       = mysql
    general_log         = 1
    general_log_file    = /var/log/mysql/mysql/mysql.log
    log-error             = /var/log/mysql/mysql/mysql_error.log
    log-warnings   = 0
    symbolic-links    = 0
    
    [mysqld2]
    bind-address = 0.0.0.0
    port       = 63332
    datadir    = /bdd/bd_2/postfix
    pid-file   = /bdd/bd_2/postfix/sock/mysqld.pid
    socket     = /bdd/bd_2/postfix/sock/mysql.sock
    ### user       = postfix
    user       = mysql
    general_log         = 1
    general_log_file    = /var/log/mysql/postfix/postfix.log
    log-error             = /var/log/mysql/postfix/postfix_error.log
    log-warnings   = 0
    symbolic-links    = 0
    
    [mysqld3]
    bind-address = 0.0.0.0
    port       = 63333
    datadir    = /bdd/bd_3/bacula
    pid-file   = /bdd/bd_3/bacula/sock/mysqld.pid
    socket     = /bdd/bd_3/bacula/sock/mysql.sock
    ### user       = bacula
    user       = mysql
    general_log         = 1
    general_log_file    = /var/log/mysql/bacula/bacula.log
    log-error             = /var/log/mysql/bacula/bacula_error.log
    log-warnings   = 0
    symbolic-links    = 0
    
    [mysqld4]
    bind-address = 0.0.0.0
    port       = 63334
    datadir    = /bdd/bd_4/fxa
    pid-file   = /bdd/bd_4/fxa/sock/mysqld.pid
    socket     = /bdd/bd_4/fxa/sock/mysql.sock
    ### user       = fxa
    user       = mysql
    general_log         = 1
    general_log_file    = /var/log/mysql/fxa/fxa.log
    log-error             = /var/log/mysql/fxa/fxa_error.log
    log-warnings   = 0
    symbolic-links    = 0
    
    !includedir /etc/my.cnf.d
    Here status for instance 3 :
    Code:
    linux-cc07:~ # systemctl -l status mysqld@3
    mysqld@3.service - MySQL Multi Server for instance 3
       Loaded: loaded (/etc/systemd/system/mysqld@.service; enabled)
       Active: active (running) since Mon 2015-03-16 15:16:09 CET; 1h 32min ago
     Main PID: 1920 (mysqld_safe)
       CGroup: /system.slice/system-mysqld.slice/mysqld@3.service
               ├─1920 /bin/sh /usr/bin/mysqld_safe --bind-address=0.0.0.0 --port=63333 --datadir=/bdd/bd_3/bacula --pid-file=/bdd/bd_3/bacula/sock/mysqld.pid --socket=/bdd/bd_3/bacula/sock/mysql.sock --user=mysql --general_log=1 --general_log_file=/var/log/mysql/bacula/bacula.log --log-error=/var/log/mysql/bacula/bacula_error.log --log-warnings=6 --symbolic-links=0
               └─2128 /usr/sbin/mysqld --basedir=/usr --datadir=/bdd/bd_3/bacula --plugin-dir=/usr/lib64/mysql/plugin --bind-address=0.0.0.0 --general_log=1 --general_log_file=/var/log/mysql/bacula/bacula.log --log-warnings=6 --symbolic-links=0 --log-error=/var/log/mysql/bacula/bacula_error.log --pid-file=/bdd/bd_3/bacula/sock/mysqld.pid --socket=/bdd/bd_3/bacula/sock/mysql.sock --port=63333
    Thanks for helping. JCD
    __________

    server leap 15-- ASUS g75vw KDE leap 42.3 -- ASUS g750JZ KDE leap 42.3 -- acer aspire s13 win 10 home -- HP Omen win 10 home - scan EPSON V500 - Brother HL2250DN - Samsung CLP-325W

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •