docker mysql 主从复制

前言

准备工作

安装docker用docker安装mysql镜像 docker pull mysql:5.6

主数据库 master

添加master配置文件

mkdir -p /usr/local/mysqlData/master/cnf mkdir -p /usr/local/mysqlData/master/data

vim /usr/local/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

启动master mysql 数据库容器

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=123456 mysql:5.6或者mysql镜像id

进入容器开启root远程访问

docker exec -it 942c2253509c /bin/bash root@942c2253509c:/# mysql -uroot -p123456 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, 1 warning (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)

查看当前账户信息

保证 host字段为 ‘%’ ，plugin字段为 ‘mysql_native_password’ ， authentication_string字段有认证字符串

mysql> use mysql; # 修改 root and % 的密码 mysql> update user set authentication_string=password("123456") where user="root" and host="%"; mysql> select host,user,plugin,authentication_string from mysql.user;

查询主数据库的日志文件(File)和Pos(Position)

mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000007 | 675 | | | | +------------------+----------+--------------+------------------+-------------------+ row in set (0.00 sec)

从数据库 slave

添加slave配置文件

mkdir -p /usr/local/mysqlData/slave/cnf mkdir -p /usr/local/mysqlData/slave/data

vim /usr/local/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

启动slave mysql 数据库容器

创建远程连接用户，并赋予查询数据库，以及查询的权限，可以用于读写分离

docker run -itd -p 3307:3306 --name slave -v /usr/local/mysqlData/slave/cnf:/etc/mysql/conf.d -v /usr/local/mysqlData/slave/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.6或者mysql镜像id

进入容器开启root远程访问

docker exec -it 942c2253509c /bin/bash root@942c2253509c:/# mysql -uroot -p123456 mysql> grant SHOW DATABASES,SELECT on *.* to 'slave'@'%' identified by '123456'; mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)

通过从数据库设置主数据库配置

# mysql> stop slave; mysql> change master to master_host='192.168.72.128',master_user='root',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=120; 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

测试

master

mysql> create database slavetest; Query OK, 1 row affected (0.00 sec)

slave

mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | slavetest | | sys | +--------------------+ rows in set (0.00 sec)

注意问题

确保局域网内可以访问master数据库和slave数据库show slave status\G 必须满足 Slave_IO_Running: Yes , Slave_SQL_Running: Yes