MySQL 运维

MySQL 主从跳过
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

修复表
REPAIR TABLE aaa;

MySQL导出存储过程
mysqldump -h localhost -u root -p -t -d -R db_tms_two > D:\cccc.sql
mysqldump -hlocalhost -uroot -ntd -R hqgr > hqgr.sql
其中的 -ntd 是表示导出存储过程；-R是表示导出函数

mySQL 同步数据量过大(最大1G)
max-allowed-packet=1M

锁定数据库不能进行写入操作
FLUSH TABLES WITH READ LOCK;

解锁数据库
UNLOCK TABLES;

查看具体的log命令
show variables like ‘log_bin’;

查看所有的log命令
show variables like ‘log_%’;

MySQL 导出二进制日志为SQL
mysqlbinlog –start-datetime=”2013-04-04 00:00:00” –stop-datetime=”2013-04-05 23:59:59” d:\hycx-40-bin.000006 -r D:\20130402.sql

mysql 导出数据
mysqldump -uroot -p db_tms_two_sd_134 > D:\db_tms_sd_20130316.sql

查看mysql权限
SHOW GRANTS FOR sdmysql2013@’10.82.66.241’;

mysql用户权限
SELECT
INSERT
UPDATE
DELETE
CREATE
EXECUTE
TRIGGER

mysql刷新
flush privileges;

取消MySql用户权限
REVOKE SELECT, INSERT, UPDATE, DELETE ON easyinfo_two.* FROM ‘sdmysql2013’@’10.82.66.242’

mysql授权若账号不存在自动创建
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,EXECUTE,TRIGGER ON 数据库.表 TO 账号@”访问IP” IDENTIFIED BY “密码”;

mysql授权所有权限
GRANT ALL PRIVILEGES ON db_tms_two.* TO sdmysql2013@’10.82.66.241’ ;

mysql创建用户
CREATE USER 账号 IDENTIFIED BY ‘密码’;
CREATE USER 账号@”访问Ip” IDENTIFIED BY ‘密码’;

查看存储过程
show procedure status;

查看进程
show processlist;
show full processlist ;

查看表结构
desc table_name;
describe table_name;
show columns from table_name;
show create table table_name;

查看计划任务：
SHOW VARIABLES LIKE ‘%event%’;

启动计划任务：
SET GLOBAL event_scheduler = ON;

mysql查询自增：
语法 select (@i:=@i+1) as i,column_name_1,* from table,(select @i:=0) as it where column_name_2 <> 0 order by i desc

查看数据的编码格式:
show variables like ‘character%’;

basedir = E:/Program Files/mysql-5.5.14-win32
datadir = E:/Program Files/mysql-5.5.14-win32/data

event_scheduler = ON
character-set-server=utf8
跳过自动启动slave
skip_slave_start