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
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
- Make sure root pass is in
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
root pass. To do so:
- Stop the
- Usually /etc/rc.d/init.d/mysql stop
chkservd to keep it from interfering with
- /etc/rc.d/init.d/chkservd stop
mysqld without the grant tables
- mysqld --skip-grant-tables -u mysql &
- 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
There is no space after the -p . If the password was "pass" you would
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:
- 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:
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.
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.
Previously, passwords were stored in
does not exist, the .pgpass file will be copied into
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:
- To configure RoundCube to use SQLite, run the following script at the command line as the
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.
- Once the conversion is complete, you can drop the mySQL RoundCube database.
- For every cPanel account, run the following script (replace account with the account name):
- 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
- Add the following to the
- Save the
- 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.