For cPanel & WHM version 72
Skip to end of metadata
Go to start of metadata

 

When I try to load a large SQL dump, why do I receive the following error: 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 script.

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

How can I configure a MySQL database for remote connection?

  1. Determine the IP address to use to connect to the database.
  2. Add this IP address to the Host text box in cPanel's Remote MySQL interface (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 can I create a new SQL database?

You can add a database in cPanel's Databases section (cPanel >> Home >> Databases).

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:

Only perform one of the following methods. 

In the WHM interface:

  1. Navigate to WHM's  MySQL Root Password interface (WHM >> Home >> 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.

From the command line:

  1. Stop the tailwatchd daemon and temporarily disable it:

    touch /etc/tailwatchddisable
    killall tailwatchd
  2. Stop the mysqld daemon: 

    /scripts/restartsrv_mysql --stop
  3. Start the mysqld daemon:

    mysqld_safe --skip-grant-tables
  4. Change the MySQL root 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 all of the server's current MySQL processes: 

    killall mysqld
  6. Restart your MySQL server: 

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

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

    /scripts/restartsrv_tailwatchd 

Can I assign users 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 for each database type.

For example, if you set the Max SQL Databases option to 5, users can create the following databases:

  • Up to five MySQL databases.
  • Up to five PostgreSQL databases.

You can use the following interfaces to set or modify this value:

  • The Max SQL Databases text box in WHM's  Create a New Account  interface (WHM >> Home >> Account Functions >> Create a New Account).
  • The SQL Databases text box in WHM's  Modify an Account  interface (WHM >> Home >> Account Functions >> Modify an Account).
  • The Max Databases text box in WHM's  Add a Package  interface (WHM >> Home >> Packages >> Add a Package).
  • The Max Databases text box in WHM's  Edit a Package  interface (WHM >> Home >> Packages >> Edit a Package).

What does the Max Databases setting represent?

The Max Databases setting (Max SQL Databases or SQL Databases in some interfaces) represents the number of databases that an account can create for each available type of database.

If a system administrator sets this value to 5 and allows both MySQL and PostgreSQL databases, the account may create up to five MySQL databases and up to five PostgreSQL databases. In this scenario, the cPanel interface will display 10 as the Max Databases value.

You can use the following interfaces to set or modify this value:

  • The Max SQL Databases text box in WHM's  Create a New Account  interface (WHM >> Home >> Account Functions >> Create a New Account).
  • The SQL Databases text box in WHM's  Modify an Account  interface (WHM >> Home >> Account Functions >> Modify an Account).
  • The Max Databases text box in WHM's  Add a Package  interface (WHM >> Home >> Packages >> Add a Package).
  • The Max Databases text box in WHM's  Edit a Package  interface (WHM >> Home >> Packages >> Edit a Package).

How can I back up a MySQL database?

Run any of the following commands:

/path/to/bin/mysqldump -u root -p my_database > my_database_backup.sql
  • This command prompts you for the MySQL root user's password before you back up the my_database database to the my_database_backup.sql file.

 

/path/to/bin/mysqldump my_database > my_database_backup.sql
  • This command uses the password configuration in the ~/.my.cnf file to connect before you back up the my_database database to the my_database_backup.sql file.

 

mysqldump -u $user -p database > backup_file
  • This command prompts you for the MySQL root user's password before you back up the my_database database to the my_database_backup.sql file.

    Note

    Replace $user with your MySQL username.

How can I import data into MySQL?

Use the command line to import data into MySQL. The type and format of the data that you want to import determines how you import it.

To determine the best method, use one of the following MySQL manuals:

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 the mysqld daemon is functional. If it is, verify that the mysqld daemon points to 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 points to the other socket, make a symbolic link to point it to the correct socket.

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

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

If the buildapache application used the MySQL libraries and headers on the server itself, and you change the MySQL version, Apache cannot function correctly. Because cPanel & WHM updates MySQL RPMs whenever Red Hat releases updates, this could automatically break thousands of servers in a matter of a few hours. For this reason, cPanel always builds Apache and PHP with the -builtin option.

Why does PostgreSQL display a Password authentication failed for user error?

To resolve this issue, ensure that the password is in the /root/.pgpass file. PostgreSQL passwords use the following format:  *:*:*:postgres:PASSWORD

If the password is not in the /root/.pgpass file, you must modify the /var/lib/pgsql/data/pg_hba.conf file.

To do this, perform the following steps:

  1. Run the touch /etc/tailwatchddisable command to disable the tailwatchd daemon.
  2. Run the killall tailwatchd command to stop all tailwatchd processes on the server.
  3. Edit the /var/lib/pgsql/data/pg_hba.conf file to change md5 to trust.
  4. Run the /scripts/restartsrv_postgresql restart command to restart PostgreSQL.
  5. Run the following commands:

    postgres=# alter user postgres with encrypted password =new_pass
    postgres=# \q
  6. Edit the /var/lib/pgsql/data/pg_hba.conf file and change trust to md5.
  7. Run the rm /etc/tailwatchddisable command.
  8. Run the /scripts/restartsrv_tailwatchd command to restart the tailwatchd daemon.

Note:

Previously, the system stored passwords in the /var/lib/pgsql/.pgpass file. If the /root/.pgpass file does not exist, the system copies the .pgpass file to the /root/.pgpass file.

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. You configure all of your customer connections to occur over TCP, but the connection string differs.
  • On the backend, the only applications that connect with domain sockets are Horde, SquirrelMail, and phpMyAdmin. The system uses your configuration's variables to build these connections dynamically.
  • All service status information displays normally.

How can I remove support for InnoDB?

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

To remove InnoDB support, you must make changes to MySQL and Roundcube. As the root user, perform the following steps:

  1. For every cPanel account, run the /scripts/convert_roundcube_mysql2sqlite username script, where username represents the cPanel account username.)
  2. Open the MySQL configuration file in your preferred text editor. By default, this file is the  /etc/my.cnf file.
  3. Add the following text to the [mysqld] section of the file:

    default-storage-engine=MyISAM
    skip-innodb
    default-tmp-storage-engine=MyISAM
  4. Save the my.cnf file.
  5. Restart MySQL.

Notes:

  • If any other applications require the use of InnoDB, configure those applications to use MyISAM, or disable them entirely.
  • Convert any databases that currently use the InnoDB engine to use the MyISAM engine.

To confirm that you disabled InnoDB, perform the following steps:

  1. Navigate to WHM's phpMyAdmin interface (WHM >> Home >> SQL Services >> phpMyAdmin).
  2. Select the Engines tab.
  3. InnoDB reports its status on the server.

I cannot access my PostgreSQL databases

If you cannot access your PostgreSQL databases, reset your cPanel account's password.

To reset your account's password, perform the following steps:

  1. Click Change Password in the user menu to navigate to cPanel's Password & Security interface (cPanel >> Home >> Preferences >> Password & Security).
  2. Enter the appropriate passwords in the text boxes.
  3. Select the Synchronize MySQL password checkbox.
  4. 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 cPanel's PostgreSQL Databases interface (cPanel >> 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 five MySQL databases and up to five PostgreSQL databases.

Can I require database prefixing for some of my cPanel accounts and not for others?

You cannot require prefixing for some cPanel accounts and not for other accounts. The Require a username prefix on names of new databases and database users setting in the SQL section of WHM's Tweak Settings interface (WHM >> Home >> Server Configuration >> Tweak Settings) is a global setting. However, you can create individual MySQL databases that do not require prefixing. To do this, perform the following steps:

  1. Log in to the WHM interface as the root user.
  2. Navigate to the SQL section of WHM's Tweak Settings interface (WHM >> Home >> Server Configuration >> Tweak Settings).
  3. Set the Require a username prefix on names of new databases and database users setting's value to off.
  4. Navigate to cPanel's MySQL Databases interface (cPanel >> Home >> Databases >> MySQL Databases)
  5. Create the desired databases.
  6. To re-enable database prefixing, return to the SQL section of WHM's Tweak Settings interface (WHM >> Home >> Server Configuration >> Tweak Settings) and set the Require a username prefix on names of new databases and database users setting's value to on.

You can also use the WHM API 1 set_tweaksetting  function to toggle on and off this setting.


Additional documentation