MySql主从部署的实现步骤

硬件环境：香橙派5 aarch64架构
软件环境：ubuntu 22.04.3 lts
软件版本：mysql-8.0.42
操作方式：mysql_1,mysql_2容器
主节点：mysql_1
启动命令：docker run --name mysql_master \
  -p 3308:3306 \
  -v /data/test/mysql_master/data:/var/lib/mysql \
  -v /data/test/mysql_master/conf:/etc/mysql/conf.d \
  -v /data/test/mysql_master/logs:/var/log/mysql \
  -v /etc/localtime:/etc/localtime:ro \
  -e mysql_root_password=test0123 \
  -e mysql_database=myappdb \
  -e mysql_user=myuser \
  -e mysql_password=test0123 \
  --restart=always \
  -d mysql:8.0.42 \
  --default-authentication-plugin=mysql_native_password \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci

从节点：mysql_2
启动命令：docker run --name mysql_slave \
  -p 3309:3306 \
  -v /data/test/mysql_slave/data:/var/lib/mysql \
  -v /data/test/mysql_slave/conf:/etc/mysql/conf.d \
  -v /data/test/mysql_slave/logs:/var/log/mysql \
  -v /etc/localtime:/etc/localtime:ro \
  -e mysql_root_password=test0123 \
  -e mysql_database=myappdb \
  -e mysql_user=myuser \
  -e mysql_password=test0123 \
  --restart=always \
  -d mysql:8.0.42 \
  --default-authentication-plugin=mysql_native_password \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4
 
[mysqld]
# 设置时区
default-time_zone = '+8:00'
#设置密码验证规则
authentication_policy=mysql_native_password
# 限制导入和导出的数据目录
# 为空，不限制导入到处的数据目录；
secure_file_priv=
init_connect='set collation_connection = utf8mb4_general_ci'
init_connect='set names utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
skip-name-resolve
 
# 开启logbin
log_bin=binlog
# binlog日志格式
binlog_format=row
# mysql主从备份serverid
server_id=1
 
#参数优化，避免mysql占用太多内存
# 减少缓冲池大小
innodb_buffer_pool_size = 64m
# 降低临时表大小
tmp_table_size = 16m
# 设置最大连接数
max_connections = 10

# 0表示读写 (主机)，1表示只读(从机)
read-only=0

#设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000

#控制单个二进制日志大小。此参数的最大和默认值是1gb
max_binlog_size=20

#设置不要复制的数据库
binlog-ignore-db=test

#设置需要复制的数据库,不写参数则默认全部记录，可以填写多个
binlog-do-db=需要复制的主数据库名字
例如:
binlog-do-db=dbtest01
binlog-do-db=dbtest02

#设置binlog格式
binlog_format=statement

#进入容器内部
docker exec -it mysql_master /bin/bash
#登录mysql
mysql -u root -p
#查看数据库
show databases;
#切换到mysql库
use mysql;
#创建master用户
create user 'master'@'%' identified by 'root';
grant all on *.* to 'master'@'%';
flush privileges;

[client]
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4
 
[mysqld]
# 设置时区
default-time_zone = '+8:00'
#设置密码验证规则
authentication_policy=mysql_native_password
# 限制导入和导出的数据目录
# 为空，不限制导入到处的数据目录；
secure_file_priv=
init_connect='set collation_connection = utf8mb4_general_ci'
init_connect='set names utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake
skip-name-resolve
 
#server_id不要和主库的server_id相同就行
server_id=2
#一般从数据库作为读数据库
default-storage-engine=myisam

#进入容器内部
docker exec -it mysql_slave /bin/bash
#登录mysql
mysql -u root -p
#查看数据库
show databases;
#切换到mysql库
use mysql;
#创建slave用户
create user 'slave'@'%' identified by 'root';
grant all on *.* to 'slave'@'%';
flush privileges;

#查看master节点binlog日志状态
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| file          | position | binlog_do_db | binlog_ignore_db | executed_gtid_set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      841 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

#停止slave
stop slave;

#查看master节点binlog日志状态，master_log_file、master_log_pos根据master数据库查询信息配置
 change master to master_host = '172.17.0.5', #host
 master_port = 3306,#
 master_user = 'master',#binlog
 master_password = 'passwd',#binlog
 master_log_file = 'binlog.000003',#binlog1
 master_log_pos = 841;
 
#启动slave
start slave;

#查看slave库状态
mysql> show slave status\g;
*************************** 1. row ***************************
               slave_io_state: waiting for source to send event
                  master_host: 172.17.0.5
                  master_user: slave
                  master_port: 3306
                connect_retry: 60
              master_log_file: binlog.000003
          read_master_log_pos: 841
               relay_log_file: 4b87ab620b91-relay-bin.000002
                relay_log_pos: 323
        relay_master_log_file: binlog.000003
             slave_io_running: yes
            slave_sql_running: yes
              replicate_do_db: 
          replicate_ignore_db: 
           replicate_do_table: 
       replicate_ignore_table: 
      replicate_wild_do_table: 
  replicate_wild_ignore_table: 
                   last_errno: 0
                   last_error: 
                 skip_counter: 0
          exec_master_log_pos: 841
              relay_log_space: 540
              until_condition: none
               until_log_file: 
                until_log_pos: 0
           master_ssl_allowed: no
           master_ssl_ca_file: 
           master_ssl_ca_path: 
              master_ssl_cert: 
            master_ssl_cipher: 
               master_ssl_key: 
        seconds_behind_master: 0
master_ssl_verify_server_cert: no
                last_io_errno: 0
                last_io_error: 
               last_sql_errno: 0
               last_sql_error: 
  replicate_ignore_server_ids: 
             master_server_id: 1
                  master_uuid: 61357c39-2e76-11f0-9f6f-0242ac110005
             master_info_file: mysql.slave_master_info
                    sql_delay: 0
          sql_remaining_delay: null
      slave_sql_running_state: replica has read all relay log; waiting for more updates
           master_retry_count: 86400
                  master_bind: 
      last_io_error_timestamp: 
     last_sql_error_timestamp: 
               master_ssl_crl: 
           master_ssl_crlpath: 
           retrieved_gtid_set: 
            executed_gtid_set: 
                auto_position: 0
         replicate_rewrite_db: 
                 channel_name: 
           master_tls_version: 
       master_public_key_path: 
        get_master_public_key: 0
            network_namespace: 
1 row in set, 1 warning (0.01 sec)

mysql -u root -h 127.0.0.1 -p -p3308

#登录master节点创建简单数据表
create table `test` (
  `id` int not null auto_increment,
  `test_str` varchar(100) character set utf8mb4 collate utf8mb4_unicode_ci default null,
  primary key (`id`)
) engine=innodb auto_increment=4 default charset=utf8mb4 collate=utf8mb4_unicode_ci;

mysql> show tables;
+-------------------+
| tables_in_myappdb |
+-------------------+
| test              |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from test;
empty set (0.00 sec)

mysql> insert into test (test_str) values( '1');
query ok, 1 row affected (0.02 sec)

mysql> select * from test;
+----+----------+
| id | test_str |
+----+----------+
|  1 | 1        |
+----+----------+
1 row in set (0.00 sec)

mysql -u root -h 127.0.0.1 -p -p3309

mysql> use myappdb
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a

database changed
mysql> show tables;
+-------------------+
| tables_in_myappdb |
+-------------------+
| test              |
+-------------------+
1 row in set (0.01 sec)

mysql> select * from test;
+----+----------+
| id | test_str |
+----+----------+
|  1 | 1        |
+----+----------+
1 row in set (0.01 sec)