Distributed | Simple problem repair process encountered in DBLE docker deployment

Author: Yang Taotao

Senior database expert, specializing in MySQL research for more than ten years. Good at MySQL, PostgreSQL, MongoDB and other open source databases related to backup and recovery, SQL tuning, monitoring operation and maintenance, high-availability architecture design, etc. Currently working in Aikesheng, providing MySQL-related technical support and MySQL-related course training for major operators and banking and financial enterprises.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

First of all, if you completely follow the official website documentation, there is definitely no problem. The DBLE official website documentation has been written in great detail.

It happens that I have the latest MySQL docker image (MySQL 8.0.29) in my environment. I am lazy to change the DBLE background MySQL version to 8.0.29, and the subnet to 172.20.0.0/16 (I already have other docker containers on my machine that occupy the default subnet operation).

After installing DBLE, there are two small problems:

1. Since the IP address is different from the configuration packaged by the DOCKER image, the subsequent initialization will not be successful.

2. After modifying the IP address, a library prompts that the administrator does not have permission to create.

Then let's fix these two small problems.

1. Pull down the latest version of DBLE first.

 root@ytt-large:/home/ytt# docker image ls | grep -E '^action|^mysql'
   mysql                8.0.29    b2500a44757f   8 days ago     524MB
   actiontech/dble      latest    9988614a8e4b   6 months ago   755MB

2. Create a docker network environment and connect MySQL and DBLE in the background.

 root@ytt-large:/home/ytt# docker network create \
   >  -o "com.docker.network.bridge.name"="dble-net" \
   >   --subnet 172.20.0.0/16 dble-net
   360a9408c35cd8b8d49ad2e58ca447d5518dbea2d954badc0e618ad5d0c072a1

3. Create two backend MySQL services, the version is MySQL 8.0.29, and the mapped port 3306 is 33061 and 33062 respectively.

 root@ytt-large:/home/ytt# docker run --name backend-mysql1 \

>  --ip 172.20.0.2 -e MYSQL_ROOT_PASSWORD=123456  \
>  -p 33061:3306 --network=dble-net  \
>  -d mysql:8.0.29 --server-id=1
54505aeca71ae7c4553a0fa98e705ee302cdfc08c2b472768afc6170dddf6d37

root@ytt-large:/home/ytt#  docker run --name backend-mysql2 \
>   --ip 172.20.0.3 -e MYSQL_ROOT_PASSWORD=123456 \
>  -p 33062:3306 --network=dble-net \
>  -d mysql:8.0.29 --server-id=2 
5f907b977fc242be35dc01840a5393f2ee754572dd1d59e2fb072032df1ed8d0

4. After about 30 seconds of MySQL initialization, start DBLE.

 root@ytt-large:/home/ytt# docker run -d -i -t --name dble-server \

>  --ip 172.20.0.5 -p 8066:8066 -p 9066:9066 \
>  --network=dble-net actiontech/dble:latest
df80d0e2451c237afb4792f93e29738579f125288daf0dfee4484ddca8350110

5. The normal initialization of DBLE needs to load the shard nodes according to the configuration file, and import the sample library table file /opt/dble/conf/template_table.sql. Check the DBLE startup log, the error content is: Failed to connect to service port 8066, it should be that the IP address in the configuration file has not been changed synchronously.

 root@ytt-large:/home/ytt# docker logs dble-server
   dble init&start in docker
   Starting dble-server...
   wait-for-it.sh: waiting 15 seconds for 127.0.0.1:8066
   wait-for-it.sh: 127.0.0.1:8066 is available after 6 seconds
   init shardingNode and execute template_table.sql
   ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 104
   ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
   dble init finish

6. Use docker cp to modify db.xml in the host environment and copy it to the container or enter the container environment and directly modify the url value in db.xml to the correct IP address, then exit the container and restart dble-server.

 root@ytt-large:/home/ytt# docker exec -it dble-server /bin/bash
   
   [root@df80d0e2451c /]# cat /opt/dble/conf/db.xml | grep 172
           <dbInstance name="instanceM1" url="172.20.0.2:3306" user="root" password="123456" maxCon="300" minCon="10"
           <dbInstance name="instanceM2" url="172.20.0.3:3306" user="root" password="123456" maxCon="300" minCon="10"
           
   root@ytt-large:/home/ytt# docker restart dble-server
   dble-server

7. Check the DBLE log again. There is a new error message: It prompts that the service account does not have permission to access the database testdb2.

 [root@df80d0e2451c /]# dble init&start in docker
   Starting dble-server...
   wait-for-it.sh: waiting 15 seconds for 127.0.0.1:8066
   wait-for-it.sh: 127.0.0.1:8066 is available after 2 seconds
   init shardingNode and execute template_table.sql
   ERROR 1044 (HY000) at line 200 in file: '/opt/dble/conf/template_table.sql': Access denied for user 'root' to database 'testdb2'
   ERROR 1146 (42S02) at line 202 in file: '/opt/dble/conf/template_table.sql': Table 'testdb.tb_test1' doesn't exist in the config of sharding
   ERROR 1146 (42S02) at line 207 in file: '/opt/dble/conf/template_table.sql': Table 'testdb.tb_test1' doesn't exist
   ERROR 1146 (42S02) at line 210 in file: '/opt/dble/conf/template_table.sql': Table 'testdb.tb_test2' doesn't exist in the config of sharding
   ERROR 1146 (42S02) at line 215 in file: '/opt/dble/conf/template_table.sql': Table 'testdb.tb_test2' doesn't exist
   dble init finish

8. Connect DBLE to check and find that the permissions are sufficient. That should be the logic library is not added in the user configuration file.

 root@ytt-large:/home/ytt# mysql -uroot -p123456 -P8066 -h 127.0.0.1 -e "show grants for root" -s |grep 'CREATE'
mysql: [Warning] Using a password on the command line interface can be insecure.
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE,... ON *.* TO `root`@`%` WITH GRANT OPTION

9. Similar to step 6, add the logic library testdb2 to the DBLE configuration file user.xml, exit the container and restart dble-server when finished.

 [root@df80d0e2451c ~]# cat /opt/dble/conf/user.xml | grep 'testdb2'
   <shardingUser name="root" password="123456" schemas="testdb,testdb2" readOnly="false" blacklist="blacklist1" maxCon="20"/>
       
root@ytt-large:/home/ytt# docker restart dble-server
dble-server

10. Check the log content again, there is no error.

 root@ytt-large:/home/ytt# docker logs dble-server | tail -n 6
    Starting dble-server...
    Removed stale pid file: /opt/dble/dble.pid
    wait-for-it.sh: waiting 15 seconds for 127.0.0.1:8066
    wait-for-it.sh: 127.0.0.1:8066 is available after 2 seconds
    init shardingNode and execute template_table.sql
    dble init finish

11. Connect to the service port and check the logical library: It has been created normally.

 root@ytt-large:/home/ytt# mysql -uroot -p123456 -h127.0.0.1 -P8066 -e "show databases"
mysql: [Warning] Using a password on the command line interface can be insecure.
    +----------+
    | DATABASE |
    +----------+
    | testdb   |
    | testdb2  |
    +----------+