mysql 5.5添加外键出错

〇〇 · 发表于 2011-6-4 19:46

mysql> show table status;
+----------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name    | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free  | Auto_increment | Create_time       | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| customer | InnoDB |    10 | Compact |  1490739 |          193 | 288112640 |             0 |    24690688 | 2628780032 |          NULL | 2011-06-04 18:54:51 | NULL       | NULL    | latin1_swedish_ci |    NULL |             |       |
| lineitem | InnoDB |    10 | Compact | 59992975 |          153 |  9216983040 |             0 |          0 | 2628780032 |          NULL | 2011-06-03 13:24:11 | NULL       | NULL    | latin1_swedish_ci |    NULL |             |       |
| nation | InnoDB |    10 | Compact |    25 |          655 |    16384 |             0 |       16384 | 2628780032 |          NULL | 2011-06-04 08:29:16 | NULL       | NULL    | latin1_swedish_ci |    NULL |             |       |
| orders | InnoDB |    10 | Compact | 14943175 |          131 |  1963982848 |             0 |          0 | 2628780032 |          NULL | 2011-06-04 18:50:00 | NULL       | NULL    | latin1_swedish_ci |    NULL |             |       |
| part    | InnoDB |    10 | Compact |  1997259 |          161 | 322732032 |             0 |          0 | 2628780032 |          NULL | 2011-06-04 07:24:45 | NULL       | NULL    | latin1_swedish_ci |    NULL |             |       |
| partsupp | InnoDB |    10 | Compact |  8077565 |          173 |  1404043264 |             0 | 200032256 | 2628780032 |          NULL | 2011-06-04 10:09:23 | NULL       | NULL    | latin1_swedish_ci |    NULL |             |       |
| region | InnoDB |    10 | Compact |       5 |          3276 |    16384 |             0 |          0 | 2628780032 |          NULL | 2011-06-04 07:17:45 | NULL       | NULL    | latin1_swedish_ci |    NULL |             |       |
| supplier | InnoDB |    10 | Compact | 99766 |          184 | 18366464 |             0 |    2637824 | 2628780032 |          NULL | 2011-06-04 08:32:19 | NULL       | NULL    | latin1_swedish_ci |    NULL |             |       |
+----------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+------------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
8 rows in set (0.03 sec)

〇〇 · 发表于 2011-6-4 19:56

可能是这里满了

[root@redflag11012501 /]# du -sh /tmp
220M /tmp
[root@redflag11012501 /]# cd /var/lib/mysql
[root@redflag11012501 mysql]# ll
总计 15862612
drwx--x--x 2 mysql mysql    4096 06-02 17:59 mysql
-rw-rw---- 1 root  root       126 06-03 10:45 mysql-bin.000001
-rw-rw---- 1 root  root    11687 06-03 12:32 mysql-bin.000002
-rw-rw---- 1 root  root       150 06-03 12:32 mysql-bin.000003
-rw-rw---- 1 root  root  1808831579 06-03 13:33 mysql-bin.000004
-rw-rw---- 1 root  root  1191758268 06-03 13:54 mysql-bin.000005
-rw-rw---- 1 root  root  6744144959 06-03 16:13 mysql-bin.000006
-rw-rw---- 1 root  root       1297 06-04 18:38 mysql-bin.000007
-rw-rw---- 1 root  root       150 06-04 18:44 mysql-bin.000008
-rw-rw---- 1 root  root       150 06-04 18:44 mysql-bin.000009
-rw-rw---- 1 root  root  1621035996 06-04 19:22 mysql-bin.000010
-rw-rw---- 1 root  root  1621035512 06-04 19:26 mysql-bin.000011
-rw-rw---- 1 root  root  1619499209 06-04 19:30 mysql-bin.000012
-rw-rw---- 1 root  root  1621004024 06-04 19:36 mysql-bin.000013
-rw-rw---- 1 root  root       126 06-04 19:40 mysql-bin.000014
-rw-rw---- 1 root  root       150 06-04 19:42 mysql-bin.000015
-rw-rw---- 1 root  root       107 06-04 19:42 mysql-bin.000016
-rw-rw---- 1 root  root       304 06-04 19:42 mysql-bin.index
srwxrwxrwx 1 root  root          0 06-04 19:40 mysql.sock
drwx------ 2 mysql mysql    4096 06-02 17:59 performance_schema
-rw-rw---- 1 mysql root       3093 06-04 18:42 redflag11012501.err
-rw-rw---- 1 root  root          6 06-04 19:40 redflag11012501.pid
-rw-rw---- 1 root  root       8948 06-04 19:42 redflag11012501-slow.log
-rw-r--r-- 1 root  root       121 06-02 17:59 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql       121 06-02 17:59 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x 2 mysql mysql    4096 06-02 17:59 test
drwx------ 2 root  root       4096 06-04 19:43 tpch

〇〇 · 发表于 2011-6-4 20:01

看几个大文件，刚好和最大的表差不多。。

mysql> show binary logs;
+------------------+------------+
| Log_name       | File_size  |
+------------------+------------+
| mysql-bin.000001 |       126 |
| mysql-bin.000002 |    11687 |
| mysql-bin.000003 |       150 |
| mysql-bin.000004 | 1808831579 |
| mysql-bin.000005 | 1191758268 |
| mysql-bin.000006 | 6744144959 |
| mysql-bin.000007 |    1297 |
| mysql-bin.000008 |       150 |
| mysql-bin.000009 |       150 |
| mysql-bin.000010 | 1621035996 |
| mysql-bin.000011 | 1621035512 |
| mysql-bin.000012 | 1619499209 |
| mysql-bin.000013 | 1621004024 |
| mysql-bin.000014 |       126 |
| mysql-bin.000015 |       150 |
| mysql-bin.000016 |       107 |
+------------------+------------+
16 rows in set (0.01 sec)

mysql>  purge binary logs to 'mysql-bin.000015';
Query OK, 0 rows affected (3.18 sec)

mysql>  show binary logs;
+------------------+-----------+
| Log_name       | File_size |
+------------------+-----------+
| mysql-bin.000015 |    150 |
| mysql-bin.000016 |    107 |
+------------------+-----------+
2 rows in set (0.00 sec)

[root@redflag11012501 mysql]# ll
总计 56
drwx--x--x 2 mysql mysql 4096 06-02 17:59 mysql
-rw-rw---- 1 root  root 150 06-04 19:42 mysql-bin.000015
-rw-rw---- 1 root  root 107 06-04 19:42 mysql-bin.000016
-rw-rw---- 1 root  root 38 06-04 19:57 mysql-bin.index
srwxrwxrwx 1 root  root    0 06-04 19:40 mysql.sock
drwx------ 2 mysql mysql 4096 06-02 17:59 performance_schema
-rw-rw---- 1 mysql root  3093 06-04 18:42 redflag11012501.err
-rw-rw---- 1 root  root    6 06-04 19:40 redflag11012501.pid
-rw-rw---- 1 root  root  9163 06-04 19:58 redflag11012501-slow.log
-rw-r--r-- 1 root  root 121 06-02 17:59 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysql mysql  121 06-02 17:59 RPM_UPGRADE_MARKER-LAST
drwxr-xr-x 2 mysql mysql 4096 06-02 17:59 test
drwx------ 2 root  root  4096 06-04 19:43 tpch

〇〇 · 发表于 2011-6-4 20:07

有时候发现mysql-bin.000001、mysql- bin.000002等文件占用了空间，那么这些文件是干吗的？这是数据库的操作日志，例如UPDATE一个表，或者DELETE一些数据，即使该语句没有匹配的数据，这个命令也会存储到日志文件中，还包括每个语句执行的时间，也会记录进去的。

这样做主要有以下两个目的：
1：数据恢复
如果你的数据库出问题了，而你之前有过备份，那么可以看日志文件，找出是哪个命令导致你的数据库出问题了，想办法挽回损失。
2：主从服务器之间同步数据
主服务器上所有的操作都在记录日志中，从服务器可以根据该日志来进行，以确保两个同步。

处理方法分两种情况：
1：只有一个mysql服务器，那么可以简单的注释掉这个选项就行了。
vi /etc/my.cnf把里面的log-bin这一行注释掉，重启mysql服务即可。

〇〇 · 发表于 2011-6-4 20:22

做完了

mysql>  alter table lineitem add constraint lineitem_pk primary key (l_orderkey,l_linenumber);
Query OK, 0 rows affected (17 min 55.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

中间
由
文件系统             1K-块       已用    可用已用% 挂载点
/dev/mapper/VolGroup00-LogVol00
                  24535684  12822408  10446796  56% /
/dev/sda1             101086    12734    83133  14% /boot
tmpfs                46104576  19730284  26374292  43% /dev/shm
/dev/mapper/vg0-datalv
                  774092544 505053080 229717864  69% /user1

变化到
[root@redflag11012501 tmp]# df
文件系统             1K-块       已用    可用已用% 挂载点
/dev/mapper/VolGroup00-LogVol00
                  24535684  22077324 1191880  95% /
/dev/sda1             101086    12734    83133  14% /boot
tmpfs                46104576  19730284  26374292  43% /dev/shm
/dev/mapper/vg0-datalv
                  774092544 507504888 227266056  70% /user1

，现在
[root@redflag11012501 tmp]# df
文件系统             1K-块       已用    可用已用% 挂载点
/dev/mapper/VolGroup00-LogVol00
                  24535684 6653140  16616064  29% /
/dev/sda1             101086    12734    83133  14% /boot
tmpfs                46104576  19730284  26374292  43% /dev/shm
/dev/mapper/vg0-datalv
                  774092544 512400292 222370652  70% /user1