最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】CentOS中如何导入mysql的schema去创建数据库的表

MySQL crifan 4383浏览 0评论

【背景】

折腾:

【记录】在CentOS 6.6中安装OpenFire

期间,想要把mysql的所谓schema:

/opt/openfire/resources/database/openfire_mysql.sql

导入到mysql的方式,去创建数据库的表。

【折腾过程】

1.参考了:

Openfire: Database Installation Guide

的:

Unix/Linux: cat openfire_mysql.sql | mysql [databaseName];

不管用:

[root@bogon ~]# cat /opt/openfire/resources/database/openfire_mysql.sql | mysql openfire_xxx
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@bogon ~]# cat /opt/openfire/resources/database/openfire_mysql.sql | mysql openfire_xxx;
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@bogon ~]# 

2.突然意识到,貌似上述语句是在mysql的界面中输入的,所以去试试:

[root@bogon ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21345
Server version: 5.5.44-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

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> cat /opt/openfire/resources/database/openfire_mysql.sql | mysql openfire_xxx;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cat /opt/openfire/resources/database/openfire_mysql.sql | mysql openfire_schooli' at line 1
mysql> 

还是不对。

3.搜:

centos  mysql create table import schema

没有找到有用的。

4.搜:

mysql import schema command line

参考:

command line – MySQL Import Database Schema – Stack Overflow

去试试:

[root@bogon ~]# mysql -u root -p openfire_xxx < /opt/openfire/resources/database/openfire_mysql.sql 
Enter password: 

就导入完成了。

5.接着进去看看都有哪些表:

[root@bogon ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21514
Server version: 5.5.44-cll-lve MySQL Community Server (GPL) by Atomicorp

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

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
    -> q
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'q' at line 2
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| openfire_xxx |
| performance_schema |
| xxx          |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> select openfire_xxx;
ERROR 1054 (42S22): Unknown column 'openfire_xxx' in 'field list'
mysql> use openfire_xxx;
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_openfire_xxx |
+------------------------------+
| ofExtComponentConf           |
| ofGroup                      |
| ofGroupProp                  |
| ofGroupUser                  |
| ofID                         |
| ofMucAffiliation             |
| ofMucConversationLog         |
| ofMucMember                  |
| ofMucRoom                    |
| ofMucRoomProp                |
| ofMucService                 |
| ofMucServiceProp             |
| ofOffline                    |
| ofPresence                   |
| ofPrivacyList                |
| ofPrivate                    |
| ofProperty                   |
| ofPubsubAffiliation          |
| ofPubsubDefaultConf          |
| ofPubsubItem                 |
| ofPubsubNode                 |
| ofPubsubNodeGroups           |
| ofPubsubNodeJIDs             |
| ofPubsubSubscription         |
| ofRemoteServerConf           |
| ofRoster                     |
| ofRosterGroups               |
| ofSASLAuthorized             |
| ofSecurityAuditLog           |
| ofUser                       |
| ofUserFlag                   |
| ofUserProp                   |
| ofVCard                      |
| ofVersion                    |
+------------------------------+
34 rows in set (0.00 sec)

mysql> 

终于导入好了。

 

【总结】

想要通过导入mysql的schema文件,即xxx.sql文件,的方式去创建mysql数据库的表的话,可以用:

mysql -u root –p database_name < mysql_file.sql

再输入密码,即可导入。

转载请注明:在路上 » 【已解决】CentOS中如何导入mysql的schema去创建数据库的表

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
82 queries in 0.186 seconds, using 22.11MB memory