获取mysql镜像
➜ ~ docker pull mysql
➜ ~ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql latest d435eee2caa5 3 weeks ago 456MB
复制代码启动mysql
➜ ~ docker run -itd --name docker-mysql-master -v /Users/chenyuan/Data/docker/mysql-data-master:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -p 33061:3306 mysql
88820868af121cbac02f48a8c8e5c9eae5c6cf7241eefd3646634e14526a940f
➜ ~ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
88820868af12 mysql "docker-entrypoint.s…" About a minute ago Up About a minute 33060/tcp, 0.0.0.0:33061->3306/tcp docker-mysql-master
➜ ~ docker exec -it 88820868af12 bash
root@88820868af12:/#
root@88820868af12:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
复制代码确定挂载的mysql-data文件
➜ mysql-data-master ll
total 356592
drwxr-x--- 12 chenyuan staff 384B Dec 19 17:46 #innodb_temp
-rw-r----- 1 chenyuan staff 1.2K Dec 19 17:46 88820868af12.err
-rw-r----- 1 chenyuan staff 56B Dec 19 17:45 auto.cnf
-rw-r----- 1 chenyuan staff 2.9M Dec 19 17:46 binlog.000001
-rw-r----- 1 chenyuan staff 155B Dec 19 17:46 binlog.000002
-rw-r----- 1 chenyuan staff 32B Dec 19 17:46 binlog.index
-rw------- 1 chenyuan staff 1.6K Dec 19 17:45 ca-key.pem
-rw-r--r-- 1 chenyuan staff 1.1K Dec 19 17:45 ca.pem
-rw-r--r-- 1 chenyuan staff 1.1K Dec 19 17:45 client-cert.pem
-rw------- 1 chenyuan staff 1.6K Dec 19 17:45 client-key.pem
-rw-r----- 1 chenyuan staff 5.3K Dec 19 17:46 ib_buffer_pool
-rw-r----- 1 chenyuan staff 48M Dec 19 17:46 ib_logfile0
-rw-r----- 1 chenyuan staff 48M Dec 19 17:45 ib_logfile1
-rw-r----- 1 chenyuan staff 12M Dec 19 17:46 ibdata1
-rw-r----- 1 chenyuan staff 12M Dec 19 17:46 ibtmp1
drwxr-x--- 8 chenyuan staff 256B Dec 19 17:46 mysql
-rw-r----- 1 chenyuan staff 29M Dec 19 17:46 mysql.ibd
drwxr-x--- 105 chenyuan staff 3.3K Dec 19 17:45 performance_schema
-rw------- 1 chenyuan staff 1.6K Dec 19 17:45 private_key.pem
-rw-r--r-- 1 chenyuan staff 452B Dec 19 17:45 public_key.pem
-rw-r--r-- 1 chenyuan staff 1.1K Dec 19 17:45 server-cert.pem
-rw------- 1 chenyuan staff 1.6K Dec 19 17:45 server-key.pem
drwxr-x--- 3 chenyuan staff 96B Dec 19 17:46 sys
-rw-r----- 1 chenyuan staff 12M Dec 19 17:46 undo_001
-rw-r----- 1 chenyuan staff 10M Dec 19 17:46 undo_002
➜ mysql-data-master pwd
/Users/chenyuan/Data/docker/mysql-data-master
复制代码Binlog配置
查看binlog日志的地方，通过命令查看。因为没有设置过所以看不到。
mysql> mysql> show variables like '%datadir%';
Empty set (0.01 sec)
root@88820868af12:/etc/mysql# pwd
/etc/mysql
root@88820868af12:/etc/mysql# ls -l
total 12
drwxrwxr-x 1 root root 4096 Nov 23 01:48 conf.d
-rw-rw-r-- 1 root root 1174 Nov 23 01:48 my.cnf
-rw-r--r-- 1 root root 1469 Sep 20 09:04 my.cnf.fallback
# 后面设置好后，就能看到了。
mysql> show variables like '%datadir%';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)
复制代码当前容器提交为镜像
到这里遇到一个非常好玩的事情，就是获取的mysql镜像是一个非常干净的容器，常用的命令都没有。比如：yum、ifconfig、cat等。所以我需要把当前的容器打一个镜像包。并且docker run的时候要挂载一个本地的目录，避免待会儿需要上传一些工具包。
docker commit [OPTIONS] CONTAINER [REPOSITORY[:TAG]]
-a :提交的镜像作者；
-c :使用Dockerfile指令来创建镜像；
-m :提交时的说明文字；
-p :在commit时，将容器暂停。
复制代码➜ ~ docker commit -a "chengcheng222e@sina.com" -m "created by vernon" 88820868af12 mysql-versnon:v1
sha256:e9691f399c321ea221b48e6142e9501f0ee69964fa4be687ac189f8444d75d66
➜ ~ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql-versnon v1 e9691f399c32 13 seconds ago 456MB
mysql latest d435eee2caa5 3 weeks ago 456MB
➜ Tools docker run -itd --name docker-mysql-master -v /Users/chenyuan/Data/docker/mysql-data-master:/var/lib/mysql -v /Users/chenyuan/Tools:/root/tools -e MYSQL_ROOT_PASSWORD=root -p 33061:3306 mysql-versnon:v1
复制代码这里注意一下，因为docker pull mysql镜像中的shell里面命令太少了，非常的不方便。这里建议大家还是利用dockefile的方式或者是docker pull centos的方式来安装mysql，不然你会怀疑人生。
通过dockerfile的方式：
FROM centos:7
MAINTAINER 2019-09-27 chenyuan chengcheng222e@sina.com
# Linux lib
RUN yum install -y tar
RUN yum install -y unzip
RUN yum install -y initscripts
# Software space
RUN mkdir -p ~/tools/
COPY jdk1.8.0_45.tar.gz ~/tools/
COPY mysql-5.6.45-linux-glibc2.12-x86_64.tar.gz ~/tools/
# JDK
WORKDIR ~/tools/
RUN tar -zxvf jdk1.8.0_45.tar.gz
RUN mv jdk1.8.0_45 /opt/
RUN ln -s
/opt/jdk1.8.0_45/bin/* /usr/local/sbin/
ENV JAVA_HOME /opt/jdk1.8.0_45
ENV JRE_HOME ${JAVA_HOME}/jre
ENV CLASSPATH .:${JAVA_HOME}/lib:${JRE_HOME}/lib
ENV PATH ${JAVA_HOME}/bin:$PATH
# MySQL
RUN cd ~/tools/
RUN yum -y install numactl
RUN yum -y install libaio
RUN yum -y install pwgen
RUN yum install -y perl-Data-Dumper
RUN tar -zxvf mysql-5.6.45-linux-glibc2.12-x86_64.tar.gz
RUN mv mysql-5.6.45-linux-glibc2.12-x86_64 /usr/local/mysql
RUN groupadd mysql
RUN useradd -g mysql mysql
RUN chown -R mysql /usr/local/mysql
RUN chgrp -R mysql /usr/local/mysql
RUN cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
COPY my.cnf /etc/my.cnf
RUN /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
ENV PATH $PATH:/usr/local/mysql/bin
EXPOSE 3306
复制代码my.conf配置
修改my.conf的内容
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
user=mysql
default-storage-engine=INNODB
character-set-server=utf8
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
socket = /tmp/mysql.sock
server-id = 1
log-bin=mysql-bin
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
复制代码Binlog格式
Binlog的格式也有三种：STATEMENT、ROW、MIXED 。
-
STATMENT模式：基于SQL语句的复制（statement-based replication, SBR），每一条会修改数据的sql语句会记录到binlog中。
- 优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。
- 缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
-
基于行的复制（row-based replication, RBR）：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。
- 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
- 缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。
-
混合模式复制（mixed-based replication, MBR）：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
设置Binglog
通过show variables的方式来查看binlog的一个实际情况。
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF |
| log_bin_basename | |
| log_bin_index | |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
6 rows in set (0.00 sec)
复制代码从上面可以看出，没有开启binlog日志，那么我们接下来开启binlog。
在/etc/my.cnf里面开启binlog配置。
server-id = 1
log-bin=mysql-bin
复制代码再次查看：
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------+
6 rows in set (0.01 sec)
复制代码查看binlog_format
mysql> show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
# 默认的格式就是 STATEMENT
复制代码确定binlog日志文件
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.45-log, Binlog ver: 4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
复制代码创建表
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> CREATE TABLE `person` (
-> `id` int(11) DEFAULT NULL,
-> `first_name` varchar(20) DEFAULT NULL,
-> `age` int(11) DEFAULT NULL,
-> `gender` char(1) DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> ;
Query OK, 0 rows affected (0.04 sec)
INSERT INTO test.person (id, first_name, age, gender) VALUES (1, 'Bob', 25, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (2, 'Jane', 20, 'F');
INSERT INTO test.person (id, first_name, age, gender) VALUES (3, 'Jack', 30, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (4, 'Bill', 32, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (5, 'Nick', 22, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (6, 'Kathy', 18, 'F');
INSERT INTO test.person (id, first_name, age, gender) VALUES (7, 'Steve', 36, 'M');
INSERT INTO test.person (id, first_name, age, gender) VALUES (8, 'Anne', 25, 'F');
复制代码查看binlog
mysql> show binlog events in 'mysql-bin.000001';
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.45-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Query | 1 | 386 | use `test`; CREATE TABLE `person` (
`id` int(11) DEFAULT NULL,
`first_name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`gender` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
| mysql-bin.000001 | 386 | Query | 1 | 465 | BEGIN |
| mysql-bin.000001 | 465 | Query | 1 | 619 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (1, 'Bob', 25, 'M') |
| mysql-bin.000001 | 619 | Xid | 1 | 650 | COMMIT /* xid=14 */ |
| mysql-bin.000001 | 650 | Query | 1 | 729 | BEGIN |
| mysql-bin.000001 | 729 | Query | 1 | 884 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (2, 'Jane', 20, 'F') |
| mysql-bin.000001 | 884 | Xid | 1 | 915 | COMMIT /* xid=15 */ |
| mysql-bin.000001 | 915 | Query | 1 | 994 | BEGIN |
| mysql-bin.000001 | 994 | Query | 1 | 1149 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (3, 'Jack', 30, 'M') |
| mysql-bin.000001 | 1149 | Xid | 1 | 1180 | COMMIT /* xid=16 */ |
| mysql-bin.000001 | 1180 | Query | 1 | 1259 | BEGIN |
| mysql-bin.000001 | 1259 | Query | 1 | 1414 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (4, 'Bill', 32, 'M') |
| mysql-bin.000001 | 1414 | Xid | 1 | 1445 | COMMIT /* xid=17 */ |
| mysql-bin.000001 | 1445 | Query | 1 | 1524 | BEGIN |
| mysql-bin.000001 | 1524 | Query | 1 | 1679 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (5, 'Nick', 22, 'M') |
| mysql-bin.000001 | 1679 | Xid | 1 | 1710 | COMMIT /* xid=18 */ |
| mysql-bin.000001 | 1710 | Query | 1 | 1789 | BEGIN |
| mysql-bin.000001 | 1789 | Query | 1 | 1945 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (6, 'Kathy', 18, 'F') |
| mysql-bin.000001 | 1945 | Xid | 1 | 1976 | COMMIT /* xid=19 */ |
| mysql-bin.000001 | 1976 | Query | 1 | 2055 | BEGIN |
| mysql-bin.000001 | 2055 | Query | 1 | 2211 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (7, 'Steve', 36, 'M') |
| mysql-bin.000001 | 2211 | Xid | 1 | 2242 | COMMIT /* xid=20 */ |
| mysql-bin.000001 | 2242 | Query | 1 | 2321 | BEGIN |
| mysql-bin.000001 | 2321 | Query | 1 | 2476 | use `test`; INSERT INTO test.person (id, first_name, age, gender) VALUES (8, 'Anne', 25, 'F') |
| mysql-bin.000001 | 2476 | Xid | 1 | 2507 | COMMIT /* xid=21 */ |
+------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
26 rows in set (0.01 sec)
复制代码常用命令
show variables like 'binlog_format'
set globle binlog_format='MIXED'
show variables like 'log_bin'
show binary logs
复制代码遇到问题:
[mysql@c738746e9623 support-files]$ ./mysql.server start
Starting MySQL... ERROR! The server quit without updating PID file (/usr/local/mysql/data/c738746e9623.pid).
log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
server-id=1
# 修改为
server-id = 1
log-bin=mysql-bin
复制代码参考地址：
如果大家喜欢我的文章，可以关注个人订阅号。欢迎随时留言、交流。
