美文网首页程序员
Linux下mysql删除库和创建库

Linux下mysql删除库和创建库

作者: 百里香香 | 来源:发表于2018-03-19 14:42 被阅读0次

    集群中的hive是要用到MySQL数据库的,在此纪录下。因为中间的其他问题曾经删除过一次hive服务,然后重新建hive数据时没有指定字符集和排序规则,导致运行sqoop迁移数据到hive时,hive启动自己建表时抛出异常,很多资料都说是字符集的问题,在此只能重新建库,重新添加服务了。

    抛的异常
    FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Transaction failed to commit)
    
    1.查看数据库
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | amon               |
    | cm                 |
    | hive               |
    | hue                |
    | mysql              |
    | oozie              |
    +--------------------+
    
    2.查看字符集
    mysql> show character set;
    +----------+-----------------------------+---------------------+--------+
    | Charset  | Description                 | Default collation   | Maxlen |
    +----------+-----------------------------+---------------------+--------+
    | big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
    | dec8     | DEC West European           | dec8_swedish_ci     |      1 |
    | cp850    | DOS West European           | cp850_general_ci    |      1 |
    | hp8      | HP West European            | hp8_english_ci      |      1 |
    | koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
    | latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
    | latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
    | swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
    | ascii    | US ASCII                    | ascii_general_ci    |      1 |
    | ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
    | sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
    | hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
    | tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
    | euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
    | koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
    | gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
    | greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
    | cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
    ......
    
    3.查看排序规则
    mysql> show collation;
    +--------------------------+----------+-----+---------+----------+---------+
    | Collation                | Charset  | Id  | Default | Compiled | Sortlen |
    +--------------------------+----------+-----+---------+----------+---------+
    | big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
    | big5_bin                 | big5     |  84 |         | Yes      |       1 |
    | dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
    | dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
    | cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
    | cp850_bin                | cp850    |  80 |         | Yes      |       1 |
    | hp8_english_ci           | hp8      |   6 | Yes     | Yes      |       1 |
    | hp8_bin                  | hp8      |  72 |         | Yes      |       1 |
    | koi8r_general_ci         | koi8r    |   7 | Yes     | Yes      |       1 |
    | koi8r_bin                | koi8r    |  74 |         | Yes      |       1 |
    | latin1_german1_ci        | latin1   |   5 |         | Yes      |       1 |
    | latin1_swedish_ci        | latin1   |   8 | Yes     | Yes      |       1 |
    ......
    
    4.删除数据库hive
    mysql> drop database hive;
    Query OK, 54 rows affected (2.42 sec)
    
    5.创建带有字符集的数据库
    mysql> create database if not exists hive character set 'utf8' collate 'utf8_general_ci';
    Query OK, 1 row affected (0.00 sec)
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | amon               |
    | cm                 |
    | hive               |
    | hue                |
    | mysql              |
    | oozie              |
    +--------------------+
    
    6.删除服务,重新添加服务

    相关文章

      网友评论

        本文标题:Linux下mysql删除库和创建库

        本文链接:https://www.haomeiwen.com/subject/jytpqftx.html