LAMPdocs: Linux, Apache, MySQL, PHP

Today I will show a simple command that will allow you to restore a database from a previously made sql file. No matter, how the backup was done, if it has sql commands, you will restore it in the fastest possible way.

If your database backup file exceeds 10 megabytes, it’ll be very hard to restore it using usual panel options (I mean Directadmin, Cpanel, PhpMyAdmin, etc). If you have shell a access to your server, you may significally decrease the time necessary for backup restore. All you have to do id to upload this backup to an accessible location, and then to run a command:

mysql -ppassword -u user database_name -h localhost < path_to_sql_file

This command will start mysql database restore ptocess without prompting for a password. If you want to enter your password each time, you should leave -p option empty.

This is the fastest way to recreate your database using a dump file.

Today when I tried to perform some MySQL tasks and got error 28. After several searches I found that this error means that no free space is left on server HDD. When I ran df command, I was very surprised, but my disk usage was 100% - so much that MySQL couldn’t even create a log file. It’s a strange thing I didn’t notice this before. I had to check MySQL when my blog’s categories have disappeared. I highly recommend to enable DirectAdmin disk usage warning as I eventually missed disk overuse on my VPS.

When I tried to create admin backup for DirectAdmin, I got the message that one of my MySQL databases has errors and these errors were propagated to my backup. I’d suggest this to DirectAdmin developers, as using a simple command should avoid such errors. There is a linux shell tool called mysqlcheck, that allows to check, repair and optimize mysql databases without stopping mysqld. I will show you its usage so you can run this command before creating any kind of backups.

mysqlcheck -uroot -ppass -Aor

Male sure to include MySQL root password for this operation. If you don’t know your root password, check DirectAdmin Help for information regarding it. Let’s describe used options in brief. -A tells us that all databases will be checked. -r - Repairs selected databases, -o - Optimizes selected databases. This simple operation will allow you to have only good databases in your backups. You may also add this operation to your root crontab to keep your databases optimized.