Mysql Backup Restore

Portal Home > Knowledgebase > Articles Database > Mysql Backup Restore

Posted by ashras99, 01-24-2011, 09:13 AM
I like to know following command in etc/my.cnf really works and advisable to add? myisam-recover=backup,force Secondly it is a good idea to setup the cron to optimize the tables weekly automatically?
Posted by lynxus, 01-24-2011, 09:19 AM
Dont know about the first, But the second is exactly what i have setup. Midnight on Sunday i run an optimize.
Posted by ashras99, 01-24-2011, 09:22 AM
Can you please tell me how you setup the optimize?
Posted by lynxus, 01-24-2011, 09:26 AM
Something like: Then just have your crontab to execute the php file whenever you want.
Posted by ashras99, 01-24-2011, 09:36 AM
if we have several tables then username/password for all tables need to assign in this script?
Posted by lynxus, 01-24-2011, 09:45 AM
the script will do a show tables on your database chosen.. So use whatever username and password you would normally use to do stuff to all the tables.
Posted by ashras99, 01-24-2011, 09:48 AM
so mention the following lines several time in script as per the number of tables... mysql_connect("localhost", "ADMINUSERNAME", "PASSWORD") or die(mysql_error()); mysql_select_db("DATABASE_TO_USE") or die(mysql_error()); mysql_connect("localhost", "ADMINUSERNAME", "PASSWORD") or die(mysql_error()); mysql_select_db("DATABASE_TO_USE") or die(mysql_error()); mysql_connect("localhost", "ADMINUSERNAME", "PASSWORD") or die(mysql_error()); mysql_select_db("DATABASE_TO_USE") or die(mysql_error()); right?
Posted by lynxus, 01-24-2011, 10:03 AM
Nope, That script on its own will: - Connect to the mysql server - Select a database to work with - Select ALL the tables from that database. - While loop through EACH table and run an optimize. If you need it done to multiple databases, just create a new file for each database and call it separately.
Posted by YUPAPA, 01-24-2011, 10:44 AM
Is this the same as: mysqlcheck --optimize -p{SQL_PASS} -u {SQL_USER} {SQL_DB} ?
Posted by lynxus, 01-24-2011, 11:06 AM
Quite possibly. Never done it with that before.
Posted by ashras99, 02-04-2011, 02:19 PM
Any more suggestions on optimize and recover
Posted by YUPAPA, 02-05-2011, 07:25 AM
You use the myisam-recover when you want to get into maintenance mode. lynxus has shown you about optimization. If you want to tune and tweak further, you should look at the settings from my.cnf.
Posted by ashras99, 02-05-2011, 09:37 AM
someone told me optimization is not necessary if you are not deleted massive data or moving the data from database and like to recover the space.