你的位置:首页 > 数据库

[数据库]mysql数据库权限及编码


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 http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm

安装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)

解决方法:

1、先修改 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表权限,已都有权限:

 | tigase | *04B3CD6E8CDAF8E0494A9E3076FDCDCBD155A98A | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y  

tigase可以使用  tigasedb 。

 

 

mysqldump -u root -p testdb > testdb.sql (不包括存储过程和函数)
mysqldump -u root -p -R testdb > testdb.sql (**包括存储过程和函数**)