Before we start with the MySQL we need to understand what does database mean?

Database is nothing but the collection of data which will be used for any website. Let’s take an example over here, suppose a computer having some files saved name as rrdns.doc, server.doc, host.doc so on. While finding a particular file name which includes business proposal data which has been made some time ago say for RRDNS Hosting So which file should you open? One way is to sequentially check each and every single file starting from "rrdns.doc" till you gets the right data but this will bring us a highly inefficient method to getting a right file. There are multiple database software’s that can help to locate the correct file without to manually open every single file sequentially like MySQL. MySQL is database software which is just program to lets you store and retrieve that data as efficiently as possible. MySQL is easy to use, powerful, secure and scalable. Because of its small size and speed, it is the ideal database solution for Web sites. It has a solid data security layer which protects data from intruders. MySQL is Specialised database software and Open Source Database Management System that mean it is possible to use and modify the software. MySQL databases are commonly used by programs that run on websites.it is what which holds all of website database tables, Queries. It’s used with web applications and closely to the popularity of PHP RRDNS servers has been thoroughly tested to prevent memory leaks which having MySQL databases. RRDNS Clients can be access their MySQL database remotely from RRDNS server through standard TCP/IP sockets using graphical application such as HeidiSQL. All web hosting companies generally support associated with Unix/Linux base server, If you want your own page and want MYSQL/PHP support check out http://www.rrdnshosting.com/. If we want to build a website with bunch of information that the visitors cannot quickly find OR Visitors want to impart information to you, both of these problems can be solved by building a database for the website. With the help of MySQL databases we can arrange our website information in a systematic format. Database software’s have many methods for storing, retrieve and organising the stored data. How to check and select database from command line? To login database server.

# mysql -h localhost -p -u databaseusername
# mysql> SHOW databases; To check the available databases.
# mysql> SELECT database(); To get currunt database name.

How to Create Database from command line?

mysql> create database databasename;
mysql> grant usage on *.* to databaseusername@localhost identified by 'Databasepassword';
mysql> grant all privileges on databasename.* to databaseuser@localhost ;
mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on pauldb.* to databaseusername@localhost;
mysql> set password for databaseuser = password('mysecretpassword');

How To Enable Query Cache?

Before enable query cache it is enabled or not using following command.

mysql> show variables like 'have_query_cache';

If the result is 0 like in the following example, then the query cache is not enabled: To Install Query Cache On the Server:

mysql> SET GLOBAL query_cache_size = 16777216;

To verify if the changes have been done or not. mysql> SHOW VARIABLES LIKE 'query_cache_size'; To Enable Query cache via /etc/my.cnf

Vi /etc/my.cnf

Insert following Values into the file

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

To resolve mysqldump: Got error: 1045: Access denied for user Error. 1. Check that whether you have created the proper MySQL user and gave them the required permission. You can check the user permission by following command.

# SHOW GRANTS FOR 'user'@'localhost';

OR

#SHOW GRANTS FOR 'user'@'192.168.1.10';

2. If the permissions are not proper, you might think of providing it properly by

# GRANT ALL ON database.* TO 'user'@'localhost' identified by 'password';

3. Check whether you are including the host properly in case of remote MySQL connections.

# mysql -h host -u user -p database

3. Finally, if you have created user and didn’t restarted your MySQL server or ran following command, your MySQL user permissions will not take effect in-spite of creating them. (Ps. This was the issue I was having.)

#FLUSH PRIVILEGES

Removing blocked IP addresses manually from cPHulk's database.

# mysql> use cphulkd;
# mysql> SELECT * FROM brutes WHERE `IP`=’xxx.xxx.xxx.xxx’;
# mysql> DELETE FROM brutes WHERE `IP`=’xxx.xxx.xxx.xxx’;
# mysql>quit

How to enable MySQL query logs To enable the query log put following entry into the /etc/my.cnf file under [mysqld] section. log = path to query.log general_log=1

Posted in Information | No Comments »