Documentation Home> AllDocumentation/AllFAQEditAttach

SQL Databases FAQ

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

Most likely, you will need to restart mysqld with the -O max_allowed_packet=# option.

How do I configure a MySQL database for remote connection?

You will need to add the IP address from which you wish to connect to the databse in the Remote MySQL section of the cPanel interface.

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

Port 3306 can be used for remote MySQL connections. You will need to configure access in the Remote MySQL section of your cPanel interface.

How do I create a new database in SQL?

You will need to use the Databases section and add a database under PostgreSQL Databases or MySQL Databases. Just fill in the name of the database and click on Add. You can also use the MySQL Databases Wizard or PostgreSQL Databases Wizard.

Mysql gives error of access denied for root@localhost?

  • Make sure root pass is in /root/.my.cnf.
This is usually the first root pass the box was given once cpanel is installed. If it still does not work, you will need to reset the mysqld root pass. To do so:
  1. Stop the mysqld.
    • Usually /etc/rc.d/init.d/mysql stop
  2. Stop chkservd to keep it from interfering with mysqld
    • /etc/rc.d/init.d/chkservd stop
  3. Start mysqld without the grant tables
    • mysqld --skip-grant-tables -u mysql &
  4. Change the pass.
    • mysql -u root mysql UPDATE user SET Password=PASSWORD('new_password') WHERE user='root'; FLUSH PRIVILEGES;

Now you just killall -9 mysqld and start it up normally with it's safe_mysqld script.

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

You can set the maximum number of total databases that user can create with packages, as well as when creating accounts without packages.

If you set a limit for databases, this will allow that limit for each type. So right now, you can offer:

  • X MySQL, or
  • X PostgreSQL, or
  • X MySQL and X PostgreSQL.

In the future, you will be able to offer X PostgreSQL and Y MySQL.

How can I backup a mysql database?

Rune either of the following commands:

  • /path/to/bin/mysqldump -u $mysqlusername -p$mysqlpassword $mysqldatabasename > backup.sql
  • mysqldump -u user -p --opt database > backup_file


ALERT! Note: There is no space after the -p . If the password was "pass" you would
use -ppass

How do I import data into MySQL?

You will need to import data from the command line into MySQL. The way you will do this depends on the type and format of the data you wish to import. Here are the 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?

You should check that mysqld is running first of all. If it is, check to see that it's looking for the correct UNIX socket.
On FreeBSD it would look something like this: 

# netstat -f unix |grep mysql
x65b33c0 stream      0      0 e5477840        0        0        0 /tmp/mysql.sock

On Linux is would look something like:

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

If you have one and an application is looking for the other, make a symbolic link (ln -s) from the one you see to the one it wants.

The mysql version displays a different version in phpinfo that what is installed on the server.

The API version you see in phpinfo is the builtin mysql api included in php. If the buildapache application used the ibraries and headers of mysql installed on the server itself, apache would not be able to work if the mysql version was hanged. Since mysql rpms are updated in cpanel whenever redhat releases updates, this could automatically break thousands of servers in a matter of a few hours, hence apache and php will always be built with the -builtin option.

I see ERROR at line 2: Unknown command '\_'. when I list mysql databases. You are running mysql version 4.0.12 which has a bug that causes this error. Upgrading to a later version should resolve the problem.

Run the following:

  1. /scripts/upcp
  2. The second step depends on what operating system your server is running.
    • In Linux: /scripts/sysup
    • In FreeBSD: /scripts/fixmysqlbsd

Postgres gives an error of 'Password authentication failed for user.

Make sure the password is in /root/.pgpass

The format is as follows:

*:*:*:postgres:PASSWORD
 
Modify /var/lib/pgsql/data/pg_hba.conf
 
It should contain the following...
 
local   all         all                                              md5
host    all         all         127.0.0.1          255.255.255.255   md5
 
Change 'md5' to 'trust' to disable authentication then restart postgres.
 
`/etc/init.d/postgresql restart`
 
Now you should be able to connect to postgresql as user 'postgres' without a password to modify the password.
 
`psql -u template1`
 
Run the following sql command.
 
"alter user postgres with password 'NEW PASSWORD HERE';"

Finally change /var/lib/pgsql/data/pg_hba.conf back to its original format and restart postgresql one more time. Now you should be able to authenticate using user postgres and the password you specified.

tip 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 running a remote MySQL host impact MySQL services?

Using a remote MySQL setup, will be functionally the same a using a local setup. Since you are configuring all your customer connections to occur over tcp, the only difference will be their connection string. On the backend, the only applications that are connecting with domain sockets are horde, squirrel mail, and phpMyAdmin. All these connections are built dynamically from system variables determined by your configuration. All service status information should be displayed normally. In short, running a remote MySQL host will have little to no impact.

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.

If you do not have root access, have your system administrator perform the following:

  1. To configure RoundCube to use SQLite, run the following script at the command line as the root user:
    /scripts/convert_roundcube_mysql2sqlite
    ALERT! Warning: Because the MySQL database contains all data for every email account that uses RoundCube, this part of the conversion can be a lengthy process.
    1. Once the conversion is complete, you can drop the mySQL RoundCube database.
    2. For every cPanel account, run the following script (replace account with the account name):
      /scripts/convert_roundcube_mysql2sqlite account
      .
  2. At the command line, as the root user, 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.

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.

Also, you will need to convert any databases that currently use the InnoDB engine to use the MyISAM engine.

To confirm that InnoDB is disabled, nagivate to the following interface within WHM: PhpMyAdmin >> More >> Engines >> InnoDB. InnoDB will report itself as disabled on the server.

Edit | Attach | Print version | History: r18 < r17 < r16 < r15 < r14 | Backlinks | Raw View | More topic actions
Topic revision: r18 - 15 Apr 2013 - 16:31:05 - Main.StacyWyatt