Page tree
Skip to end of metadata
Go to start of metadata

This document is for a previous release of cPanel & WHM. To view our latest documentation, visit our Home page.

For cPanel & WHM 11.46

 

When I try to load a large SQL dump, why do I receive the following: ERROR 2006: MySQL Server has gone away?

To address this issue, perform the following steps:

  1. Open the /etc/my.cnf file with your preferred text editor.
  2. Enter max_allowed_packet=# in the [mysqld] section. Replace # with a number.
  3. Save the file.
  4. Restart MySQL with the # /scripts/restartsrv_mysql command.

For more information, read the Version 5.5 or Version 5.6 documentation.

How do I configure a MySQL database for remote connection?

  1. Determine the IP address that you will use to connect to the database.
  2. Add this IP address to the Host field in the Remote MySQL section of cPanel (Home >> Databases >> Remote MySQL).

I want to use programs directly from my computer to connect to a MySQL database. Which port can I use to connect to a MySQL database?

Use port 3306 to connect to a remote MySQL server.

How do I create a new SQL database?

You can add a database with one of the options in the Databases section of cPanel (Home >> Databases).

Note:

For more information, read our database documentation.

Why does MySQL give an access denied error for root@localhost?

To avoid this issue, ensure that the MySQL root password is in the /root/.my.cnf file on the password line. If the MySQL root password is not in the /root/.my.cnf file, chose one of the following methods to reset the mysqld root password:

Important

You only have to perform one of the following methods. 

In the WHM interface:

  1. Navigate to WHM's MySQL Root Password interface (WHM >> SQL Services >> MySQL Root Password).
  2. In the Password text box, enter your new password. 
  3. In the Password Again text boxenter you new password again. 
  4. Click Change Password.

In the terminal:

  1. Stop tailwatchd and temporarily disable it:

    # touch /etc/tailwatchddisable
    # killall tailwatchd
  2. Stop mysqld: 

    # /etc/rc.d/init.d/mysql stop
  3. Start mysqld:

    # mysqld_safe --skip-grant-tables
  4. Change the password:

    # mysql -u root mysql -e "UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; FLUSH PRIVILEGES;"

    Note

    Replace new_password with your new desired password.  

  5. Kill MySQL: 

    # killall mysqld
  6. Restart your MySQL server: 

    # /scripts/restartsrv_mysql
  7. Delete the tailwatchddisable file you created: 

    # rm /etc/tailwatchddisable 
  8. Restart the tailwatchd server:

    # /scripts/restartsrv_tailwatchd 

Can users be assigned privileges to a certain number of MySQL databases and a different number of PostgreSQL databases?

No. However, packages allow you to set the maximum number of total databases that a user can create regardless of the type.

For example, if you set the Max Databases option in WHM to 10, users can have :

  • Ten MySQL databases
  • Ten PostgreSQL databases
  • Two MySQL and eight PostgreSQL databases
    • This can be any combination that adds to ten.

How can I backup a MySQL database?

Run any of the following commands:

 

/path/to/bin/mysqldump -u root -p my_database > my_database_backup.sql
  • This will prompt you for the MySQL root user's password before you backup my_database to my_database_backup.sql

 

/path/to/bin/mysqldump my_database > my_database_backup.sql
  • This will use the password configuration in ~/.my.cnf to connect before you backup my_database to my_database_backup.sql

 

mysqldump -u $user -p database > backup_file
  • This will prompt you for the MySQL root user's password before you backup my_database to my_database_backup.sql

    Note

    Replace $user with your MySQL username.

How do I import data into MySQL?

You need to import data from the command line into MySQL. The type and format of the data that you want to import will determine how you import it. Listed below are the MySQL manuals to help you determine the best method:

Why do I get this MySQL error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) 0?

To avoid this issue, verify that mysqld is functional. If it is, verify that mysqld is pointed to the the correct UNIX socket.

For example:

# netstat -ax |grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     362783486 /var/lib/mysql/mysql.sock

Note:

If you have one socket and an application is pointed to the other socket, make a symbolic link (ln -s) to point it to the correct socket.

Why does the MySQL version display a different version in phpinfo than what is installed on the server?

The API version you see in phpinfo is the built-in MySQL API that is included in PHP.

If the buildapache application used the libraries and headers of MySQL that are installed on the server itself, Apache would not be able to work if the MySQL version was changed. Since MySQL RPMs are updated in cPanel whenever Red Hat releases updates, this could automatically break thousands of servers in a matter of a few hours, so Apache and PHP will always be built with the -builtin option.

Why does Postgres give this error: Password authentication failed for user?

To resolve this issue, make sure the password is in /root/.pgpass

  • The format is *:*:*:postgres:PASSWORD

If the password is not in /root/.pgpass, then modify the /var/lib/pgsql/data/pg_hba.conf file. To do this, run the following commands:

  1. touch /etc/tailwatchddisable
  2. killall tailwatchd
  3. Edit the /var/lib/pgsql/data/pg_hba.conf file, and change md5 to trust
  4. /etc/init.d/postgresql restart
  5. postgres=# alter user postgres with encrypted password =new_pass
  6. postgres=# \q
  7. Edit the /var/lib/pgsql/data/pg_hba.conf file, and change trust back to md5
  8. rm /etc/tailwatchddisable
  9. /scripts/restartsrv_tailwatchd

Note:

Previously, passwords were stored in /var/lib/pgsql/.pgpass. If /root/.pgpass does not exist, the .pgpass file will be copied into /root/.pgpass.

How does a remote MySQL host impact MySQL services?

If you run a remote MySQL host, you will experience little to no impact.

  • A remote MySQL setup functions the same as a local setup. Since you will configure all your customer connections to occur over TCP, the only difference will be their connection string.
  • On the backend, the only applications that connect with domain sockets are Horde, SquirrelMail, and phpMyAdmin. All these connections are built dynamically from system variables determined by your configuration.
  • All service status information should be displayed normally.

How do I remove support for InnoDB?

If you only use MyISAM-formatted databases in MySQL, you can disable InnoDB to reduce the amount of memory that MySQL uses.

To remove InnoDB support, you must make changes to MySQL and RoundCube. As root, follow these steps:

  1. For every cPanel account, run the /scripts/convert_roundcube_mysql2sqlite account script. (Replace account with the account name.)
  2. Open the MySQL configuration file in your preferred text editor.
    • By default, this file is located at /etc/my.cnf
  3. Add the following to the [mysqld] section:
    • skip-innodb
    • innodb=OFF
    • default_storage_engine=MyISAM
  4. Save the my.cnf file.
  5. Restart MySQL.

Notes:

  • If you have any other applications that require the use of InnoDB, you will need to configure those applications to use MyISAM, or disable them entirely.
  • You need to convert any databases that currently use the InnoDB engine to use the MyISAM engine.

To confirm that InnoDB is disabled:

  1. Click the PhpMyAdmin feature in WHM (Home >> SQL Services >> phpMyAdmin).
  2. Select the Engines tab.
  3. InnoDB will report its status on the server.

I cannot access my PostgreSQL databases

If you cannot access your PostgreSQL databases, reset your cPanel account's password with the Allow MySQL password change option checked. To reset your account's password, follow these steps:

  1. Navigate to cPanel's Change Password interface (Home >> Preferences >> Change Password).
  2. Select Allow MySQL password change.
  3. Change the password and click Change your password now!

Warning:

Some versions of PostgreSQL are ANSI SQL-92 compliant and do not support recursive grants, wildcard grants, or future grants. To allow multiple Users to access your PostgreSQL Tables, click Synchronize Grants in the PostgreSQL Databases interface (Home >> Databases >> PostgreSQL Databases) after you add a Table.

Does the maximum number of database setting represent the maximum number of databases total for an account or the maximum number of each available type of database?

The maximum number of databases setting represents the number of databases that an account can create of each available type of database. Therefore, if a system administrator sets this value to 5 and allows MySQL and PostgreSQL databases, the account may create up to 5 MySQL databases and up to 5 PostgreSQL databases.