The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed. This logging order contrasts to the binary log, for which statements are written after they are executed but before any locks are released. (Also, the query log contains all statements, whereas the binary log does not contain statements that only select data.)

To enable the general query log, start mysqld with the –log[=file_name] or -l [file_name] option.

If no file_name value is given for –log or -l, the default name is host_name.log in the data directory.

Server restarts and log flushing do not cause a new general query log file to be generated (although flushing closes and reopens it). On Unix, you can rename the file and create a new one by using the following commands:

shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> cp host_name-old.log backup-directory
shell> rm host_name-old.log

Before 5.0.17, you cannot rename a log file on Windows while the server has it open. You must stop the server and rename the file, and then restart the server to create a new log file. As of 5.0.17, this applies only to the error log. However, a stop and restart can be avoided by using FLUSH LOGS, which causes the server to rename the error log with an -old suffix and open a new error log.

Related Links:

• http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html
• http://php.about.com/b/
• http://blog.ulf-wendel.de/?p=204
• http://planet.mysql.com/
• http://blog.mclaughlinsoftware.com/2010/06/26/debugging-mysql-function/
• http://neilarmitage.com/2010/05/mysql-stored-procedure-debugging/
• http://www.mysqlperformanceblog.com/2007/02/08/debugging-sleeping-connections-with-mysql/
• http://mysqldatabaseadministration.blogspot.com/
• http://mysqldba.blogspot.com/
• http://blogs.mysql.com/kaj/
• http://mysqlpreacher.com/wordpress/

Once in a while something will happen to a server and the mysql database will get corrupted.

A specific instance comes to mind on one of our Cacti monitoring servers.

The /var partition filled up due to too many messages being sent to the root user in /var/spool/. This caused MySQL to crash as well since the cacti poller couldnt write to the poller_output table in MySQL.

The result was all graphs being blank within cacti.

In any case, a thorough analysis of the mysql database was in order and I decided to post this quick tutorial for performing quick / lengthy table checks for offline and online MySQL databases.

Stage 1: Checking your tables

Run:

myisamchk *.MYI

or

myisamchk -e *.MYI

if you have more time. Use the -s (silent) option to suppress unnecessary information.

If the mysqld server is stopped, you should use the –update-state option to tell myisamchk to mark the table as “checked.”

You have to repair only those tables for which myisamchk announces an error. For such tables, proceed to Stage 2.

If you get unexpected errors when checking (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

First, try :

myisamchk -r -q tbl_name

(-r -q means “quick recovery mode”).

This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

1. Make a backup of the data file before continuing.

2. Use

myisamchk -r tbl_name

(-r means “recovery mode”)

This removes incorrect rows and deleted rows from the data file and reconstructs the index file.

3. If the preceding step fails, use

myisamchk --safe-recover tbl_name

Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode does not (but is slower).

Note: If you want a repair operation to go much faster, you should set the values of the sort_buffer_size and key_buffer_size variables each to about 25% of your available memory when running myisamchk.

If you get unexpected errors when repairing (such as out of memory errors), or if myisamchk crashes, go to Stage 3.

Stage 3: Difficult repair

You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:

1. Move the data file to a safe place.

2. Use the table description file to create new (empty) data and index files:

   shell> mysql db_name
     mysql> SET AUTOCOMMIT=1;
     mysql> TRUNCATE TABLE tbl_name;
     mysql> quit

3. Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)

Go back to Stage 2. :

myisamchk -r -q should work. (This should not be an endless loop.)

You can also use the REPAIR TABLE tbl_name USE_FRM SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use REPAIR TABLE. See Section 12.5.2.6, “REPAIR TABLE Syntax”.

Stage 4: Very difficult repair

You should reach this stage only if the .frm description file has also crashed. That should never happen, because the description file is not changed after the table is created:

1. Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with

myisamchk -r

2. If you do not have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new data file, and then move the .frm description and .MYI index files from the other database to your crashed database. This gives you new description and index files, but leaves the .MYD data file alone. Go back to Stage 2 and attempt to reconstruct the index file.

Thats it!

Related Links:

• http://articles.techrepublic.com.com/5100-10878_11-5193721.html
• http://help.hardhathosting.com/question.php/123
• http://www.whoismadhur.com/2008/02/04/how-to-repair-mysql-tables/
• http://dev.mysql.com/doc/refman/5.0/en/repair.html
• http://paulstamatiou.com/how-to-quickie-repair-mysql-tables-in-phpmyadmin
• http://www.databasejournal.com/features/mysql/article.php/10897_3300511_2/Repairing-Database-Corruption-in-MySQL.htm

It is often necessary, when designing high availability environments to implement a database replication scenario with MySQL.

This simple how-to is intended to setup a simple master / slave relationship.

PREPARATION OF MASTER SERVER

1. Select a master server. It can be either one.

2. Make sure all databases that you want to replicate to the slave already exist! The easist way is to just copy the database dirs inside your MySQL data directory intact over to your slave, and then recursively chown them to “mysql:mysql”. Remember, the binary structures are file-system dependant, so you can’t do this between MySQL servers on different OS’s. In this instance you will want to use mysqldump most likely.

3. Create /etc/my.cnf if you do not already have one:

[mysqld]
socket=/tmp/mysql.sock [enter YOUR path to mysql.sock here]
server-id=1
log-bin=mysql-bin
binlog-do-db=bossdb # input the database which should be replicated
binlog-ignore-db=mysql1 # input the database that should be ignored for replication
binlog-ignore-db=mysql2 # input the database that should be ignored for replication


4. Permit your slave server to replicate by issuing the following SQL command (substituting your slave’s IP and preferred password):

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replicate'@'192.168.1.1' IDENTIFIED BY 'somepass';


5. Shut down and restart MySQL daemon and verify that all is functional.

PREPARATION OF SLAVE

1. Create /etc/my.cnf if you do not already have one:

[mysqld]
socket=/tmp/mysql.sock [enter YOUR path to mysql.sock here]
server-id=2 [MUST be different to master]
master-host=192.168.1.1
master-user=replicate
master-password=somepass

2. Shut down and restart MySQL on slave.

3. Issue the following SQL command to check status:

mysql> show slave status\G;

Ensure that the following two fields are showing this :

Slave_IO_Running: Yes
Slave_SQL_Running: Yes


If not, try to issue the following command :

mysql> start slave;

This will manually start the slave process. Note that only updated tables and entries after the slave process has started will be sent from the master to the slave — it is not a differential replication.

TESTING

Just update some data on the master, and query that record on the slave. The update should be instantaneous.

Test creating a table on the master MySQL server database :

mysql> use replicateddb;
Database changed

mysql> CREATE TABLE example4( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), name VARCHAR(30), age INT);
Query OK, 0 rows affected (0.04 sec)

And check the database on the slave to ensure that the recently created table on the master was replicated properly.

Related Links:

• No Related Links