Get mirror image

docker run -p 33006:3306 --name mysql-master    -v /data/docker/mysql/conf/:/etc/mysql/mysql.conf.d/:rw -v /data/docker/mysql/logs:/var/log/mysql/:rw -v /data/docker/mysql/data/:/var/lib/mysql/:rw -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

• Enter docker mysql
  docker exec -it mysql /bin/bash

• docker mysql authorization (pay attention to permission protection)
  GRANT ALL PRIVILEGES ON . TO 'root'@'%'WITH GRANT OPTION;

• Catalogue introduction
  basedir installation directory
  Default address of datadir file directory / var/lib/mysql/
  Error log error = / data / MySQL / logs / MySQL error.log default location / var/log/mysql/error.log [agent required]
  slow_query_log_file = /data/mysql/logs/mysql-slow.log

Master slave configuration

Main:
    server-id=1  
    log_bin=/var/log/mysql/binlog  
//From:
    server-id=2  
    log_bin=/var/log/mysql/binlog  
    innodb_file_per_table=ON  
    skip_name_resolve=ON  
    binlog-format=row  
    log-slave-updates=true  
    relay_log=/var/log/mysql/relay.log  

Profile comparison

[mysqld]
    These properties are for the MySQL Server, and you can use this file: /etc/mysql/mysql.conf.d/mysqld.cnf
[mysql]
    These properties are for the MySQL Client (command line), and you can use this file: /etc/mysql/conf.d/mysql.cnf
[mysqld_safe]
    These properties are for MySQL when you start it up in safe mode using mysql_safe and can be found in this file: /etc/mysql/conf.d/mysqld_safe_syslog.cnf

Master slave configuration

1. To configure cnf
    //Main:
        [client]
        port        = 3306
        socket      = /var/run/mysqld/mysqld.sock

        [mysqld_safe]
        pid-file    = /var/run/mysqld/mysqld.pid
        socket      = /var/run/mysqld/mysqld.sock
        nice        = 0

        [mysqld]
        user        = mysql
        pid-file    = /var/run/mysqld/mysqld.pid
        socket      = /var/run/mysqld/mysqld.sock
        port        = 3306
        basedir     = /usr
        datadir     = /var/lib/mysql
        tmpdir      = /tmp
        lc-messages-dir = /usr/share/mysql
        explicit_defaults_for_timestamp
        character_set_server=utf8mb4
        log-error = /var/log/mysql/mysql-error.log
        log-bin = mysql-bin 
        server-id = 1 

        # Recommended in standard MySQL setup
        sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

        # Disabling symbolic-links is recommended to prevent assorted security risks
        symbolic-links=0
    //From configuration
        [client]
        port        = 3306
        socket      = /var/run/mysqld/mysqld.sock

        [mysqld_safe]
        pid-file    = /var/run/mysqld/mysqld.pid
        socket      = /var/run/mysqld/mysqld.sock
        nice        = 0

        [mysqld]
        user        = mysql
        pid-file    = /var/run/mysqld/mysqld.pid
        socket      = /var/run/mysqld/mysqld.sock
        port        = 3306
        basedir     = /usr
        datadir     = /var/lib/mysql
        tmpdir      = /tmp
        lc-messages-dir = /usr/share/mysql
        explicit_defaults_for_timestamp
        character_set_server=utf8mb4
        log-error = /var/log/mysql/mysql-error.log
        log-bin = mysql-bin 
        server-id = 2

        # Recommended in standard MySQL setup
        sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

        # Disabling symbolic-links is recommended to prevent assorted security risks
        symbolic-links=0        

Start master

docker run -p 33006:3306 --name mysql-master    -v /data/docker/mysql/conf/33006/:/etc/mysql/mysql.conf.d/:rw -v /data/docker/mysql/logs/33006/:/var/log/mysql/:rw -v /data/docker/mysql/data/33006/:/var/lib/mysql/:rw -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
docker exec -it mysql-master /bin/bash

start slave

docker run -p 33007:3306 --name mysql-slave     -v /data/docker/mysql/conf/33007/:/etc/mysql/mysql.conf.d/:rw -v /data/docker/mysql/logs/33007/:/var/log/mysql/:rw -v /data/docker/mysql/data/33007/:/var/lib/mysql/:rw -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
docker exec -it mysql-slave /bin/bash

master authorization

GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';

• master status

  1. show master status;
  2. Remember the bin log file and the offset

• slave connection master
  change master to master_host='127.0.0.1',master_user='backup',master_password='123456', master_log_file='mysql-bin.000004',master_log_pos=439,master_port=33006;

Configuration verified successfully

• slave staus
  Log in mysql on slave and type in the command

  show slave status;
  The result shows: slave? IO? State: waiting for master to send event. It was a success.

• data validation
  Create the response database, data table and insert some test data on the master. If the slave contains these data, the whole configuration process is completed.

Q&A

During startup, you may encounter the situation that the log.index file cannot be created due to insufficient permissions. In this case, one solution is to first not mount the log file directory, start the container, enter the container through the chown command, set the log file directory to be mounted, and then restart the container.

Follow-up

Later, we will complete the open source of Linken based on the current configuration Databus And Alibaba open source canal The configuration, architecture and implementation principle of.

Provide a detailed configuration file

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
#log-error  = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
#Support symbolic link, that is, you can manage the database of other directories through soft connection. It is better not to open it. When there is not enough space for a disk or partition, you can open this parameter to store data to other disks or partitions.
#http://blog.csdn.net/moxiaomomo/article/details/17092871
symbolic-links=0

########basic settings########
server-id = 11 
port = 3306
user = mysql
#If autocommit=0 is set, the user will remain in a transaction until a commit or rollback statement is executed, then the current transaction will not be ended and a new transaction will be restarted. The advantage of set autocommit=0 is to reduce one begin interaction in the scenario of frequent transaction opening.
autocommit = 1
#utf8mb4 encoding is a superset of utf8 encoding, which is compatible with utf8 and can store 4 bytes of emoticons. 
#The advantage of utf8mb4 encoding is that when storing and acquiring data, the encoding and decoding of emoticons are not needed.
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
# For the same host, if there are more than the number of broken wrong connections, the host will be forbidden to connect. If you need to disable the host, execute: FLUSH HOST.
max_connect_errors = 1000
#Database isolation level
transaction_isolation = READ-COMMITTED
#When MySQL completes some join (connection) requirements, in order to reduce the reading times of the "driven table" participating in the join to improve performance, it needs to use the join buffer to assist in completing the join operation. When the join buffer is too small, MySQL will not store the buffer in the disk file, but first operate the results in the join buffer with the table of the requirement join, and then empty the join Data in the buffer, continue to write the remaining result set to the secondary buffer
join_buffer_size = 128M
tmp_table_size = 64M
tmpdir = /tmp
#If this value is set too small, it will lead to failure to write a single record to the database after exceeding the limit, and subsequent record writes will also fail
max_allowed_packet = 64M
#The number of seconds mysql waits before closing an interactive connection
interactive_timeout = 1200
#The number of seconds mysql waits before closing a non interactive connection
wait_timeout = 600
#Size of MySQL read in buffer
read_buffer_size = 16M
#Random read buffer size of MySQL
read_rnd_buffer_size = 8M
#Size of MySQL sequential read buffer
sort_buffer_size = 8M
########log settings########
log_error = /var/log/docker_log/mysql/error.log
#Open slow query log
slow_query_log = 1
#The SQL exceeding the secondary setting value is recorded in the slow query log
long_query_time = 6
slow_query_log_file = /var/log/docker_log/mysql/slow.log
#Indicates a query without index under the record
log_queries_not_using_indexes = 1
#Record management statement
log_slow_admin_statements = 1
#Turn on the log to copy slow queries copied from the library
log_slow_slave_statements = 1
#Set the number of logs per minute that grow without index queries
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
########replication settings########
#Save master.info and relay.info in the table
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
#After every n transaction commits, MySQL will perform a disk synchronization instruction such as fsync to force the data in binlog ﹣ cache to write to the disk. Set to zero for the system to decide
sync_binlog = 5
#Turn on the global transaction ID, GTID can ensure that data replication and data integration can be realized from one server to other servers
gtid_mode = on
#To turn on gtid, the master slave must be fully open
enforce_gtid_consistency = 1
#Whether the update from the server is written to the binary log
log_slave_updates = 1
#Three modes: state (possible inconsistency between master and slave data, low daily quality), ROW (generating a large number of binary logs), MIXED
binlog_format = mixed
#Relay log records that the binary logs of the primary server are read from the I/O thread of the server to the local files of the secondary server, and then the SQL thread reads the contents of the relay log and applies it to the secondary server
relay_log = /var/log/docker_log/mysql/relay.log
relay_log_recovery = 1
#Turn on simple gtid, which will improve the performance of mysql recovery
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
#This parameter should be added to my.cnf at the beginning of initialization. If the table has been created and modified, an error will be reported when starting MySQL. Best for 8K
#innodb_page_size = 16K
innodb_page_size = 8K
#Data buffer pool size, 75% of physical memory recommended
innodb_buffer_pool_size = 2G
#When the value of buffer pool is large, it is 1, and the smaller value is 8
innodb_buffer_pool_instances = 8
#Load the buffer pool at runtime, quickly preheat the buffer pool, dump the contents of the buffer pool (the index of the file page) to the file, and then quickly load it to the buffer pool. Avoid the preheating process of database and improve the performance of application access
innodb_buffer_pool_load_at_startup = 1
#Runtime dump buffer pool
innodb_buffer_pool_dump_at_shutdown = 1
#After processing the user query in innodb, the result has changed in the buffer pool of memory space, but it has not been recorded to disk. This kind of page is called dirty page, and the process of recording dirty page to disk is called dirty page
innodb_lru_scan_depth = 2000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
#The maximum waiting time for a transaction to acquire a resource. If the transaction has not been allocated to a resource before this time, the application fails. The default is 50s
innodb_lock_wait_timeout = 30
#The path of the log group, which is the home directory of data by default;
innodb_log_group_home_dir = /data/mysql/
#innodb_undo_directory = /data/mysql/undolog/
#This parameter controls the open and write mode of innodb data file and redo log. Http://blog.csdn.net/gua/gua/article/details/44916207
#InnoDB? Flush? Method = o? Direct - store directly to disk without system cache,
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_strict_mode = 1
#innodb has a lot of exclusive table space, and its disadvantages will lead to too large single table file
#innodb_file_per_table = 1
#undo log rollback segment is 128 by default
innodb_undo_logs = 128
#The traditional mechanical hard disk is recommended, while the solid-state hard disk can be turned off
#innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
#Control whether to use independent purge thread
innodb_purge_threads = 1
#When changed to ON, a single column index is allowed up to 3072. Otherwise, the maximum is 767
innodb_large_prefix = 1
innodb_thread_concurrency = 8
#When enabled, all deadlocks will be recorded in the error log
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 16M 
########semi sync replication settings########
#Semisynchronous replication
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
#Represents the percentage of the hottest page on each bp instance LRU dumped. By setting this parameter, you can reduce the number of pages dumped.
innodb_buffer_pool_dump_pct = 40
#Dirty process N-1
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
#Controls how often undo logs are reclaimed (shrunk). undo log space does not shrink until its rollback segment is freed,
innodb_purge_rseg_truncate_frequency = 128
log_timestamps=system
#This parameter is based on MySQL 5.7 group replication group replication. Do not set it if it is not used
#transaction_write_set_extraction=MURMUR32
#http://www.cnblogs.com/hzhida/archive/2012/08/08/2628826.html
show_compatibility_56=on