项目作者: w303972870

项目描述 :
基于alpine:latest的源码安装全量的mariadb
高级语言: Shell
项目地址: git://github.com/w303972870/mariadb-galera.git
创建时间: 2018-09-28T09:47:07Z
项目社区:https://github.com/w303972870/mariadb-galera

开源协议:

下载


该版本是mariadb源码编译安装组建比较全的一个版本,专门为了使用xtrabackup集群而出现的,已经内置了sphinx、MariaDB-Galera和xtrabackup等,该镜像比较大,找时间再进行精简

  1. docker pull w303972870/mariadb-galera
软件 版本
mariadb 10.3.10

启动命令示例:为了初始化必须指定一个默认的root密码MYSQL_ROOT_PASSWORD

  1. docker run -dit -p 3306:3306 -p 4567:4567 -p 4568:4568 -p 4444:4444 -v /data/mariadb/:/data/ -v /etc/localtime:/etc/localtime -e MYSQL_ROOT_HOST=192.168.12.% -e MYSQL_ROOT_PASSWORD=123456 -e WSREP_NEW_CLUSTER=yes docker.io/w303972870/mariadb-galera
变量 解释
MYSQL_ROOT_HOST ‘root’@’${MYSQL_ROOT_HOST}’ IDENTIFIED BY ‘${MYSQL_ROOT_PASSWORD}
MYSQL_ROOT_PASSWORD ‘root’@’${MYSQL_ROOT_HOST}’ IDENTIFIED BY ‘${MYSQL_ROOT_PASSWORD}
MYSQL_RANDOM_ROOT_PASSWORD 随机生成一个root密码
MYSQL_INITDB_SKIP_TZINFO 不导入时区信息到MYSQL中,默认不使用该参数进行导入时区
MYSQL_DATABASE 默认创建一个数据库
MYSQL_USER 新建一个用户
MYSQL_PASSWORD 新建用户的密码
WSREP_NEW_CLUSTER 集群用参,作为集群时,启动集群第一台时该值传入yes其他的传入no或者不传入即可(如果是全新的数据库,需要mysql_install_db先不要用这个参数,等初始化数据库后结束容器再加上这个参数启动第一台)
SKIP_INIT_SCRIPT 当存在这个参数任意值时不执行初始化等操作直接执行启动mysql命令,同时上面这些参数也会失效(集群非第一台机器启动命令要用到)

使用集群必须再加上映射端口4567,4568,4444

启动之后,需要mysql -h 127.0.0.1 -p3306 -u root连接容器mysql后重新配置访问限制,例如:

  1. GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.12.%' IDENTIFIED BY '123456' WITH GRANT OPTION;
  2. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
  3. GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.17.0.%' IDENTIFIED BY '123456' WITH GRANT OPTION;

数据目录:/data/database/

bin-log目录:/data/bin-logs/

日志目录:/data/logs/

默认配置文件:/data/etc/my.cnf

已开放3306端口

我的/data/mariadb-galera/目录结构,注意目录权限,无权限会影响启动

  1. /data/mariadb-galera/
  2. ├── database
  3. ├── galera
  4. ├── bin-logs
  5. ├── docker-entrypoint-initdb.d
  6. ├── etc
  7. └── my.cnf
  8. └── logs

docker-entrypoint-initdb.d目录内可以放置.sh,.sql,.sq.gz三类文件,作用可以看docker-entrypoint.sh

  1. for f in /data/docker-entrypoint-initdb.d/*; do
  2. case "$f" in
  3. *.sh) echo "$0: running $f"; . "$f" ;;
  4. *.sql) echo "$0: running $f"; execute < "$f"; echo ;;
  5. *.sql.gz) echo "$0: running $f"; gunzip -c "$f" | execute; echo ;;
  6. *) echo "$0: ignoring $f" ;;
  7. esac
  8. echo
  9. done

使用步骤

  1. 全新数据库需要初始化,第一台机器先初始化数据库:(先不要使用-e WSREP_NEW_CLUSTER=yes 选项,
    同时my.cnf先将wsrep_on=0之后执行以下命令启动容器,初始化数据库),初始化完成后需要GRANT设置访问权限
  1. docker run -dit -p 3306:3306 --net host -p 4567:4567 -p 4568:4568 -p 4444:4444 -v /data/mariadb-galera/:/data/
  2. -v /etc/localtime:/etc/localtime -e MYSQL_ROOT_HOST=172.17.0.% -e MYSQL_ROOT_PASSWORD=123456 docker.io/w303972870/mariadb-galera
  1. 将第1步启动起来的容器结束掉,将my.cnf中的wsrep_on=1,重新启动第一台机器要加上 -e WSREP_NEW_CLUSTER=yes
  1. docker run -dit -p 3306:3306 --net host -p 4567:4567 -p 4568:4568 -p 4444:4444 -v /data/mariadb-galera/:/data/
  2. -v /etc/localtime:/etc/localtime -e MYSQL_ROOT_HOST=172.17.0.% -e MYSQL_ROOT_PASSWORD=123456 -e WSREP_NEW_CLUSTER=yes docker.io/w303972870/mariadb-galera
  1. 第二台机器不需要初始化数据库,直接启动加入集群即可,但是由于没有数据库,所以需要加上-e SKIP_INIT_SCRIPT=yes跳过初始化脚本
  1. docker run -dit -p 3306:3306 --net host -p 4567:4567 -p 4568:4568 -p 4444:4444 -v /data/mariadb-galera/:/data/
  2. -v /etc/localtime:/etc/localtime -e MYSQL_ROOT_HOST=172.17.0.% -e MYSQL_ROOT_PASSWORD=123456 -e SKIP_INIT_SCRIPT=yes docker.io/w303972870/mariadb-galera
  1. 第四台机器及以后多机器启动,跟第二台启动一样

附上一个简单的my.cnf配置文件

  1. [client]
  2. port= 3306
  3. socket = /data/database/mysql.sock
  4. default-character-set = utf8mb4
  5. [mysqld]
  6. port= 3306
  7. socket = /data/database/mysql.sock
  8. tmpdir = /tmp/
  9. basedir=/usr/
  10. datadir = /data/database/
  11. pid-file = /data/database/mysql.pid
  12. user = mysql
  13. server-id = 1
  14. bind-address=0.0.0.0
  15. init-connect = 'SET NAMES utf8mb4'
  16. character-set-server = utf8mb4
  17. skip-name-resolve
  18. #skip-networking
  19. max_connections= 16384
  20. open_files_limit = 65535
  21. table_open_cache = 1024
  22. max_allowed_packet= 100M
  23. binlog_cache_size = 1M
  24. max_heap_table_size = 8M
  25. tmp_table_size = 128M
  26. read_buffer_size = 2M
  27. read_rnd_buffer_size = 8M
  28. sort_buffer_size = 8M
  29. join_buffer_size = 8M
  30. query_cache_limit = 2M
  31. ft_min_word_len = 4
  32. log_bin = /data/bin-logs/mysql-bin
  33. binlog_format = ROW
  34. expire_logs_days = 30
  35. log_error = /data/logs/mysql-error.log
  36. slow_query_log = 1
  37. long_query_time = 1
  38. slow_query_log_file = /data/logs/mysql-slow.log
  39. general_log = 1
  40. log_output = FILE
  41. general_log_file = /data/logs/general.log
  42. performance_schema = 0
  43. #lower_case_table_names = 1
  44. skip-external-locking
  45. default_storage_engine=innodb
  46. #default-storage-engine = MyISAM
  47. innodb_open_files = 500
  48. innodb_write_io_threads = 4
  49. #####################################################################33
  50. skip_external_locking
  51. lower_case_table_names=1
  52. event_scheduler=0
  53. back_log=512
  54. default-time-zone='+8:00'
  55. max_connect_errors=99999
  56. max_length_for_sort_data = 16k
  57. wait_timeout=172800
  58. interactive_timeout=172800
  59. net_buffer_length = 8K
  60. table_open_cache_instances = 2
  61. table_definition_cache = 4096
  62. thread_cache_size = 512
  63. explicit_defaults_for_timestamp=ON
  64. #******************************* MyISAM Specific options ****************************
  65. key_buffer_size = 256M
  66. bulk_insert_buffer_size = 8M
  67. myisam_sort_buffer_size = 64M
  68. myisam_max_sort_file_size = 10G
  69. myisam_repair_threads = 1
  70. myisam_recover_options=force
  71. # ***************************** INNODB Specific options ****************************
  72. innodb_file_per_table = 1
  73. innodb_strict_mode = 1
  74. innodb_flush_method = O_DIRECT
  75. innodb_checksum_algorithm=crc32
  76. innodb_autoinc_lock_mode=2
  77. innodb_flush_log_at_trx_commit=0
  78. #### Buffer Pool options
  79. innodb_buffer_pool_size = 4G
  80. innodb_buffer_pool_instances = 2
  81. innodb_max_dirty_pages_pct = 90
  82. innodb_adaptive_flushing = ON
  83. innodb_flush_neighbors = 0
  84. innodb_lru_scan_depth = 4096
  85. #innodb_change_buffering = inserts
  86. innodb_old_blocks_time = 1000
  87. #### galera
  88. wsrep_on=1
  89. wsrep_provider="/usr/lib64/galera/libgalera_smm.so"
  90. wsrep_cluster_name=eric_cluster
  91. wsrep_provider_options="pc.ignore_sb=true;pc.ignore_quorum=true;gcache.size=5G;gcache.name=/data/galera/galera.cache"
  92. wsrep_cluster_address="gcomm://192.168.12.2,192.168.12.3,192.168.12.4"
  93. wsrep_slave_threads=16
  94. wsrep_node_name='manager-node'
  95. wsrep_node_address='192.168.12.2'
  96. wsrep_certify_nonPK=1
  97. wsrep_max_ws_rows=131072
  98. wsrep_max_ws_size=1073741824
  99. wsrep_debug=0
  100. wsrep_convert_LOCK_to_trx=0
  101. wsrep_retry_autocommit=1
  102. wsrep_auto_increment_control=1
  103. wsrep_drupal_282555_workaround=0
  104. wsrep_causal_reads=0
  105. #wsrep_notify_cmd=/usr/local/bin/wsrep-notify.sh
  106. wsrep_sst_method=mariabackup
  107. wsrep_sst_auth="root:123456"
  108. #### audit
  109. #server_audit_file_path = /data/logs/server_audit.log
  110. [mysqldump]
  111. quick
  112. max_allowed_packet = 2G
  113. default-character-set = utf8mb4
  114. [myisamchk]
  115. key_buffer = 512M
  116. sort_buffer_size = 512M
  117. read_buffer = 8M
  118. write_buffer = 8M
  119. [mysqlhotcopy]
  120. interactive-timeout
  121. [mysqld_safe]
  122. open-files-limit = 65535
  123. [mysql]
  124. no-auto-rehash
  125. show-warnings
  126. prompt="\\u@\\h : \\d \\r:\\m:\\s> "
  127. default-character-set = utf8mb4

MariaDB [(none)]> show ENGINES;

Engine Support Transactions XA Savepoints
MRG_MyISAM YES NO NO NO
MyISAM YES NO NO NO
BLACKHOLE YES NO NO NO
MEMORY YES NO NO NO
Aria YES NO NO NO
ARCHIVE YES NO NO NO
CSV YES NO NO NO
PERFORMANCE_SCHEMA YES NO NO NO
SEQUENCE YES YES NO YES
InnoDB DEFAULT YES YES YES
FEDERATED YES YES NO YES
SPHINX YES NO NO NO

附上一个脚本,用来配置my.cnf中的wsrep_notify_cmd=/data/etc/wsrep-notify.sh,用来创建wsrep概要,
生成’membeship’和’status’两张表,记录所有的成员和节点状态变化

  1. #!/bin/sh -eu
  2. USER=root
  3. PSWD=123456
  4. HOST=192.168.12.2
  5. PORT=3306
  6. SCHEMA="wsrep"
  7. MEMB_TABLE="$SCHEMA.membership"
  8. STATUS_TABLE="$SCHEMA.status"
  9. BEGIN="
  10. SET wsrep_on=0;
  11. DROP SCHEMA IF EXISTS $SCHEMA; CREATE SCHEMA $SCHEMA;
  12. CREATE TABLE $MEMB_TABLE (
  13. idx INT UNIQUE PRIMARY KEY,
  14. uuid CHAR(40) UNIQUE, /* node UUID */
  15. name VARCHAR(32), /* node name */
  16. addr VARCHAR(256) /* node address */
  17. ) ENGINE=MEMORY;
  18. CREATE TABLE $STATUS_TABLE (
  19. size INT, /* component size */
  20. idx INT, /* this node index */
  21. status CHAR(16), /* this node status */
  22. uuid CHAR(40), /* cluster UUID */
  23. prim BOOLEAN /* if component is primary */
  24. ) ENGINE=MEMORY;
  25. BEGIN;
  26. DELETE FROM $MEMB_TABLE;
  27. DELETE FROM $STATUS_TABLE;
  28. "
  29. END="COMMIT;"
  30. configuration_change()
  31. {
  32. echo "$BEGIN;"
  33. local idx=0
  34. for NODE in $(echo $MEMBERS | sed s/,/\ /g)
  35. do
  36. echo "INSERT INTO $MEMB_TABLE VALUES ( $idx, "
  37. # Don't forget to properly quote string values
  38. echo "'$NODE'" | sed s/\\//\',\'/g
  39. echo ");"
  40. idx=$(( $idx + 1 ))
  41. done
  42. echo "
  43. INSERT INTO $STATUS_TABLE
  44. VALUES($idx, $INDEX,'$STATUS', '$CLUSTER_UUID', $PRIMARY);
  45. "
  46. echo "$END"
  47. }
  48. status_update()
  49. {
  50. echo "
  51. SET wsrep_on=0;
  52. BEGIN;
  53. UPDATE $STATUS_TABLE SET status='$STATUS';
  54. COMMIT;
  55. "
  56. }
  57. COM=status_update # not a configuration change by default
  58. while [ $# -gt 0 ]
  59. do
  60. case $1 in
  61. --status)
  62. STATUS=$2
  63. shift
  64. ;;
  65. --uuid)
  66. CLUSTER_UUID=$2
  67. shift
  68. ;;
  69. --primary)
  70. [ "$2" = "yes" ] && PRIMARY="1" || PRIMARY="0"
  71. COM=configuration_change
  72. shift
  73. ;;
  74. --index)
  75. INDEX=$2
  76. shift
  77. ;;
  78. --members)
  79. MEMBERS=$2
  80. shift
  81. ;;
  82. esac
  83. shift
  84. done
  85. # Undefined means node is shutting down
  86. if [ "$STATUS" != "Undefined" ]
  87. then
  88. $COM | mysql -B -u$USER -p$PSWD -h$HOST -P$PORT
  89. fi
  90. exit 0