Linux: Setting up a MySQL database server

Let us turn to the second practical lesson of the series and talk about setting up a virtual environment of a dedicated server - let's prepare a node (VM) for storing and maintaining access to the MySQL / PostgreSQL database.



In this installation, we will use the MySQL database server. All settings for this tutorial will be done exclusively on vm04 with the IP address 192.168.1.13

MySQL server installation

Enter the following yum-manager command to install the MySQL database server on the RHEL / CentOS Linux system:

# yum install mysql mysql-server 

MySQL server setup

Edit the /etc/my.cnf file:

')

 # vi /etc/my.cnf 

Make sure that the MySQL server is accessible from the virtual machines vm01 and vm02 with Apache + php5 installed on it. Find the [mysqld] section and add / correct the following parameters to make the mysqld database service available remotely :

 # ,   skip-networking  ( ) # skip-networking #    bind-address=192.168.1.13 

Optimization of MySQL server settings

It is necessary to optimize the MySQL server, otherwise it will remove a significant part of the resources of the virtual machine vm04 . You can add or correct settings as follows (for details, see the MySQL manual ).

 ######################################################################## # !!! #     #   mysqld  my.cnf     # ..        #     ######################################################################## #       # symbolic-links symbolic-links=0 ##  ,    , YMMV skip-name-resolve skip-slave-start skip-external-locking #                 # #  ,       # 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 #  # log_queries_not_using_indexes  = 1 slow_query_log                 = 1 slow_query_log_file            = /var/lib/mysql/slowquery.log #     # 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 #  # max_allowed_packet = 16M max_connect_errors = 1000000 #   # 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 the changes and close the file. Reboot / restart the MySQL server:

 # chkconfig mysqld on # service mysqld start # service mysqld reload # service mysqld restart 

Make sure that mysqld is running on TCP port # 3306:

 # netstat -tulpn | grep :3306 

MySQL Firewall Configuration Database Server

Edit the configuration of the firewall / etc / sysconfig / iptables :

 # vi /etc/sysconfig/iptables 

Ensure that the virtual machines vm01 and vm02 can access the database server:

 ##   mysqld  web- Apache  Lighttpd # -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 the iptables service :

 # service iptables restart 

Increase disk quotas and port range of the database server

For loaded DBMS servers, you should increase the limit on the number of file descriptors ( FD limits ) and increase the number of available IP ports.

 #        fs.file-max = 50000 #     net.ipv4. ip_local_port_range = 2000 65000 

We activate the changes by entering the following sysctl command that changes the parameters of the Linux kernel based on the settings we specified:

 # sysctl -p 

Creating MySQL databases and user accounts

In this section, we present the basic parameters for manually creating the MySQL database. For the sake of our task, we will create a MySQL database and a user with the following parameters:

• DB name: foo
• DB USER NAME: bar
• DB PASSWORD: mypassword
• WHOM IS ACCESS TO THE DATABASE ALLOWED : localhost , vm01 , vm02 with the following IP addresses - 192.168.1.10 and 192.168.1.11

Enter the following command to create the database and required users:

 # /usr/bin/mysql -u root -h localhost -p 

To create a database named foo , enter the following command in the mysql terminal line:

 mysql > CREATE DATABASE foo; 

You must grant access rights to this database to a MySQL user named bar , under which the Apache + php5 application servers will connect to the database.

 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 the MySQL console, enter the following command:

 mysql > quit 

Now you can create tables or load data using a SQL file. You can automate this procedure by writing a shell or Perl script to add MySQL users and databases .

Check your brand new database and user settings with “remote” vm01 and vm02

Connect via ssh to the vm01 virtual host or vm03 host and enter the following command to ping the Apache / Lighttpd web server:

 $ mysql -u bar -h 192.168.1.13 -p'mypassword' foo 

or so:

 $ mysql -u bar -h vm04 -p'mypassword' foo 

PhpMyAdmin

PhpMyAdmin is a well-known web interface to the MySQL database. This server-side script is used to remotely administer MySQL using only one browser. PhpMyAdmin can administer both the entire MySQL server and one database. This package is recommended for all new MySQL database users and administrators.

• Prologue
• Step # 1: Setup / Installation: NFS File Server
• Step 2: Setup / Installation: MySQL Database Server
• Step 3: Configure / Install: Memcached Caching Server
• Step # 4: Setup / Installation: Apache + php5 web server application
• Step 5: Configure / Install: Lighttpd Web Server for Static Assets
• Step 6: Setup / Installation: Nginx reverse (reverse) proxy server

Links to the sequel at the end of the post will be posted during the week, as the translation progresses and if you wish to listen further, this translation.