This vm node stores your data in RDBMS such as mysql or postgresql. In this setup, I’m going to use MySQL database server. You need to type the following commandss on vm04 having an IP address 192.168.1.13 only.
Install the mysql server
Type the following yum command to install mysql database server on RHEL /Centos Linux based systems:
# yum install mysql mysql-server
Configure the mysql server
Edit /etc/my.cnf, enter:
# vi /etc/my.cnf
Make sure mysql server can be accessed from your vm01 and vm02 server i.e. Apache+php5 server. Locate [mysqld] section and add/correct as follows so that mysqld can reached remotely :
# Make sure skip-networking directive is commented (or removed) # skip-networking # Turn on remote access bind-address=192.168.1.13 |
Optimization settings
You need to optimize mysql server otherwise it is going to eat all your CPU and other resources on vm04. You can add or correct settings as follows (see mysql manual for more info):
####################################################################### # WARNING!!! # Security and optimization settings # Read mysqld and my.cnf man page for more info # as the following settings depends upon hardware and your requirements ######################################################################## # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 ## Go faster and skip some stuff, YMMV skip-name-resolve skip-slave-start skip-external-locking # PER CLIENT SETTINGS # # bit high but I got tons of ram here # sort_buffer_size = 2M read_buffer_size = 2M binlog_cache_size = 1M wait_timeout = 200 interactive_timeout = 300 max_allowed_packet = 12M thread_stack = 128K table_cache = 1024 myisam_sort_buffer_size = 1M tmp_table_size = 12M max_heap_table_size = 12M # LOGGING # log_queries_not_using_indexes = 1 slow_query_log = 1 slow_query_log_file = /var/lib/mysql/slowquery.log # CACHES AND LIMITS # tmp_table_size = 12M max_heap_table_size = 12M query_cache_type = 1 query_cache_limit = 2M query_cache_size = 32M max_connections = 500 thread_cache_size = 50 open_files_limit = 65535 table_definition_cache = 4096 table_open_cache = 1024 # MyISAM # key_buffer_size = 32M myisam_recover = FORCE,BACKUP # SAFETY # max_allowed_packet = 16M max_connect_errors = 1000000 # BINARY LOGGING # log_bin = /var/lib/mysql/mysql-bin expire_logs_days = 14 sync_binlog = 1 # INNODB # innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 10G |
Save and close the file. Restart / reload the mysql server:
# chkconfig mysqld on
# service mysqld start
# service mysqld reload
# service mysqld restart
Verify that mysqld running on tcp port #3306:
# netstat -tulpn | grep :3306
MySQL database server firewall configuration
Edit /etc/sysconfig/iptables, enter:
# vi /etc/sysconfig/iptables
Make sure vm01 and vm02 can access the database server:
## open mysqld server port for the apache and lighttpd web server # -A INPUT -m state --state NEW -s 192.168.1.10 -m tcp -p tcp --dport 3306 -j ACCEPT -A INPUT -m state --state NEW -s 192.168.1.11 -m tcp -p tcp --dport 3306 -j ACCEPT |
Save and close the file. Restart iptables, enter:
# service iptables restart
Increase file system and ports limits on vm04 database server
For busy RDBMS server you need to increase system file descriptor (FD limits) and IP port limits:
# Increase system file descriptor limit to fs.file-max = 50000 # Increase system IP port limits net.ipv4.ip_local_port_range = 2000 65000 |
Load the changes by typing the following sysctl command to modify Linux kernel parameters at runtime:
# sysctl -p
Creating MySQL databases and accounts
This section provides basic instructions for manually creating a MySQL database. In this example, create a mysql database and user as follows:
- DB NAME : foo
- DB USER NAME : bar
- DB PASSWORD : mypassword
- ALLOW DB ACCESS FROM : localhost, vm01, and vm02 having an IP address 192.168.1.10 and 192.168.1.11 only
Type the following command to create the database and required users:
# /usr/bin/mysql -u root -h localhost -p
Type the following commands at mysql> prompt. To create your database called foo, type:
mysql> CREATE DATABASE foo;
You must grant access rights for this database to the MySQL user called bar through which the Apache+php5 application server will be connecting. Type:
mysql> GRANT ALL ON foo.* TO bar@localhost IDENTIFIED BY 'mypassword'; mysql> GRANT ALL ON foo.* TO bar@192.168.1.10 IDENTIFIED BY 'mypassword'; mysql> GRANT ALL ON foo.* TO bar@192.168.1.11 IDENTIFIED BY 'mypassword';
To exit from the MySQL monitor, type:
mysql> quit
You can now create tables or load data using .sql files. You can automate this procedure by writing shell or perl script to add the mysql user and database.
Test your new db and user settings from remote vm01 or vm02
Ssh into the vm01 or vm03 and type the following command to test the connectivity from the Apache / Lighttpd web server:
$ mysql -u bar -h 192.168.1.13 -p'mypassword' foo
OR
$ mysql -u bar -h vm04 -p'mypassword' foo
phpMyAdmin
The phpMyAdmin program act as the web-interface for MySQL. It is used to handle the administration of MySQL over the WWW using a browser. PhpMyAdmin can administer a whole MySQL server or a single database. This is recommended package for all new MySQL database users and admins.
Hi,
thanks…
Please let me know the installation process for mysql on Centos 6
thanks
Tank you sirs. This helped lods..
When I modify my my.cnf bind-address with my IP address after restarting mysql service getting error message
Shutting down MySQL
.. *
Starting MySQL
….. * The server quit without updating PID file (/var/lib/mysql/v-testmate4.pid).
And on accessing mysql -u root -p ‘password’
It gives error : ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
This will work fine but it will depend on your my.cnf
rpm -qa |grep mysql-server
rpm -qa mysql-server
yum install mysql-server
Loaded plugins: product-id, refresh-packagekit, subscription manager
Updating Red Hat repositories.
Setting up Install Process
No package mysql-server” available.
Error: Nothing to do
…..
…
my question is, how can i install mysql-server on my OS through YUM?
Thank you.
Thank you sir.The steps are very easy to foll0w