赞
踩
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;
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
然后重新启动服务。即可。
#1.先创建一个数据库
create database bak_bonc_test;
#2.进行恢复数据
mysql -uroot -p bak_bonc_test <e:\\bonc_test2.sql
#3.查看:
库: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
#先删除bak_bonc_test 库中 user_login_tb的这个表
#对其进行恢复: source e:\\user_login_tb.sql;
这两部分后续完善,参考《可扩展高性能mysql》第4天
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。