mysql数据库状态配置和查询

查看Mysql表大小

select concat(table_schema,'.',table_name) as 'table name', \
concat(round(table_rows/1000000,4),'m') as \
'number of rows', concat(round(data_length/(1024*1024*1024),4),'g') as 'data size', \
concat \(round(index_length/(1024*1024*1024),4),'g') as 'index size', \
concat(round((data_length+index_length)/(1024*1024*1024),4),'g') \

设置字符集

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

云RDS获取root超级权限

各种厂商的云库对于root的权限限制的还是又有点严格,比如删除binlog。即使root用户删除也会遇到问题:

$ show binary logs; 
$ purge binary logs to 'mysql-bin.000258';
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
解决办法:
mysql -u root -p
UPDATE mysql.user SET Super_Priv='Y' WHERE user='root' AND host='%';
FLUSH PRIVILEGES;
mysql> SHOW GRANTS FOR 'root';
+------------------------------------------------------------------+
| Grants for johnDoe                                               |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `root`                                  |
| GRANT ALL PRIVILEGES ON `db1`.* TO `root`                     |
+------------------------------------------------------------------+
即可。

注意此种方法测试环境下可以用用,线上环境不到万不得已不要折腾,有可能出现其他隐藏问题。切记不可贸然尝试!!!

本文链接:目录"--EOF--