mysql backup / restore issue

NaSRi

Verified User
Joined
Aug 16, 2014
Messages
82
Hi

I have problem with mysql backup command , it says some of tables doesn't exit on my database and cause error .
mysqldump: Got error: 1146: Table 'kelachay_news.wp_pollsa' doesn't exist when using LOCK TABLES

I used --single-transaction in my command like this

mysqldump --single-transaction -u kxxxxay_news -p kxxxxhay_news > kelxxxy_news.sql

this method works on one of my database but for the other not , the orginal database that shown on directadmin has 70.mb space while the backup has 7.2 mb space .

7.2M kxxxxx_news.sql

i tried to repair database , but it doesn't help
[root@server kelachaynews]# mysqlcheck -u kelachay_news -p kelachay_news
Enter password:
kelxxxxx_news.wp_TABLE_STATISTICS OK
kexxx_news.wp_TABLE_STATISTICS_RAW OK
kxxx_news.wp_commentmeta OK
kexxx_news.wp_comments OK
kelxxxxx_news.wp_links OK
kelxxxxx_news.wp_newsletter OK
kxxxxx_news.wp_newsletter_profiles OK
kexxx_news.wp_options OK
kexxxx_news.wp_pollsa
Error : Table 'kexxxx_news.wp_pollsa' doesn't exist
status : Operation failed
kelachay_news.wp_pollsip
Error : Table 'kelachay_news.wp_pollsip' doesn't exist
status : Operation failed
kexxxx_news.wp_pollsq
Error : Table 'kelachay_news.wp_pollsq' doesn't exist
status : Operation failed
kexxxx_news.wp_post_views_history OK
kexxxx_news.wp_post_views_realtime OK
kexxxx_news.wp_post_views_summary OK
kexxxx_news.wp_postmeta OK
kexxxx_news.wp_posts OK
kexxxx_news.wp_term_relationships OK
kexxxx_news.wp_term_taxonomy OK
kexxxx_news.wp_terms OK
kexxxx_news.wp_ucan_post_submissions OK
kexxxx_news.wp_usermeta OK
kexxxx_news.wp_users OK
kexxxx_news.wp_wp125_ads OK
 
Hello,

Are those tables ( 'kelachay_news.wp_pollsip', 'kelachay_news.wp_pollsq', 'kexxxx_news.wp_pollsa') InnoDB based?

Check this: http://stackoverflow.com/questions/7759170/mysql-table-doesnt-exist-but-it-does-or-it-should

Yes sir , it's InnoDB .

I tried to these steps .


up vote
8
down vote
stop mysqld
backup mysql folder: cp -a /var/lib/mysql /var/lib/mysql-backup
copy database folder from old machine to /var/lib/mysql
override ib* (ib_logfile* , ibdata ) from old database
start mysqld
dump dabase
mysqldump >dbase.mysql
stop mysql service
remove /var/log/mysql
rename /var/lib/mysql-backup to /var/lib/mysql
start mysqld
create the database
mysqldump < dbase.mysql



After I copied ib* (ib_logfile* , ibdata ) from old machine to new machine , Mysql couldn't start , and I receive this error .

Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/server.xxxxx.pid).
 
Your server might use ib_logfile of another size. So you you might need to tune innodb_log_file_size in my.cnf to reflect the real size.
 
Your server might use ib_logfile of another size. So you you might need to tune innodb_log_file_size in my.cnf to reflect the real size.

what should I put for it ?

innodb_log_file_size = number ? like this innodb_log_file_size = 1000
 
Back
Top