Constructing MARIADB master-slave cluster mirroring using Dockerfile in Docker

First, the use of methods

1, separate MySQL mode
Docker run-d \
-E Timezone=asia/shanghai \
-V/DATA/MARIADB-MASTER:/DATA/MARIADB \
-E mysql_root_password=lookback \
-P 3,306:3,306 \
mariadb:10.0.21
2, Mariadb MASTER
Docker run-d \
-E Timezone=asia/shanghai \
-V/DATA/MARIADB-MASTER:/DATA/MARIADB \
-E mysql_root_password=lookback \
-E master=1 \
-E server_id=1 \
-E replication_password=lookback \
-P 3,306:3,306 \
mariadb:10.0.21
3, Mariadb SLAVE
#情景1 No SQL Import
Docker run-d \
-E Timezone=asia/shanghai \
-V/DATA/MARIADB-SLAVE:/DATA/MARIADB \
-E mysql_root_password=lookback \
-E slave=1 \
-E server_id=3 \
-E replication_password=lookback \
-E master_log_file=mysql-bin.000003 \
-E master_log_pos=326 \
-E master_port=3306 \
-E master_host=172.17.0.2 \
-P 3,307:3,306 \
mariadb:10.0.21

#情景2 to import and synchronize with SQL files first
Docker run-d \
-E Timezone=asia/shanghai \
-V/DATA/MARIADB-SLAVE:/DATA/MARIADB \
-E mysql_root_password=lookback \
-E slave=1 \
-E server_id=3 \
-E replication_password=lookback \
-E database_file=database.sql \
-E master_log_file=mysql-bin.000003 \
-E master_log_pos=326 \
-E master_port=3306 \
-E master_host=172.17.0.2 \
-P 3,307:3,306 \
mariadb:10.0.21
#需要把database. sql files stored in advance in the/data/mariadb-slave directory
Ii. introduction of various parameters

After filling ...
Third, first look at MARIADB Master

#启动MariaDB主


[root@legion135/docker/image/mariadb]# Docker run-d \


-E mysql_root_password=lookback \


-E master=1 \


-E server_id=1 \


-E replication_password=lookback \


-P 3,306:3,306 \


mariadb:10.0.21


35e0f6fd2fd08b4e503d4d01f49797179f91f43d85acf05f06049eb430880362


#查询MariaDB主的ip地址


[root@legion135/docker/image/mariadb]# Docker exec-it 35e ifconfig | Sed-rn '/^[^ \t]/{n;s/(^[^]*). *addr: ([^]*). */\1=\2/p} '


eth0=172.17.0.2


#启动MariaDB的SLAVE


[root@legion135/docker/image/mariadb]# Docker run-d \


-E mysql_root_password=lookback \


-E slave=1 \


-E server_id=3 \


-E replication_password=lookback \


-E master_log_file=mysql-bin.000003 \


-E master_log_pos=326 \


-E master_port=3306 \


-E master_host=172.17.0.2 \


-P 3,307:3,306 \


mariadb:10.0.21


20b79e28546b222e863af83056474f5784a73e1f25668d47bc21db5a24c57b67


#查看SLAVE的ip地址


[root@legion135/docker/image/mariadb]# Docker exec-it 20b ifconfig | Sed-rn '/^[^ \t]/{n;s/(^[^]*). *addr: ([^]*). */\1=\2/p} '


eth0=172.17.0.3


#查看SLAVE的状态


[root@legion135/docker/image/mariadb]# Yum install-y-Q MySQL


Delta RPMs reduced 1.5 M of updates to 126 K (91% saved)


[root@legion135/docker/image/mariadb]# mysql-uroot-plookback-h172.17.0.3


Welcome to the MARIADB Monitor. Commands End With; or \g.


Your mariadb Connection ID is 6


Server version:10.0.21-mariadb-wsrep-log Source Distribution, wsrep_25.10.r4144

Copyright (c), 2015, Oracle, MARIADB Corporation Ab and others.

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

MARIADB [(None)]> show SLAVE Status\g


1. Row ***************************


Slave_io_state:waiting for Master to send event


master_host:172.17.0.2


Master_user:replication


master_port:3306


Connect_retry:60


master_log_file:mysql-bin.000003


read_master_log_pos:326


relay_log_file:mysql-relay-bin.000002


relay_log_pos:535


relay_master_log_file:mysql-bin.000003


Slave_io_running:yes


Slave_sql_running:yes


replicate_do_db:


replicate_ignore_db:


Replicate_do_table:


Replicate_ignore_table:


Replicate_wild_do_table:


Replicate_wild_ignore_table:


last_errno:0


Last_error:


skip_counter:0


exec_master_log_pos:326


relay_log_space:832


Until_condition:none


Until_log_file:


until_log_pos:0


Master_ssl_allowed:no


Master_ssl_ca_file:


Master_ssl_ca_path:


Master_ssl_cert:


Master_ssl_cipher:


Master_ssl_key:


seconds_behind_master:0


Master_ssl_verify_server_cert:no


last_io_errno:0


Last_io_error:


last_sql_errno:0


Last_sql_error:


Replicate_ignore_server_ids:


Master_server_id:1


MASTER_SSL_CRL:


Master_ssl_crlpath:


Using_gtid:no


Gtid_io_pos:


1 row in Set (0.00 sec)

MARIADB [(none)]> \q
Bye
[root@legion135/docker/image/mariadb]#
Four, and then look at the launch of MARIADB master and slave

[root@legion135/docker/image/mariadb]# Docker exec-it 35e0f6fd2fd0 cat/tmp/mysql-first-time.sql
--What ' s done with this file shouldn ' t be replicated
--or products like Mysql-fabric won ' t work
SET @ @SESSION. sql_log_bin=0;

DELETE from Mysql.user;
Grant all privileges in *.* to ' root ' @ '% ' identified by ' Lookback ' with Grant OPTION;
--grant all privileges in *.* to ' root ' @ ' 127.0.0.1 ' identified by ' Lookback ' with GRANT OPTION;
--grant all privileges in *.* to ' root ' @ ' localhost ' identified by ' Lookback ' with GRANT OPTION;
DROP DATABASE IF EXISTS test;
GRANT REPLICATION SLAVE on *.* to ' REPLICATION ' @ ' identified by ' lookback ';
START MASTER;
FLUSH privileges;
[root@legion135/docker/image/mariadb]# Docker exec-it 20b79e28546b cat/tmp/mysql-first-time.sql
--What ' s done with this file shouldn ' t be replicated
--or products like Mysql-fabric won ' t work
SET @ @SESSION. sql_log_bin=0;

DELETE from Mysql.user;


Grant all privileges in *.* to ' root ' @ '% ' identified by ' Lookback ' with Grant OPTION;


--grant all privileges in *.* to ' root ' @ ' 127.0.0.1 ' identified by ' Lookback ' with GRANT OPTION;


--grant all privileges in *.* to ' root ' @ ' localhost ' identified by ' Lookback ' with GRANT OPTION;


DROP DATABASE IF EXISTS test;


GRANT REPLICATION SLAVE on *.* to ' REPLICATION ' @ ' 172.17.0.2 ' identified by ' lookback ';


Change MASTER to master_host= ' 172.17.0.2 ', master_port=3306,master_user= ' replication ', master_password= ' lookback ', Master_log_file= ' mysql-bin.000003 ', master_log_pos= 326;


START SLAVE;


FLUSH privileges;


[root@legion135/docker/image/mariadb]# Docker exec-it 35e0f6fd2fd0 Cat/data/mariadb/general.log


Mysqld, Version:10.0.21-mariadb-wsrep-log (Source distribution, wsrep_25.10.r4144). Started with:


TCP port:3306 Unix Socket:/tmp/mysql.sock


Time Id Command Argument


151202 8:36:22 4 Connect replication@172.17.0.3 as anonymous on


4 Query SET NAMES utf8mb4


4 Query SELECT Unix_timestamp ()


151202 8:36:23 4 Query show VARIABLES like ' server_id '


4 Query SET @master_heartbeat_period = 1799999979520


4 Query SET @master_binlog_checksum = @ @global. binlog_checksum


4 Query SELECT @master_binlog_checksum


4 Query SET @mariadb_slave_capability =4


4 Query SELECT binlog_gtid_pos (' mysql-bin.000003 ', 326)


4 binlog Dump Log: ' mysql-bin.000003 ' pos:326


[root@legion135/docker/image/mariadb]# Docker exec-it 20b79e28546b Cat/data/mariadb/general.log


Mysqld, Version:10.0.21-mariadb-wsrep-log (Source distribution, wsrep_25.10.r4144). Started with:


TCP port:3306 Unix Socket:/tmp/mysql.sock


Time Id Command Argument


151202 8:36:22 4 Connect out replication@172.17.0.2:3306


151202 8:55:37 6 Connect root@172.17.0.1 as anonymous on


6 Query SELECT @ @version_comment Limit 1


151202 8:55:47 6 Query show SLAVE STATUS


151202 8:56:54 6 Quit


[root@legion135/docker/image/mariadb]#


Finally, the Dockerfile file.


From centos-x86_64-minimal:6.7

Maintainer from www.dwhd.org by Lookback (mondeolove@gmail.com)

ARG Path=/bin: $PATH
ARG mariadb_version=10.0.21

ENV install_dir=/usr/local/mariadb \
Data_dir=/data/mariadb

ADD my.cnf/etc/my.cnf

RUN rpm--import/etc/pki/rpm-gpg/rpm-gpg-key-* && \


Yum install-y libxml2-devel lz4-devel openssl-devel libpcap nmap lsof socat wget cmake which && \


Groupadd--system MySQL && \


Useradd--system--gid mysql mysql && \


Mkdir-p $DATA _dir &&


Chown-r mysql.mysql $DATA _dir &&


Wget-c https://downloads.mariadb.org/interstitial/mariadb-galera-${mariadb_version}/source/mariadb-galera-${ Mariadb_version}.tar.gz && \


Wget-c http://www.phontron.com/kytea/download/kytea-0.4.7.tar.gz && \


Tar XF kytea-0.4.7.tar.gz && \


CD kytea-0.4.7/&& \


./configure && \


Make-j $ (awk '/processor/{i++}end{print i} '/proc/cpuinfo) && \


Make install && CD ... && \


Tar XF mariadb-galera-${mariadb_version}.tar.gz && \


CD mariadb-${mariadb_version}/&& \


CMake. -dcmake_install_prefix= $INSTALL _dir \


-dmysql_datadir= $DATA _dir \


-dwith_ssl=system \


-dwith_innobase_storage_engine=1 \


-dwith_archive_storage_engine=1 \


-dwith_blackhole_storage_engine=1 \


-dwith_sphinx_storage_engine=1 \


-dwith_aria_storage_engine=1 \


-dwith_xtradb_storage_engine=1 \


-dwith_partition_storage_engine=1 \


-dwith_federatedx_storage_engine=1 \


-dwith_myisam_storage_engine=1 \


-dwith_perfschema_storage_engine=1 \


-dwith_extra_charsets=all \


-dwith_embedded_server=1 \


-dwith_readline=1-dwith_zlib=system \


-dwith_libwrap=0 \


-dextra_charsets=all \


-denabled_local_infile=1 \


-dmysql_unix_addr=/tmp/mysql.sock \


-ddefault_charset=utf8 \


-DDEFAULT_COLLATION=UTF8_GENERAL_CI \


-dwith_wsrep=1 \


-dwith_innodb_disallow_writes=1 && \


Make-j $ (awk '/processor/{i++}end{print i} '/proc/cpuinfo) && \


Make install && CD ... && \


/BIN/RM-RF/{kytea-0.4.7.tar.gz,mariadb-$MARIADB _version,mariadb-galera-$MARIADB _version.tar.gz,kytea-0.4.7, mariadb.conf} && \


/BIN/RM-RF $DATA _dir/*.err

ENV path=/usr/local/mariadb/bin: $PATH \
MAX_CONNECTIONS=100 \
port=3306 \
max_allowed_packet=16m \
query_cache_size=16m \
query_cache_type=1 \
innodb_buffer_pool_size=128m \
innodb_log_file_size=48m \
innodb_flush_method= \
innodb_old_blocks_time=1000 \
Innodb_flush_log_at_trx_commit=1 \
sync_binlog=0 \
General_log=on

ADD run.sh/run.sh
RUN chmod +x/run.sh

VOLUME ["/usr/local/mariadb", "/data/mariadb"]

Expose 3306

entrypoint ["/run.sh"]

CMD ["Mysqld"]
#!/bin/bash
#########################################################################
# File Name:run.sh
# Author:lookback
# email:admin#dwhd.org
# Version:
# Created time:2015 Year December 02 Wednesday 15:54 27 seconds
#########################################################################

Set-e
Install_dir=/usr/local/mariadb
Date_dir=/data/mariadb

If [-N "$TIMEZONE"]; Then
Rm-rf/etc/localtime && \
Ln-s/usr/share/zoneinfo/$TIMEZONE/etc/localtime
Fi

[-D "$DATE _dir"] | | Mkdir-p $DATE _dir

Sed-ri "s@^ (port). *@\1=${port}@"/etc/my.cnf


Sed-ri "s@^ (basedir). *@\1=${install_dir}@"/etc/my.cnf


Sed-ri "s@^ (datadir). *@\1=${data_dir}@"/etc/my.cnf


Sed-ri "s@^ (pid-file). *@\1=${data_dir}/mysql.pid@"/etc/my.cnf


Sed-ri "s@^ (max_connections). *@\1=${max_connections}@"/etc/my.cnf


Sed-ri "s@^ (max_allowed_packet). *@\1=${max_allowed_packet}@"/etc/my.cnf


Sed-ri "s@^ (query_cache_size). *@\1=${query_cache_size}@"/etc/my.cnf


Sed-ri "s@^ (query_cache_type). *@\1=${query_cache_type}@"/etc/my.cnf


Sed-ri "s@^ (innodb_log_file_size). *@\1=${innodb_log_file_size}@"/etc/my.cnf


Sed-ri "s@^ (sync_binlog). *@\1=${sync_binlog}@"/etc/my.cnf


Sed-ri "s@^ (innodb_buffer_pool_size). *@\1=${innodb_buffer_pool_size}@"/etc/my.cnf


Sed-ri "s@^ (innodb_old_blocks_time). *@\1=${innodb_old_blocks_time}@"/etc/my.cnf


Sed-ri "s@^ (innodb_flush_log_at_trx_commit). *@\1=${innodb_flush_log_at_trx_commit}@"/etc/my.cnf


Sed-ri "s@^ (general_log\s). *@\1= ${general_log}@"/etc/my.cnf

If [-N "$INNODB _flush_method"]; Then
Sed-ri "/^innodb_flush_log_at_trx_commit/a Innodb_flush_method=${innodb_flush_method}"/etc/my.cnf
Fi

if ["${1:0:1}" = '-']; Then
Set--Mysqld "$@"
Fi

If ["$" = ' mysqld ']; Then
If [-Z "$MYSQL _root_password"]; Then
echo >&2 ' error:missing Mysql_root_password '
echo >&2 ' Did you forget to Add-e mysql_root_password= ...? '
Exit 1
Fi

if [!-d "$DATA _dir/mysql"]; Then
Echo ' Running mysql_install_db ... '
CD $INSTALL _dir/&& $INSTALL _dir/scripts/mysql_install_db--user=mysql--datadir= "$DATA _dir" >/dev/null 2 >&1
Echo ' Finished mysql_install_db '

Tempsqlfile= '/tmp/mysql-first-time.sql '
Cat > "$tempSqlFile" <<-eof
--What ' s done with this file shouldn ' t be replicated
--or products like Mysql-fabric won ' t work
SET @ @SESSION. sql_log_bin=0;

            DELETE from Mysql.user;
            GRANT all privileges in *.* to ' root ' @ '% ' Identified by ' ${mysql_root_password} ' with GRANT OPTION;
           --grant all privileges in *.* to ' root ' @ 127.0.0.1 ' identified by ' ${mysql_root_password} ' with GRANT OPTION;
           --grant all privileges in *.* to ' root ' @ localhost ' identified by ' ${mysql_root_password} ' with GRANT OPTION;
            DROP DATABASE IF EXISTS test;
        EOF

        if [[1] =~ ^ ($MASTER | $SLAVE) $]; then
             [-Z "${replication_username}"] && replication_username= Replication
            [-Z "${slave_host}"] && slave_host= "%"

            If [-Z ${server_id}]; then
                 Echo >&2 ' error:  Missing server_id '
                 Echo >&2 '   Did you forget to Add-e server_id= ...? '
                exit 1
             elif ["${master}" = "1"]; Then
                server_id=1
            fi
             Sed-ri "s@^ (Server-id). *@\1=${server_id}@"/etc/my.cnf

If [-Z "$REPLICATION _password"]; Then
echo >&2 ' error:missing Replication_password '
echo >&2 ' Did you forget to Add-e replication_password= ...? '
Exit 1
Fi

echo "GRANT REPLICATION SLAVE on *.* to ' ${replication_username} ' @ ' ${master_host} ' identified by ' ${replication_password '; ' &gt;&gt; ' $tempSqlFile '


#cat &gt;&gt; "$tempSqlFile" &lt;&lt;-eof


# GRANT REPLICATION SLAVE on *.* to ' ${replication_username} ' @ ' ${master_host} ' identified by ' ${replication_password} ';


#--create USER ' ${replication_username} ' @ '% ' identified by ' ${replication_password} ';


#--grant Reload,lock tables,replication client,file on *.* to ' ${replication_username} ' @ '% ';


#--create USER ' ${replication_username} ' @ '% ' identified by ' ${replication_password} ';


#--grant Reload,lock tables,replication client,replication slave,file on *.* to ' ${replication_username} ' @ '% ';


#EOF


Fi

Fi
Fi

If ["$SLAVE" = 1]; Then
#if [[!] "1" =~ ^ ($SLAVE) $]]; Then
If [-Z "$MASTER _log_file"]; Then
echo >&2 ' error:missing master_log_file '
echo >&2 ' Did you forget to Add-e master_log_file= ...? '
Exit 1
Fi

If [-Z "$MASTER _log_pos"]; Then
echo >&2 ' error:missing master_log_pos '
echo >&2 ' Did you forget to Add-e master_log_pos= ...? '
Exit 1
Fi

If [-Z "$MASTER _port"]; Then
echo >&2 ' error:missing master_port '
echo >&2 ' Did you forget to Add-e master_port= ...? '
Exit 1
Fi

    If [-Z "$MASTER _host"]; then
        echo >&2 ' err or:  missing master_host '
        echo >&2 '   Did you forget to a Dd-e master_host= ...?
        exit 1
    fi

    If [-N "$DATABASE _file"] then
        if [!-F "$DATA _dir /$DATABASE _file "]; Then
            echo >&2 ' error:missing DATABASE _file '
            echo >&2 '   $DATABASE _ File must is a SQL file in $DATA _dir directory! '
            exit 1
         fi
        echo "source $DATA _dir/${database_file};" >> "$ Tempsqlfile "
    fi

echo "Change MASTER to master_host= ' ${master_host} ', master_port=${master_port},master_user= ' ${replication_username } ', master_password= ' ${replication_password} ', master_log_file= ' ${master_log_file} ', master_log_pos= ${MASTER_LOG_ POS}; ">> $tempSqlFile"
echo "START SLAVE" >> "$tempSqlFile"
Fi

If ["$MASTER" = "1"]; Then
echo "START MASTER" >> "$tempSqlFile"
Fi

echo "FLUSH Privileges" >> "$tempSqlFile"
Set--"$@"--init-file= "$tempSqlFile"
Chown-r mysql:mysql "$DATA _dir"

Exec "$@"
[Client]
Port = 3306
Socket =/tmp/mysql.sock
Default-character-set = Utf8mb4

[Mysqld]
Port = 3306
Socket =/tmp/mysql.sock

Basedir =/usr/local/mariadb
DataDir =/data/mariadb
Pid-file =/data/mariadb/mysql.pid
user = MySQL
Bind-address = 0.0.0.0
Server-id = 1

Init-connect = ' SET NAMES utf8mb4 '
Character-set-server = Utf8mb4

Skip-name-resolve
#skip-networking
Back_log = 300

max_connections = 1000
Max_connect_errors = 6000
Open_files_limit = 65535
Table_open_cache = 1024
Max_allowed_packet = 4M
Binlog_cache_size = 1M
Max_heap_table_size = 8M
Tmp_table_size = 128M

Read_buffer_size = 2M
Read_rnd_buffer_size = 8M
Sort_buffer_size = 8M
Join_buffer_size = 8M
Key_buffer_size = 256M

Thread_cache_size = 64

Query_cache_type = 1
Query_cache_size = 64M
Query_cache_limit = 2M

Ft_min_word_len = 4

Log_bin = Mysql-bin
Binlog_format = ROW
Expire_logs_days = 30

Log_error =/data/mariadb/mysql-error.log
Slow_query_log = 1
Long_query_time = 1
Slow_query_log_file =/data/mariadb/mysql-slow.log
General_log = On
Log_output = FILE
General_log_file =/data/mariadb/general.log

Performance_schema = 0

#lower_case_table_names = 1

Skip-external-locking

Default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
Innodb_open_files = 500
Innodb_buffer_pool_size = 1024M
Innodb_write_io_threads = 4
Innodb_read_io_threads = 4
innodb_thread_concurrency = 0
Innodb_purge_threads = 1
Innodb_flush_log_at_trx_commit = 2
Innodb_log_buffer_size = 2M
Innodb_log_file_size = 32M
Innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
Innodb_lock_wait_timeout = 120

Bulk_insert_buffer_size = 8M
Myisam_sort_buffer_size = 64M
Myisam_max_sort_file_size = 10G
Myisam_repair_threads = 1

Interactive_timeout = 28800
Wait_timeout = 28800

[Mysqldump]
Quick
Max_allowed_packet = 16M

[Myisamchk]
Key_buffer_size = 256M
Sort_buffer_size = 8M
Read_buffer = 4M
Write_buffer = 4M