MySQL Administration

From TeleFlow

This documentation has been pulled together as quick guide about MySQL administration. As with all the wikis on this site, please contribute and improve with suggestions for great software and links to other resources. A great GUI interface for managing MySQL and interacting with with the database is Navicat. There is also an open source environment called HeidiSQL

Contents 1 How to Repair MySQL 2 How to Re-index a Table 3 How to Restore a Table 4 How to Fix a Table that Does not want to Delete 5 How to Grant Access to Users 6 How to Backup a MySQL Databse or Table 6.1 Copy Files 6.2 MySQLDump Batch for Backups

How to Repair MySQL

[_] Find the offending SQL Statement 
    - Determine table / tables that are a problem
[_] cd c:\mysql\bin
[_] mysqldump database_name table > name.sql
   - eg:  mysqldump camps absence > backupdumptable.sql
[_] from a command prompt:  
mysql
// use database
use camps
// repair table tablename
repair table absence

[_] mysqldump databasename tablename > backupdumptable.sql
[_] exit

How to Re-index a Table

[_] from a command prompt:  
mysql
// use database
use databasename
// Back up table first
mysqldump databasename tablename > backuptablename.sql
// repair table tablename
repair table tablename

How to Restore a Table

[_] Command prompt - cd mysql bin
mysql
use database [databasename]
drop table [tablename]
quit
[_] // edit backupdumptable file to include "use [database]" at the start
[_] // remove any /*  */ comments
[_] Command prompt - mysql < backupdumptable.sql 
[_] // This will restore only the table that is missing

eg: mysql -u root -p pass < tfvoiceoffice.sql

How to Fix a Table that Does not want to Delete

[_] - From Navicat do a SQL Dump of table (eg: Employee.sql).  
[_] - This will give a file that includes the create information
[_] - From Navicat Delete the Table
[_] - Query run the SQL Dump file (specifically the Create statement within)

How to Grant Access to Users

 - NOTE: Users must be created for the Database they are going to use.
         There are no longer superusers.

         mysql -u -p databasename
         > use databasename;
         > create user 'root';
         > grant all on camps.* to 'root'@'%';
         > set password for 'root'@'localhost' = password('');
         > flush privileges;
         > set password for 'root'@'%' = 'password('');
         > flush privileges;

NOTE:  To open up to ALL users:
         > grant all on camps.* to ''@'%';

How to Backup a MySQL Databse or Table

Here are a couple of examples of backing up a MySQL database. There are two ways to achieve it, but the second is the more ideal as the database does not need to be shutdown.

Copy Files

net stop mysql
xcopy "C:\Program Files\MySQL\MySQL Server 5.0\Data\" /s/e .
net start mysql

MySQLDump Batch for Backups

This is the batch file that will backup the database for this Wiki on Venus. Please note that the --hex-blob is probably essential for blob files.

rem Backup the Wiki Database without taking it down
title MySQL Backup Loop
:top
cd "C:\Program Files\MySQL\MySQL Server 5.0\bin"
copy x:\WikiData\wikidb.sql x:\WikiData\wikidb.sqllast
mysqldump --complete-insert --hex-blob wikidb > wikidb.sql
copy wikidb.sql x:\WikiData

rem Sleep for 24 hours
@ping 127.0.0.1 -n 86400 -w 1000 > nul
goto top

net stop mysql
cd c:\mysql\data\camps
copy *.* \mysqlbackup
..\..\bin\myisamchk --recover --quick *.myi
..\..\bin\myisamchk --safe-recover *.myi > \mysqlfix.txt
net start mysql