select version(); #查看mysql版本
stop slave;set global sql_slave_skip_counter=1;start slave;
#从库不同步处理,第二句表示跳过一步错误,后面的数字可变
stop slave ;
CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('库.表');
start slave;
#从库不同步处理,复制的时候忽略指定的表
use table;flush tables with read lock; #备份锁表
use table;unlock tables; #解锁
select a.id,a.column,b.id,b.column from a left join b on a.id = b.id #关联查询
select CONCAT_WS(‘#’,str1,str2,…) from #查询结果以#号分隔
set global expire_logs_days = 10;flush logs;
#设置自动删除binlog文件的天数
PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
#删除10天前的binlog日志
PURGE MASTER LOGS BEFORE '2000-01-01 00:00:00';
#删除2000-01-01 00:00:00时间之前前的binlog日志
show full processlist; #查看进程
alter table old_db.mytable rename new_db.mytable #把表从一个库移到另一个库
- 备份脚本及恢复,前提:必须开启binlog, 若没开启log_bin,则修改mysql配置文件my.cnf,添加以下配置,重启mysql使配置生效
全量备份脚本:
#!/bin/bash
backuppath="/home/ccodrunner/mysqlforbak"
backupdate=`date +%Y%m%d`
logpath="/home/ccodrunner/mysqlforbak/log"
logfile="/home/ccodrunner/mysqlforbak/log/backup-0.log"
username="ucds"
password="ucds"
if [ ! -d $backuppath ];then
mkdir -p $backuppath
fi
if [ ! -d $logpath ];