CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;
比较实用
SELECT COUNT(*) TABLES, table_schema FROM information_schema.TABLES WHERE table_schema = 'database_name'
//查看test_user库user表的auto_increment的值
select auto_increment from information_schema.tables where table_schema='test_user' and table_name='user';
//修改test_user库user表的auto_increment值为1000
alter table test_user.user auto_increment=10000;
ALTER TABLE 表名 MODIFY COLUMN c_id int(11) NULL AUTO_INCREMEN
alter table customers change id id int not null auto_increment primary key;
ALTER TABLE tabelname ADD new_field_id int(5) unsigned default 0 not null auto_increment ,ADD primary key (new_field_id);
drop database db_name;
show table status where comment='view';
show create view VIEW_NAME;
DROP VIEW [IF EXISTS] view_name;
mysqldump --force //--force参数
- tee 将source命令的打印结果输出到指定文件中,便于查年source中的Error
//tee的功能是把你的所有输入和输出都记录到日志文件中去
tee /tmp/xxx.out (xxx.out为log文件的文件名)
source xxx.sql;
// 使用notee命令来关闭日志记录
notee;
/usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v mybinlog.000042 | less
--base64-output=DECODE-ROWS -v 这个参数是为了base64解码用的,便于看到原始的sql语句
- 通过mysqlbinlog 和 grep 命令定位binlog 指定操作
mysqlbinlog --base64-output=DECODE-ROWS -v mybinlog.000042 | grep -i -A 10 -B 10 'delete xxx' > result.log //将结果重定向到result.log中
grep -A 后几行
grep -B 前几行