mysql做主从备份

mkdir mysql
cd mysql 

version: '3.1'

services:
  db0: image: mysql:5.7 ports: - "3300:3306" # command: --default-authentication-plugin=mysql_native_password restart: always environment: MYSQL_ROOT_PASSWORD: 123456 volumes: - ./my_master.cnf:/etc/mysql/my.cnf
  db1: image: mysql:5.7 ports: - "3301:3306" # command: --default-authentication-plugin=mysql_native_password restart: always environment: MYSQL_ROOT_PASSWORD: 123456 volumes: - ./my_slave.cnf:/etc/mysql/my.cnf

# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
# [必须]启用二进制日志
log-bin=mysql-bin # [必须]服务器唯一ID
server-id=1 

# Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
# [非必须]Slave可以不启用二进制日志，配置二进制日志可以便于Master和Slave交换角色
log-bin=mysql-bin # [必须]服务器唯一ID
server-id=2 

fangleMac:~ fangle$ docker ps 
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
aabec4d6d0c2 mysql:5.7 "docker-entrypoint.s…"   43 minutes ago Up 43 minutes 33060/tcp, 0.0.0.0:3300->3306/tcp   mysql_db0_1
b59d7fe2d10a mysql:5.7 "docker-entrypoint.s…"   43 minutes ago Up 43 minutes 33060/tcp, 0.0.0.0:3301->3306/tcp   mysql_db1_1
fangleMac:~ fangle$ docker exec -it aabec4d6d0c2 bash 
root@aabec4d6d0c2:/# 
root@aabec4d6d0c2:/# mysql -uroot -p123456
mysql> GRANT REPLICATION SLAVE ON *.* to 'nick'@'%' identified by '123456';
mysql> FLUSH PRIVILEGES;

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

fangleMac:~ fangle$ docker ps 
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
aabec4d6d0c2 mysql:5.7 "docker-entrypoint.s…"   About an hour ago   Up About an hour 33060/tcp, 0.0.0.0:3300->3306/tcp   mysql_db0_1
b59d7fe2d10a mysql:5.7 "docker-entrypoint.s…"   About an hour ago   Up About an hour 33060/tcp, 0.0.0.0:3301->3306/tcp   mysql_db1_1
fangleMac:~ fangle$ docker exec -it b59d7fe2d10a bash 
root@b59d7fe2d10a:/# mysql -uroot -p123456
mysql> change master to master_host='db0',master_user='nick',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=589;
mysql> start slave;

mysql> show slave status\G;
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: db0 Master_User: nick Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 1039 Relay_Log_File: b59d7fe2d10a-relay-bin.000002 Relay_Log_Pos: 770 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes

mysql> flush logs;
Query OK, 0 rows affected, 64 warnings (0.16 sec

mysql>purge binary logs to 'bin.000055';

mysql>purge binary logs before '2017-05-01 13:09:51';

expire_logs_days = 15
max_binlog_size = 1073741824

show master logs; #获得主服务器上的一系列日志
PURGE MASTER LOGS TO 'binlog.000058';   #删除binlog.000005之前的，不包括binlog.000058