在linux中数据库备份遇到的问题

今天做服务器续费时，发现有台服务器很久没有用，问了相关同事，了解到主要是用来做历史数据查询用，那当然，相对应的备机没有使用价值了(主要是价格太贵O(∩_∩)O哈哈~) ，领导让把主机的数据库做一个备份，吓死宝宝了，从来没做过数据库备份的呢。最最最关键的是，我不知道数据库密码呀，大家也都不知道，在脚本里面找了一整个上午，终于找到，不容易啊！！！/(ㄒoㄒ)/~~  

       在做备份时，遇到了好多好多的问题，首先因为这个服务器用了很多年，查看一下数据库还是mysql 5.0的版本，直接登录mysql居然有问题

[root@AY1208201027300735890 ~]# mysql -u root -p-bash: mysql: command not found

怎么也想不明白，怎么会not found呢，后考虑到数据库版本的问题，ps一下，果然有问题

[root@AY1208201027300735890 ~]# ps -ef | grep mysqlroot     16207     1  0 13:55 pts/2    00:00:00 /bin/sh /home/phpstat/mysql-5.0.92/bin/mysqld_safe --defaults-file=/home/phpstat/etc/mysql-5.0.92/my.cnf --datadir=/mnt/mysqldata --pid-file=/mnt/mysqldata/AY1208201027300735890.pidmysql    16246 16207  0 13:55 pts/2    00:00:00 /home/phpstat/mysql-5.0.92/libexec/mysqld --defaults-file=/home/phpstat/etc/mysql-5.0.92/my.cnf --basedir=/home/phpstat/mysql-5.0.92 --datadir=/mnt/mysqldata --user=mysql --pid-file=/mnt/mysqldata/AY1208201027300735890.pid --skip-external-locking --port=3306 --socket=/tmp/mysql.sockroot     16288 15754  0 14:31 pts/0    00:00:00 grep mysql

后修改一下登录方式，还是有问题

[root@AY1208201027300735890 ~]# /home/phpstat/mysql-5.0.92/bin/mysql -u root -pEnter password: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/home/phpstat/tmp/mysql.sock' (2)

鉴于是mysql.sock的问题，找了一下，发现该文件的目录不在此

便又修改登录方式

[root@AY1208201027300735890 ~]# /home/phpstat/mysql-5.0.92/bin/mysql -S /tmp/mysql.sock -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 5.0.92 Source distributionType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 

这下终于OK了 :-D

数据库既然成功登录，现在可以开始备份数据库

直接用mysqldump具体也是提示说common not found ，显然和登录时一样的问题。

[root@AY1208201027300735890 ~]# mysqldump -u root -p information_schema > /mnt/information_schema.sql-bash: mysqldump: command not found

[root@AY1208201027300735890 ~]# /home/phpstat/mysql-5.0.92/bin/mysqldump -u root -p information_schema > /mnt/information_schema.sqlEnter password: mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/home/phpstat/tmp/mysql.sock' (2) when trying to connect

[root@AY1208201027300735890 ~]# /home/phpstat/mysql-5.0.92/bin/mysqldump -S /tmp/mysql.sock -u root -p information_schema > /mnt/information_schema.sqlEnter password: [root@AY1208201027300735890 ~]# cd /mnt[root@AY1208201027300735890 mnt]# lltotal 2550232-rw-r--r--  1 root root       1223 Oct 21 14:20 information_schema.sql

到此，终于解决所有问题，不写了，我去备份了

————————————————————————————————————————————————

备份中，部分数据表有问题，导致备份失败

1. 提示Can't fund file

mysqldump: Got error: 1017: Can't find file: 'phpstat_analytics_10_form' (errno: 2) when using LOCK TABLES

进入数据库，打开表格phpstat_analytice_10_form提示有问题

mysql> use phpstat_analytics_1_mysql_log;mysql> select * from phpstat_analytics_10_form limit 1;ERROR 1017 (HY000): Can't find file: 'phpstat_analytics_10_form' (errno: 2)

翻查资料提示说检测表格并修复即可

[www@AY1208201027300735890 ~]$ /home/phpstat/mysql-5.0.92/bin/mysql -S /tmp/mysql.sock -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 323Server version: 5.0.92 Source distributionType 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use phpstat_analytics_1_mysql_log;Database changedmysql> check tables phpstat_analytics_10_form;+---------------------------------------------------------+-------+----------+---------------------------------------------------------+| Table                                                   | Op    | Msg_type | Msg_text                                                |+---------------------------------------------------------+-------+----------+---------------------------------------------------------+| phpstat_analytics_1_mysql_log.phpstat_analytics_10_form | check | Error    | Can't find file: 'phpstat_analytics_10_form' (errno: 2) | | phpstat_analytics_1_mysql_log.phpstat_analytics_10_form | check | error    | Corrupt                                                 | +---------------------------------------------------------+-------+----------+---------------------------------------------------------+2 rows in set (0.00 sec)mysql> repair table phpstat_analytics_10_form;+---------------------------------------------------------+--------+----------+---------------------------------------------------------+| Table                                                   | Op     | Msg_type | Msg_text                                                |+---------------------------------------------------------+--------+----------+---------------------------------------------------------+| phpstat_analytics_1_mysql_log.phpstat_analytics_10_form | repair | Error    | Can't find file: 'phpstat_analytics_10_form' (errno: 2) | | phpstat_analytics_1_mysql_log.phpstat_analytics_10_form | repair | error    | Corrupt                                                 | +---------------------------------------------------------+--------+----------+---------------------------------------------------------+2 rows in set (0.00 sec)mysql> check tables phpstat_analytics_10_form;+---------------------------------------------------------+-------+----------+---------------------------------------------------------+| Table                                                   | Op    | Msg_type | Msg_text                                                |+---------------------------------------------------------+-------+----------+---------------------------------------------------------+| phpstat_analytics_1_mysql_log.phpstat_analytics_10_form | check | Error    | Can't find file: 'phpstat_analytics_10_form' (errno: 2) | | phpstat_analytics_1_mysql_log.phpstat_analytics_10_form | check | error    | Corrupt                                                 | +---------------------------------------------------------+-------+----------+---------------------------------------------------------+2 rows in set (0.00 sec)mysql> 

试了一下，但没什么效果

2.

mysqldump: Error 1194: Table 'chat_record' is marked as crashed and should be repaired when dumping table `chat_record` at row: 1070553

目前以上未解决，待有空再处理