{"title": "Mysql\u7b14\u8bb0", "update_time": "2016-04-25 15:03:09", "tags": "mysql", "pid": "242", "icon": "linux.png"}
## Mysql使用utf-8编码 一个MySQL的my.cnf配置文件，将myql的默认编码设为UFT-8 ``` [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 collation-server = utf8_general_ci default-collation = utf8_general_ci character-set-server = utf8 default-character-set = utf8 character-set-client-handshake = false [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ``` 查看MySQL运行编码 ``` mysql> show variables like 'character\_set\_%'; ``` ## 用户权限相关 MySQL 的root密码恢复 ``` # sudo /etc/init.d/mysqld stop # sudo mysqld_safe --user=mysql --skip-grant-tables --skip-networking & # mysql -u root mysql mysql> use mysql; mysql> UPDATE user SET Password=PASSWORD('密码') where USER='root'; mysql> FLUSH PRIVILEGES; mysql> quit # sudo /etc/init.d/mysqld restart # mysql -uroot -p Enter password: <输入新设的密码newpassword> mysql> ``` MySQL创建用户 ``` CREATE USER '用户名'@'%' IDENTIFIED BY '密码'; ``` MySQL用户授权 ``` GRANT ALL PRIVILEGES ON 数据库名.* TO '用户名'@'%'; ``` MySQL 创建用户并授权 ``` GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' IDENTIFIED BY '密码' WITH GRANT OPTION; FLUSH PRIVILEGES; ``` 使用mysql命令行登录mysql数据库命令行(-p表示提示密码，-P后面跟的是mysql服务开启的端口) ``` mysql -h 主机ip -u 用户名 -p -P mysql端口 -D 数据库名 ``` ## MySQL使用相关 一条普通的建表语句 ``` create table benchmark_info ( sn varchar(255), hostname varchar(255), test varchar(255), type varchar(255), subtype varchar(255), uniontype varchar(255), name varchar(255), value double, dt char(8), primary key (sn) ); ``` 建立一个id自增的表 ``` create table mytable( id int auto_increment primary key, hosts text ); ``` 查看指定表的空间占用大小 ``` select concat(round(sum(DATA_LENGTH/1024/1024),2), 'MB') as data \ from information_schema.TABLES \ where table_name= 'benchmark_info' ; ``` 使用Insert Select语句 ``` INSERT INTO benchmark_info (sn,type,value) \ select 'benchmark',type,sum(value)/count(value) from benchmark_info \ where test='cpu_pai' and name!='0' group by type; ``` 查看表的建表语句 ``` show create table benchmark_info ``` ## Mac下安装MySQL ``` brew install mysql ``` 如果发现无法启动，可能是因为data目录权限问题，尝试 ``` chown -R mysql /usr/local/var/mysql ``` 如果安装Mysql-python时报lib问题。可以尝试增加以下环境变量 ``` export DYLD_LIBRARY_PATH="/usr/local/lib" export VERSIONER_PYTHON_PREFER_64_BIT=no export VERSIONER_PYTHON_PREFER_32_BIT=yes ```