How Do I Enable Remote Access To MySQL Database Server?

last updated in Categories Linux, MySQL, UNIX

By default remote access to the MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server. This post will explain how to setup a user account and access a mysql server remotely on a Linux or Unix-like systems.

Enable the query cache in MySQL to improve performance

last updated in Categories Howto, Linux, MySQL, RedHat/Fedora Linux, Suse Linux, Sys admin, Tips, Ubuntu Linux, UNIX, Windows, Windows server

If you want to get optimized and speedy response from your MySQL server then you need to add following two configurations directive to your MySQL server:

query_cache_size=SIZE
The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache.

query_cache_type=OPTION
Set the query cache type. Possible options are as follows:
0 : Don’t cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE

Howto enable caching in MySQL

You can setup caching as follows:
$ mysql -u root –p
Output:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16 to server version: 4.1.15-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Now setup cache size 16Mb:

mysql> SET GLOBAL query_cache_size = 16777216;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache_size'; 

Output:

 +------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file:
# vi /etc/my.cnf
Append config directives as follows:

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.

Linux: Iptables Allow MYSQL server incoming request on port 3306

last updated in Categories Howto, Iptables, Linux, MySQL, Security, Tips

MySQL database is a popular for web applications and acts as the database component of the LAMP, MAMP, and WAMP platforms. Its popularity as a web application is closely tied to the popularity of PHP, which is often combined with MySQL. MySQL is open source database server and by default it listen on TCP port 3306. In this tutorial you will learn how to open TCP port # 3306 using iptables command line tool on Linux operating system.

MySql backup script

last updated in Categories Backup, MySQL

This mysql database shell (bash download link at the end of script) script can be used:

1) To backup databases file /backup dir and later pick up by your script

2) You can skip few databases from backup!

Download script from above url.

Open file backup.bash.txt and customize it:
MyUSER=”YOUR-MYSQL-USER” # mysql username
MyPASS=”YOUR-MYSQL-USER-PASSWORD” # mysql password
MyHOST=”localhost” # mysql Hostname

Optional setup the name of database which you do not wish to backup:
IGGY="test db2 db3"

Rename backup.bash.txt file to mysqlbackup.bash and execute it as follows
$ ./mysqlbackup.bash

You may need to setup a cron job to take backup everyday.