Remote MySQL with SOCAT socket

How to make a remote MySQL server with socket by using SOCAT.
Remote MySQL advantages
- move MySQL CPU usage from local CWP web server
- move MySQL disk IO operations from disk
- move MySQL disk space usage to other server
- improve performances by moving MySQL to SSD multi-core powered server
...

on the remote MYSQL server
This needs to be a clean server, best would be a clean CentOS 6 minimal without any control panel.

Install MySQL server and Socat

yum install mysql-server socat -y

Run Socat listening for MySQL on the port 3307

socat TCP-LISTEN:3307,reuseaddr,fork UNIX-CONNECT:/var/lib/mysql/mysql.sock &

on the CWP Web server
1. Backup all databases
2. Import databases to the remote MySQL server
3. Update MySQL root password from MySQL server and add it in following files:
- /root/.my.cnf
- /usr/local/cwpsrv/htdocs/resources/admin/include/db_conn.php
Update postfix
- /usr/local/cwpsrv/htdocs/resources/admin/include/postfix.php
** Other option would be that you change MySQL root password on the MySQL server.
4. Stop local MySQL server
5. Add IP from CWP server to MySQL and from MySQL to CWP in firewall allow list.

Please note that also in the bellow listed files you need to replace $db_host and $db_host_postfix from localhost to your remote mysql server ip
/usr/local/cwpsrv/htdocs/resources/admin/include/db_conn.php
/usr/local/cwpsrv/htdocs/resources/admin/include/postfix.php

Now lets create remote MySQL socket connection with socat (replace SERVER-IP)

socat UNIX-LISTEN:/var/lib/mysql/mysql.sock,fork,reuseaddr,unlink-early,user=mysql,group=mysql,mode=777 TCP:SERVER-IP:3307 &

If you have successful login in the cwp and local MySQL server is stopped than this means that you are connected on the remote MySQL server.
Once you have a working remote mysql server then you need to create remote mysql users root/postifx

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'postfix'@'%' IDENTIFIED BY 'old-password';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'old-password';
MariaDB [(none)]> flush privileges;

Disable or remove local MySQL server from web hosting server.

For assistance you can contact our support
http://centos-webpanel.com/contact

Tagged: