MySQL数据库运维总结

mysql常用命令{

./mysql/bin/mysqld_safe --user=mysql &   # 启动 mysql 服务

./mysql/bin/mysqladmin -uroot -p -S ./mysql/data/mysql.sock shutdown    # 停止mysql服务

mysqlcheck -uroot -p -S mysql.sock --optimize --databases account       # 检查、修复、优化MyISAM表

mysqlbinlog slave-relay-bin.000001              # 查看二进制日志(报错加绝对路径)

mysqladmin -h myhost -u root -p create dbname   # 创建数据库

flush privileges;             # 刷新权限

show databases;               # 显示所有数据库

use dbname;                   # 打开数据库

show tables;                  # 显示选中数据库中所有的表

desc tables;                  # 查看表结构

drop database name;           # 删除数据库

drop table name;              # 删除表

create database name;         # 创建数据库

select 列名称 from 表名称;    # 查询

show grants for repl@%;         # 查看用户权限

show processlist;             # 查看mysql进程

show full processlist;        # 显示进程全的语句

select user();                # 查看已登录的用户

show slave status\G;          # 查看主从状态

show variables;               # 查看所有参数变量

show status;                  # 运行状态

show table status             # 查看表的引擎状态   

show table status like '%table_name%'\G;

drop table if exists user                       # 表存在就删除

create table if not exists user                 # 表不存在就创建

select host,user,password from mysql.user;            # 查询用户权限 

create table ka(ka_id varchar(6),qianshu int);  # 创建表

show variables like 'character_set_%';          # 查看系统的字符集和 排序 方式的设定

show variables like '%timeout%';                # 查看超时(wait_timeout)

delete from mysql.user where user='';                 # 删除空用户

delete from mysql.user where user='sss' and host='localhost' ;    # 删除用户

drop user 'sss'@'localhost';                                # 使用此方法删除用户更为靠谱

ALTER TABLE mytable ENGINE = MyISAM ;                       # 改变现有的表使用的存储引擎

SHOW TABLE STATUS from  库名  where Name='表名';            # 查询表引擎

mysql -uroot -p -A -ss -h10.10.10.5 -e "show databases;"    # shell中获取数据不带表格 -ss参数

CREATE TABLE innodb (id int, title char(20))  ENGINE = INNODB                     # 创建表指定存储引擎的类型(MyISAM或INNODB)

grant replication slave on *.* to '用户'@'%' identified by '密码';               # 创建主从复制用户

ALTER TABLE player ADD INDEX weekcredit_faction_index (weekcredit, faction);     # 添加索引

alter table name add column accountid(列名)  int(11) NOT NULL(字段不为空);       # 插入字段

update host set monitor_state='Y',hostname='xuesong' where ip='192.168.1.1';     # 更新数据

自增表{

create table oldBoy  (id INTEGER  PRIMARY KEY AUTO_INCREMENT, name CHAR(30) NOT NULL, age integer , sex CHAR(15) );  # 创建自增表

insert into oldBoy(name,age,sex) values(%s,%s,%s)  # 自增插入数据

}

登录mysql的命令{

# 格式： mysql -h 主机地址 -u 用户名 -p 用户密码

mysql -h110.110.110.110 -P3306 -uroot -p

mysql -uroot -p -S /data1/mysql5/data/mysql.sock -A  --default-character-set=GBK

}

shell执行mysql命令{

mysql -u root -p'123' xuesong < file.sql   # 针对指定库执行 sql 文件中的语句,好处不需要转义特殊符号,一条语句可以换行.不指定库执行时语句中需要先use

mysql -u$username -p$passwd -h$dbhost -P$dbport -A -e "      

use $dbname;

delete from data where date=('$date1');

"    # 执行多条mysql命令

mysql -uroot -p -S mysql.sock -e "use db;alter table gift add column accountid  int(11) NOT NULL;flush privileges;"    # 不登陆mysql插入字段

}

备份数据库{

mysqldump -h host -u root -p --default-character-set=utf8 dbname >dbname_backup.sql               # 不包括库名，还原需先创建库，再use 

mysqldump -h host -u root -p --database --default-character-set=utf8 dbname >dbname_backup.sql    # 包括库名，还原不需要创建库

/bin/mysqlhotcopy -u root -p    # mysqlhotcopy只能备份MyISAM引擎

mysqldump -u root -p -S mysql.sock --default-character-set=utf8 dbname table1 table2  > /data/db.sql    # 备份指定的表

mysqldump -uroot -p123  -d database > database.sql    # 备份数据库结构

innobackupex --user=root --password="" --defaults-file=/data/mysql5/data/my_3306.cnf --socket=/data/mysql5/data/mysql.sock --slave-info --stream=tar --tmpdir=/data/dbbackup/temp /data/dbbackup/ 2>/data/dbbackup/dbbackup.log | gzip 1>/data/dbbackup/db50.tar.gz   

# xtrabackup备份需单独安装软件 优点: 速度快,压力小,可直接恢复主从复制

}

还原数据库{

mysql -h host -u root -p dbname < dbname_backup.sql   

mysql>source 路径.sql   # 登陆mysql后还原sql文件

}

赋权限{

# 指定IP: $IP  本机: localhost   所有IP地址: %   # 通常指定多条

grant all on zabbix.* to user@"$IP";             # 对现有账号赋予权限

grant select on database.* to user@"%" Identified by "passwd";     # 赋予查询权限(没有用户，直接创建)

grant all privileges on database.* to user@"$IP" identified by 'passwd';         # 赋予指定IP指定用户所有权限(不允许对当前库给其他用户赋权限)

grant all privileges on database.* to user@"localhost" identified by 'passwd' with grant option;   # 赋予本机指定用户所有权限(允许对当前库给其他用户赋权限)

grant select, insert, update, delete on database.* to user@'ip'identified by "passwd";   # 开放管理操作指令

revoke all on *.* from user@localhost;     # 回收权限

}

更改密码{

update user set password=password('passwd') where user='root'

mysqladmin -u root password 'xuesong'

}

mysql忘记密码后重置{

cd /data/mysql5

/data/mysql5/bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

use mysql;

mysql>update user set password=password('123123') where user='root';

或者直接在配置文件中修改:

vim /etc/my.cnf

[mysqld]

skip-grant-tables

}

mysql主从复制失败恢复{

slave stop;

reset slave;

change master to master_host='10.10.10.110',master_port=3306,master_user='repl',master_password='repl',master_log_file='master-bin.000010',master_log_pos=107,master_connect_retry=60;

slave start;

}

sql语句使用变量{

use xuesong;

set @a=concat('my',weekday(curdate()));    # 组合时间变量

set @sql := concat('CREATE TABLE IF NOT EXISTS ',@a,'( id INT(11) NOT NULL )');   # 组合sql语句

select @sql;                    # 查看语句

prepare create_tb from @sql;    # 准备

execute create_tb;              # 执行

}

检测mysql主从复制延迟{

1、在从库定时执行更新主库中的一个timeout数值

2、同时取出从库中的timeout值对比判断从库与主库的延迟

}

mysql慢查询{

select * from information_schema.processlist where command in ('Query') and time >5\G      # 查询操作大于5S的进程

开启慢查询日志{

# 配置文件 /etc/my.conf

[mysqld]

log-slow-queries=/var/lib/mysql/slowquery.log         # 指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log

long_query_time=5                                     # 记录超过的时间，默认为10s

log-queries-not-using-indexes                         # log下来没有使用索引的query,可以根据情况决定是否开启  可不加

log-long-format                                       # 如果设置了，所有没有使用索引的查询也将被记录    可不加

# 直接修改生效

show variables like "%slow%";                         # 查看慢查询状态 

set global slow_query_log='ON';                       # 开启慢查询日志 变量可能不同，看上句查询出来的变量

}

mysqldumpslow慢查询日志查看{

-s  # 是order的顺序，包括看了代码，主要有 c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒序 

-t  # 是top n的意思，即为返回前面多少条的数据 

-g  # 后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log                # 访问次数最多的20个sql语句

mysqldumpslow -s r -t 20 host-slow.log                # 返回记录集最多的20个sql

mysqldumpslow -t 10 -s t -g "left join" host-slow.log # 按照时间返回前10条里面含有左连接的sql语句

show global status like '%slow%';                     # 查看现在这个session有多少个慢查询

show variables like '%slow%';                         # 查看慢查询日志是否开启，如果slow_query_log和log_slow_queries显示为on，说明服务器的慢查询日志已经开启

show variables like '%long%';                         # 查看超时阀值

desc select * from wei where text='xishizhaohua'\G;   # 扫描整张表 tepe:ALL  没有使用索引 key:NULL

create index text_index on wei(text);                 # 创建索引

}

}

mysql操作次数查询{

select * from information_schema.global_status;

com_select

com_delete

com_insert

com_update

}

}