mysqldump -u userNameWithPrivileges -p databaseName > outputFileName.sql
mysql -u userNameWithPrivileges -p database < outputFileName.sql
* Replace “userNameWithPrivileges” with the username of a MySQL account that can read/write to the database being backed up / restored.
If you try to backup a MySQL database with a lot of tables using mysqldump, you may get an error similar to the following:
mysqldump: Got error: 29: File './test/test.MYD' not found (Errcode: 24) when using LOCK TABLES
To fix the problem you can add the –skip-lock-tables option to your mysqldump command line like so:
mysqldump --skip-lock-tables -u root -p db_foo > db_foo_backup_13FEB09.sql
Or you can permanently fix the problem by increasing the number of open files allowed by your MySQL service by specifying a sufficiently large value for the open_files_limit setting in your MySQL server configuration file. The open_files_limit needs to be placed under the [mysqld] section of the MySQL server configuration file. On CentOS 5 the default location of the MySQL service configuration file is /etc/my.cnf. Try specifying a value of 8192 like so:
[mysqld] open_files_limit = 8192
After you make the change to the MySQL server configuration file you’ll need to restart the MySQL service to put the changes into effect. On CentOS 5 you can restart the MySQL service with the following command:
service mysqld restart