CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
在tigase中，发送消息有文本的形式，比如发送emoji表情， 编码必须采用 utf8mb4 ， utf8mb4 is a utf8 character set, which was added to MySQL in version 5.3.3, that fully supports unicode. utf8mb4是一种可支持4个字节UTF编码，一个字符最多能有4字节，所以能支持更多的字符集。比如可以支持emoji表情。
mysql> show create table msg_history;
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table
| Create Table
|
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| msg_history | CREATE TABLE `msg_history` (
`msg_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expired` datetime DEFAULT NULL,
`sender_uid` bigint(20) unsigned DEFAULT NULL,
`receiver_uid` bigint(20) unsigned NOT NULL,
`msg_type` int(11) NOT NULL,
`message` text CHARACTER SET utf8mb4 NOT NULL,
UNIQUE KEY `msg_id` (`msg_id`),
KEY `expired` (`expired`),
KEY `sender_uid` (`sender_uid`,`receiver_uid`),
KEY `receiver_uid` (`receiver_uid`,`sender_uid`)
) ENGINE=InnoDB AUTO_INCREMENT=110488 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
另外：
CREATE TABLE `qcloud_sms_template` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`id`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8;只有 mysql 5.6.5+ 才支持 DATETIME类型
所以安装 mysql 的时候 必须升级到 5.6.5 以上版本。
centos 上安装mysql：
service mysqld stop
yum remove mysql mysql-*
查看是否有残余的mysq，输入命令：
yum list installed | grep mysql
如果有，可输入命令删除：
rum remove mysql－libs
下载安装最新的rpm文件
rpm -Uvh
安装MySQL，输入命令：
yum install mysql-community-server
问题：
[root@host-192-168-1-36 software]# mysql -utigase -ptigase
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user \'tigase\'@\'localhost\' (using password: YES)
解决方法：
１、先修改 root 密码：
用SET PASSWORD命令
mysql -u root
mysql> SET PASSWORD FOR \'root\'@\'localhost\' = PASSWORD(\'newpass\');
或者用UPDATE直接编辑user表
mysql -u root
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD(\'newpass\') WHERE user = \'root\';
mysql> FLUSH PRIVILEGES;
用root 账号登录mysql， 查看mysql 数据库 中 的 user 表，是否有 tigase账号， 如果没有，则执行：
GRANT USAGE ON *.* TO \'tigase\'@\'localhost\' IDENTIFIED BY \'tigase\';
2）授权法。例如，你想wow使用mypassword从任何主机连接到mysql服务器的话。
GRANT ALL PRIVILEGES ON *.* TO \'wow\'@\'%\' IDENTIFIED BY \'mypassword\' WITH GRANT OPTION;
如果你想只允许用户wow从ip为192.168.83.56的主机连接到192.168.11.12的mysql服务器，并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO \'wow\'@\'192.168.83.56\' IDENTIFIED BY \'mypassword\' WITH GRANT OPTION;
FLUSH PRIVILEGES
如果用tigase账号登录mysql 后，无法使用 tigasedb 数据库：
mysql> use tigasedb;
ERROR 1044 (42000): Access denied for user \'tigase\'@\'localhost\' to database \'tigasedb\'
查看user表：
| localhost | tigase | *04B3CD6E8CDAF8E0494A9E3076FDCDCBD155A98A | N | N | N | N | N | N | N | N | N | N
权限都是 N，
解决办法：
使用 root 账号登录mysql ，
mysql> use mysql;
mysql> GRANT ALL ON *.* TO \'tigase\'@\'localhost\';
mysql> FLUSH PRIVILEGES;
再次查看 user表权限，已都有权限：