MySQL and MariaDB

We all know ,MariaDB yes MySQL A branch of , So what's the difference between them ?

 picture

 picture

 picture

 picture

MariaDB and Mysql The main difference :

  • MariaDB Yes 12 New storage engine , and MySQL There are fewer storage engines .

  • MariaDB Has a larger connection pool , Most support 200,000 More than connections , and MySQL Has a smaller connection pool .

  • stay MariaDB in , Copy faster , And in the MySQL in , Slow replication .

  • MariaDB It's open source. , and MySQL Some proprietary code is used in its Enterprise Edition .

  • MariaDB Data masking and dynamic columns are not supported , and MySQL Support .

  • Relatively speaking ,MariaDB Than MySQL fast .


From the perspective of market share ,MySQL Than MariaDB Still much higher .




MySQL and MariaDB Replication technology of


MySQL The simplest is asynchronous replication , As shown in the figure below : The main instance is readable and writable , Examples are readable .

The architecture of active and standby replication is as follows : The traditional data master-slave system belongs to asynchronous replication , From the library IO Threads connect to the main library , Get the master database binary log and write it to the local relay log , And update the master-info file ( Store information about the main library ), Reuse from the library SQL Thread execution relay log .

Semi synchronous replication is based on the basic master-slave replication , Using plug-ins to complete semi synchronous replication , Traditional master-slave replication , Whether or not the binary log is correctly obtained from the library , The main library is constantly updated , In semi synchronous replication, only when it is confirmed that the slave writes the binary log to the relay log, can it be committed , If you don't return from the library ack, The master library will automatically cancel the semi synchronous replication state , Enter the most basic master-slave replication mode .



The second technology is group replication , abbreviation MGR:

 picture

MGR yes MySQL Official at 2016 year 12 A new high availability and high expansion solution launched in May .MGR yes MySQL The official in the 5.7.17 Version of the introduction of a database high availability and high expansion solution , In the form of plug-ins , It realizes the final consistency of data under the distributed environment , It is MySQL5.7 New features in the version , It provides high availability 、 High expansion 、 Highly reliable MySQL The cluster service .


Group replication is a technology that can be used to implement fault-tolerant systems . A replication group is a group that interacts with each other through messaging server colony . Replication groups are made up of server Membership , As above, master1,master2,master3, All members complete their own affairs independently . When the client initiates an update transaction first , The transaction is executed locally first , After the execution, the commit operation of the transaction will be initiated . You need to broadcast the resulting copy write set before you actually commit it , Copy to other members . If the conflict detection is successful , The group decides that the transaction can be committed , Other members can apply , Otherwise, roll back . Final , This means that all members of the group receive the same set of transactions in the same order . So members of the group apply the same changes in the same order , Ensure strong consistency of data within the group .

 picture

A simple process for new members to join a group :


  1. When new members join the group , The original members of the group will insert a view switching event into the binary log .

  2. Find one of the members donor Missing data before donation , If this donor All of a sudden, it's offline , New members will be new donor Get missing data , At this time, the group is still updating , New members will write new events to a temporary space in memory

  3. When you get the view switch event , The new member will start executing the events saved to the temporary memory space


MariaDB There are mainly active and standby replication ( and MySQL similar ) and Galera Two kinds of , I haven't seen anything like that at the moment MGR Technology . There's no more discussion here .





OpenShift Provided MySQL Container mirror

If we want to achieve MySQL The container of , How to do it? ?

Red hat OpenShift Provide MySQL Container image of , You can see the latest is MySQL 8.0.

 picture

The container image is highly secure :

 picture

Look at the mirror image dockerfile Part of , Image based on RHEL8.1

 picture

 picture

View the image of the container package list:




Run in the simplest way MySQL Container mirror

Use the MySQL Mirror image , The easiest way is to run it directly ( During the experiment , We use based on rhel7 Of mysql8 Mirror image ):

podman run -d --name mysql_database -e MYSQL_USER=user -e MYSQL_PASSWORD=pass -e MYSQL_DATABASE=db -p 3306:3306 rhscl/mysql-80-rhel7

View running mysql Containers :

The above shows the simplest operation mysql The way the container is mirrored . This image contains environment variables and their explanations are as follows .

MYSQL_USERUser name for MySQL account to be created
MYSQL_PASSWORDPassword for the user account
MYSQL_DATABASEDatabase name
MYSQL_ROOT_PASSWORDPassword for the root user (optional)
The following environment variables influence the MySQL configuration file. They are all optional.
MYSQL_LOWER_CASE_TABLE_NAMES (default: 0)Sets how the table names are stored and compared
MYSQL_MAX_CONNECTIONS (default: 151)The maximum permitted number of simultaneous client connections
MYSQL_MAX_ALLOWED_PACKET (default: 200M)The maximum size of one packet or any generated/intermediate string
MYSQL_FT_MIN_WORD_LEN (default: 4)The minimum length of the word to be included in a FULLTEXT index
MYSQL_FT_MAX_WORD_LEN (default: 20)The maximum length of the word to be included in a FULLTEXT index
MYSQL_AIO (default: 1)Controls the innodb_use_native_aio setting value in case the native AIO is broken. See http://help.directadmin.com/item.php?id=529
MYSQL_TABLE_OPEN_CACHE (default: 400)The number of open tables for all threads
MYSQL_KEY_BUFFER_SIZE (default: 32M or 10% of available memory)The size of the buffer used for index blocks
MYSQL_SORT_BUFFER_SIZE (default: 256K)The size of the buffer used for sorting
MYSQL_READ_BUFFER_SIZE (default: 8M or 5% of available memory)The size of the buffer used for a sequential scan
MYSQL_INNODB_BUFFER_POOL_SIZE (default: 32M or 50% of available memory)The size of the buffer pool where InnoDB caches table and index data
MYSQL_INNODB_LOG_FILE_SIZE (default: 8M or 15% of available memory)The size of each log file in a log group
MYSQL_INNODB_LOG_BUFFER_SIZE (default: 8M or 15% of available memory)The size of the buffer that InnoDB uses to write to the log files on disk
MYSQL_DEFAULTS_FILE (default: /etc/my.cnf)Point to an alternative configuration file
MYSQL_BINLOG_FORMAT (default: statement)Set sets the binlog format, supported values are row and statementMYSQL_LOG_QUERIES_ENABLED (default: 0)To enable query logging set this to 1
MYSQL_DEFAULT_AUTHENTICATION_PLUGIN (default: caching_sha2_password)Set default authentication plugin. Accepts values mysql_native_password or caching_sha2_password.
You can also set the following mount points by passing the -v /host:/container flag to Docker./var/lib/mysql/dataMySQL data directory
When using --memory Parameter set run MySQL Image and you do not specify values for some parameters , Their values are automatically calculated based on available memory .MYSQL_KEY_BUFFER_SIZE (default: 10%)key_buffer_size
MYSQL_READ_BUFFER_SIZE (default: 5%)read_buffer_size
MYSQL_INNODB_BUFFER_POOL_SIZE (default: 50%)innodb_buffer_pool_size
MYSQL_INNODB_LOG_FILE_SIZE (default: 15%)innodb_log_file_size
MYSQL_INNODB_LOG_BUFFER_SIZE (default: 15%)innodb_log_buffer_size


Template deployment mysql

In the above section , We use pod Direct operation mysql Container mirror , It is obviously inappropriate to do so in production . We can go through mysql The template is in OCP Deploy on :

 picture

It can be deployed directly mysql 8.0:

 picture

Deployment success :

# oc get pods |grep -i davidmysql-david-1-deploy      0/1     Completed   0          57smysql-david-1-qfvqm       1/1     Running     0          49s oc new-app mysql-80-rhel7:30~https://github.com/sclorg/mysql-container.git \

This kind of deployment is also very convenient .




Customized deployment mysql

Next , We show customized deployment mysql. Use S2I It's customized in a different way .builder image It's the beginning of this article mysql8.0 Mirror image .s2i Source code address :https://github.com/sclorg/mysql-container.git


First, import red hat mysql The container mirrors is:

 oc import-image rhscl/mysql-80-rhel7 --from=registry.access.redhat.com/rhscl/mysql-80-rhel7 --confirm --all


Next , utilize image stream Customized deployment mysql:

 oc new-app mysql-80-rhel7:30~https://github.com/sclorg/mysql-container.git \--name my-mysql-rhel7 \--context-dir=examples/extend-image \--env MYSQL_OPERATIONS_USER=opuser \--env MYSQL_OPERATIONS_PASSWORD=oppass \--env MYSQL_DATABASE=opdb \--env MYSQL_USER=user \--env MYSQL_PASSWORD=pass

Let's look at the input parameters above examples/extend-image Corresponding directory :

mysql-cfg/  When starting the container , The files in this directory will be used as mysqld Configuration of daemons . Run... On this file envsubst Command to still allow custom mirroring using environment variables :

mysql-container/examples/extend-image/mysql-cfg/myconfig.cnf[mysqld]stored_program_cache = 524288--env MYSQL_USER=user \--env MYSQL_PASSWORD=pass


mysql-pre-init/  Available in this directory Shell Script (* .sh) It's starting mysqld Before the daemons .

mysql-container/examples/extend-image/mysql-data/init.sql
CREATE TABLE products (id INTEGER, name VARCHAR(256), price FLOAT, variant INTEGER);CREATE TABLE products_variant (id INTEGER, name VARCHAR(256));INSERT INTO products_variant (id, name) VALUES ('1', 'blue'), ('2', 'green');

mysql-init/  Start locally mysqld When the daemons , Will get the... Available in this directory mysql-init / Shell Script (* .sh). At this stage , Use $ {mysql_flags} Connect to the local running daemons , for example mysql $ mysql_flags <dump.sql

mysql-container/examples/extend-image/mysql-init/80-add-arbitrary-users.shcreate_arbitrary_users() {  # Do not care what option is compulsory here, just create what is specified  log_info "Creating user specified by MYSQL_OPERATIONS_USER (${MYSQL_OPERATIONS_USER}) ..."mysql $mysql_flags <<EOSQL    CREATE USER '${MYSQL_OPERATIONS_USER}'@'%' IDENTIFIED BY '${MYSQL_OPERATIONS_PASSWORD}';EOSQL

 log_info "Granting privileges to user ${MYSQL_OPERATIONS_USER} for ${MYSQL_DATABASE} ..."mysql $mysql_flags <<EOSQL      GRANT ALL ON \`${MYSQL_DATABASE}\`.* TO '${MYSQL_OPERATIONS_USER}'@'%' ;      FLUSH PRIVILEGES ;EOSQL}

if ! [ -v MYSQL_RUNNING_AS_SLAVE ]; then  create_arbitrary_usersfi

mysql-container/examples/extend-image/mysql-init/90-init-db.shinit_arbitrary_database() {  local thisdir  local init_data_file  thisdir=$(dirname ${BASH_SOURCE[0]})  init_data_file=$(readlink -f ${thisdir}/../mysql-data/init.sql)  log_info "Initializing the arbitrary database from file ${init_data_file}..."  mysql $mysql_flags ${MYSQL_DATABASE} < ${init_data_file}}

if ! [ -v MYSQL_RUNNING_AS_SLAVE ] && $MYSQL_DATADIR_FIRST_INIT ; then  init_arbitrary_databasefi



It can be provided to s2i Variables used in the script for :


mysql The tool $ mysql_flags Parameters , It will connect to the local running during initialization mysqld

$ MYSQL_RUNNING_AS_MASTER Variables in use run-mysqld-master Define when the command runs the container

$ MYSQL_RUNNING_AS_SLAVE Variable , In the use of run-mysqld-slave Define when the command runs the container


Defined when initializing a container from an empty data directory $ MYSQL_DATADIR_FIRST_INIT Variable


stay s2i During construction , Copy all supplied files to /opt/app-root/src Directory , And copy it to the result image . If there are some configuration files in the target directory , Then the file with the same name will be overwritten . Again , Only one file with the same name can be used to customize , And the files provided by users are much better than /usr/share/container-scripts/mysql /- The default file in is preferred , So you can cover them .



see mysql Deployment part log :

[[email protected] /opt/davidwei-cluster1/data]# oc logs -f my-mysql-rhel7-2-5lnts=> sourcing 20-validate-variables.sh ...=> sourcing 25-validate-replication-variables.sh ...=> sourcing 30-base-config.sh ...---> 16:35:48     Processing basic MySQL configuration files ...=> sourcing 60-replication-config.sh ...=> sourcing 70-s2i-config.sh ...---> 16:35:48     Processing additional arbitrary  MySQL configuration provided by s2i ...=> sourcing 20-default-authentication-plugin.cnf ...=> sourcing 40-paas.cnf ...=> sourcing 50-my-tuning.cnf ...---> 16:35:48     Initializing database ...---> 16:35:48     Running /opt/rh/rh-mysql80/root/usr/libexec/mysqld --initialize --datadir=/var/lib/mysql/data---> 16:35:53     Starting MySQL server with disabled networking ...---> 16:35:53     Waiting for MySQL to start ...---> 16:35:54     Waiting for MySQL to start ...2020-03-27T16:35:53.690350Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.2020-03-27T16:35:53.700015Z 0 [System] [MY-010116] [Server] /opt/rh/rh-mysql80/root/usr/libexec/mysqld (mysqld 8.0.17) starting as process 852020-03-27T16:35:54.410486Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.2020-03-27T16:35:54.459936Z 0 [System] [MY-010931] [Server] /opt/rh/rh-mysql80/root/usr/libexec/mysqld: ready for connections. Version: '8.0.17'  socket: '/tmp/mysql.sock'  port: 0  Source distribution.2020-03-27T16:35:54.486976Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/lib/mysql/mysqlx.sock'---> 16:35:55     MySQL started successfullymysql: [Warning] Using a password on the command line interface can be insecure.The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.---> 16:35:55     Creating user specified by MYSQL_USER (user) ...---> 16:35:55     Creating database opdb ...---> 16:35:55     Granting privileges to user user for opdb ...---> 16:35:55     Initialization finished=> sourcing 40-datadir-action.sh ...---> 16:35:55     Running datadir action: upgrade-warn---> 16:35:55     Warning: Version of the data could not be determined. It is because the file mysql_upgrade_info is missing in the data directory, which is most probably because it was not created when initialization of data directory. In order to allow seamless updates to the next higher version in the future, the file mysql_upgrade_info will be created. If the data directory was created with a different version than 8.0, it is required to run this container with the MYSQL_DATADIR_ACTION environment variable set to 'force', or run 'mysql_upgrade' utility manually; the mysql_upgrade tool checks the tables and creates such a file as well. For upstream documentation about upgrading, see: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html---> 16:35:55     Storing version '8.0.17' information into the data dir '/var/lib/mysql/data/mysql_upgrade_info'=> sourcing 50-passwd-change.sh ...---> 16:35:55     Setting passwords ...---> 16:35:55     Shutting down MySQL ...2020-03-27T16:35:55.555498Z 15 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.17).2020-03-27T16:35:57.798891Z 0 [System] [MY-010910] [Server] /opt/rh/rh-mysql80/root/usr/libexec/mysqld: Shutdown complete (mysqld 8.0.17)  Source distribution.---> 16:35:58     Cleaning up environment variables MYSQL_USER, MYSQL_PASSWORD, MYSQL_DATABASE and MYSQL_ROOT_PASSWORD ...---> 16:35:58     Running final exec -- Only MySQL server logs after this point2020-03-27T16:35:58.909778Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.2020-03-27T16:35:58.911732Z 0 [System] [MY-010116] [Server] /opt/rh/rh-mysql80/root/usr/libexec/mysqld (mysqld 8.0.17) starting as process 12020-03-27T16:35:59.321049Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.2020-03-27T16:35:59.353887Z 0 [System] [MY-010931] [Server] /opt/rh/rh-mysql80/root/usr/libexec/mysqld: ready for connections. Version: '8.0.17'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution.2020-03-27T16:35:59.460547Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/lib/mysql/mysqlx.sock' bind-address: '::' port: 33060

contrast git Source content of gitclone Then check and compare , You can see from the log that the customized script is executed :

[[email protected] /]# git clone https://github.com/sclorg/mysql-containerCloning into 'mysql-container'...
[[email protected] /]# cd mysql-container/[[email protected] /mysql-container]# ls5.5  5.6  5.7  8.0  common  examples  imagestreams  LICENSE  Makefile  README.md  root-common  s2i-common  test[[email protected] /mysql-container]# find ./ -name 50*./5.6/root-common/usr/share/container-scripts/mysql/cnf/50-my-tuning.cnf./5.6/root-common/usr/share/container-scripts/mysql/init/50-passwd-change.sh./root-common/usr/share/container-scripts/mysql/cnf/50-my-tuning.cnf./root-common/usr/share/container-scripts/mysql/init/50-passwd-change.sh


MySQL After successful deployment, as shown in the figure below :

 picture

 [[email protected] ~]# oc get svcNAME             TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGEmy-mysql-rhel7   ClusterIP   172.30.99.167   <none>        3306/TCP   20m[[email protected] ~]# oc rsh my-mysql-rhel7-1-zcwpc
sh-4.2$ mysql -h 172.30.99.167  -P 3306 -u user -ppassmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 15Server version: 8.0.17 Source distributio
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>


Up to now , We finished in OpenShift Deployment in China mysql Single instance .




stay OpenShift Deployment in China MySQL Copy


Use mysql The primary and secondary replication templates are deployed . This is a master-slave copy template . It can be customized based on this template . This article does not make customized modification :

(https://github.com/sclorg/mysql-container/blob/master/examples/replica/mysql_replica.json)

[[email protected] ~]# oc create -f mysql.jsontemplate.template.openshift.io/mysql-replication-example created

[[email protected] ~]# oc get templateNAME                        DESCRIPTION                 PARAMETERS        OBJECTSmysql-replication-example   MySQL Replication Example   8 (3 generated)   6

 picture

 picture


[[email protected] ~]# oc get podsNAME                      READY   STATUS              RESTARTS   AGEmy-mysql-rhel7-1-zcwpc    1/1     Running             0          42mmysql-master-2-deploy     1/1     Running             0          55smysql-master-2-zpvr5      0/1     ContainerCreating   0          47smysql-slave-2-deploy      1/1     Running             0          49smysql-slave-2-prlmq       0/1     ContainerCreating   0          41s
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 | foo          |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> SHOW SLAVE HOSTS;
+------------+-------------+------+------------+
| Server_id  | Host        | Port | Master_id  |
+------------+-------------+------+------------+
| 3314680171 | 172.17.0.20 | 3306 | 1301393349 |
| 3532875540 | 172.17.0.18 | 3306 | 1301393349 |
+------------+-------------+------+------------+
2 rows in set (0.01 sec)