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

相关文档: