ServerBuddies Support Blog

MySQL - reset a lost MySQL root password

The MySQL root password allows full access to the MySQL database and allows for all actions to be undertaken including creating new users, new databases, setting access rules and so on.

The first thing to do is stop MySQL.

Now start mysql in safe mode.

Next we need to start MySQL in safe mode - that is to say, we will start MySQL but skip the user privileges table.

*ampersand (&) at the end of the command is required.

Login

Next, instruct MySQL which database to use:

Reset password
Enter the new password for the root user as follows:

and finally, flush the privileges and then stop and start mysql.

cPanel FTP and MySQL General Information and Error Log files

FTP: Logins and General Errors.

General information and login attempts are logged here

FTP Transactions

This is a symbolic link in most cases to /usr/local/apache/domlogs/ftpxferlog, which contains a history ofthe transactions made by FTP users.

MySQL: General Information and Errors

This path could vary, but is generally located in /var/lib/mysql. Could also be located at /var/log/mysqld.log

What to Do If MySQL Keeps Crashing on your cPanel Server

First, you should try to find out whether the problem is that the mysqld server 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 and restarted, you may find the reason by looking in the server’s error log.

There may be some internal lock problem. mysqladmin -u root processlist usually is able to make a connection even in these cases, and can provide useful information about the current number of connections and their status.

Run the command mysqladmin -i 5 status or mysqladmin -i 5 -r status in a separate window to produce statistics while you run your other queries.

Stop the mysqld server with mysqladmin shutdown, run myisamchk from the data directory to check all MyISAM tables, and restart mysqld.

MySQL Proxy Server

The MySQL Proxy is an application that communicates over the network using the MySQL Network Protocol and provides communication between one or more MySQL servers and one or more MySQL clients. In the most basic configuration, MySQL Proxy simply passes on queries from the client to the MySQL Server and returns the responses from the MySQL Server to the client.

To install, unpack the archive into the desired directory, and then modify your PATH environment variable so that you can use the mysql-proxy command directly:

If you want to update the path globally on a system, you may need administrator privileges to modify the appropriate /etc/profile, /etc/bashrc, or other system configuration file.

Reference: http://dev.mysql.com/

Running Multiple MySQL Servers on Linux

The easiest way is to run multiple MySQL servers on Linux is to compile them with different TCP/IP ports and Unix socket files so that each one is listening on different network interfaces. Compiling in different base directories for each installation also results automatically in a separate, compiled-in data directory, log file, and PID file location for each server.

Assume that an existing 5.0.19 server is configured for the default TCP/IP port number (3306) and Unix socket file (/tmp/mysql.sock). To configure a new 5.5.4 server to have different operating parameters, use a configure command something like this:

Here, port_number and file_name must be different from the default TCP/IP port number and Unix socket file path name, and the –prefix value should specify an installation directory different from the one under which the existing MySQL installation is located.

Reference: http://dev.mysql.com/