Tux Training

Occasionally, under circumstances involving high load on the server, multiple INSERTs and UPDATEs, coupled with many SELECTs (see Section 28.5 for the syntax of SQL code), or hardware failure, your database server may corrupt a table. This is something it shouldn’t happen, but of course this doesn’t help you if it does. According to the MySQL manual on Corrupted MyISAM Tables, you can get corrupted tables if some of the following things happens:

• The mysqld process being killed in the middle of a write.
• Unexpected shutdown of the computer (for example, if the computer is turned off).
• A hardware error.
• You are using an external program (like myisamchk) on a live table.
• A software bug in the MySQL or MyISAM code.

and the typical symptoms for a corrupt table are:

• You get the error
  

  Incorrect key file for table: '...'. Try to repair it

  while selecting data from the table.

• Queries don’t find rows in the table or return incomplete data.

Continue reading ‘How to repair a corrupt table in MySQL’ »

Software installed and functioning by the end of this walk through :

• FreeBSD 7.1
• Apache 2.2
• PHP 5.2.8
• MySQL 6.0.9

First off grab a copy of FreeBSD 7.1 from http://www.freebsd.org/where.html

Once you have FreeBSD installed we can start.

Continue reading ‘How to setup a FAMP (FreeBSD, Apache, MySQL & PHP) server’ »

mtop allows you to monitor your MySQL instance in real-time. It shows you uptime of the instance, number of queries executed per second, slow queries, number active threads, etc.

If on Ubuntu/Debian: sudo apt-get -y install mtop

Use following command to watch your MySQL server stats with 1 second refresh interval.

mtop -se 1

If you get the following error, it means that mysqld has received many connect requests from the host 'host_name' that have been interrupted in the middle:

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
 Continue reading ‘What to do in MySQL if ‘host_name’ is blocked’ »

By default, MySQL Server will be installed with root superuser without any password. You can connect to MySQL server as root without requiring password or by keying in blank password. However, if you have set the password for root and forget or unable to recall the password, then you will need to reset the root password for MySQL.

Continue reading ‘Recover MySQL Database root password’ »

1. Login to the FreeBSD system as root, or su – root to get to super user environment.
2. Enter the following commands at FreeBSD command line interface (CLI) (Wait till each command to finish processing before running the next command):
3. Continue reading ‘Install MySQL on FreeBSD via Ports Collection’ »

To show the users in a MySQL database, first login as an administrative user, then run this query:

SELECT USER from mysql.user;

Do you need to change your web host or switch your database server? This is probably the only time when you really think of backing up your MySQL data. If you’ve got a website with a database or your custom database running for your applications, it is imperative that you make regular backups of the database. In this article, I will outline two easy ways of backing up and restoring databases in MySQL.

The easiest way to backup your database would be to telnet to the your database server machine and use the mysqldump command to dump your whole database to a backup file. If you do not have telnet or shell access to your server, don’t worry about it; I shall outline a method of doing so using the PHPMyAdmin web interface, which you can setup on any web server which executes PHP scripts.

Continue reading ‘Backing Up and Restoring Your MySQL Database’ »

After you have dumped out your data into a file as described here, FTP or scp that dump file to the home directory (/) on our system.

Once you have uploaded the dump file to your account here, get a shell prompt on our system using telnet or ssh.
Now import the dump file into MySQL by typing all the following on 1 single line at the shell prompt:

mysql -p -h DBSERVER dbname < dbname.sql

The above assumes that your database name on our system is “dbname” and the dumpfile that you uploaded was named “dbname.sql“. Replace those with your correct database name and dumpfile filename. Also replace DBSERVER with your correct database server name.