DB 의 고가용성(HA)를 위해 이중화를 시키기 위함
애플리케이션 Backend 의 DB 컨테이너가 문제가 발생했을때 즉시, 복제가 되어있는 DB서버에 연결해야만 한다.
또한, CLI로 명령어를 일일이 타이핑하기는 불편하기 때문에 쉘(sh)파일로 작성해 자동으로 구성하게 만들고자 하였다.
Git : https://github.com/Leeeuijooo/mariadb-docker-master-slave.git
$ curl -fsSL https://get.docker.com -o get-docker.sh
$ sudo sh get-docker.sh
$ sudo curl -L "https://github.com/docker/compose/releases/download/1.27.4/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
$ sudo chmod +x /usr/local/bin/docker-compose
$ sudo ln -s /usr/local/bin/docker-compose /usr/bin/docker-compose
$ docker-compose -v
docker-compose version 1.27.4, build 40524192
root@user1:~/mariadb# ls
docker-compose.yml mariadb_root_password.txt msdb.sh slave
dump.sql master my_script.log slave_status.txt
ip_output.txt master_status.txt rm_msdb.sh
root@user1:~/mariadb# tree
.
├── docker-compose.yml
├── dump.sql
├── ip_output.txt
├── mariadb_root_password.txt
├── master
│ ├── config
│ │ └── my.cnf
│ ├── data
│ │ ├── aria_log.00000001
│ │ ├── aria_log_control
│ │ ├── ddl_recovery.log
│ │ ├── ib_buffer_pool
│ │ ├── ibdata1
│ │ ├── ib_logfile0
│ │ ├── ibtmp1
│ │ ├── multi-master.info
│ │ ├── mysql
│ │ │ ├── columns_priv.frm
│ │ │ ├── columns_priv.MAD
│ │ │ ├── columns_priv.MAI
│ │ │ ├── column_stats.frm
│ │ │ ├── column_stats.MAD
│ │ │ ├── column_stats.MAI
│ │ │ ├── db.frm
│ │ │ ├── db.MAD
│ │ │ ├── db.MAI
│ │ │ ├── db.opt
│ │ │ ├── event.frm
│ │ │ ├── event.MAD
│ │ │ ├── event.MAI
│ │ │ ├── func.frm
│ │ │ ├── func.MAD
│ │ │ ├── func.MAI
│ │ │ ├── general_log.CSM
│ │ │ ├── general_log.CSV
│ │ │ ├── general_log.frm
│ │ │ ├── global_priv.frm
│ │ │ ├── global_priv.MAD
│ │ │ ├── global_priv.MAI
│ │ │ ├── gtid_slave_pos.frm
│ │ │ ├── gtid_slave_pos.ibd
│ │ │ ├── help_category.frm
│ │ │ ├── help_category.MAD
│ │ │ ├── help_category.MAI
│ │ │ ├── help_keyword.frm
│ │ │ ├── help_keyword.MAD
│ │ │ ├── help_keyword.MAI
│ │ │ ├── help_relation.frm
│ │ │ ├── help_relation.MAD
│ │ │ ├── help_relation.MAI
│ │ │ ├── help_topic.frm
│ │ │ ├── help_topic.MAD
│ │ │ ├── help_topic.MAI
│ │ │ ├── index_stats.frm
│ │ │ ├── index_stats.MAD
│ │ │ ├── index_stats.MAI
│ │ │ ├── innodb_index_stats.frm
│ │ │ ├── innodb_index_stats.ibd
│ │ │ ├── innodb_table_stats.frm
│ │ │ ├── innodb_table_stats.ibd
│ │ │ ├── plugin.frm
│ │ │ ├── plugin.MAD
│ │ │ ├── plugin.MAI
│ │ │ ├── proc.frm
│ │ │ ├── proc.MAD
│ │ │ ├── proc.MAI
│ │ │ ├── procs_priv.frm
│ │ │ ├── procs_priv.MAD
│ │ │ ├── procs_priv.MAI
│ │ │ ├── proxies_priv.frm
│ │ │ ├── proxies_priv.MAD
│ │ │ ├── proxies_priv.MAI
│ │ │ ├── roles_mapping.frm
│ │ │ ├── roles_mapping.MAD
│ │ │ ├── roles_mapping.MAI
│ │ │ ├── servers.frm
│ │ │ ├── servers.MAD
│ │ │ ├── servers.MAI
│ │ │ ├── slow_log.CSM
│ │ │ ├── slow_log.CSV
│ │ │ ├── slow_log.frm
│ │ │ ├── tables_priv.frm
│ │ │ ├── tables_priv.MAD
│ │ │ ├── tables_priv.MAI
│ │ │ ├── table_stats.frm
│ │ │ ├── table_stats.MAD
│ │ │ ├── table_stats.MAI
│ │ │ ├── time_zone.frm
│ │ │ ├── time_zone_leap_second.frm
│ │ │ ├── time_zone_leap_second.MAD
│ │ │ ├── time_zone_leap_second.MAI
│ │ │ ├── time_zone.MAD
│ │ │ ├── time_zone.MAI
│ │ │ ├── time_zone_name.frm
│ │ │ ├── time_zone_name.MAD
│ │ │ ├── time_zone_name.MAI
│ │ │ ├── time_zone_transition.frm
│ │ │ ├── time_zone_transition.MAD
│ │ │ ├── time_zone_transition.MAI
│ │ │ ├── time_zone_transition_type.frm
│ │ │ ├── time_zone_transition_type.MAD
│ │ │ ├── time_zone_transition_type.MAI
│ │ │ ├── transaction_registry.frm
│ │ │ ├── transaction_registry.ibd
│ │ │ └── user.frm
│ │ ├── mysql-bin.000001
│ │ ├── mysql-bin.000002
│ │ ├── mysql-bin.000003
│ │ ├── mysql-bin.index
│ │ ├── performance_schema
│ │ │ └── db.opt
│ │ ├── sys
│ │ │ ├── db.opt
│ │ │ ├── host_summary_by_file_io.frm
│ │ │ ├── host_summary_by_file_io_type.frm
│ │ │ ├── host_summary_by_stages.frm
│ │ │ ├── host_summary_by_statement_latency.frm
│ │ │ ├── host_summary_by_statement_type.frm
│ │ │ ├── host_summary.frm
│ │ │ ├── innodb_buffer_stats_by_schema.frm
│ │ │ ├── innodb_buffer_stats_by_table.frm
│ │ │ ├── innodb_lock_waits.frm
│ │ │ ├── io_by_thread_by_latency.frm
│ │ │ ├── io_global_by_file_by_bytes.frm
│ │ │ ├── io_global_by_file_by_latency.frm
│ │ │ ├── io_global_by_wait_by_bytes.frm
│ │ │ ├── io_global_by_wait_by_latency.frm
│ │ │ ├── latest_file_io.frm
│ │ │ ├── memory_by_host_by_current_bytes.frm
│ │ │ ├── memory_by_thread_by_current_bytes.frm
│ │ │ ├── memory_by_user_by_current_bytes.frm
│ │ │ ├── memory_global_by_current_bytes.frm
│ │ │ ├── memory_global_total.frm
│ │ │ ├── metrics.frm
│ │ │ ├── processlist.frm
│ │ │ ├── ps_check_lost_instrumentation.frm
│ │ │ ├── schema_auto_increment_columns.frm
│ │ │ ├── schema_index_statistics.frm
│ │ │ ├── schema_object_overview.frm
│ │ │ ├── schema_redundant_indexes.frm
│ │ │ ├── schema_table_lock_waits.frm
│ │ │ ├── schema_table_statistics.frm
│ │ │ ├── schema_table_statistics_with_buffer.frm
│ │ │ ├── schema_tables_with_full_table_scans.frm
│ │ │ ├── schema_unused_indexes.frm
│ │ │ ├── session.frm
│ │ │ ├── session_ssl_status.frm
│ │ │ ├── statement_analysis.frm
│ │ │ ├── statements_with_errors_or_warnings.frm
│ │ │ ├── statements_with_full_table_scans.frm
│ │ │ ├── statements_with_runtimes_in_95th_percentile.frm
│ │ │ ├── statements_with_sorting.frm
│ │ │ ├── statements_with_temp_tables.frm
│ │ │ ├── sys_config.frm
│ │ │ ├── sys_config.MAD
│ │ │ ├── sys_config.MAI
│ │ │ ├── user_summary_by_file_io.frm
│ │ │ ├── user_summary_by_file_io_type.frm
│ │ │ ├── user_summary_by_stages.frm
│ │ │ ├── user_summary_by_statement_latency.frm
│ │ │ ├── user_summary_by_statement_type.frm
│ │ │ ├── user_summary.frm
│ │ │ ├── version.frm
│ │ │ ├── wait_classes_global_by_avg_latency.frm
│ │ │ ├── wait_classes_global_by_latency.frm
│ │ │ ├── waits_by_host_by_latency.frm
│ │ │ ├── waits_by_user_by_latency.frm
│ │ │ ├── waits_global_by_latency.frm
│ │ │ ├── x@0024host_summary_by_file_io.frm
│ │ │ ├── x@0024host_summary_by_file_io_type.frm
│ │ │ ├── x@0024host_summary_by_stages.frm
│ │ │ ├── x@0024host_summary_by_statement_latency.frm
│ │ │ ├── x@0024host_summary_by_statement_type.frm
│ │ │ ├── x@0024host_summary.frm
│ │ │ ├── x@0024innodb_buffer_stats_by_schema.frm
│ │ │ ├── x@0024innodb_buffer_stats_by_table.frm
│ │ │ ├── x@0024innodb_lock_waits.frm
│ │ │ ├── x@0024io_by_thread_by_latency.frm
│ │ │ ├── x@0024io_global_by_file_by_bytes.frm
│ │ │ ├── x@0024io_global_by_file_by_latency.frm
│ │ │ ├── x@0024io_global_by_wait_by_bytes.frm
│ │ │ ├── x@0024io_global_by_wait_by_latency.frm
│ │ │ ├── x@0024latest_file_io.frm
│ │ │ ├── x@0024memory_by_host_by_current_bytes.frm
│ │ │ ├── x@0024memory_by_thread_by_current_bytes.frm
│ │ │ ├── x@0024memory_by_user_by_current_bytes.frm
│ │ │ ├── x@0024memory_global_by_current_bytes.frm
│ │ │ ├── x@0024memory_global_total.frm
│ │ │ ├── x@0024processlist.frm
│ │ │ ├── x@0024ps_digest_95th_percentile_by_avg_us.frm
│ │ │ ├── x@0024ps_digest_avg_latency_distribution.frm
│ │ │ ├── x@0024ps_schema_table_statistics_io.frm
│ │ │ ├── x@0024schema_flattened_keys.frm
│ │ │ ├── x@0024schema_index_statistics.frm
│ │ │ ├── x@0024schema_table_lock_waits.frm
│ │ │ ├── x@0024schema_table_statistics.frm
│ │ │ ├── x@0024schema_table_statistics_with_buffer.frm
│ │ │ ├── x@0024schema_tables_with_full_table_scans.frm
│ │ │ ├── x@0024session.frm
│ │ │ ├── x@0024statement_analysis.frm
│ │ │ ├── x@0024statements_with_errors_or_warnings.frm
│ │ │ ├── x@0024statements_with_full_table_scans.frm
│ │ │ ├── x@0024statements_with_runtimes_in_95th_percentile.frm
│ │ │ ├── x@0024statements_with_sorting.frm
│ │ │ ├── x@0024statements_with_temp_tables.frm
│ │ │ ├── x@0024user_summary_by_file_io.frm
│ │ │ ├── x@0024user_summary_by_file_io_type.frm
│ │ │ ├── x@0024user_summary_by_stages.frm
│ │ │ ├── x@0024user_summary_by_statement_latency.frm
│ │ │ ├── x@0024user_summary_by_statement_type.frm
│ │ │ ├── x@0024user_summary.frm
│ │ │ ├── x@0024wait_classes_global_by_avg_latency.frm
│ │ │ ├── x@0024wait_classes_global_by_latency.frm
│ │ │ ├── x@0024waits_by_host_by_latency.frm
│ │ │ ├── x@0024waits_by_user_by_latency.frm
│ │ │ └── x@0024waits_global_by_latency.frm
│ │ └── testdb
│ │ ├── db.opt
│ │ ├── testtable.frm
│ │ └── testtable.ibd
│ └── mysql-init-files
│ └── create.sql
├── master_status.txt
├── msdb.sh
├── my_script.log
├── rm_msdb.sh
├── slave
│ ├── config
│ │ └── my.cnf
│ ├── data
│ │ ├── aria_log.00000001
│ │ ├── aria_log_control
│ │ ├── ddl_recovery.log
│ │ ├── ib_buffer_pool
│ │ ├── ibdata1
│ │ ├── ib_logfile0
│ │ ├── ibtmp1
│ │ ├── master.info
│ │ ├── multi-master.info
│ │ ├── mysql
│ │ │ ├── columns_priv.frm
│ │ │ ├── columns_priv.MAD
│ │ │ ├── columns_priv.MAI
│ │ │ ├── column_stats.frm
│ │ │ ├── column_stats.MAD
│ │ │ ├── column_stats.MAI
│ │ │ ├── db.frm
│ │ │ ├── db.MAD
│ │ │ ├── db.MAI
│ │ │ ├── db.opt
│ │ │ ├── event.frm
│ │ │ ├── event.MAD
│ │ │ ├── event.MAI
│ │ │ ├── func.frm
│ │ │ ├── func.MAD
│ │ │ ├── func.MAI
│ │ │ ├── general_log.CSM
│ │ │ ├── general_log.CSV
│ │ │ ├── general_log.frm
│ │ │ ├── global_priv.frm
│ │ │ ├── global_priv.MAD
│ │ │ ├── global_priv.MAI
│ │ │ ├── gtid_slave_pos.frm
│ │ │ ├── gtid_slave_pos.ibd
│ │ │ ├── help_category.frm
│ │ │ ├── help_category.MAD
│ │ │ ├── help_category.MAI
│ │ │ ├── help_keyword.frm
│ │ │ ├── help_keyword.MAD
│ │ │ ├── help_keyword.MAI
│ │ │ ├── help_relation.frm
│ │ │ ├── help_relation.MAD
│ │ │ ├── help_relation.MAI
│ │ │ ├── help_topic.frm
│ │ │ ├── help_topic.MAD
│ │ │ ├── help_topic.MAI
│ │ │ ├── index_stats.frm
│ │ │ ├── index_stats.MAD
│ │ │ ├── index_stats.MAI
│ │ │ ├── innodb_index_stats.frm
│ │ │ ├── innodb_index_stats.ibd
│ │ │ ├── innodb_table_stats.frm
│ │ │ ├── innodb_table_stats.ibd
│ │ │ ├── plugin.frm
│ │ │ ├── plugin.MAD
│ │ │ ├── plugin.MAI
│ │ │ ├── proc.frm
│ │ │ ├── proc.MAD
│ │ │ ├── proc.MAI
│ │ │ ├── procs_priv.frm
│ │ │ ├── procs_priv.MAD
│ │ │ ├── procs_priv.MAI
│ │ │ ├── proxies_priv.frm
│ │ │ ├── proxies_priv.MAD
│ │ │ ├── proxies_priv.MAI
│ │ │ ├── roles_mapping.frm
│ │ │ ├── roles_mapping.MAD
│ │ │ ├── roles_mapping.MAI
│ │ │ ├── servers.frm
│ │ │ ├── servers.MAD
│ │ │ ├── servers.MAI
│ │ │ ├── slow_log.CSM
│ │ │ ├── slow_log.CSV
│ │ │ ├── slow_log.frm
│ │ │ ├── tables_priv.frm
│ │ │ ├── tables_priv.MAD
│ │ │ ├── tables_priv.MAI
│ │ │ ├── table_stats.frm
│ │ │ ├── table_stats.MAD
│ │ │ ├── table_stats.MAI
│ │ │ ├── time_zone.frm
│ │ │ ├── time_zone_leap_second.frm
│ │ │ ├── time_zone_leap_second.MAD
│ │ │ ├── time_zone_leap_second.MAI
│ │ │ ├── time_zone.MAD
│ │ │ ├── time_zone.MAI
│ │ │ ├── time_zone_name.frm
│ │ │ ├── time_zone_name.MAD
│ │ │ ├── time_zone_name.MAI
│ │ │ ├── time_zone_transition.frm
│ │ │ ├── time_zone_transition.MAD
│ │ │ ├── time_zone_transition.MAI
│ │ │ ├── time_zone_transition_type.frm
│ │ │ ├── time_zone_transition_type.MAD
│ │ │ ├── time_zone_transition_type.MAI
│ │ │ ├── transaction_registry.frm
│ │ │ ├── transaction_registry.ibd
│ │ │ └── user.frm
│ │ ├── mysql-bin.000001
│ │ ├── mysql-bin.000002
│ │ ├── mysql-bin.000003
│ │ ├── mysql-bin.index
│ │ ├── performance_schema
│ │ │ └── db.opt
│ │ ├── relaylog.000001
│ │ ├── relaylog.000002
│ │ ├── relaylog.index
│ │ ├── relay-log.info
│ │ ├── sys
│ │ │ ├── db.opt
│ │ │ ├── host_summary_by_file_io.frm
│ │ │ ├── host_summary_by_file_io_type.frm
│ │ │ ├── host_summary_by_stages.frm
│ │ │ ├── host_summary_by_statement_latency.frm
│ │ │ ├── host_summary_by_statement_type.frm
│ │ │ ├── host_summary.frm
│ │ │ ├── innodb_buffer_stats_by_schema.frm
│ │ │ ├── innodb_buffer_stats_by_table.frm
│ │ │ ├── innodb_lock_waits.frm
│ │ │ ├── io_by_thread_by_latency.frm
│ │ │ ├── io_global_by_file_by_bytes.frm
│ │ │ ├── io_global_by_file_by_latency.frm
│ │ │ ├── io_global_by_wait_by_bytes.frm
│ │ │ ├── io_global_by_wait_by_latency.frm
│ │ │ ├── latest_file_io.frm
│ │ │ ├── memory_by_host_by_current_bytes.frm
│ │ │ ├── memory_by_thread_by_current_bytes.frm
│ │ │ ├── memory_by_user_by_current_bytes.frm
│ │ │ ├── memory_global_by_current_bytes.frm
│ │ │ ├── memory_global_total.frm
│ │ │ ├── metrics.frm
│ │ │ ├── processlist.frm
│ │ │ ├── ps_check_lost_instrumentation.frm
│ │ │ ├── schema_auto_increment_columns.frm
│ │ │ ├── schema_index_statistics.frm
│ │ │ ├── schema_object_overview.frm
│ │ │ ├── schema_redundant_indexes.frm
│ │ │ ├── schema_table_lock_waits.frm
│ │ │ ├── schema_table_statistics.frm
│ │ │ ├── schema_table_statistics_with_buffer.frm
│ │ │ ├── schema_tables_with_full_table_scans.frm
│ │ │ ├── schema_unused_indexes.frm
│ │ │ ├── session.frm
│ │ │ ├── session_ssl_status.frm
│ │ │ ├── statement_analysis.frm
│ │ │ ├── statements_with_errors_or_warnings.frm
│ │ │ ├── statements_with_full_table_scans.frm
│ │ │ ├── statements_with_runtimes_in_95th_percentile.frm
│ │ │ ├── statements_with_sorting.frm
│ │ │ ├── statements_with_temp_tables.frm
│ │ │ ├── sys_config.frm
│ │ │ ├── sys_config.MAD
│ │ │ ├── sys_config.MAI
│ │ │ ├── user_summary_by_file_io.frm
│ │ │ ├── user_summary_by_file_io_type.frm
│ │ │ ├── user_summary_by_stages.frm
│ │ │ ├── user_summary_by_statement_latency.frm
│ │ │ ├── user_summary_by_statement_type.frm
│ │ │ ├── user_summary.frm
│ │ │ ├── version.frm
│ │ │ ├── wait_classes_global_by_avg_latency.frm
│ │ │ ├── wait_classes_global_by_latency.frm
│ │ │ ├── waits_by_host_by_latency.frm
│ │ │ ├── waits_by_user_by_latency.frm
│ │ │ ├── waits_global_by_latency.frm
│ │ │ ├── x@0024host_summary_by_file_io.frm
│ │ │ ├── x@0024host_summary_by_file_io_type.frm
│ │ │ ├── x@0024host_summary_by_stages.frm
│ │ │ ├── x@0024host_summary_by_statement_latency.frm
│ │ │ ├── x@0024host_summary_by_statement_type.frm
│ │ │ ├── x@0024host_summary.frm
│ │ │ ├── x@0024innodb_buffer_stats_by_schema.frm
│ │ │ ├── x@0024innodb_buffer_stats_by_table.frm
│ │ │ ├── x@0024innodb_lock_waits.frm
│ │ │ ├── x@0024io_by_thread_by_latency.frm
│ │ │ ├── x@0024io_global_by_file_by_bytes.frm
│ │ │ ├── x@0024io_global_by_file_by_latency.frm
│ │ │ ├── x@0024io_global_by_wait_by_bytes.frm
│ │ │ ├── x@0024io_global_by_wait_by_latency.frm
│ │ │ ├── x@0024latest_file_io.frm
│ │ │ ├── x@0024memory_by_host_by_current_bytes.frm
│ │ │ ├── x@0024memory_by_thread_by_current_bytes.frm
│ │ │ ├── x@0024memory_by_user_by_current_bytes.frm
│ │ │ ├── x@0024memory_global_by_current_bytes.frm
│ │ │ ├── x@0024memory_global_total.frm
│ │ │ ├── x@0024processlist.frm
│ │ │ ├── x@0024ps_digest_95th_percentile_by_avg_us.frm
│ │ │ ├── x@0024ps_digest_avg_latency_distribution.frm
│ │ │ ├── x@0024ps_schema_table_statistics_io.frm
│ │ │ ├── x@0024schema_flattened_keys.frm
│ │ │ ├── x@0024schema_index_statistics.frm
│ │ │ ├── x@0024schema_table_lock_waits.frm
│ │ │ ├── x@0024schema_table_statistics.frm
│ │ │ ├── x@0024schema_table_statistics_with_buffer.frm
│ │ │ ├── x@0024schema_tables_with_full_table_scans.frm
│ │ │ ├── x@0024session.frm
│ │ │ ├── x@0024statement_analysis.frm
│ │ │ ├── x@0024statements_with_errors_or_warnings.frm
│ │ │ ├── x@0024statements_with_full_table_scans.frm
│ │ │ ├── x@0024statements_with_runtimes_in_95th_percentile.frm
│ │ │ ├── x@0024statements_with_sorting.frm
│ │ │ ├── x@0024statements_with_temp_tables.frm
│ │ │ ├── x@0024user_summary_by_file_io.frm
│ │ │ ├── x@0024user_summary_by_file_io_type.frm
│ │ │ ├── x@0024user_summary_by_stages.frm
│ │ │ ├── x@0024user_summary_by_statement_latency.frm
│ │ │ ├── x@0024user_summary_by_statement_type.frm
│ │ │ ├── x@0024user_summary.frm
│ │ │ ├── x@0024wait_classes_global_by_avg_latency.frm
│ │ │ ├── x@0024wait_classes_global_by_latency.frm
│ │ │ ├── x@0024waits_by_host_by_latency.frm
│ │ │ ├── x@0024waits_by_user_by_latency.frm
│ │ │ └── x@0024waits_global_by_latency.frm
│ │ └── testdb
│ │ └── db.opt
│ └── mysql-init-files
└── slave_status.txt
16 directories, 431 files
root@user1:~/mariadb# cat docker-compose.yml
version: "3.7"
services:
db_master:
image: mariadb:10.7.1
container_name: db_master
restart: always
environment:
MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mariadb_root_password
volumes:
- ./master/data:/var/lib/mysql
- ./master/config/:/etc/mysql/conf.d
- ./master/mysql-init-files/:/docker-entrypoint-initdb.d/
ports:
- "33306:3306"
secrets:
- mariadb_root_password
db_slave:
image: mariadb:10.7.1
container_name: db_slave
restart: always
environment:
MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mariadb_root_password
volumes:
- ./slave/data:/var/lib/mysql
- ./slave/config/:/etc/mysql/conf.d
- ./slave/mysql-init-files/:/docker-entrypoint-initdb.d/
ports:
- "43306:3306"
secrets:
- mariadb_root_password
depends_on:
- db_master
links:
- db_master
secrets:
mariadb_root_password:
file: ./mariadb_root_password.txt
[mysqld]
log-bin=mysql-bin
server-id=1
[mysqld]
log-bin=mysql-bin
server-id=2
relay-log=relaylog
log-slave_updates=1
#!/bin/bash
LOG_FILE=./my_script.log
exec &>>$LOG_FILE
docker-compose -f ./docker-compose.yml up -d
sleep 7
docker exec db_master mysql -u root -proot -e "CREATE DATABASE testdb;"
echo "create database Done."
sleep 1
docker exec db_master mysql -u root -proot -e "grant all privileges on testdb.* to user@'%' identified by 'user';"
echo "grant privileges to slave user Done."
sleep 1
docker exec db_master mysql -u root -proot -e "grant replication slave on *.* to 'user'@'%';"
echo "grant replication slave done"
sleep 1
docker exec db_master mysql -u root -proot -e "flush privileges;"
echo "flush privileges done"
sleep 1
docker exec db_master mysql -u root -proot -e "use testdb; create table testtable ( no int(8), primary key (no) ); "
echo "create table done"
sleep 1
#docker exec db_master mysql -u root -proot -e "exit"
#echo "exit mysql(db_master)"
#docker exec db_master mysql -u root -proot -e "exit"
#echo "exit container(slave)"
sleep 1
docker exec db_master mysqldump -uroot -proot testdb > dump.sql
echo "send mysql dump to local "
sleep 2
docker cp dump.sql db_slave:.
echo "copy dump.sql to db-slave"
sleep 2
docker exec db_slave mysql -u root -proot -e "CREATE DATABASE testdb;"
echo "start sync done"
sleep 1
docker exec db_slave mysql -u root -proot -e "exit"
echo "exit mysql(db_slave)"
sleep 1
docker exec db_slave mysql -u root -proot -e "exit"
echo "exit container(slave)"
sleep 1
docker exec db_slave mysql -u root -proot testdb < dump.sql
echo "slave get dump.sql"
echo "before change master to master query"
sleep 2
docker exec db_master mysql -u root -proot -e "show master status\G" > master_status.txt
echo "master_status file save finished"
sleep 2
docker exec db_master ip addr show eth0 > ip_output.txt
ip_address=$(cat ip_output.txt | awk '/inet / {print $2}' | cut -f1 -d'/')
echo $ip_address
sleep 2
search_string="mysql-bin"
file_name="master_status.txt"
if [ -n "$(grep "$search_string" "$file_name")" ]; then
master_log_File=$(grep "$search_string" "$file_name" | awk '{print $2}')
echo "success!"
echo "master_log_File is="$master_log_File
else
echo "문자열을 찾을 수 없삼"
fi
search_string="Position"
file_name="master_status.txt"
if [ -n "$(grep "$search_string" "$file_name")" ]; then
master_log_Position=$(grep "$search_string" "$file_name" | awk '{print $2}')
echo "success!"
echo "master_log_Position is="$master_log_Position
else
echo "문자열을 찾을 수 없삼"
fi
sleep 2
docker exec db_slave mysql -u root -proot -e "CHANGE MASTER TO MASTER_HOST='${ip_address}',
MASTER_USER='user', MASTER_PASSWORD='user', MASTER_LOG_FILE='${master_log_File}', MASTER_LOG_POS=${master_log_Position};"
echo "Finished connected"
sleep 2
docker exec db_slave mysql -u root -proot -e "start slave;"
echo "success!"
echo "start slave!"
sleep 2
docker exec db_slave mysql -u root -proot -e "show slave status\G" > slave_status.txt
echo "add slave_status.txt file"
sleep 2
search_string="Last_Errno"
file_name="slave_status.txt"
if [ -n "$(grep "$search_string" "$file_name")" ]; then
last_errno=$(grep "$search_string" "$file_name" | awk '{print $2}')
echo "success!"
echo "Last_Errno is = " $last_errno
echo "0 이면 master_slave 성공입니다."
else
echo "Failed Master-slave"
fi
search_string="Last_IO_Errno"
file_name="slave_status.txt"
if [ -n "$(grep "$search_string" "$file_name")" ]; then
last_io_errno=$(grep "$search_string" "$file_name" | awk '{print $2}')
echo "success!"
echo "Last_IO_Errno is = " $last_io_errno
echo "0 이면 master_slave 성공입니다."
else
echo "Failed Master-slave"
fi
sudo
작성은 안함!#!/bin/bash
rm -rf dump.sql
docker-compose -f docker-compose.yml down
rm -rf slave_status.txt
rm -rf master_status.txt
rm -rf ip_output.txt
rm -rf ./master/data/*
rm -rf ./slave/data/*
root@user1:~/mariadb# sh rm_msdb.sh
Stopping db_slave ... done
Stopping db_master ... done
Removing db_slave ... done
Removing db_master ... done
Removing network mariadb_default
******************************************************
root@user1:~/mariadb# sh msdb.sh
Creating network "mariadb_default" with the default driver
Creating db_master ... done
Creating db_slave ... done
create database Done.
grant privileges to slave user Done.
grant replication slave done
flush privileges done
create table done
send mysql dump to local
Preparing to copy...
Copying to container - 3.584kB
Successfully copied 3.584kB to db_slave:.
copy dump.sql to db-slave
start sync done
exit mysql(db_slave)
exit container(slave)
slave get dump.sql
before change master to master query
master_status file save finished
172.22.0.2
success!
master_log_File is=mysql-bin.000003
success!
master_log_Position is=1121
Finished connected
success!
start slave!
add slave_status.txt file
success!
Last_Errno is = 0
0 이면 master_slave 성공입니다.
success!
Last_IO_Errno is = 0
0 이면 master_slave 성공입니다.
# master
root@6a2081cfd688:/# mysql -u root -proot;
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.7.1-MariaDB-1:10.7.1+maria~focal-log mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> create database testdb2;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use testdb2;
Database changed
MariaDB [testdb2]> create table testdb2table ( no int(8), primary key (no) );
Query OK, 0 rows affected (0.003 sec)
MariaDB [testdb2]> insert into testdb2table values (1), (2);
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
#slave
root@46be316119b4:/# mysql -u root -proot;
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.7.1-MariaDB-1:10.7.1+maria~focal-log mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| testdb2 |
+--------------------+
6 rows in set (0.001 sec)
MariaDB [(none)]> use testdb2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [testdb2]> show tables;
+-------------------+
| Tables_in_testdb2 |
+-------------------+
| testdb2table |
+-------------------+
1 row in set (0.000 sec)
MariaDB [testdb2]> select * from testdb2table;
+----+
| no |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.000 sec)