Of course docker pulls mysql first
mkdir /usr/local/mysqlData/master/cnf mkdir /usr/local/mysqlData/master/data vi /usr/loal/mysqlData/master/cnf/mysql.cnf
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1 max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log-bin = mysql-bin server-id = 1 binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 28800
wait_timeout = 28800
Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M
docker run -itd -p 3306:3306 –name master -v /usr/local/mysqlData/master/cnf:/etc/mysql/conf.d -v / usr/local/mysqlData/master/data: / var/lib/mysql – e MYSQL_ROOT_PASSWORD = 5709795 eeffa masterpwd, and add a copy of master data users, etc. Will use
[root@192 ~]# docker exec -it 942c2253509c /bin/bash root@942c2253509c:/# mysql -uroot -pmasterpwd
mysql> GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘masterpwd’ WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 SEC) mysql> GRANT REPLICATION SLAVE on. to ‘reader’@’%’ identified by ‘readerpwd’;
Query OK, 0 rows affected (0.00 SEC)
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
mkdir /usr/local/mysqlData/slave/cnf mkdir /usr/local/mysqlData/slave/data vi /usr/loal/mysqlData/slave/cnf/mysql.cnf
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql
symbolic-links=0
character-set-server = utf8
#skip-networking
innodb_print_all_deadlocks = 1 max_connections = 2000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 28M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log-bin = mysql-bin server-id = 2 binlog_format = mixed
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
interactive_timeout = 28800
wait_timeout = 28800
Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M
Create a remote connection user, and grant the database query, and query permission, can be used for read and write separation
docker run -itd -p 3307:3306 –name master -v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=slavepwd 5709795eeffa
[root@192 ~]# docker exec -it 942c2253509c /bin/bash root@942c2253509c:/# mysql -uroot -pslavepwd mysql> grant SHOW DATABASES,SELECT on . to ‘slave’@’%’ identified by ‘slavepwd’; mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 SEC)
mysql> show master status; +——————+———-+————–+——————+——————-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +——————+———-+————–+——————+——————-+ | mysql-bin.000007 | 675 | | | | + — — — — — — — — — — — — — — — — — – + — — — — — — — — — – + — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — – + — — — — — — — — — — — — — — — — — — — + 1 row in the set (0.00 SEC)
mysql> change master to Master_host = ‘192.168.1.9, master_user =’ reader ‘, master_password = ‘readerpwd’, master_log_file = ‘000007’ mysql – bin., master_log _pos=501; Query OK, 0 rows affected, 2 warnings (0.03 SEC)
mysql> start slave; Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.9 Master_User: reader Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 501 Relay_Log_File: 0b763a8d1ddd-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes
mysql> create database slavetest; Query OK, 1 row affected (0.00 sec)
mysql> show databases; +——————–+ | Database | +——————–+ | information_schema | | mysql | | performance_schema | | Slavetest | | sys | + — — — — — — — — — — — — — — — — — — — — + 5 rows in the set (0.00 SEC)