Connect to MySQL

mysql -u aamnah -p —h db.mysite.com -P 3306 db_name  

OR

mysql --user=username --password=password --host=host --port=3306 db_name
options values
-u or --user username
-p or --password password
-h or --host host (default: localhost)
-P or --port Port (default: 3306)

Replace db_name with the name of the database you want to connect to.

CRUD Databases

Create MySQL database
mysql > CREATE DATABASE foo ;

Users and Privileges

Creating MySQL User
mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password' ;
  • Syntax for account names is ‘userName’@’hostName’.

  • An account name consisting only of a user name is equivalent to ‘userName’@’%’. For example, ‘me’ is equivalent to ‘me’@’%’.

Change user password
SET PASSWORD FOR
'jeffrey'@'localhost' = PASSWORD('mypass');

If you are changing your own password and not of another user, you can omit the FOR clause

SET PASSWORD = PASSWORD('mypass');
Allowing User to Connect
# grant usage on server so the user can connect  
mysql> GRANT USAGE ON *.* TO 'username'@'host' ;
Granting Privileges
mysql> GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'host' ;
CHECK if you can connect to the created database with the user you crteated
mysql -uusername -p --host=db.mysite.com databasename 
Deleting MySQL User
mysql> DROP USER 'username'@'host' ;
List all users
SELECT User,Host FROM mysql.user;
View Users and Permissions
SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user
View User Permissions for individual Databases
SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db

Backups

backup all databases in one file (eventually add the option –add-locks):
mysqldump -u username -p -–all-databases > file.sql
backup all databases in one gzipped file:
mysqldump -u username -p -–all-databases | gzip > file.sql.gz
backup selected databases
mysqldump -u username -p -–databases db1 db2 db3 | gzip > dbs.sql.gz

Restores

restore all databases:
mysql -u username -p < file.sql 
restore compressed backup file
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
Import SQL database
mysql -u username -p password databasename < filename.sql
Sample Command for creating a backup and restoring at remote server in one command:
mysqldump --user=root --password=password --host=mysql.mydomain.com db_1 | mysql --host=db.mysite.com --user=username --password=password db_1

Troubleshooting

  • If not connecting, check if the port is open on the server you are connecting from. Default MySQL port is 3306. On KH server it couldn’t connect because the port was closed. Also, see if the allow remote login option is enabled/disabled.

  • If the connection is not getting through the error would be ‘could not connect’.

  • If login is incorrect the error would be ‘access denied’. mysql_connect(): Access denied for user.

  • The user you are connecting with needs to be created at RDS. It doesn’t matter if the server you are connecting from has it or not.

Opening Ports: Ports should be opened in the firewall configuration. The default port for MySQL is 3306.

This can be accessed in WHM » Plugins » ConfigServer Security & Firewall » Firewall configuration » Port settings.