【1】环境kvm虚拟化,host主机上有centos和windows2008两个系统,在centos系统上创建好LAMP利用discuz来做实验,删除mysql其中一张存储用户的表,然后登陆网站做测试。
[root@kvm ~]# virsh //进入virsh shell.
Welcome to virsh, the virtualization interactive terminal.
Type: 'help' for help with commands
'quit' to quit
virsh # list //查看所有guest系统,显示有centos和windows2008两个系统。
Id Name State
----------------------------------------------------
1 sn01 running
2 windows2008_iis running
virsh # console 1 //连接guest系统
Connected to domain sn01
Escape character is ^]
[root@sn01 ~]# mysql -uroot -p //进入mysql数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 253
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; //查看所有数据库,其中discuz就是我们测试的数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| discuz |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
【2】备份mysql数据库
[root@sn01 /]# mkdir /home/discuz //创建备份mysql的数据库文件夹,在/home下创建一个discuz目录
[root@sn01 /]# ll -d /home/discuz //查看discuz目录权限
drwxr-xr-x 2 root root 4096 Apr 9 09:31 /home/discuz/
[root@sn01 /]# mysqldump -uroot -p discuz --tab=/home/discuz //第一次利用备份数据失败
Enter password:
mysqldump: Got error: 1: Can't create/write to file '/home/discuz/pre_common_admincp_perm.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
[root@sn01 /]# chown mysql.mysql /home/discuz //改变目录拥有者和组为mysql,然后在执行上面的命令。
[root@sn01 home]# du -sh discuz //备份成功。
3.0M discuz/
[root@sn01 home]#ls //显示当前目录备份的内容,两个文件一个sql一个txt.
pre_forum_bbcode.sql pre_ucenter_members.sql
pre_forum_bbcode.txt pre_ucenter_members.txt
pre_forum_collectioncomment.sql pre_ucenter_mergemembers.sql
【3】删除discuz数据库中存储用户的表
[root@sn01 home]# mysql -uroot -p //进入mysql数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>mysql> show databases; //查看当前所有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| discuz | |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use discuz; //进入discuz数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>show tables; //查看所有表,我们把其中一张保存管理员密码的表删除。
| pre_ucenter_members |
| pre_ucenter_mergemembers |
| pre_ucenter_newpm |
| pre_ucenter_notelist |
| pre_ucenter_pm_indexes |
| pre_ucenter_pm_lists |
| pre_ucenter_pm_members |
| pre_ucenter_pm_messages_0 |
| pre_ucenter_pm_messages_1 |
| pre_ucenter_pm_messages_2 |
| pre_ucenter_pm_messages_3 |
| pre_ucenter_pm_messages_4 |
| pre_ucenter_pm_messages_5 |
| pre_ucenter_pm_messages_6 |
| pre_ucenter_pm_messages_7 |
| pre_ucenter_pm_messages_8 |
| pre_ucenter_pm_messages_9 |
| pre_ucenter_protectedmembers |
| pre_ucenter_settings |
| pre_ucenter_sqlcache |
| pre_ucenter_tags |
| pre_ucenter_vars |
+-----------------------------------+
282 rows in set (0.00 sec)
mysql> select *from pre_ucenter_members; //查看表中的内容。
+-----+----------+----------------------------------+-----------------+------+---------+----------------+------------+-------------+---------------+--------+---------+
| uid | username | password | email | myid | myidkey | regip | regdate | lastloginip | lastlogintime | salt | secques |
+-----+----------+----------------------------------+-----------------+------+---------+----------------+------------+-------------+---------------+--------+---------+
| 1 | admin | 1b2f882f03b6f28551f399a6da61fcd5 | admin@admin.com | | | hidden | 1396993985 | 0 | 0 | 19fb1c | |
| 2 | user1 | 181636de2c0096c0e4d5175323df2ca4 | user1@admin.com | | | 192.168.200.74 | 1396994081 | 0 | 0 | 1331cb | |
+-----+----------+----------------------------------+-----------------+------+---------+----------------+------------+-------------+---------------+--------+---------+
2 rows in set (0.00 sec)
mysql> drop table pre_ucenter_members; //删除pre_ucenter_members这张表
Query OK, 0 rows affected (0.00 sec)
【4】恢复数据库表步骤:
mysql>mysql> show databases; //查看当前所有数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| discuz | |
| mysql |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use discuz; //进入discuz数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> source /home/discuz/pre_ucenter_members.sql; //利用source把sql先导入进去。
Query OK, 0 rows affected (0.00 se
[root@sn01 /]#mysqlimport -uroot -p discuz /home/discuz/pre_ucenter_members.txt //利用mysqlimport导入txt文件
Enter password:
discuz.pre_ucenter_members: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0