首页 > 代码库 > mysql_5

mysql_5

--数据库恢复
--导出(使用sql)
select * from table_name into  outfile file_name export_options | dumpfile file_name 
export_options:
    [fields
            [terminated by ‘string‘]--用来指定字段值之间的符号,如‘,‘
            [[optionally] enclosed by char]--用来指定包裹文件中字符值的符号,如‘"‘
            [escaped by ‘char‘ ]--用来指定转义字符
    ]
    [lines  terminated by ‘string‘ ]--指定一行结束的标志
--默认
fields terminated by \t enclosed by ‘‘ escaped by \\
lines terminated by \n
--如果使用DUMPFILE而不是使用OUTFILE,导出的文件里所有的行都彼此紧挨着放置,值和行之间没有任何标记,成了一个长长的值。
--导入(使用sql)
load data [low_priority | concurrent] [local] infile file_name.txt--
    [replace | ignore]
    into table tbl_name
    [fields
        [terminated by ‘string‘]
        [[optionally] enclosed by char]
        [escaped by ‘char‘ ]
    ]
    [lines
        [starting by ‘string‘]
        [terminated by ‘string‘]
    ]
    [ignore number lines]
    [(col_name_or_user_var,...)]
    [set col_name = expr,...)]
--使用mysqldump备份数据 (可以用source导入)
mysqldump [OPTIONS] database [tables]
OR    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] > filename
OR    mysqldump [OPTIONS] --all-databases [OPTIONS] > filename
options:
tables:
--使用mysqlimport导入数据(只能跟据文件名导入表)
mysqlimport [options] db_name filename ...
--使用binlog
--1.启用日志:
--在my.ini mysqld 后面加一句 log-bin
--2.重启服务
net stop mysql 
net start mysql
--3.恢复
mysqlbinlog bin_log.000001 | mysql -uroot -p12345
--4.删除日志(如果日志太多)
reset master;--删除所有日志
purge {master | binary} logs to log_name--按日志名称删除
--
purge {master | binary} logs before date--按时间删除
--
purge master logs to shay3wwx2945501-bin.000003

 

mysql_5