mysql备份表的几种方法总结_Mysql

第一种，小表的备份

1.create table a as select * from b

2.create table a like select * from b

3.分布操作

create table a like b；

insert into a select * from b;

第二种，对整个数据库的备份与恢复

2.1 对整个数据库进行备份

1.创建备份用户和密码:zhangsan_backup/zs123

create user 'zhangsan_backup'@'localhost' identified by 'zs123';

2.赋予该用户有哪些权限

grant  select ,reload,lock tables,replication client,show view,event,process on *.* to 'zhangsan_backup'@'localhost';

3.备份数据

#1
mysqldump  -uzhangsan_backup -p --master-data=2 --single-transaction --routines --triggers --events bonc_test > e:\\bonc_test2.sql
#2
mysqldump  -uzhangsan_backup -p  bonc_test >   e:\\bonc_test.sql

 
mysql> create user 'zhangsan_backup'@'localhost' identified by 'zs123';
error 1290 (hy000): the mysql server is running with the --skip-grant-tables option so it cannot execute this statement
mysql> flush privileges;
query ok, 0 rows affected (0.15 sec)
 
mysql> create user 'zhangsan_backup'@'localhost' identified by 'zs123';
query ok, 0 rows affected (0.02 sec)
 
mysql> grant  select ,reload,lock tables,replication client,show view,event,process on *.* to 'zhangsan_backup'@'localho
st';
query ok, 0 rows affected (0.02 sec)
 
c:\windows\system32>mysqldump  -uzhangsan_backup -p --master-data=2 --single-transaction --routines --triggers --events
bonc_test > e:\\bonc_test2.sql
enter password: *****
 
c:\windows\system32>

注意如果在操作中报：mysqldump: error: binlogging on server not active的错误，则在windows中的my.ini文件中最后面新增：

#备份数据库
log-bin=mysql-bin
server-id=1
然后重新启动服务。即可。

2.2 对整个数据库进行恢复

#1.先创建一个数据库

create database bak_bonc_test;

#2.进行恢复数据

mysql -uroot -p bak_bonc_test <e:\\bonc_test2.sql

#3.查看：

第三种，对某个数据表进行备份

3.1 对整个数据表进行备份

库：bonc_test 表： user_login_tb

mysqldump -uzhangsan_backup -p --master-data=2 --single-transaction --routines --triggers --events bonc_test user_login_tb > e:\\user_login_tb.sql