MySql教程
搭建mysql主从复制（报错：Last_Error: Error 'Unknown database）
本文主要是介绍搭建mysql主从复制（报错：Last_Error: Error 'Unknown database），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
一、准备挂载文件
为了将配置文件在宿主机做挂载，先运行测试镜像拷贝配置文件
1、拉取镜像
docker pull mysql:8.0.26
2、创建测试容器mysql-demo
docker run -it -p 3300:3306 \ --name mysql-demo \ -e MYSQL_ROOT_PASSWORD=123456 \ -d mysql:8.0.26
运行时改成一排执行：
docker run -it -p 3300:3306 --name mysql-demo -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.26
3、在root目录下创建mysql-master文件夹，在mysql-master文件夹下创建conf和data文件夹。
mkdir -p ~/mysql-master/conf ~/mysql-master/data
4、将mysql-demo容器内的/etc/mysql/my.cnf文件拷贝到~/mysql-master/conf目录下
docker cp mysql-demo:/etc/mysql/my.cnf ~/mysql-master/conf
5、将mysql-master中的内容全部拷贝到mysql-slave中
cp -r ~/mysql-master ~/mysql-slave
6、停止并删除测试容器
docker stop mysql-demo docker rm mysql-demo
二、创建自定义网络
默认的bridge 网桥无法指定固定的ip，会导致mysql服务的ip不固定，最好配置为自定义网络
docker network create --driver bridge --subnet 192.172.0.0/16 --gateway 192.172.0.1 newnet
查看网络
[root@xxx conf]# docker network ls NETWORK ID NAME DRIVER SCOPE dfd016599931 bridge bridge local 9951733f6b75 host host local 8efcdae3efd4 newnet bridge local 9eae2373bc1f none null local
三、创建主从容器
创建两个自定义网路的MySQL容器，使用newnet定义IP时，前面两个数字要与newnet的对应，后面两个可以随便指定，但不能超过255
1、创建主容器
docker run -it -p 3301:3306 \ --name mysql-master \ --net newnet --ip 192.172.0.30 \ -v ~/mysql-master/conf/my.cnf:/etc/mysql/my.cnf \ -v ~/mysql-master/data:/var/lib/mysql \ --privileged=true \ --restart=always \ -e MYSQL_ROOT_PASSWORD=123456 \ -e TZ=Asia/Shanghai \ mysql:8.0.26
执行时去掉\改成一排
docker run -it -p 3301:3306 --name mysql-master --net newnet --ip 192.172.0.30 -v ~/mysql-master/conf/my.cnf:/etc/mysql/my.cnf -v ~/mysql-master/data:/var/lib/mysql --privileged=true --restart=always -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai mysql:8.0.26
控制台打印日志如下：
[root@xxx conf]# docker run -it -p 3301:3306 --name mysql-master --net newnet --ip 192.172.0.30 -v ~/mysql-master/conf/my.cnf:/etc/mysql/my.cnf -v ~/mysql-master/data:/var/lib/mysql --privileged=true --restart=always -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai mysql:8.0.26 2022-07-23 09:58:48+08:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.26-1debian10 started. 2022-07-23 09:58:48+08:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql' 2022-07-23 09:58:48+08:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.26-1debian10 started. 2022-07-23 09:58:48+08:00 [Note] [Entrypoint]: Initializing database files 2022-07-23T01:58:48.354370Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.26) initializing of server in progress as process 43 2022-07-23T01:58:48.363794Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2022-07-23T01:58:49.105917Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2022-07-23T01:58:50.972408Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main 2022-07-23T01:58:50.972752Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main 2022-07-23T01:58:51.081275Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. 2022-07-23 09:58:55+08:00 [Note] [Entrypoint]: Database files initialized 2022-07-23 09:58:55+08:00 [Note] [Entrypoint]: Starting temporary server mysqld will log errors to /var/lib/mysql/f0d909d9adcb.err mysqld is running as pid 94 2022-07-23 09:58:56+08:00 [Note] [Entrypoint]: Temporary server started. Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it. 2022-07-23 09:58:58+08:00 [Note] [Entrypoint]: Stopping temporary server 2022-07-23 09:59:01+08:00 [Note] [Entrypoint]: Temporary server stopped 2022-07-23 09:59:01+08:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up. 2022-07-23T01:59:01.295583Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.26) starting as process 1 2022-07-23T01:59:01.306472Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2022-07-23T01:59:01.508782Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2022-07-23T01:59:01.729201Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main 2022-07-23T01:59:01.729347Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main 2022-07-23T01:59:01.730375Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2022-07-23T01:59:01.730543Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2022-07-23T01:59:01.734139Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory. 2022-07-23T01:59:01.753416Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock 2022-07-23T01:59:01.753477Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.26' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2、创建从容器
docker run -it -p 3302:3306 \ --name mysql-slave \ --net newnet --ip 192.172.0.31 \ -v ~/mysql-slave/conf/my.cnf:/etc/mysql/my.cnf \ -v ~/mysql-slave/data:/var/lib/mysql \ --privileged=true \ --restart=always \ -e MYSQL_ROOT_PASSWORD=123456 \ -e TZ=Asia/Shanghai \ mysql:8.0.26
执行时去掉\改为一行执行
docker run -it -p 3302:3306 --name mysql-slave --net newnet --ip 192.172.0.31 -v ~/mysql-slave/conf/my.cnf:/etc/mysql/my.cnf -v ~/mysql-slave/data:/var/lib/mysql --privileged=true --restart=always -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai mysql:8.0.26
此时已经启动了两个容器mysql-master和mysql-slave
[root@xxx conf]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 7f7b489358dc mysql:8.0.26 "docker-entrypoint.s…" 20 seconds ago Up 19 seconds 33060/tcp, 0.0.0.0:3302->3306/tcp, :::3302->3306/tcp mysql-slave f0d909d9adcb mysql:8.0.26 "docker-entrypoint.s…" 4 minutes ago Up 4 minutes 33060/tcp, 0.0.0.0:3301->3306/tcp, :::3301->3306/tcp mysql-master
查看newnet网络
命令：
docker network inspect newnet
部分结果如下：
"Containers": {
"7f7b489358dcf9d00934f44b0af93c32ffd4a2d5f3f3e92804b87d2dddc66b7b": {
"Name": "mysql-slave",
"EndpointID": "849399bdedfe72e87b63dd676882308b2cc48852baba63f5a685a03bffc5c574",
"MacAddress": "02:42:c0:ac:00:1f",
"IPv4Address": "192.172.0.31/16",
"IPv6Address": ""
},
"f0d909d9adcbec956cf595594a2452383a68f115a3982cec2fb516c0c741c3e3": {
"Name": "mysql-master",
"EndpointID": "6fc7a3327ae274fa394e635789f15e3bdd8781b05096e65abe95a8466eaeb0d9",
"MacAddress": "02:42:c0:ac:00:1e",
"IPv4Address": "192.172.0.30/16",
"IPv6Address": ""
}
},
如果是云服务器，则要在安全组放开3301和3302端口。
本地电脑的navicat访问mysql-master
发现该数据库中已经有了msyql自带的四个数据库。
3、修改主容器的my.conf文件
在宿主机中，配置mysql-master挂载的my.cnf文件
编辑my.cnf文件
vim ~/mysql-master/conf/my.cnf
在my.cnf文件中放入如下内容
server-id=100 log-bin=master-bin #开启二进制文件 #binlog-do-db=demo #需要同步的二进制数据库名； binlog-ignore-db=information_schema #不同步的二进制数据库名,如果不设置可以将其注释掉; binlog-ignore-db=mysql binlog-ignore-db=performance_schema binlog-ignore-db=sys #log-slave-update #这个是把更新的记录写到二进制文件中；
如下所示：
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # 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 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 Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL # Custom config should go here !includedir /etc/mysql/conf.d/ server-id=100 log-bin=master-bin #binlog-do-db=demo binlog-ignore-db=information_schema binlog-ignore-db=mysql binlog-ignore-db=performance_schema binlog-ignore-db=sys #log-slave-update
server-id: 唯一服务器ID不能和其他服务器的server-id重复;
4、重启mysql-master使配置生效
配置完成后，需要重启mysql-master容器使其修改的配置文件生效，使用如下命令使mysql进行重启
docker restart mysql-master
四、配置mysql-slave的my.cnf文件
相同方法配置mysql-slave挂载的my.cnf文件
1、编辑my.cnf文件
vim ~/mysql-slave/conf/my.cnf
2、添加如下配置
# 设置server_id,注意要唯一 server-id=101 # 开启二进制日志功能，以备Slave作为其它Slave的Master时使用 log-bin=slave-bin # relay_log配置中继日志 relay_log=/var/lib/mysql/relay.log read_only=1 # 设置为只读,该项如果不设置，表示slave可读可写
如下所示：
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved. # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; version 2 of the License. # # 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 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 Server configuration file. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL # Custom config should go here !includedir /etc/mysql/conf.d/ # 设置server_id,注意要唯一 server-id=101 # 开启二进制日志功能，以备Slave作为其它Slave的Master时使用 log-bin=slave-bin # relay_log配置中继日志 relay_log=/var/lib/mysql/relay.log read_only=1 # 设置为只读,该项如果不设置，表示slave可读可写
注意：一定要加上replicate-do-db，否则后面会报错：Last_Error: Error 'Unknown database，后面会讲到。
应该添加replicate-do-db参数，如下所示：
# 设置server_id,注意要唯一 server-id=101 # 开启二进制日志功能，以备Slave作为其它Slave的Master时使用 log-bin=slave-bin # relay_log配置中继日志 relay_log=/var/lib/mysql/relay.log read_only=1 # 设置为只读,该项如果不设置，表示slave可读可写 replicate-do-db=nacos_config # 指定要同步的数据库，必须的
3、重启mysql-slave容器
docker restart mysql-slave
五、创建用户并授权
在Master数据库创建数据同步用户，授予用户 slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。
1、进入mysql-master容器
docker exec -it mysql-master bash
2、登录mysql-master
mysql -uroot -p123456
3、创建slave用户
create user 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';
4、授予slave用户REPLICATION SLAVE权限和REPLICATION CLIENT权限
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
5、刷新
flush privileges;
6、查看权限
SHOW GRANTS FOR 'slave'@'%';
结果
mysql> SHOW GRANTS FOR 'slave'@'%'; +-------------------------------------------------------------------+ | Grants for slave@% | +-------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `slave`@`%` | +-------------------------------------------------------------------+ 1 row in set (0.00 sec)
表示授权成功!
7、进入mysql-slave容器，以slave账号连接mysql-master。
[root@xxx ~]# docker exec -it mysql-slave bash root@7f7b489358dc:/# mysql -h192.172.0.30 -uslave -pslave mysql: [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 35 Server version: 8.0.26 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>
说明从服务器通过账号slave，密码slave可以连接mysql-master容器。
8、查看Master状态
show master status;
结果如下：
+-------------------+----------+--------------+-------------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+-------------------------------------------------+-------------------+ | master-bin.000001 | 681 | | information_schema,mysql,performance_schema,sys | | +-------------------+----------+--------------+-------------------------------------------------+-------------------+
记住File和Position，后面需要用到。此时一定不要操作Master库，否则将会引起Master状态的变化，File和Position字段也将会进行变化。
六、开启主从复制
进入到mysql-slave容器的mysql客户端，执行如下命令：
注：mysqld 是 MySQL 的主程序，服务器端。mysql 是 MySQL 的命令行工具，客户端。1、进入mysql-slave容器
docker exec -it mysql-slave bash
2、登录
mysql -uroot -ip123456
3、执行如下命令
在进行mysql主从复制配置时，change master用于配置和改变slave服务器用于连接master服务器的参数，以便slave服务器读取master服务器的binlog以及slave服务器的relay log，同时更新master.info和relay-log.info信息。
注意:执行该语句之前，从服务器上如果IO线程和SQL线程已经启动，需要先停止，执行stop slave命令。
change master to master_host='192.172.0.30', master_user='slave', master_password='slave', master_port=3306, master_log_file='master-bin.000001', master_log_pos=681, master_connect_retry=30;
执行时放在一行执行
change master to master_host='192.172.0.30', master_user='slave', master_password='slave', master_port=3306, master_log_file='master-bin.000001', master_log_pos=681, master_connect_retry=30;
命令说明：
4、查看主从同步状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.172.0.30
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 681
Relay_Log_File: relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 681
Relay_Log_Space: 156
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: NULL
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: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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.00 sec)
ERROR:
No query specified
mysql replication 中slave机器上有两个关键的进程，死一个都不行，一个是slave_sql_running，一个是Slave_IO_Running，一个负责与主机的io通信，一个负责自己的slave mysql进程。Slave_IO_Running: no 表示从主库读取数据的IO不通。
如果Slave_SQL_Running:显示为No，请检查宿主机~/mysql-master/data/auto.cnf 与~/mysql-slave/data/auto.cnf 是否相同，若相同，请根据容器中mysql命令select uuid();进行修改。
查看~/mysql-master/data/auto.cnf
[root@xxx ~]# cat ~/mysql-master/data/auto.cnf [auto] server-uuid=fdef6cc7-0a2a-11ed-a3d3-0242c0ac001e
查看~/mysql-slave/data/auto.cnf
[root@xxx ~]# cat ~/mysql-slave/data/auto.cnf [auto] server-uuid=9eadc9d0-0a2b-11ed-8fc7-0242c0ac001f
发现不同。
如果不同，则执行如下命令
mysql> stop slave; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> START SLAVE; Query OK, 0 rows affected, 1 warning (0.01 sec)
start slave表示开启主从复制，
再次查看主从同步状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.172.0.30
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 681
Relay_Log_File: relay.000002
Relay_Log_Pos: 325
Relay_Master_Log_File: master-bin.000001
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: 1
Exec_Master_Log_Pos: 681
Relay_Log_Space: 524
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: 100
Master_UUID: fdef6cc7-0a2a-11ed-a3d3-0242c0ac001e
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.00 sec)
此时如果Slave_IO_State: Waiting for source to send event，且Slave_IO_Running和Slave_SQL_Running都为YES，表示主从复制成功.
七、报错：Last_Error: Error 'Unknown database
本地电脑中navicat登录主容器和从容器
主容器:
此时主容器中有四个库
1、在navicate中执行如下sql语句
CREATE DATABASE nacos_config;
USE nacos_config;
/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = config_info */
/******************************************/
CREATE TABLE `config_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`data_id` varchar(255) NOT NULL COMMENT 'data_id',
`group_id` varchar(255) DEFAULT NULL,
`content` longtext NOT NULL COMMENT 'content',
`md5` varchar(32) DEFAULT NULL COMMENT 'md5',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
`src_user` text COMMENT 'source user',
`src_ip` varchar(50) DEFAULT NULL COMMENT 'source ip',
`app_name` varchar(128) DEFAULT NULL,
`tenant_id` varchar(128) DEFAULT '' COMMENT '租户字段',
`c_desc` varchar(256) DEFAULT NULL,
`c_use` varchar(64) DEFAULT NULL,
`effect` varchar(64) DEFAULT NULL,
`type` varchar(64) DEFAULT NULL,
`c_schema` text,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_configinfo_datagrouptenant` (`data_id`,`group_id`,`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='config_info';
/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = config_info_aggr */
/******************************************/
CREATE TABLE `config_info_aggr` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`data_id` varchar(255) NOT NULL COMMENT 'data_id',
`group_id` varchar(255) NOT NULL COMMENT 'group_id',
`datum_id` varchar(255) NOT NULL COMMENT 'datum_id',
`content` longtext NOT NULL COMMENT '内容',
`gmt_modified` datetime NOT NULL COMMENT '修改时间',
`app_name` varchar(128) DEFAULT NULL,
`tenant_id` varchar(128) DEFAULT '' COMMENT '租户字段',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_configinfoaggr_datagrouptenantdatum` (`data_id`,`group_id`,`tenant_id`,`datum_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='增加租户字段';
/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = config_info_beta */
/******************************************/
CREATE TABLE `config_info_beta` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`data_id` varchar(255) NOT NULL COMMENT 'data_id',
`group_id` varchar(128) NOT NULL COMMENT 'group_id',
`app_name` varchar(128) DEFAULT NULL COMMENT 'app_name',
`content` longtext NOT NULL COMMENT 'content',
`beta_ips` varchar(1024) DEFAULT NULL COMMENT 'betaIps',
`md5` varchar(32) DEFAULT NULL COMMENT 'md5',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
`src_user` text COMMENT 'source user',
`src_ip` varchar(50) DEFAULT NULL COMMENT 'source ip',
`tenant_id` varchar(128) DEFAULT '' COMMENT '租户字段',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_configinfobeta_datagrouptenant` (`data_id`,`group_id`,`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='config_info_beta';
/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = config_info_tag */
/******************************************/
CREATE TABLE `config_info_tag` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`data_id` varchar(255) NOT NULL COMMENT 'data_id',
`group_id` varchar(128) NOT NULL COMMENT 'group_id',
`tenant_id` varchar(128) DEFAULT '' COMMENT 'tenant_id',
`tag_id` varchar(128) NOT NULL COMMENT 'tag_id',
`app_name` varchar(128) DEFAULT NULL COMMENT 'app_name',
`content` longtext NOT NULL COMMENT 'content',
`md5` varchar(32) DEFAULT NULL COMMENT 'md5',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
`src_user` text COMMENT 'source user',
`src_ip` varchar(50) DEFAULT NULL COMMENT 'source ip',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_configinfotag_datagrouptenanttag` (`data_id`,`group_id`,`tenant_id`,`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='config_info_tag';
/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = config_tags_relation */
/******************************************/
CREATE TABLE `config_tags_relation` (
`id` bigint(20) NOT NULL COMMENT 'id',
`tag_name` varchar(128) NOT NULL COMMENT 'tag_name',
`tag_type` varchar(64) DEFAULT NULL COMMENT 'tag_type',
`data_id` varchar(255) NOT NULL COMMENT 'data_id',
`group_id` varchar(128) NOT NULL COMMENT 'group_id',
`tenant_id` varchar(128) DEFAULT '' COMMENT 'tenant_id',
`nid` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`nid`),
UNIQUE KEY `uk_configtagrelation_configidtag` (`id`,`tag_name`,`tag_type`),
KEY `idx_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='config_tag_relation';
/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = group_capacity */
/******************************************/
CREATE TABLE `group_capacity` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`group_id` varchar(128) NOT NULL DEFAULT '' COMMENT 'Group ID，空字符表示整个集群',
`quota` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '配额，0表示使用默认值',
`usage` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '使用量',
`max_size` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '单个配置大小上限，单位为字节，0表示使用默认值',
`max_aggr_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '聚合子配置最大个数，，0表示使用默认值',
`max_aggr_size` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '单个聚合数据的子配置大小上限，单位为字节，0表示使用默认值',
`max_history_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '最大变更历史数量',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='集群、各Group容量信息表';
/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = his_config_info */
/******************************************/
CREATE TABLE `his_config_info` (
`id` bigint(64) unsigned NOT NULL,
`nid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`data_id` varchar(255) NOT NULL,
`group_id` varchar(128) NOT NULL,
`app_name` varchar(128) DEFAULT NULL COMMENT 'app_name',
`content` longtext NOT NULL,
`md5` varchar(32) DEFAULT NULL,
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`src_user` text,
`src_ip` varchar(50) DEFAULT NULL,
`op_type` char(10) DEFAULT NULL,
`tenant_id` varchar(128) DEFAULT '' COMMENT '租户字段',
PRIMARY KEY (`nid`),
KEY `idx_gmt_create` (`gmt_create`),
KEY `idx_gmt_modified` (`gmt_modified`),
KEY `idx_did` (`data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='多租户改造';
/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = tenant_capacity */
/******************************************/
CREATE TABLE `tenant_capacity` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`tenant_id` varchar(128) NOT NULL DEFAULT '' COMMENT 'Tenant ID',
`quota` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '配额，0表示使用默认值',
`usage` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '使用量',
`max_size` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '单个配置大小上限，单位为字节，0表示使用默认值',
`max_aggr_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '聚合子配置最大个数',
`max_aggr_size` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '单个聚合数据的子配置大小上限，单位为字节，0表示使用默认值',
`max_history_count` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '最大变更历史数量',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='租户容量信息表';
CREATE TABLE `tenant_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`kp` varchar(128) NOT NULL COMMENT 'kp',
`tenant_id` varchar(128) default '' COMMENT 'tenant_id',
`tenant_name` varchar(128) default '' COMMENT 'tenant_name',
`tenant_desc` varchar(256) DEFAULT NULL COMMENT 'tenant_desc',
`create_source` varchar(32) DEFAULT NULL COMMENT 'create_source',
`gmt_create` bigint(20) NOT NULL COMMENT '创建时间',
`gmt_modified` bigint(20) NOT NULL COMMENT '修改时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_tenant_info_kptenantid` (`kp`,`tenant_id`),
KEY `idx_tenant_id` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='tenant_info';
CREATE TABLE `users` (
`username` varchar(50) NOT NULL PRIMARY KEY,
`password` varchar(500) NOT NULL,
`enabled` boolean NOT NULL
);
CREATE TABLE `roles` (
`username` varchar(50) NOT NULL,
`role` varchar(50) NOT NULL,
UNIQUE INDEX `idx_user_role` (`username` ASC, `role` ASC) USING BTREE
);
CREATE TABLE `permissions` (
`role` varchar(50) NOT NULL,
`resource` varchar(255) NOT NULL,
`action` varchar(8) NOT NULL,
UNIQUE INDEX `uk_role_permission` (`role`,`resource`,`action`) USING BTREE
);
INSERT INTO users (username, password, enabled) VALUES ('nacos', '$2a$10$EuWPZHzz32dJN7jexM34MOeYirDdFAZm2kuWj7VEOJhhZkDrxfvUu', TRUE);
INSERT INTO roles (username, role) VALUES ('nacos', 'ROLE_ADMIN');
此时查看从容器的数据库，发现同步没有成功，此时进入从容器查看主从同步状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.172.0.30
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 27299
Relay_Log_File: relay.000002
Relay_Log_Pos: 585
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1049
Last_Error: Error 'Unknown database 'nacos_config'' on query. Default database: 'nacos_config'. Query: '/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = config_info */
/******************************************/
CREATE TABLE `config_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`data_id` varchar(255) NOT NULL COMMENT 'data_id',
`group_id` varchar(255) DEFAULT NULL,
`content` longtext NOT NULL COMMENT 'content',
`md5` varchar(32) DEFAULT NULL COMMENT 'md5',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
`src_user` text COMMENT 'source user',
`src_ip` varchar(50) DEFAULT NULL COMMENT 'source ip',
`app_name` varchar(128) DEFAULT NULL,
`tenant_id` varchar(128) DEFAULT '' COMMENT '租户字段',
`c_desc` varchar(256) DEFAULT NULL,
`c_use` varchar(64) DEFAULT NULL,
`effect` varchar(64) DEFAULT
Skip_Counter: 0
Exec_Master_Log_Pos: 941
Relay_Log_Space: 27142
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1049
Last_SQL_Error: Error 'Unknown database 'nacos_config'' on query. Default database: 'nacos_config'. Query: '/******************************************/
/* 数据库全名 = nacos_config */
/* 表名称 = config_info */
/******************************************/
CREATE TABLE `config_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`data_id` varchar(255) NOT NULL COMMENT 'data_id',
`group_id` varchar(255) DEFAULT NULL,
`content` longtext NOT NULL COMMENT 'content',
`md5` varchar(32) DEFAULT NULL COMMENT 'md5',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
`src_user` text COMMENT 'source user',
`src_ip` varchar(50) DEFAULT NULL COMMENT 'source ip',
`app_name` varchar(128) DEFAULT NULL,
`tenant_id` varchar(128) DEFAULT '' COMMENT '租户字段',
`c_desc` varchar(256) DEFAULT NULL,
`c_use` varchar(64) DEFAULT NULL,
`effect` varchar(64) DEFAULT
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: fdef6cc7-0a2a-11ed-a3d3-0242c0ac001e
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 220723 11:25:49
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.00 sec)
ERROR:
No query specified
原因分析：在配置mysql-slave的my.cnf文件时，没有配置replicate-do-db参数，
2、修改如下：
# 设置server_id,注意要唯一 server-id=101 # 开启二进制日志功能，以备Slave作为其它Slave的Master时使用 log-bin=slave-bin # relay_log配置中继日志 relay_log=/var/lib/mysql/relay.log read_only=1 # 设置为只读,该项如果不设置，表示slave可读可写 replicate-do-db=nacos_config # 指定要同步的数据库，必须的
3、重启mysql-slave容器。
4、进入mysql-slave容器，停掉从库的slave
stop slave;
并在主库删除掉新建的库
drop database nacos_config;
5、重新进行主从复制
进入主容器,
[root@xxx ~]# docker exec -it mysql-master bash root@f0d909d9adcb:/# mysql -uroot -p123456 mysql: [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 29 Server version: 8.0.26 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> show master status; +-------------------+----------+--------------+-------------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+-------------------------------------------------+-------------------+ | master-bin.000001 | 39400 | | information_schema,mysql,performance_schema,sys | | +-------------------+----------+--------------+-------------------------------------------------+-------------------+ 1 row in set (0.00 sec) mysql>
进入从容器，执行以下命令
change master to master_host='192.172.0.30', master_user='slave', master_password='slave', master_port=3306, master_log_file='master-bin.000001', master_log_pos=39400, master_connect_retry=30;
6、查看主从同步状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.172.0.30
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 52701
Relay_Log_File: relay.000004
Relay_Log_Pos: 325
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: nacos_config
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: 52701
Relay_Log_Space: 524
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: 100
Master_UUID: fdef6cc7-0a2a-11ed-a3d3-0242c0ac001e
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.00 sec)
ERROR:
No query specified
7、主容器再次执行sql脚本，发现mysql-slave容器的数据库中多出了一个nacos_config的库，而且表的数据跟主容器的表的数量一致，说明MySQL主从复制搭建成功！
这篇关于搭建mysql主从复制（报错：Last_Error: Error 'Unknown database）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
部署MySQL集群入门：新手必读指南11-20
-
部署MySQL集群教程：初学者指南11-20
-
部署MySQL集群项目实战：新手教程11-20
-
部署MySQL集群资料：新手入门教程11-20
-
MySQL集群部署教程：入门级详解11-20
-
MySQL集群教程：入门与实践指南11-20
-
部署MySQL集群教程：新手入门指南11-20
-
MySQL读写分离教程：轻松入门11-20
-
部署MySQL集群入门：一步一步搭建你的数据库集群11-20
-
部署MySQL集群学习：入门教程11-19
-
如何部署MySQL集群：新手入门教程11-19
-
Mysql安装教程：新手必看的详细安装指南11-19
-
Mysql安装入门：新手必读指南11-18
-
MySQL事务MVCC原理入门详解11-18
-
MySQL资料：新手入门教程11-16
栏目导航
