docker mysql 主从复制

docker mysql 主从复制

前言

准备工作

1. 安装docker
2. 用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)

注意问题

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