mysql-mirror start script debian

haproxy.cfg

global
    log /dev/log local0
    log /dev/log local1 notice
    user haproxy
    group haproxy

defaults
    log global
    retries 2
    timeout connect 3000ms
    timeout server 5000ms
    timeout client 5000ms


listen mysql-cluster
    timeout connect 3000ms
    timeout server 6h
    timeout client 6h
    timeout tunnel 6h

    bind 127.0.0.1:3307
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mysql-slave 10.50.10.3:3307 check
    server mysql-master localhost:3306 backup check

mysql-mirror.cnf

[mysqld_safe]
socket		= /var/run/mysql-mirror-3307/mysqld.sock
nice		= 0
skip_log_error
syslog

[mysqld]
skip-name-resolve
user = mysql-mirror-3307
pid-file = /var/run/mysql-mirror-3307/mysqld.pid
socket = /var/run/mysql-mirror-3307/mysqld.sock
port = 3307
basedir	= /usr
datadir = /var/lib/mysql-mirror-3307
tmpdir = /tmp
lc_messages_dir	= /usr/share/mysql
lc_messages	= en_US
skip-external-locking
bind-address = 0.0.0.0
max_connections	= 100
connect_timeout	= 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size	= 16M
tmp_table_size = 32M
max_heap_table_size	= 32M
myisam_recover = BACKUP
key_buffer_size	= 128M
table_open_cache = 400
myisam_sort_buffer_size	= 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M
query_cache_limit = 128K
query_cache_size = 64M


;general_log_file = /var/log/mysql-mirror-3307/mysql.log
;general_log = 1
log_warnings = 2
slow_query_log_file	= /var/log/mysql-mirror-3307/mariadb-slow.log
long_query_time = 1
log_slow_verbosity	= query_plan

server-id = 33
relay_log = /var/log/mysql-mirror-3307/relay-bin
relay_log_index	= /var/log/mysql-mirror-3307/relay-bin.index
relay_log_info_file	= /var/log/mysql-mirror-3307/relay-bin.info
read_only

default_storage_engine	= myisam

innodb_buffer_pool_size	= 256M
innodb_log_buffer_size	= 8M
innodb_file_per_table	= 1
innodb_open_files	= 400
innodb_io_capacity	= 400
innodb_flush_method	= O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[isamchk]
key_buffer		= 16M

mysql-mirror.sh

#!/bin/bash
#
### BEGIN INIT INFO
# Provides:          mysql-mirror-3307
# Required-Start:    $remote_fs $syslog
# Required-Stop:     $remote_fs $syslog
# Should-Start:      $network $named $time
# Should-Stop:       $network $named $time
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: Start and stop the mysql-mirror-3307 database server daemon
# Description:       Controls the main MariaDB database server daemon "mysqld"
#                    and its wrapper script "mysqld_safe".
### END INIT INFO
#
set -e
set -u
${DEBIAN_SCRIPT_DEBUG:+ set -v -x}

test -x /usr/sbin/mysqld || exit 0

. /lib/lsb/init-functions

SELF=$(cd $(dirname $0); pwd -P)/$(basename $0)
CONF=/etc/mysql/mysql-mirror-3307

# priority can be overriden and "-s" adds output to stderr
ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql-mirror-3307 -i"

# Safeguard (relative paths, core dumps..)
cd /
umask 077

# mysqladmin likes to read /root/.my.cnf. This is usually not what I want
# as many admins e.g. only store a password without a username there and
# so break my scripts.
export HOME=/etc/mysql/

## Fetch a particular option from mysql's invocation.
#
# Usage: void mysqld_get_param option
mysqld_get_param() {
    /usr/sbin/mysqld --defaults-file="$CONF" --print-defaults \
        | tr " " "\n" \
        | grep -- "--$1" \
        | tail -n 1 \
        | cut -d= -f2
}

## Do some sanity checks before even trying to start mysqld.
sanity_checks() {
    # check for config file
    if [ ! -r "$CONF" ]; then
        log_warning_msg "$0: WARNING: "$CONF" cannot be read. See README.Debian.gz"
        echo                "WARNING: "$CONF" cannot be read. See README.Debian.gz" | $ERR_LOGGER
    fi

    # check for diskspace shortage
    datadir=`mysqld_get_param datadir`
    if LC_ALL=C BLOCKSIZE= df --portability $datadir/. | tail -n 1 | awk '{ exit ($4>4096) }'; then
        log_failure_msg "$0: ERROR: The partition with $datadir is too full!"
        echo                "ERROR: The partition with $datadir is too full!" | $ERR_LOGGER
        exit 1
    fi
}


#
# main()
#

case "${1:-''}" in
'start')
    sanity_checks;
    rundir=$(dirname `mysqld_get_param pid-file`)
    test -e "$rundir" || install -m 755 -o mysql-mirror-3307 -g root -d "$rundir"
    # Start daemon
    log_daemon_msg "Starting MariaDB database server (mysql-mirror-3307)" "mysqld"
    set +e
        start-stop-daemon \
                --start \
                --chdir `mysqld_get_param datadir` \
                --background \
                --oknodo \
                --pidfile `mysqld_get_param pid-file` \
                --exec "/usr/bin/mysqld_safe" \
                -- \
                    --defaults-file="$CONF"
        set -e
    log_end_msg 0
    ;;

'stop')
    # * As a passwordless mysqladmin (e.g. via ~/.my.cnf) must be possible
    # at least for cron, we can rely on it here, too. (although we have
    # to specify it explicit as e.g. sudo environments points to the normal
    # users home and not /root)
        log_daemon_msg "Stopping MariaDB database server (mysql-mirror-3307)" "mysqld"
        set +e
        start-stop-daemon \
                --stop \
                --oknodo \
                --retry 30 \
                --pidfile `mysqld_get_param pid-file`
        set -e
    log_end_msg 0
    ;;

'restart')
    set +e; $SELF stop; set -e
    $SELF start
    ;;

'reload'|'force-reload')
    log_daemon_msg "Reloading MariaDB database server (mysql-mirror-3307)" "mysqld"
    $SELF restart
    log_end_msg 0
    ;;

'status')
        set +e
        start-stop-daemon \
                --status \
                --oknodo \
                --pidfile `mysqld_get_param pid-file`
        set -e
    ;;

*)
    echo "Usage: $SELF start|stop|restart|reload|force-reload|status"
    exit 1
    ;;
esac

mysqld-repl-howto.txt

-- MASTER: create an user for replication
CREATE USER 'repl'@'%' IDENTIFIED BY 'Zkf5GIy-d47BTXt1-KR_.';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

-- MASTER: flush binlog and lock tables
FLUSH TABLES WITH READ LOCK;

-- SHELL: CREATE DATABASE DUMP (or use rsync to master node)

-- MASTER (in another connection): show status
SHOW MASTER STATUS;
-- Write down the file and posistion id

-- MASTER: exit all remaing connections (this will unlock tables)

--
-- ----------------------------------------------------------
-- SLAVE: restore database [if rsync was used, you do not need to restone anything ]

-- SLAVE: set master (got info from SHOW MASTER STATUS);

CHANGE MASTER TO
    MASTER_HOST='localhost',
    MASTER_USER='repl',
    MASTER_PORT=13306,
    MASTER_PASSWORD='slavepass',
    MASTER_LOG_FILE='log-1-bin.000001',
    MASTER_LOG_POS=595;

-- SLAVE: start replication [cmd: start slave]


-- ############################################################################
-------------------------------------------------------------------------------
-- ############################################################################


-- ----- SHOW SLAVE STATUS\G
-- -rsync -a --exclude='cel.*' 172.16.7.66:/var/lib/mysql/ /var/lib/mysql_remote/
-- see autossh [https://superuser.com/questions/37738/how-to-reliably-keep-an-ssh-tunnel-open]
Tue Mar 21 22:30:40 BRT 2017
-- MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |    71682 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
=============================

CHANGE MASTER TO
    MASTER_HOST='172.16.7.66',
    MASTER_USER='repl',
    MASTER_PORT=63306,
    MASTER_PASSWORD='Zkf5GIy-d47BTXt1-KR_.',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=71682;

172.16.7.66
iptables -t nat -A PREROUTING -p tcp -s 172.16.7.69 -d 172.16.7.66 --dport 63306 -j DNAT --to-destination 127.0.0.1:3306

sysctl -w net.ipv4.conf.eth0.route_localnet=1

-- http://turbogears.readthedocs.io/en/latest/cookbook/master-slave.html
-- http://unix.stackexchange.com/questions/111433/iptables-redirect-outside-requests-to-127-0-0-1


--     CREATE USER 'repl'@'%' IDENTIFIED BY 'GQhASL2WPr8y';

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 23843838 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

mysql>
------------
RESET SLAVE;

CHANGE MASTER TO
    MASTER_HOST='192.168.88.1',
    MASTER_USER='repl',
    MASTER_PORT=3306,
    MASTER_PASSWORD='GQhASL2WPr8y',
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=23843838;

NOTES.txt

# install initd script
update-rc.d mysql-mirror defaults

# create user for mirror (avoid mirror use main server and vice-versa)
useradd -s /usr/sbin/nologin -r -M mysql-mirror

# create the run directory
install -m 755 -o mysql-mirror -g root -d /var/run/mysqld-mirror

# create the log directory
install -m 755 -o mysql-mirror -g root -d /var/log/mysql-mirror

# rsync mysql directory (many times until ready for a lock)
rsync -e 'ssh -p 2222' -avP [email protected]:/var/lib/mysql/ /var/lib/mysql-mirror/ 
chown -R mysql-mirror:mysql-mirror /var/lib/mysql-mirror/

xinet_mysql_from_outside.conf

# description: Forward connections from port 63306 the 127.0.0.1:3306 port.
service mysql-from-outside
{
    disable         = no
    socket_type     = stream
    type        = UNLISTED
    wait            = no
    user            = nobody
    bind        = 172.16.7.66
    port        = 63306
    redirect    = localhost 3306
}
# 

Lembrar de: https://github.com/mysql/mysql-server/blob/4826e159b432c961290cdbe651da0cfe3d7b2539/support-files/mysql.server.sh

Lembrar de: https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html

LEMBRAR: https://www.digitalocean.com/community/tutorials/how-to-use-haproxy-to-set-up-mysql-load-balancing--3