Uncategorized

These command assume you are running a cPanel MySQL installation and logged into the server as a root user where you can do a mysql -u root without having to specify a password.
Import a database:

mysql -u root db_name < file.sql

Dump a Database (with special characters):

mysqldump -Q –add-drop-table db_name > file.sql

Dump a Database, 4.0 compatible:

mysqldump –compatible=mysql40 –add-drop-table –quote-name db_name > file.sql

Dump Multiple Databases:

mysqldump -Q –add-drop-table –databases db_name1 db_name2 > file.sql

Restore Multiple Databases (must be done as root):

mysql -u root < file.sql

Log into MySQL prompt as the user (or root):

mysql -u username -p

Show Databases: (will only show databases the user has access to. Root has all.)

show databases;

Drop a whole database:

drop database user_databasename;

Create a database: *only a root mysql user can use this command

create database user_newdb;

Select a database to work on:

use user_testdb;

Drop a specific table:

drop table table_name;

Show all MySQL Processes:

show full processlist;

When trying to log into the MySQL shell as root you get an access denied error. To fix:

Add this line to /etc/my.cnf and restart MySQL:

skip-grant-tables

Follow these steps to reset the MySQL root password:

mysql -u root

mysql> FLUSH PRIVILEGES;
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY ‘password’ WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit;

service mysql restart

Remove the line you added to my.cnf and restart MySQL again.

Now go into WHM and reset the MySQL root password again. This is is necessary in order to build an association with cPanel and phpmyadmin, as well as the root user on the server to MySQL (meaning, when logged in as root to the server, you don’t have to specify a mysql root password to log in).

If you get some variation of the error below:

error: ‘Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

You need to recreate the MySQL socket symlink in /tmp:

ln -s /var/lib/mysql/mysql.sock /tmp/

If you have magically disappearing mysql.sock file, it could point to a memory issue or disk space issue on the /tmp partition.

This is a common error when dealing with command line software that uses MySQL:

error while loading shared libraries: libmysqlclient.so.14: cannot open shared object file: No such file or directory

Usually this simple command will correct the problem:

cp /usr/lib/mysql/libmysqlclient.so.14 /usr/lib

To find out what packages are installed, you can run a list:

pear list-all

To update the PEAR repositories:

pear channel-update pear.php.net

To install a package, for instance, XML_RPC:

pear download XML_RPC
pear install XML_RPC

Note on installations: you don’t always have to download the module first, but, on dedicated server environments where /tmp is mounted noexec, you may need to download the module first and then move the archive into another directory and install it manually.

If you don’t know the specific name of a PEAR module you want to install, you can search for it:

pear search <name>

To show your PEAR configuration variables:

pear config-show

To change variables:

pear config-set variable_name <value>

Packaged in with cPanel 11 is a new Ruby installer that will seamlessly install and integrate Ruby in your system and cPanel.  To install, simple run this command:

/scripts/installruby

Once this is installed, you can test that it is functional by running:

ruby -v

If you’re more old-fashioned or are running a version of cPanel that does not have the installer, you can  find a manual installation tutorial here.