Constructing MARIADB master-slave cluster mirroring using Dockerfile in Docker

Source: Internet
Author: User
Tags anonymous flush gpg docker run

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.