The following command will optimize all databases in mysql (excpect those that don't support it due to their storage engine).
mysqlcheck -Aao
You can throw the
--auto-repair
flag in if you are brave.
Browsing Posts tagged mysql
If you need to change the TTLs on all domains in Plesk at once, you can massage the database with the following command:
mysql> UPDATE `dns_zone` SET `ttl` = '300', `ttl_unit` = '60' WHERE `id` >1;
Substitute the TTL values for what you need. Since the flat files are still used by named to provide DNS resolution, those will need to be updated as well. This command will do the trick:
mysql -Ns -uadmin -p`cat /etc/psa/.psa.shadow` -D psa -e 'select name from domains' | awk '{print "/usr/local/psa/admin/sbin/dnsmng update " $1 }' | sh
Here is a handy little snippet for deleting all the tables in a database. Replace 'uname' and 'dbname' as required.
mysql -u uname dbname -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "drop table " $1 ";"}' | mysql -u uname dbname
If your .FRM file gets hosed up, you'll need to repair it or your database(s) will be worthless. You'll still need to make sure you make regular backups of your databases so you can use them to rebuild the .FRM file.
1. Get last good backup of database (assumes the backup is an .sql formatted single file).
2. Grep out the schema from the .sql file (grep -v 'INSERT INTO' [.sql file] > somefile.sql)
3. Locate the schema for the broken table.
4. Go into a mysql shell and create a new table with a different name and same schema as the broken table (located in step 3) in the affected database.
5. Stop mysqld.
6. Copy the [newtable].frm file in /var/lib/mysql/[database] to the [brokentable].frm, overwriting [brokentable].frm
7. Remove the [newtable].MYI/.MYD files from /var/lib/mysql/[database].
8. Start mysqld.
9. Run on a count on the newly fixed table and make sure there are records.
10. Run a mysqldump of the database and make sure it completes successfully.
11. Clean up any unneeded files created in the process of the fix.
