Docker构建Mysql

r囧r小猫 2022-04-25 05:04 222阅读 0赞

## 系统环境 ##

Centos：7.4

Docker：18.09.3

Mysql：8.x

## 安装Docker ##

参见：[https://blog.csdn.net/yan\_dk/article/details/89427641][https_blog.csdn.net_yan_dk_article_details_89427641]

## 安装单Mysql ##

**//下载镜像mysql8**

\#docker pull mysql

\#自定义网络

\#docker network create  --subnet=192.168.10.0/16  mynetwork

**//生成容器**

docker run -itd \
        --net mynetwork \
        --ip 192.168.0.3 \
        -p 3308:3306 \
        -e MYSQL_ROOT_PASSWORD=123456 \
        -v /vdb1/usr/docker/mysql8/data:/usr/local/mysql/data:rw \
        -v /vdb1/usr/docker/mysql8/log:/usr/local/mysql/log:rw \
        -v /vdb1/usr/docker/mysql8/config/my.cnf:/etc/mysql/my.cnf:rw \
        -v /etc/localtime:/etc/localtime:ro \
        --name mysql-v8 \
        --restart=always \
        mysql:latest

*  mysql/data 是数据库文件存放的地方。必须要挂载到容器外，否则容器重启一切数据消失。
 *  mysql/log 是数据库主生的log。建议挂载到容器外。
 *  mysql/config/my.cnf 是数据库的配置文件，在下面会放出来。
 *  /etc/localtime:/etc/localtime:ro 是让容器的时钟与宿主机时钟同步，避免时区的问题，ro是read only的意思，就是只读。

my.cnf内容如下：

[mysqld]
    server-id = 1
    port = 3306
    basedir = /usr/local/mysql8
    datadir = /usr/local/mysql8/data
    secure-file-priv= NULL
    tmpdir  = /tmp
    socket  = /tmp/mysql8.sock
    skip_name_resolve = 1
    sql_mode=NO_ENGINE_SUBSTITUTION
    transaction_isolation = READ-COMMITTED
    character-set-server = utf8mb4
    collation-server = utf8mb4_general_ci
    init_connect='SET NAMES utf8mb4'
    sync_binlog = 20
    max_connections=10000
    innodb_buffer_pool_size = 24G
    innodb_read_io_threads = 8
    innodb_write_io_threads = 8
    innodb_flush_log_at_trx_commit = 0
    innodb_lock_wait_timeout = 120
    innodb_max_dirty_pages_pct = 90
    event_scheduler=1
    lower_case_table_names = 1
    log_bin = ON
    binlog_format = row
    binlog_cache_size = 2M
    slow_query_log = ON
    general_log = ON
    
    default_authentication_plugin= mysql_native_password

**//进入容器**

\#docker exec -it mysql-v8 /bin/bash

/\#cd /usr/local/mysql/data & ls

这是可以看到已经有数据了，再看看宿主机的相应数据目录，也是有相同数据，说明这2个数据已经做了docker的数据卷同步了。

**//进入容器后的初始化安装组件**

*  docker容器内安装yum

\#apt-get update

\#apt install yum

\#apt install wget

\#apt install weget

*  **docker容器内部自行安装vim**

使用`apt-get install vim`命令安装vim，会出现如下问题：

Reading package lists... Done
    Building dependency tree       
    Reading state information... Done
    E: Unable to locate package vim

执行`apt-get update`，然后再次执行`apt-get install vim`即可成功安装vim。

注意：使用时，若启用右键鼠标粘贴模式，需要 :set mouse -=a

*  **[使用退格键时出现^H解决方法][H]**

\#stty erase ^H

\#stty erase ^?

## 安装Mysql主从复制 ##

> 参考[https://www.cnblogs.com/songwenjie/p/9371422.html][https_www.cnblogs.com_songwenjie_p_9371422.html]
> 
> [https://blog.csdn.net/zyhlwzy/article/details/80569422][https_blog.csdn.net_zyhlwzy_article_details_80569422]

**目录规划**

![20190503092331645.png][]

**Master（主）**

**配置Master主端同步my.cnf**

[mysqld]
    ...
    log_bin = master-bin
    
    #二进制日志记录的数据库(多数据库用逗号，隔开)
    binlog-do-db = testdb
    #二进制日志中忽略数据库 (多数据库用逗号，隔开)
    #binlog-ignore-db =

docker run -itd \
        --net mynetwork \
        --ip 192.168.10.1 \
        -p 3301:3306 \
        -e MYSQL_ROOT_PASSWORD=123456 \
        -v /vdb1/usr/docker/mysql_ms/master/data:/usr/local/mysql/data:rw \
        -v /vdb1/usr/docker/mysql_ms/master/log:/usr/local/mysql/log:rw \
        -v /vdb1/usr/docker/mysql_ms/master/config/my.cnf:/etc/mysql/my.cnf:rw \
        -v /etc/localtime:/etc/localtime:ro \
        --name mysql-master1 \
        --restart=always \
        mysql:latest

**//进入容器**

\#docker exec -it mysql-master1 /bin/bash

//操作mysql

\#mysql -h192.168.10.1 -uroot -p123456

**Slave（从）**

**配置Slave从端同步my.cnf**

[mysqld]
    ...
    log_bin = slave-bin
    
    #设定需要复制的数据库(多数据库使用逗号，隔开)
    replicate-do-db=testdb

docker run -itd \
        --net mynetwork \
        --ip 192.168.10.2 \
        -p 3302:3306 \
        -e MYSQL_ROOT_PASSWORD=123456 \
        -v /vdb1/usr/docker/mysql_ms/slave/data:/usr/local/mysql/data:rw \
        -v /vdb1/usr/docker/mysql_ms/slave/log:/usr/local/mysql/log:rw \
        -v /vdb1/usr/docker/mysql_ms/slave/config/my.cnf:/etc/mysql/my.cnf:rw \
        -v /etc/localtime:/etc/localtime:ro \
        --name mysql-slave1 \
        --restart=always \
        mysql:latest

**//进入容器**

\#docker exec -it mysql-slave1 /bin/bash

//操作mysql

\#mysql -h192.168.10.2 -uroot -p123456

**mysql主从建立如下：**

![20190503095639692.png][]

**Master节点配置复制**

**指定主从用户账户**

<table> 
 <tbody> 
  <tr> 
   <td>mysql&gt; CREATE USER 'repl'@'192.168.10.2' IDENTIFIED WITH mysql_native_password BY 'rep123456';<br> mysql&gt; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.10.2';<br> mysql&gt; flush privileges;</td> 
  </tr> 
 </tbody> 
</table>

mysql> SHOW MASTER STATUS;

![2019050311001993.png][]

**Slave节点上设置主节点参数**

<table> 
 <tbody> 
  <tr> 
   <td>mysql&gt; CHANGE MASTER TO<br> MASTER_HOST='192.168.10.1',<br> MASTER_USER='repl',<br> MASTER_PASSWORD='rep123456',<br> MASTER_LOG_FILE='master-bin.000002',<br> MASTER_LOG_POS=155;</td> 
  </tr> 
 </tbody> 
</table>

mysql> show slave status\\G;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbl9kaw_size_16_color_FFFFFF_t_70][]

**启动主从同步**

mysql> start slave;

mysql> show slave status\\G;

## 问题记录 ##

*  问题：mysql主从同步配置文件正常，状态正常、日志正常，但是数据不同步

解决：注意

1) 设置my.inf参数log-bin = mysql-bin；

2) 设置slave-skip-errors=all等参数

3） 但是多数据库仍然存在问题？？？，目前只解决到支持1个数据库的主从，待后续解决。

参考[https://blog.csdn.net/woshishui6501/article/details/84103545][https_blog.csdn.net_woshishui6501_article_details_84103545]

*  问题：主从复制，mysql重启后，重新设置从端，报错ERROR 3021 (HY000): Unknown error 3021

解决：mysql>stop slave; 然后重新执行从端配置语句，再mysql>start slave;

*  问题：主从复制，mysql重启后，主端show master status，报错ERROR 1064 (42000):

解决：重新执行mysql> GRANT REPLICATION SLAVE ON \*.\* TO 'repl'@'192.168.10.12';  mysql> flush privileges;

mysql> show master status;

持续完善，待续...

参考：[https://www.cnblogs.com/badtree/articles/10130695.html][https_www.cnblogs.com_badtree_articles_10130695.html]

[https_blog.csdn.net_yan_dk_article_details_89427641]: https://blog.csdn.net/yan_dk/article/details/89427641
[H]: https://www.cnblogs.com/fankoko/p/4654647.html
[https_www.cnblogs.com_songwenjie_p_9371422.html]: https://www.cnblogs.com/songwenjie/p/9371422.html
[https_blog.csdn.net_zyhlwzy_article_details_80569422]: https://blog.csdn.net/zyhlwzy/article/details/80569422
[20190503092331645.png]: /images/20220210/2532121937d74f928395bb475e24bda6.png
[20190503095639692.png]: /images/20220210/f46b5a6e28894864b8197341dca9bd43.png
[2019050311001993.png]: /images/20220210/502e31397df54d63ab9bbe403ca7455a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lhbl9kaw_size_16_color_FFFFFF_t_70]: /images/20220210/5c45d4401bb241aba6cf8c09d7f53674.png
[https_blog.csdn.net_woshishui6501_article_details_84103545]: https://blog.csdn.net/woshishui6501/article/details/84103545
[https_www.cnblogs.com_badtree_articles_10130695.html]: https://www.cnblogs.com/badtree/articles/10130695.html