[ < ] [ > ]   [ << ] [ Up ] [ >> ]         [Top] [Contents] [Index] [ ? ]

A.4 Administration Related Issues

A.4.1 What To Do If MySQL Keeps Crashing  
A.4.2 How to Reset a Forgotten Root Password  
A.4.3 How MySQL Handles a Full Disk  
A.4.4 Where MySQL Stores Temporary Files  
A.4.5 How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'  
A.4.6 Time Zone Problems  

A.4.1 What To Do If MySQL Keeps Crashing

All MySQL versions are tested on many platforms before they are released. This doesn't mean that there aren't any bugs in MySQL, but it means if there are bugs, they are very few and can be hard to find. If you have a problem, it will always help if you try to find out exactly what crashes your system, as you will have a much better chance of getting this fixed quickly.

First, you should try to find out whether the problem is that the mysqld daemon dies or whether your problem has to do with your client. You can check how long your mysqld server has been up by executing mysqladmin version. If mysqld has died, you may find the reason for this in the file `mysql-data-directory/`hostname`.err'. See section The Error Log.

On some systems you can find in this file a stack trace of where mysqld died that you can resolve with resolve_back_stack. See section Using a Stack Trace. Note that the variable values written in the .err file may not always be 100 percent correct.

Many crashes of MySQL are caused by corrupted index files or datafiles. MySQL will update the data on disk, with the write() system call, after every SQL statement and before the client is notified about the result. (This is not true if you are running with delay_key_write, in which case only the data is written.) This means that the data is safe even if mysqld crashes, as the OS will ensure that the not flushed data is written to disk. You can force MySQL to sync everything to disk after every SQL command by starting mysqld with --flush.

The above means that normally you shouldn't get corrupted tables unless:

Because it is very difficult to know why something is crashing, first try to check whether things that work for others crash for you. Please try the following things:

A.4.2 How to Reset a Forgotten Root Password

If you never set a root password for MySQL, then the server will not require a password at all for connecting as root. It is recommended to always set a password for each user. See section How to Make MySQL Secure Against Crackers.

If you have set a root password, but forgot what it was, you can set a new password with the following procedure:

  1. Take down the mysqld server by sending a kill (not kill -9) to the mysqld server. The pid is stored in a `.pid' file, which is normally in the MySQL database directory:
     
    shell> kill `cat /mysql-data-directory/hostname.pid`

    You must be either the Unix root user or the same user mysqld runs as to do this.

  2. Restart mysqld with the --skip-grant-tables option.
  3. Set a new password with the mysqladmin password command:
     
    shell> mysqladmin -u root password 'mynewpassword'
  4. Now you can either stop mysqld and restart it normally, or just load the privilege tables with:
     
    shell> mysqladmin -h hostname flush-privileges
  5. After this, you should be able to connect using the new password.

Alternatively, you can set the new password using the mysql client:

  1. Take down and restart mysqld with the --skip-grant-tables option as described above.
  2. Connect to the mysqld server with:
     
    shell> mysql -u root mysql
  3. Issue the following commands in the mysql client:
     
    mysql> UPDATE user SET Password=PASSWORD('mynewpassword')
    ->             WHERE User='root';
mysql> FLUSH PRIVILEGES;
  4. After this, you should be able to connect using the new password.
  5. You can now stop mysqld and restart it normally.

A.4.3 How MySQL Handles a Full Disk

When a disk-full condition occurs, MySQL does the following:

To alleviate the problem, you can take the following actions:

Exceptions to the above behaveour is when you use REPAIR or OPTIMIZE or when the indexes are created in a batch after an LOAD DATA INFILE or after an ALTER TABLE statement.

All of the above commands may use big temporary files that left to themself would cause big problems for the rest of the system. If MySQL gets disk full while doing any of the above operations, it will remove the big temporary files and mark the table as crashed (except for ALTER TABLE, in which the old table will be left unchanged).

A.4.4 Where MySQL Stores Temporary Files

MySQL uses the value of the TMPDIR environment variable as the pathname of the directory in which to store temporary files. If you don't have TMPDIR set, MySQL uses the system default, which is normally `/tmp' or `/usr/tmp'. If the filesystem containing your temporary file directory is too small, you should edit mysqld_safe to set TMPDIR to point to a directory in a filesystem where you have enough space! You can also set the temporary directory using the --tmpdir option to mysqld.

MySQL creates all temporary files as hidden files. This ensures that the temporary files will be removed if mysqld is terminated. The disadvantage of using hidden files is that you will not see a big temporary file that fills up the filesystem in which the temporary file directory is located.

When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files. The maximum disk-space needed is:

 
(length of what is sorted + sizeof(database pointer))
* number of matched rows
* 2

sizeof(database pointer) is usually 4, but may grow in the future for really big tables.

For some SELECT queries, MySQL also creates temporary SQL tables. These are not hidden and have names of the form `SQL_*'.

ALTER TABLE creates a temporary table in the same directory as the original table.

If you use MySQL 4.1 or later you can spread load between several physical disks by setting --tmpdir to a list of paths separated by colon : (semicolon ; on Windows). They will be used in round-robin fashion. Note: These paths should end up on different physical disks, not different partitions of the same disk.

A.4.5 How to Protect or Change the MySQL Socket File `/tmp/mysql.sock'

If you have problems with the fact that anyone can delete the MySQL communication socket `/tmp/mysql.sock', you can, on most versions of Unix, protect your `/tmp' filesystem by setting the sticky bit on it. Log in as root and do the following:

 
shell> chmod +t /tmp

This will protect your `/tmp' filesystem so that files can be deleted only by their owners or the superuser (root).

You can check if the sticky bit is set by executing ls -ld /tmp. If the last permission bit is t, the bit is set.

You can change the place where MySQL uses / puts the socket file the following ways:

You can test that the socket works with this command:

 
shell> mysqladmin --socket=/path/to/socket version

A.4.6 Time Zone Problems

If you have a problem with SELECT NOW() returning values in GMT and not your local time, you have to set the TZ environment variable to your current time zone. This should be done for the environment in which the server runs, for example, in mysqld_safe or mysql.server. See section Environment Variables.

[ < ] [ > ]   [ << ] [ Up ] [ >> ]


Hosting by: Hurra Communications Ltd.
Generated: 2007-01-26 17:58:44