mysql删除已创建的库_Linux下mysql删除库和创建库

集群中的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.删除服务，重新添加服务