【www.bbyears.com--mysql教程】
一、使用方法
1、单独MySQL模式
docker run -d \
-e TIMEZONE=Asia/Shanghai \
-v /data/mariadb-master:/data/mariadb \
-e MYSQL_ROOT_PASSWORD=lookback \
-p 3306:3306 \
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 3306:3306 \
mariadb:10.0.21
3、MariaDB SLAVE
#情景1 没有sql导入的情况
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 3307:3306 \
mariadb:10.0.21
#情景2 先用sql文件导入再同步
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 3307:3306 \
mariadb:10.0.21
#需要把database.sql文件存提前存放在/data/mariadb-slave目录
二、各种参数的介绍
后补。。。。。。
三、先来看看MariaDB主从
#启动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 3306:3306 \
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 3307:3306 \
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) 2000, 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]#
四、然后来看看MariaDB的MASTER和SLAVE的启动情况
[root@Legion135 /docker/image/mariadb]# docker exec -it 35e0f6fd2fd0 cat /tmp/mysql-first-time.sql
-- What"s done in 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 ON *.* TO "root"@"%" IDENTIFIED BY "lookback" WITH GRANT OPTION;
--GRANT ALL PRIVILEGES ON *.* TO "root"@"127.0.0.1" IDENTIFIED BY "lookback" WITH GRANT OPTION;
--GRANT ALL PRIVILEGES ON *.* 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 in 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 ON *.* TO "root"@"%" IDENTIFIED BY "lookback" WITH GRANT OPTION;
--GRANT ALL PRIVILEGES ON *.* TO "root"@"127.0.0.1" IDENTIFIED BY "lookback" WITH GRANT OPTION;
--GRANT ALL PRIVILEGES ON *.* 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]#
最后是Dockerfile文件
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年12月02日 星期三 15时54分27秒
#########################################################################
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 [ "$1" = "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 in 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 ON *.* TO "root"@"%" IDENTIFIED BY "${MYSQL_ROOT_PASSWORD}" WITH GRANT OPTION;
--GRANT ALL PRIVILEGES ON *.* TO "root"@"127.0.0.1" IDENTIFIED BY "${MYSQL_ROOT_PASSWORD}" WITH GRANT OPTION;
--GRANT ALL PRIVILEGES ON *.* 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}";" >> "$tempSqlFile"
#cat >> "$tempSqlFile" <<-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 "error: missing MASTER_HOST"
echo >&2 " Did you forget to add -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 be 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