Get mysql image
➜ ~ docker pull mysql ➜ ~ docker images REPOSITORY TAG IMAGE ID CREATED SIZE mysql latest d435eee2caa5 3 weeks ago 456MB
mysql start
➜ ~ 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>
Determine the mounted mysql-data file
➜ 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 Configuration
Where to view the binlog log, view it by command.I can't see it because it's not set.
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 # You can see it when you set it back. mysql> show variables like '%datadir%'; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | datadir | /usr/local/mysql/data/ | +---------------+------------------------+ 1 row in set (0.00 sec)
Current container submitted as mirror
A very interesting thing to encounter here is that the mysql image you get is a very clean container and there are no common commands available.For example: yum, ifconfig, cat, etc.So I need to mirror the current container.And docker run has to mount a local directory to avoid having to upload some toolkits later.
docker commit [OPTIONS] CONTAINER [REPOSITORY[:TAG]] -a :Submitted mirror author; -c :Use Dockerfile Directive to create a mirror; -m :Description at the time of submission; -p :stay commit Pause the container.
➜ ~ 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
Note here that there are too few commands inside the shell in the docker pull mysql image to be convenient.Here's a suggestion to install mysql using dockefile or docker pull centos, otherwise you'll suspect life.
By 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 configuration
Modify my.conf content
[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 format
Binlog also has three formats: STATEMENT, ROW, MIXED.
-
STATMENT mode: based on statement-based replication (SBR) of SQL statements, each SQL statement that modifies data is recorded in the binlog.
- Advantages: There is no need to record data changes for each SQL statement and row, so fewer sub binlog s are logged, which reduces disk IO and improves performance.
- Disadvantages: Inconsistency of data in master-slave (such as sleep() function, last_insert_id(), and user-defined functions(udf) can be problematic in some cases)
-
Row-based replication (RBR): Does not record the context of each SQL statement, only which data has been modified and what it looks like.
- Advantage: There are no problems where stored procedures, or function s, or trigger s in certain situations cannot be copied correctly.
- Disadvantages: A large number of logs can be generated, especially when alter table is used.
-
Mixed-based replication (MBR): Mixed use of the above two modes. General replication uses STATEMENT mode to save binlog. For operations that cannot be replicated by STATEMENT mode, use ROW mode to save binlog. MySQL chooses the way to save the log based on the SQL statement executed.
Set Binglog
See a binlog reality by showing variables.
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)
As you can see above, the binlog log is not turned on, so let's turn on the binlog next.
Open the binlog configuration in/etc/my.cnf.
server-id = 1 log-bin=mysql-bin
Check again:
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)
View binlog_format
mysql> show variables like 'binlog_format'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | binlog_format | STATEMENT | +---------------+-----------+ 1 row in set (0.00 sec) # The default format is STATEMENT
Determine binlog log file
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 | +------------------+-----+-------------+-----------+-------------+---------------------------------------+
Create Table
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');
View 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)
Common Commands
show variables like 'binlog_format' set globle binlog_format='MIXED' show variables like 'log_bin' show binary logs
Problem encountered:
[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 # Modify to server-id = 1 log-bin=mysql-bin
Reference address:
-
https://www.cnblogs.com/grey-wolf/p/10437811.html
If you like my article, you can focus on your personal subscription number.Welcome to leave a message and exchange at any time.
