Mysql数据库 | 基于Docker搭建Mysql-8.0以上版本主从实例实战

Mysql主从环境概述

Mysql集群主从环境搭建

本次采用Docker部署集群,并且首先主从实例配置。

1.Mysql-Cluster集群服务规划

2.Mysql-Cluster集群先决条件

创建 Mysql-Cluster集群主要配置目录如下:
Mysql-Cluster集群

2.1 Mysql-Master节点[3308]

创建 Mysql-Slave节点主要配置文件目录以及配置文件如下:

Mysql-Master节点

[1].mysql.cnf配置文件:

# Copyright (c) 2015, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# 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, version 2.0, 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

#
# The MySQL  Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysql]
skip-grant-tables
skip-host-cache
skip-name-resolve
default_authentication_plugin=mysql_native_password
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
open_files_limit = 3072
back_log=200
max_connections = 20
max_connect_errors = 10
table_open_cache = 128
external-locking = FALSE
max_allowed_packet = 4M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 128
tmp_table_size = 16M
max_heap_table_size = 8M
slow_query_log = 1
long_query_time = 0.05
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 32M
default-storage-engine=InnoDB
log-bin=mysql-bin
binlog_format=row
server-id = 1
transaction_isolation = REPEATABLE-READ
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
default-storage-engine=INNODB
innodb_thread_concurrency=12
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=30
innodb_io_capacity=10000
innodb_log_buffer_size=1024M
innodb_flush_method=O_DIRECT
innodb_flush_neighbors=0
innodb_log_files_in_group=4
innodb_compression_level=0
innodb_file_per_table=1
innodb_compression_pad_pct_max=50
innodb_buffer_pool_size=256M
interactive_timeout=500
wait_timeout=500
innodb_log_file_size=256M
lower_case_table_names=1
lower_case_file_system=ON
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
[mysqld]
default_authentication_plugin=mysql_native_password
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
skip-grant-tables
skip-host-cache
skip-name-resolve
skip-grant-tables
init_connect='SET NAMES utf8mb4'
open_files_limit = 3072
back_log=200
max_connections = 20
max_connect_errors = 10
table_open_cache = 128
external-locking = FALSE
max_allowed_packet = 4M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 128
tmp_table_size = 16M
max_heap_table_size = 8M
slow_query_log = 1
long_query_time = 0.05
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 32M
default-storage-engine=InnoDB
log-bin=mysql-bin
binlog_format=row
server-id = 1
transaction_isolation = REPEATABLE-READ
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
default-storage-engine=INNODB
innodb_thread_concurrency=12
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=30
innodb_io_capacity=10000
innodb_log_buffer_size=1024M
innodb_flush_method=O_DIRECT
innodb_flush_neighbors=0
innodb_log_files_in_group=4
innodb_compression_level=0
innodb_file_per_table=1
innodb_compression_pad_pct_max=50
innodb_buffer_pool_size=256M
interactive_timeout=500
wait_timeout=500
innodb_log_file_size=256M
lower_case_table_names=1
lower_case_file_system=ON
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
[mysqldump]
quick
quote-names
max_allowed_packet=500M
[client]
default-character-set=utf8mb4
2.2 Mysql-Slave节点[3309]

创建 Mysql-Slave节点主要配置文件目录以及配置文件如下:
Mysql-Slave

[1].mysql.cnf配置文件:

 # Copyright (c) 2015, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# 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, version 2.0, 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

#
# The MySQL  Client configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysql]
skip-grant-tables
skip-host-cache
skip-name-resolve
default_authentication_plugin=mysql_native_password
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
open_files_limit = 3072
back_log=200
max_connections = 20
max_connect_errors = 10
table_open_cache = 128
external-locking = FALSE
max_allowed_packet = 4M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 128
tmp_table_size = 16M
max_heap_table_size = 8M
slow_query_log = 1
long_query_time = 0.05
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 32M
default-storage-engine=InnoDB
log-bin=mysql-bin
binlog_format=row
server-id = 2
relay-log = mysql-relay
transaction_isolation = REPEATABLE-READ
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
default-storage-engine=INNODB
innodb_thread_concurrency=12
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=30
innodb_io_capacity=10000
innodb_log_buffer_size=1024M
innodb_flush_method=O_DIRECT
innodb_flush_neighbors=0
innodb_log_files_in_group=4
innodb_compression_level=0
innodb_file_per_table=1
innodb_compression_pad_pct_max=50
innodb_buffer_pool_size=256M
interactive_timeout=500
wait_timeout=500
innodb_log_file_size=256M
lower_case_table_names=1
lower_case_file_system=ON
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
[mysqld]
default_authentication_plugin=mysql_native_password
bind-address = 0.0.0.0
mysqlx-bind-address = 0.0.0.0
skip-grant-tables
skip-host-cache
skip-name-resolve
skip-grant-tables
init_connect='SET NAMES utf8mb4'
open_files_limit = 3072
back_log=200
max_connections = 20
max_connect_errors = 10
table_open_cache = 128
external-locking = FALSE
max_allowed_packet = 4M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 128
tmp_table_size = 16M
max_heap_table_size = 8M
slow_query_log = 1
long_query_time = 0.05
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M
key_buffer_size = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 32M
default-storage-engine=InnoDB
log-bin=mysql-bin
binlog_format=row
server-id = 2
relay-log = mysql-relay
transaction_isolation = REPEATABLE-READ
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
default-storage-engine=INNODB
innodb_thread_concurrency=12
innodb_flush_log_at_trx_commit=1
innodb_max_dirty_pages_pct=30
innodb_io_capacity=10000
innodb_log_buffer_size=1024M
innodb_flush_method=O_DIRECT
innodb_flush_neighbors=0
innodb_log_files_in_group=4
innodb_compression_level=0
innodb_file_per_table=1
innodb_compression_pad_pct_max=50
innodb_buffer_pool_size=256M
interactive_timeout=500
wait_timeout=500
innodb_log_file_size=256M
lower_case_table_names=1
lower_case_file_system=ON
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
[mysqldump]
quick
quote-names
max_allowed_packet=500M
[client]
default-character-set=utf8mb4
3.Mysql-Cluster集群Docker部署脚本
3.1 Mysql-Master节点[3308]
docker run -itd -p 3308:3306 --restart always  --privileged=true --name mysql-master --network-alias mysql-master  --hostname mysql-master -v /docker/mysql-cluster/master/conf/my.cnf:/etc/mysql/my.cnf -v /docker/mysql-cluster/master/conf/conf.d/:/etc/mysql/conf/conf.d/ -v /docker/mysql-cluster/master/data:/var/lib/mysql  -e "JAVA_OPTS=-server -Xms512m -Xmx512m -Xmn256m -Duser.home=/opt -XX:MetaspaceSize=128m -XX:MaxMetaspaceSize=320m -XX:+AlwaysPreTouch -XX:-UseBiasedLocking" -e TZ="Asia/Shanghai" -e MYSQL_ROOT_PASSWORD=PivotalCloud999@Love  mysql:latest
3.2 Mysql-Slave节点[3309]
docker run -itd -p 3309:3306 --restart always  --privileged=true --name mysql-slave --network-alias mysql-slave  --hostname mysql-slave -v /docker/mysql-cluster/slave/conf/my.cnf:/etc/mysql/my.cnf -v /docker/mysql-cluster/slave/conf/conf.d/:/etc/mysql/conf/conf.d/ -v /docker/mysql-cluster/slave/data:/var/lib/mysql  -e "JAVA_OPTS=-server -Xms512m -Xmx512m -Xmn256m -Duser.home=/opt -XX:MetaspaceSize=128m -XX:MaxMetaspaceSize=320m -XX:+AlwaysPreTouch -XX:-UseBiasedLocking" -e TZ="Asia/Shanghai" -e MYSQL_ROOT_PASSWORD=PivotalCloud999@Love  mysql:latest
4.Mysql-Cluster集群Docker执行部署
4.1 Mysql-Master节点[3308]
Marklin:~ marklin$ docker run -itd -p 3308:3306 --restart always  --privileged=true --name mysql-master --network-alias mysql-master  --hostname mysql-master -v /docker/mysql-cluster/master/conf/my.cnf:/etc/mysql/my.cnf -v /docker/mysql-cluster/master/conf/conf.d/:/etc/mysql/conf/conf.d/ -v /docker/mysql-cluster/master/data:/var/lib/mysql  -e "JAVA_OPTS=-server -Xms512m -Xmx512m -Xmn256m -Duser.home=/opt -XX:MetaspaceSize=128m -XX:MaxMetaspaceSize=320m -XX:+AlwaysPreTouch -XX:-UseBiasedLocking" -e TZ="Asia/Shanghai" -e MYSQL_ROOT_PASSWORD=PivotalCloud999@Love  mysql:latest
d99ac84d4e11dd3835b89ff410a488c8041c43ec67aa4bccb1679edf57b08539
Marklin:~ marklin$
4.2 Mysql-Slave节点[3309]
Marklin:~ marklin$ docker run -itd -p 3309:3306 --restart always  --privileged=true --name mysql-slave --network-alias mysql-slave  --hostname mysql-slave -v /docker/mysql-cluster/slave/conf/my.cnf:/etc/mysql/my.cnf -v /docker/mysql-cluster/slave/conf/conf.d/:/etc/mysql/conf/conf.d/ -v /docker/mysql-cluster/slave/data:/var/lib/mysql  -e "JAVA_OPTS=-server -Xms512m -Xmx512m -Xmn256m -Duser.home=/opt -XX:MetaspaceSize=128m -XX:MaxMetaspaceSize=320m -XX:+AlwaysPreTouch -XX:-UseBiasedLocking" -e TZ="Asia/Shanghai" -e MYSQL_ROOT_PASSWORD=PivotalCloud999@Love  mysql:latest
fe3eb3b02fc0cf9e187a8158b58bb5832d70a4462b583d4ba4cf4297ff7dd8d3
Marklin:~ marklin$
4.3 Mysql-Cluster集群部署以及测试结果:

Mysql-Cluster集群部署:

Mysql-Cluster集群

测试Navicat客户端连接数据如下:

Navicat客户端

5.Mysql-Cluster集群主从节点后续配置
5.1 Mysql-Cluster集群节点IP地址

1.mysql-master节点IPAddress: docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master

Marklin:~ marklin$ docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master
172.17.0.13
Marklin:~ marklin$

2.查看mysql-slave节点的IPAddress: docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-slave

Marklin:~ marklin$ docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-slave
172.17.0.14
Marklin:~ marklin$
5.2 Mysql-Cluster集群主从用户授权配置

进入Docker后台的两种方式:
1.通过终端输入docker ps查看容器进程,最后执行docker exec -it mysql容器名称/id bash
执行docker ps:

Marklin:~ marklin$ docker ps
CONTAINER ID   IMAGE                                      COMMAND                  CREATED        STATUS         PORTS                                                                                              NAMES
fe3eb3b02fc0   mysql:latest                               "docker-entrypoint.s…"   18 hours ago   Up 2 hours     33060/tcp, 0.0.0.0:3309->3306/tcp                                                                  mysql-slave
d99ac84d4e11   mysql:latest                               "docker-entrypoint.s…"   18 hours ago   Up 2 hours     33060/tcp, 0.0.0.0:3308->3306/tcp                                                                  mysql-master
59030d0bff07   docker/dev-environments-default:stable-1   "sleep infinity"         19 hours ago   Up 4 minutes                                                                                                      mysql-cluster
d0f4142d8781   dubbo-admin:latest                         "tini -- /usr/local/…"   13 days ago    Up 2 hours     0.0.0.0:8082->8080/tcp                                                                             dubbo-monitor
c91f1bd81cf9   nacos-server:latest                        "bin/docker-startup.…"   13 days ago    Up 2 hours     0.0.0.0:8848->8848/tcp                                                                             nacos-server
fefa57c84ee1   dubbo-admin:latest                         "tini -- /usr/local/…"   13 days ago    Up 2 hours     0.0.0.0:8080->8080/tcp                                                                             dubbo-server
f3b3f54acfc3   zookeeper:latest                           "/docker-entrypoint.…"   13 days ago    Up 2 hours     0.0.0.0:2181->2181/tcp, 0.0.0.0:2888->2888/tcp, 0.0.0.0:3888->3888/tcp, 8080/tcp                   zookeeper-server
1f323ef0299f   mysql:5.7.36                               "docker-entrypoint.s…"   5 weeks ago    Up 2 hours     33060/tcp, 0.0.0.0:3307->3306/tcp                                                                  mysql5.7-server
a53b9713927f   rabbitmq:latest                            "docker-entrypoint.s…"   5 weeks ago    Up 2 hours     0.0.0.0:5671-5672->5671-5672/tcp, 4369/tcp, 15691-15692/tcp, 25672/tcp, 0.0.0.0:15672->15672/tcp   rabbitmq-server
bd7637c345b6   mysql:8.0.21                               "docker-entrypoint.s…"   5 weeks ago    Up 2 hours     0.0.0.0:3306->3306/tcp, 33060/tcp                                                                  mysql-server
87aa352ecead   redis:latest                               "docker-entrypoint.s…"   5 weeks ago    Up 2 hours     0.0.0.0:6379->6379/tcp                                                                             redis-server
6af30979d57c   minio:latest                               "/usr/bin/docker-ent…"   5 weeks ago    Up 2 hours     0.0.0.0:8081->8081/tcp, 0.0.0.0:9000->9000/tcp                                                     minio-server
51ffd8d090ba   portainer-ce:latest                        "/portainer"             5 weeks ago    Up 2 hours     8000/tcp, 9443/tcp, 0.0.0.0:9999->9000/tcp                                                         portainer-server
Marklin:~ marklin$ 

进入mysql容器: docker exec -it d99ac84d4e11 bash

Marklin:~ marklin$ docker exec -it d99ac84d4e11 bash
root@mysql-master:/#

2.通过Docker可视化后台进入:
点击Consle:

点击Conect:

进入到mysql容器:

登录mysql客户端:mysql -u root -p

root@mysql-master:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'PivotalCloud999@Love';
Query OK, 0 rows affected (0.10 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'PivotalCloud999@Love';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'PivotalCloud999@Love';
Query OK, 0 rows affected (0.06 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

如图所示:

查看master状态:show master status; ,记录二进制文件名和位置

mysql> show variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+
| character_set_server            | utf8mb4                              |
| collation_server                | utf8mb4_0900_ai_ci                   |
| immediate_server_version        | 999999                               |
| innodb_dedicated_server         | OFF                                  |
| innodb_ft_server_stopword_table |                                      |
| original_server_version         | 999999                               |
| server_id                       | 1                                    |
| server_id_bits                  | 32                                   |
| server_uuid                     | 9a675ceb-64d5-11ec-b628-0242ac11000d |
+---------------------------------+--------------------------------------+
9 rows in set (0.00 sec)

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

mysql>

如图所示:

版权声明:本文为博主原创文章,遵循相关版权协议,如若转载或者分享请附上原文出处链接和链接来源。

推荐这些文章:

mysql主从搭建

mysql安装指南
1配置准备
安装使用普通用户(本次安装统一使用mysql用户),安装包使用mysql-5.7.35-el7-x86_64.tar.gz解压版安装。

Mysql 5.7.3的安装配置

机器
192.168.1.199(es03-qa)、192.168.1.200(app01-qa)

系统
CentOS Linux release 7.6.1810 (Core)

系统内核
core-4.1-amd64:core-4.1-noarch

安装包
mysql-5.7.13-linux-glibc2.5-x86_64.tar.gz

安装路径
/hom...

Docker安装MySQL

此处拿mysql5.7作为示范

1.安装mysql5.7镜像
docker pull mysql:5.7
5.7: Pulling from library/mysql
f003217c5aae: Already exists
65d94f01a09f: Already exists
43d78aaa6078: Already exists
a0f91ffbdf69: Already exists
59ee9e07e12f: Already exists
04d82978082c: Already exists
70f46ebb971a: Already exists
ba61822c6...

Docker安装mysql

1、下载mysql
[root@VM_177_101_centos xieshuang]# docker pull mysql:5.7.18
5.7.18: Pulling from library/mysql
9f0706ba7422: Pull complete
2290e155d2d0: Pull complete
547981b8269f: Pull complete
2c9d42ed2f48: Pull complete
55e3122f1297: Pull complete
abc10bd84060: Pull complete
c0a5ce64f2b0: Pull ...

jira :数据库配置要求

https://confluence.atlassian.com/adminjiraserver0817/connecting-jira-applications-to-mysql-5-7-1071815640.html
https://confluence.atlassian.com/adminjiraserver0817/connecting-jira-applications-to-a-database-1071815634.html#ConnectingJiraapplicationstoadatabase-UpgradingJiraormigratingJiratoanothers...

Mysql环境搭建

第一步:在甲骨文mysql的官网上面选择要下载的版本https://www.mysql.com/downloads/
第二步:配置环境变量,右键我的电脑>高级系统设置>环境变量>新建系统变量>名称MYSQL_HOME,路径选择Mysql的安装目录>path变量新建,位置指向bin目录>确定
第三步:在安装目录下新建一个my.ini的配置文件,里面写如下代码
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
default-storage-engine=INNODB
s...

Mysql事务实战

事务
pre
mysql8.0查看事务隔离级别的语句
SELECT @@transaction_isolation;

创建表
CREATE TABLE hero (
number INT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8;

插入数据
INSERT INTO hero VALUES(1, '刘备', '蜀');

脏读

开启两个窗口,分别设置隔离级别

set session transaction_isol...

MySQL主从搭建

MySQL主从搭建

环境准备

mysql安装
省略

架构图

异步复制
1.确保2台服务器已安装相同版本的mysql
2.在主库上,设置一个复制使用的账户,并授予 REPLICATION SLAVE权限。这里创建一个复制用户repl,可以从IP为192.168.1.109(从库)的主机进行连接:
命令文本:GRANT REPLICATION SLAVE ON . To 'rep1'@'192.168.1.109' IDENTIFIED BY '1234test';
3.修改主数据库服务器的配置文件 my.cnf,开启 BINLOG,并设置...

7步实现mysql cluster 搭建

1.安装背景
在三台服务器 115 116 117安装好mysql 8.0
2.官网下载mysql cluster
https://dev.mysql.com/downloads/cluster/
 
3.上传到服务器并解压

#上传到服务器目录/usr/softwares并解压
tar -xzvf mysql-cluster-gpl-7.5.15-linux-glibc2.12-x86_64.tar.gz

#新建/usr/local/mysql目录
mkdir /usr/local/mysql
#将解压的文件重命名为mysql,并放到/usr/local/mysql目录下 mv...

mysql 事务总结

1. 脏读
指一个事务读取了另一个事务还未提交的数据
2. 不可重复读(可以接受的,读取的是有效的数据)
指一个事务前后两次读区的同一数据不一致 (另一个事务是提交了修改)
3. 幻读(可以接受的,读取的是有效的数据)
指一个事务两次查询的结果集纪录数不一致(另一个事务是提交了新的记录)
事务的隔离性
Innodb 的隔离级别

隔离级别
脏读
不可重复读
幻读
隔离性
并发性

顺序读(Serializable)
N
N
N
最高
最低

可重复读(Repeatable Read)
N
N
Y/N(8.0)

读已提交(Read Commited)
N
Y
Y

读...

mysql教程(上)

MySQL 创建数据库

MySQL 删除数据库

MySQL 选择数据库

MySQL 数据类型

MySQL 创建数据表

MySQL 删除数据表

MySQL 插入数据

MySQL 查询数据

MySQL WHERE 子句

MySQL UPDATE 更新

MySQL DELETE 语句

MySQL LIKE 子句

MySQL UNION 操作符

MySQL 排序

MySQL GROUP BY 语句

MySQL 连接的使用

...

文章标题:Mysql数据库 | 基于Docker搭建Mysql-8.0以上版本主从实例实战
文章链接:https://www.dianjilingqu.com/51318.html
本文章来源于网络,版权归原作者所有,如果本站文章侵犯了您的权益,请联系我们删除,联系邮箱:saisai#email.cn,感谢支持理解。
THE END
< <上一篇
下一篇>>