友情链接
Copyright © 2012-2014
Mysql笔记
2016年04月25日 江南兔子哥 运维笔记->系统管理 mysql
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