Mysql Connection my.cnf config file and explanations

……>
query_cache_size:

MySQL 4 provides one feature that can prove very handy – a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
……>
key_buffer_size:
The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.
……>
table_cache:
The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
……>
sort_buffer:
The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
……>
read_rnd_buffer_size:
The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
……>
thread_cache:
If you have a busy server that’s getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.
……>
tmp_table_size:
“Created_tmp_disk_tables” are the number of implicit temporary tables on disk created while executing statements and “created_tmp_tables” are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.
Additional reference material:

Minumum mysql memory using

 

Setting Up a MySQL User

Setting Up a MySQL User

Our application requires to access the MySQL database. For security reasons, we do not want this user to be the root MySQL user, so we define a user named www-data that has read access to the local databases and can only connect locally with some password.

  • First we need to connect to the MySQL monitor using the default password we specified earlier:

  • Then we can create our user and exit the MySQL monitor:

MySQL: Connect From an Other System / Computer

You need to allow access to 192.168.1.5 from apache server located at 192.168.1.8.

Step #1: Configure MySQL Server For Remote Access

Modify or append as follows:

Step #2: Linux Firewall Configuration For TCP Port # 3306
# >>>> You need to open TCP port # 3306 at the firewall level, enter:

Sample outputs:

How Do I Enable Remote Access To MySQL Database Server?

How Do I Enable Remote Access To MySQL Database Server?

You need type the following commands which will allow remote connections.
Step # 1: Login Using SSH (if server is outside your data center)

First, login over ssh to remote MySQL database server:

Once connected you need to edit the MySQL server configuration file my.cnf using a text editor such as vi.

Step # 3: Once file opened, locate line that read as follows

Where,

bind-address : IP address to bind to.
skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.
Step# 4 Save and Close the file

Step # 5 Grant access to remote IP address

 

Step # 6: Logout of MySQL

Type exit command to logout mysql:
mysql> exit

Step # 7: Open port 3306

 

Step # 8: Test it